Bulk Copy Data From PowerShell to SQL

Copy-DataTableToSQL
Standard

Being able to quickly submit data from PowerShell to SQL is something I often do. Usually I want the results inserted into a SQL table. Being able to do so in bulk without needing a for each insert loop is very helpful and much faster. The easiest way to do this is to use sqlclient.sqlbulkcopy. It requires that the input variable be a DataTable object so if it’s not already use the ConvertTo-DataTable function I wrote earlier. Once you have your data in the DataTable you can use it as the source in this Copy-DataTableToSQL function I wrote.

Continue reading

Get SQL Table to PowerShell Simply

Get-SqlToDataTable
Standard

For many reasons you may want to retrieve data from SQL to be used in PowerShell. Maybe it’s data from a service’s database or data that was stored in SQL from another script. I wrote this function to quickly get a SQL table into a PowerShell DataTable object. It’s provides parameters for setting the target server, database, table, and where statement. Timeout defaults to 60 seconds for the SQL client connection but can be overridden. Security defaults to SSPI, but can be set, and I didn’t add any credentials arguments as the intent is that the script is run as a user with the appropriate permissions to select data from the database. In the case of Microsoft SQL the dbreader role at a minimum is required assigned to your Windows credentials.

Continue reading

Function to Convert PowerShell Objects to DataTables

ConvertTo-DataTable
Standard

There’s many ways to store data in PowerShell but I find DataTables to be the most useful. The extra capabilities available are very flexible. I’ve made this function to quickly convert arrays, hashes, and other objects to DataTables quickly. I’ll often use this in combination with SQL connections to complete bulk copies of entire tables in one command, or even user that in combination with SQL merge statements to insert and update matched records. Continue reading

Retrieving Installed Software from all Servers in Forest Automatically

Standard

This PowerShell module I’ve developed to collect the software installed on all Windows servers within a forest. Requirements: PowerShell v2+, Remote Server Admin Tools (script uses AD cmdlets), & remote registry enable/permissions. Feel free to share this script but please provide credit if you do. Download the script here

DESCRIPTION:

It works by first getting a list of all Windows Servers in the AD forest then collecting registry entries from each of those servers and putting that into a table of data. It’s looking for the keys created by the Windows Installer/uninstaller (therefore this doesn’t list features/roles/standalone apps). It also can provide an error report which will tell you the severs that were successfully queried, failed to ping, or failed remote registry connection.

There are 2 functions in this module; Get-InstalledSoftware-Threaded & Get-InstalledSoftware.

By default these look for all Windows severs in the forest that the script is run in; you can limit the scope to a specific domain or to the name of severs matching your criteria. The threaded function allows parallel collection up to the thread limit (defaults to 1). So if you specify 50 threads and there 2000+ servers in the forest then 50 servers will be queued at the same time and as they finish new threads (powershell.exe instances) are generated until all severs are collected. When I tested it against 2000+ servers using 50 threads it ran in about 1.5Hrs with a server that had 2 cores and 8GB RAM. If you have a lot of server’s you want to get, use the threaded function. If you only have a few, use the regular function. Continue reading