title: Powershell script: Write object to SQL
date: 2015-02-01 14:14:00 +0200 +0200
draft: false
author: John Roos
----
A while ago I was playing around with PowerShell, gathering information from a lot of servers and I felt that I needed to have a simple way of storing that information in a database. I could have written my own T-SQL statements for creating the tables and then do the inserts in the powerShell scripts I was playing around with. But that felt like it would just take time so I started to think about a function that would just accept any object and do the magic of creating a table and then inserting anything I piped to it.
I wrote a script cmdlet for this and I called it Write-ObjectToSQL. I don’t like the name, but I haven’t come up with anything better for now.
This script cmdlet writes an object into a database table. If the table does not exist it will be created based on the properties of the object. For every property of the object a column will be created. The data type for each column will be converted from .Net data types into SQL Server data types. Not all data types are supported. Unsupported data types will be ignored (but can be listed). If several objects are sent through the pipeline only the first object will be used for creating the template for the table.
Piping objects to a database
I find this cmdlet to be very useful. Any time I write a small Powershell script to gather some kind of information I just pipe it to this function and then I have it instantly stored in a table in some database somewhere. Sure, the table doesnt have any indexes and the columns sizes are standardized, but thats ok most of the time. Take this code as an example:
Get-Process | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName ProcessTable
This will create a table called ProcessTable. That table will have around 36 columns with the correct (sometimes simplified) data types. It will also insert all the information about the running processes in that table. I had 85 processes running on my machine at this point. I am running Windows 10 Preview when writing this, so the amount of columns might be slightly different on other versions of Windows.
You could of course pipe any command or cmdlet to this function. As long as you are piping objects with interesting properties you are good to go. You could use Find-Hotfix that I wrote about recently and store that output to a table. In that case you just do it like this:
.\Find-Hotfix.ps1 -Hotfix 'KB2991963' -ComputerName 'server01', 'server02' |
Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName Hotfixes
Ignored properties
Sometimes when you are piping objects to Write-ObjectToSQL you will notice that some properties are ignored. Thats because that property contains a data type that is unsupported. You could use -verbose to see lots of details about whats happening when you run the script (including sql statements) but you could also use the parameter ShowIgnoredPropertiesOnly. That will show you which properties are being ignored and their data types.
Get-Process | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName ProcessTable -ShowIgnoredPropertiesOnly
Progress status
This script cmdlet contains lots of useful stuff. Other parameters include ReportEveryXObject which will give you some feedback in case you are piping many thousands of objects and the function runs for a long time. The following example is a little silly, but it shows how the function works at least.
Get-Process | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName ProcessTable -ReportEveryXObject 20
The script can be downloaded from Microsoft Script Center here. I also have a project running on GitHub. If you are using PowerShell 5.0 its available in the PowerShell Gallery as well.
Powershell script: Write object to SQL
A while ago I was playing around with PowerShell, gathering information from a lot of servers and I felt that I needed to have a simple way of storing that information in a database. I could have written my own T-SQL statements for creating the tables and then do the inserts in the powerShell scripts I was playing around with. But that felt like it would just take time so I started to think about a function that would just accept any object and do the magic of creating a table and then inserting anything I piped to it.
I wrote a script cmdlet for this and I called it Write-ObjectToSQL. I don’t like the name, but I haven’t come up with anything better for now.
This script cmdlet writes an object into a database table. If the table does not exist it will be created based on the properties of the object. For every property of the object a column will be created. The data type for each column will be converted from .Net data types into SQL Server data types. Not all data types are supported. Unsupported data types will be ignored (but can be listed). If several objects are sent through the pipeline only the first object will be used for creating the template for the table.
Piping objects to a database
I find this cmdlet to be very useful. Any time I write a small Powershell script to gather some kind of information I just pipe it to this function and then I have it instantly stored in a table in some database somewhere. Sure, the table doesnt have any indexes and the columns sizes are standardized, but thats ok most of the time. Take this code as an example:
Get-Process | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName ProcessTable
This will create a table called ProcessTable. That table will have around 36 columns with the correct (sometimes simplified) data types. It will also insert all the information about the running processes in that table. I had 85 processes running on my machine at this point. I am running Windows 10 Preview when writing this, so the amount of columns might be slightly different on other versions of Windows.
You could of course pipe any command or cmdlet to this function. As long as you are piping objects with interesting properties you are good to go. You could use Find-Hotfix that I wrote about recently and store that output to a table. In that case you just do it like this:
.\Find-Hotfix.ps1 -Hotfix 'KB2991963' -ComputerName 'server01', 'server02' |
Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName Hotfixes
Ignored properties
Sometimes when you are piping objects to Write-ObjectToSQL you will notice that some properties are ignored. Thats because that property contains a data type that is unsupported. You could use -verbose to see lots of details about whats happening when you run the script (including sql statements) but you could also use the parameter ShowIgnoredPropertiesOnly. That will show you which properties are being ignored and their data types.
Get-Process | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName ProcessTable -ShowIgnoredPropertiesOnly
Progress status
This script cmdlet contains lots of useful stuff. Other parameters include ReportEveryXObject which will give you some feedback in case you are piping many thousands of objects and the function runs for a long time. The following example is a little silly, but it shows how the function works at least.
Get-Process | Write-ObjectToSQL -Server localhost\sqlexpress -Database MyDB -TableName ProcessTable -ReportEveryXObject 20
The script can be downloaded from Microsoft Script Center here. I also have a project running on GitHub. If you are using PowerShell 5.0 its available in the PowerShell Gallery as well.
- Written by John Roos on February 1, 2015