Search results for 'app:weblogs' matching tags 'Chad Miller' and 'Windows PowerShell'

10 Tips for the SQL Server PowerShell Scripter

Anonymous — Mon, 06 May 2013 05:00:00 GMT

Summary: Microsoft PowerShell MVP, Chad Miller shares his top ten tips for the SQL Server Windows PowerShell scripter.

Microsoft Scripting Guy, Ed Wilson, is here. Today’s blog is a bit unique. I was throwing around ideas with Chad Miller, and somehow we came up with the idea that he would share some tips for the SQL Server Windows PowerShell scripter. You can read more about Chad and see his other blog posts on the Hey, Scripting Guy! Blog site.

And now, Chad…

1. Use here-strings

Here-strings are great for working with strings that span multiple lines or contain characters you would normally need to escape, such as embedded quotes. Like regular strings, when using here-strings, you still get variables replaced by their values.

$group = "PRD"

#Don't do this:

$query = "SELECT DISTINCT"

$query +="`n s.server_name"

$query += "`n FROM msdb.dbo.sysmanagement_shared_registered_servers s"

$query +="`n JOIN msdb.dbo.sysmanagement_shared_server_groups g ON s.server_group_id = g.server_group_id"

$query += "`n WHERE g.name = '$group'"

#Instead use a here-string:

$query = @"

SELECT DISTINCT

s.server_name

FROM msdb.dbo.sysmanagement_shared_registered_servers s

JOIN msdb.dbo.sysmanagement_shared_server_groups g ON s.server_group_id = g.server_group_id

WHERE g.name = '$group'

Not only is the here-string example more readable, but you can also copy and paste it right into SQL Server Management Studio. You test your queries before running them in Windows Powershell, right? for For more information about here-strings, see about_Quoting_Rules.

2. Leverage Central Management Server

Some administrators may store their list of servers in text files or Active Directory organizational units, but you have a SQL Server Central Management Server (CMS). Use your CMS to pull lists of servers and SQL Server instances for input into Windows PowerShell scripts.

$serverInstances = sqlcmd -S myCMServerInstance -d msdb -Q $query -h -1 -W

$serverInstances | foreach {new-object Microsoft.SqlServer.Management.Smo.Server($($_.server_name)) } |

Select Name, PhysicalMemory, @{n='MaxServerMemory'; e={$_.Configuration.MaxServerMemory.RunValue}}

#Get server names by removing instance name i.e. Z001\SQL1 becomes Z001

$servers = sqlcmd -S myCMServerInstance -d msdb -Q $query -h -1 -W | foreach { $_ -replace "\\.*|,.*" }

invoke-command -ComputerName $servers -ScriptBlock {get-psdrive -PSProvider FileSystem}

For more information, see Administer Multiple Servers Using Central Management Servers.

3. Load SMO assemblies

SQL Server Management Objects (SMO) is the API you'll use to script against SQL Server. To use SMO, you must first load the SMO assemblies. If you have SQL Server 2012, all you need to do to load assemblies is call Import-Module sqlps. This will load the SMO assemblies with the sqlps module. If you're not using 2012 or would prefer to load the assemblies without the sqlps module, you'll need to use Add-Type with the fully qualified name:

#For SQL Server 2008 R2 and SQL Server 2008

add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

#For SQL Server 2012

add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop

#See loaded SMO assemblies

[appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -like "*sqlserver*"}

Like SQL Server Management Studio, SMO is backwards compatible so you can script against 2000 and 2005 SQL Server using either the 2008 or 2012 version of SMO.

4. Exercise Invoke-Sqlcmd with caution

One of the cmdlets that is included with SQL Server 2012, SQL Server 2008 R2, and SQL Server 2008 is Invoke-Sqlcmd. As the name implies, the cmdlet tries to be a Windows PowerShell version of the venerable sqlcmd utility introduced in SQL Server 2005 and enhanced with each SQL Server release. The Invoke-Sqlcmd cmdlet has several bugs and workarounds that you should be aware of.

One issue is with the QueryTimeout setting. At the time of this writing, the documentation incorrectly states the following about QueryTimeout parameter:

help Invoke-Sqlcmd -Parameter QueryTimeout

QueryTimeout

Specifies the number of seconds before the queries time out. If a timeout value is not specified, the queries do not time out. The timeout must be an integer between 1 and 65535.

Required? false

Position? named

Default value

Accept pipeline input? false

Accept wildcard characters? false

This isn't true, which can be easily proven by running this simple test:

invoke-sqlcmd "waitfor delay '00:00:31'" -Database master -ServerInstance $env:computername

Invoke-Sqlcmd : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

So, you'll need to specify a query timeout. In SQL Server 2012, the bug was partially fixed and you can specify 0, which means no QueryTimeout:

invoke-sqlcmd "waitfor delay '00:00:31'" -Database master -ServerInstance $env:computername\sql1 -querytimeout 0

When you use the SQL Server 2008 R2 or SQL Server 2008 versions of Invoke-Sqlcmd, specifying 0 doesn't work. Instead, you'll need to provide a value greater than 1. Here's a trick that works:

invoke-sqlcmd "waitfor delay '00:00:31'" -Database master -ServerInstance $env:computername\sql1 -querytimeout ([int]::MaxValue)

One other issue that isn't a bug, but something to be aware of…

If you're querying data with columns larger than 4,000 characters for XML or char data types, or 1,024 bytes for binary data types, you'll want to override the default settings for MaxCharLength or MaxBinaryLength parameters:

#By default character data larger than 4,000 is truncated

invoke-sqlcmd "select REPLICATE('x', 4001)" -Database master -ServerInstance $env:computername\sql1 | foreach {($_.Column1).length}

4000

#This is fixed by specifying a value for the maxcharlength parameter

invoke-sqlcmd "select REPLICATE('x', 4001)" -Database master -ServerInstance $env:computername\sql1 -maxcharlength ([int]::MaxValue) | foreach {($_.Column1).length}

4001

#See help for more information on max length parameters

help invoke-sqlcmd -Parameter max*

-MaxCharLength

Specifies the maximum number of characters returned for columns with character or Unicode data types, such as

char, nchar, varchar, and nvarchar. The default is 4,000 characters.

Required? false

Position? named

Default value

Accept pipeline input? false

Accept wildcard characters? false

-MaxBinaryLength

Specifies the maximum number of bytes returned for columns with binary string data types, such as binary and

varbinary. The default is 1,024 bytes.

Required? false

Position? named

Default value

Accept pipeline input? false

Accept wildcard characters? false

One serious issue with Invoke-Sqlcmd is around error handling. As an example, the following command does not produce an error in Invoke-Sqlcmd at the time of this writing:

invoke-sqlcmd -ServerInstance $env:computername\sql1 -Database tempdb -Query "select 1/0" -OutputSqlErrors $true -AbortOnError -SeverityLevel 0 -ErrorLevel 0

Sqlcmd.exe and SQL Server Management Studio return an error:

sqlcmd.exe -S "%COMPUTERNAME%" -d tempdb -Q "select 1/0"

Msg 8134, Level 16, State 1, Server YourServer, Line 1 Divide by zero error encountered.

Unfortunately, there aren't any good workarounds for the error handling issue other than don't use Invoke-Sqlcmd if you need error handling. There are other issues with Invoke-Sqlcmd, which the command-line utility, sqlcmd.exe, doesn't have.

In any case it's trivial to write your own Invoke-Sqlcmd replacement. For more information, see Invoke-Sqlcmd2 in the Script Center Repository.

You may want to continue to use sqlcmd.exe for scripted data loading.

5. Don't forget SQL Server command-line utilities

One of tenets of Windows PowerShell is to not needlessly write scripts. What I mean by this is instead of writing a script to perform an action, first see if there's a cmdlet that accomplishes the task. Think of a cmdlet as a script that you don't have to write.

And if there isn't a cmdlet available? Here's where Windows PowerShell (being a shell) comes in handy. You can still use the native Windows console applications. You might even find that if there is Windows PowerShell cmdlet, the console application simply works better. At the top of my list of SQL Server command-line utilities that are still very useful: sqlcmd, dtutil, and dtexec, and Red Gate sqlcompare.

6. Read the SMO documentation

If you're going to write scripts that use SMO, you'll probably want to spend some time looking at the SMO documentation. Rather than hunt through the documentation, I find it easier to search the web for the SMO documentation on a particular class. Add the term "SMO class" to your web search, for example, "smo server class." The first item in your search results will usually be the MSDN documentation.

7. Don't use Windows PowerShell for everything

If you know T-SQL or SQL Server Reporting Services, the solutions you build can leverage the best tool for the job. What's really cool is using Windows PowerShell as only one part of the solution. One of my favorite patterns is to use Windows PowerShell to collect information, load the data into a SQL Server table, and then present the data as a web-based report in SQL Server Reporting Services.

Check out my previous post, Use PowerShell to Collect Server Data and Write to SQL for more information. Let's face it, sometimes using Windows PowerShell is more complex than what's needed. Do you need to interactively run query across multiple SQL Servers? It doesn't get much simpler than multiserver queries in SQL Server Management Studio.

8. Load data with Out-GridView

Although you could mess with scripts to export data directly into Excel or convert CSV files, a quick and dirty way to get data into Excel is to simply copy and paste from Out-GridView. This also works on small tables you open in SQL Server Management Studio Object Explorer: right-click Table and select Edit Top 200 Rows.

You'll then be able to paste Out-GridView output rows into a SQL Server table. This is especially useful when you need to run a Windows PowerShell command one-time and insert the data into Excel. Here's an example script called Get-SqlSpn, which grabs all the SPNs in a domain related to SQL Server. I'll then use Out-GridView with CTRL+A and CTRL+C to copy, and then CTRL+V to paste into Excel.

. ./get-sqlspn.ps1

$spns = Get-SqlSpn

#4. Get Column Headers

$object = $spns | select -first 1

$ht = @{}

foreach($property in $object.PsObject.get_properties()) {

$ht.add($property.Name.ToString(),$property.Name.ToString())

}

new-object psobject -Property $ht | out-gridview

#Copy/Paste heading row to Excel (Ctrl-A, Ctrl-C)

$spns | out-gridview

#Copy/Paste spns to Excel (Ctrl-A, Ctrl-C)

This image shows an example of where I copy from:

And here is my paste into Excel:

9. Adjust SMO StatementTimeout

The default statement time setting for SMO is 10 minutes. Of course, some operations (such as a backup or restore) can take longer than the default timeout of 600 seconds (10 minutes). Set the StatementTimeout setting to no timeout by specifying 0:

$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "Z001\SQL"

$server.ConnectionContext.StatementTimeout = 0

10. Handle Windows Powershell errors in SQL Server Agent jobs

By default the ErrorActionPreference is set to Continue, and this has implications on how errors bubble up to the SQL Server Job Server. If you run a Windows PowerShell command as a SQL Server Agent job and there are no syntax errors yet, the command produces an error (for example, attempting to get operating system information from an unavailable server). The SQL Server Agent job will report success. If you want an error condition to halt execution of a SQL Server Agent job or to produce an error, you'll need to add some error handling. You can set up a SQL Server Agent job with Windows PowerShell Job Step as follows:

get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'

The job will run successfully, but if you run it directly in Windows PowerShell, you'll see:

get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'

get-wmiobject : The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

At line:1 char:1

+ get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'

To bubble up Windows PowerShell errors to SQL Server Agent, you'll need to do one of the following:

A. Set your $ErrorActionPreference = "Stop"

$erroractionpreference = "Stop"

get-wmiobject Win32_OperatingSystem -ComputerName 'nothere'

B. Set ErrorAction at the cmdlet-level (more granular)

get-wmiobject Win32_OperatingSystem -ComputerName 'nothere' -ErrorAction 'Stop'

C. Use Try/Catch with ErrorActionPreference or ErrorAction

try {

get-wmiobject Win32_OperatingSystem -ComputerName 'nothere' -ErrorAction 'Stop'

}

catch {

throw "Something went wrong"

#or rethrow error

#throw $_

#or throw an error no message

#throw

}

D. Continue, and fail the SQL Server Agent job

Let's say you have a collection of computers and you want to continue on the error, but you also want to fail the job. In this case you can make use of the ErrorVariable:

#Note the -ErrorVariable parameter takes a variable name without the $ prefix.

get-wmiobject Win32_OperatingSystem -ComputerName 'localhost','nothere','Win7boot' -ErrorVariable myError

if ($myError)

{ throw ("$myError") }

~Chad

Thank you, Chad, for some extremely practical information. Well done! Join me tomorrow for the exciting conclusion to our SQL Server Week as we have a guest blog written by SQL Server MVP, Kendal Van ***.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Expert Commentary: 2012 Scripting Games Advanced Event 4

Anonymous — Thu, 19 Apr 2012 05:00:00 GMT

Summary: Microsoft MVP, Chad Miller, provides expert commentary for 2012 Scripting Games Advanced Event 4.

Microsoft Scripting Guy, Ed Wilson, is here. Chad Miller is the expert commentator for Advanced Event 4.

Chad is a senior manager of database administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999, and he has been automating administration tasks by using Windows PowerShell since 2007. Chad is a Windows PowerShell MVP and the project coordinator and developer of the Windows PowerShell-based CodePlex project, SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa PowerShell User Group, and he is a frequent speaker at users groups, SQL Saturdays, and Code Camps.

Blog: Sev17—SQL Server, PowerShell, and so on
Twitter: @cmille19

The function Get-FolderSize reports the folder size of the specified folder and all subfolders. A path (folder) must be provided to the Get-FolderSize function.

To display the folder size for the current directory, use:

Get-FolderSize

To display folder size information for a list of folders, use:

Get-FolderSize Desktop,Downloads,DropBox

Get-Content folders.txt | Get-FolderSize

The code for this script is shown here:

#######################

.SYNOPSIS

Gets folder size.

.DESCRIPTION

The Get-FolderSize function gets the folder size at the specified location.

.EXAMPLE

Get-FolderSize .

This command gets the folder size of the current directory. The dot (.) represents the item at the current location.

.EXAMPLE

Get-FolderSize C:\Windows

This command the folder size of the Windows directory.

.INPUTS

System.String

You can pipe a string that contains a path to Get-FolderSize.

.OUTPUTS

Selected.System.Management.Automation.PSCustomObject

.NOTES

Version History

v1.0 - Chad Miller - Initial release

function Get-FolderSize

{

[CmdletBinding()]

param(

[Parameter(Mandatory=$true,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]

[ValidateNotNullorEmpty()]

[string[]]$Path

)

BEGIN {

function Format-FolderSize

{

param($Size)

if ($Size -ge 1GB) {

"{0:N2} Gigabytes" -f $($Size/1GB)

}

elseif ($Size -ge 1MB) {

"{0:N2} Megabytes" -f $($Size/1MB)

}

else {

"{0:N2} Bytes" -f $Size

}

} #Format-FolderSize

}

PROCESS {

Get-Item $Path | Foreach-Object {$_; $_ | Get-Childitem -Recurse} |

Where-Object {$_.PSIsContainer} |

Select-Object FullName, @{name='Size';expression={$(Get-ChildItem $_.FullName -Recurse | Measure-Object -Sum -Property Length).Sum}} |

Sort-Object Size -Descending |

Select-Object @{name="Folder";expression={$_.FullName}}, @{name='Size of Folder';expression={$(Format-FolderSize -Size $_.Size)}}

}

END {}

} #Get-FolderSize

The main section of script is shown here:

The approach I took to solve this problem follows the structures of the PROCESS block of the script. I would start with step one and proceed to the next steps only when the results from the previous steps have been achieved.

Use the Get-Item cmdlet to retrieve information about the specified path. The ForEach-Object cmdlet returns the item, and Get-ChildItem is called recursively to retrieve all child items.
Apply a filter using Where-Object, which tests whether the PSIsContainer property is true, which means the item is of type directory.
After we have the specified directory and all subdirectories, use select-object to retrieve the FullName property, and add a Size property by using a hash table with name and expression keys. The expression uses the Measure-Object cmdlet, summing on the Length property.
Now sort the object by the newly added Size property.
Finally, use the Select-Object cmdlet to emit a custom object with Folder and Size of Folder properties. The Size of Folder property makes use of the function Format-FolderSize, which is defined in the BEGIN block of Get-FolderSize. The Format-FolderSize handles formatting the folder size in the appropriate unit of measurement (gigabytes, megabytes, or bytes). By defining the Format-Folder function in the BEGIN block, the function is private and not visible outside of the Get-FolderSize function.

~Chad

The 2012 Scripting Games Guest Commentator Week will continue tomorrow when we will present the scenario for Event 5.

Ed Wilson, Microsoft Scripting Guy

Four Easy Ways to Import CSV Files to SQL Server with PowerShell

Anonymous — Mon, 28 Nov 2011 06:00:00 GMT

Summary: Learn four easy ways to use Windows PowerShell to import CSV files into SQL Server.

Microsoft Scripting Guy, Ed Wilson, is here. I was chatting this week with Microsoft PowerShell MVP, Chad Miller, about the series of blogs I recently wrote about using CSV files. He thought a helpful addition to the posts would be to talk about importing CSV files into a SQL Server. I most heartily agreed. Welcome to Guest Blogger Week. We will start off the week with a bang-up article by Chad Miller. Chad has previously written guest blogs for the Hey, Scripting Guy! Blog. Here is a little information about Chad:

Chad Miller is a SQL Server database admin and the senior manager of database administration at Raymond James Financial. In his spare time, he is the project coordinator and developer of the CodePlex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Windows PowerShell User Group, and he is a frequent speaker at SQL Saturdays and Code Camps.

Contact information:
Blog: Sev17
Twitter: cmille19

Importing CSV files into SQL Server

Windows PowerShell has built in support for creating CSV files by using the Export-CSV cmdlet. However, the creation of a CSV file is usually only a short stop in an overall process that includes loading the file into another system. In this post, we'll look at a few scripted-based approaches to import CSV data into SQL Server. Note: SQL Server includes a component specifically for data migration called SQL Server Integration Services (SSIS), which is beyond the scope of this article.

T-SQL BULK INSERT command

The T-SQL BULK INSERT command is of the easiest ways to import CSV files into SQL Server. The BULK INSERT command requires a few arguments to describe the layout of the CSV file and the location of file. Let's look at an example of creating a CSV file by using Export-CSV, and then importing the information into a SQL Server table by using BULK INSERT.

Requirements

Sysadmin or insert and bulkadmin to SQL Server
Local access to SQL Server

Setup

1. Download the following script: Invoke-SqlCmd2.ps1

2. Create a table disk space by copying the following code in SQL Server Management Studio.

Note: The example uses a database named "hsg."

CREATE TABLE dbo.diskspace(

UsageDate datetime,

SystemName varchar(50),

Label varchar(50),

VolumeName varchar(50),

Size decimal(6,2),

Free decimal(6,2),

PrecentFree decimal(5,2)

)

The following image shows the command in SQL Server Management Studio.

3. Save the following script as Get-DiskSpaceUsage.ps1, which will be used as the demonstration script later in this post.

param($ComputerName=".")

Get-WmiObject -computername "$computername" Win32_Volume -filter "DriveType=3" | foreach {

new-object PSObject -property @{

UsageDate = $((Get-Date).ToString("yyyy-MM-dd"))

SystemName = $_.SystemName

Label = $_.Label

VolumeName = $_.Name

Size = $([math]::round(($_.Capacity/1GB),2))

Free = $([math]::round(($_.FreeSpace/1GB),2))

PercentFree = $([math]::round((([float]$_.FreeSpace/[float]$_.Capacity) * 100),2))

}

} | Select UsageDate, SystemName, Label, VolumeName, Size, Free, PercentFree

Now we will use the script Get-DiskSpaceUsage.ps1 that I presented earlier. It lists information about disk space, and it stores the information in a CSV file.

./get-diskusage.ps1 | export-csv -Path "C:\Users\Public\diskspace.csv" -NoTypeInformation

The generated CSV file shows that Export-CSV includes a text delimiter of double quotes around each field:

"UsageDate","SystemName","Label","VolumeName","Size","Free","PercentFree"

"2011-11-20","WIN7BOOT","RUNCORE SSD","D:\","59.62","31.56","52.93"

"2011-11-20","WIN7BOOT","DATA","E:\","297.99","34.88","11.7"

"2011-11-20","WIN7BOOT",,"C:\","48","6.32","13.17"

"2011-11-20","WIN7BOOT","HP_TOOLS","F:\","0.1","0.09","96.55"

Although many programs handle CSV files with text delimiters (including SSIS, Excel, and Access), BULK INSERT does not. To use BULK INSERT without a lot of work, we'll need to remove the double quotes. We can use a quick and dirty way of simply replacing all the quotes in the CSV file. In the blog post Remove Unwanted Quotation Marks from CSV Files by Using PowerShell, the Scripting Guys explains how to remove double quotes. This method can be used for circumstances where you know it won't cause problems. How do you know? Well, the data being generated from our Get-DiskspaceUsage should never have double quotes or commas in the data. So here's the code to remove the double quotes:

(Get-Content C:\Users\Public\diskspace.csv) | foreach {$_ -replace '"'} | Set-Content C:\Users\Public\diskspace.csv

UsageDate,SystemName,Label,VolumeName,Size,Free,PercentFree

2011-11-20,WIN7BOOT,RUNCORE SSD,D:\,59.62,31.56,52.93

2011-11-20,WIN7BOOT,DATA,E:\,297.99,34.88,11.7

2011-11-20,WIN7BOOT,,C:\,48,6.32,13.17

2011-11-20,WIN7BOOT,HP_TOOLS,F:\,0.1,0.09,96.55

Now we are ready to import the CSV file as follows:

. .\Invoke-SqlCmd2.ps1

$query = @"

BULK INSERT hsg.dbo.diskspace FROM 'C:\Users\Public\diskspace.csv'

WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Invoke-SqlCmd2 -ServerInstance "$env:computername\sql1" -Database hsg -Query $query

The following data shows that our CSV file was successfully imported.

UsageDate	System Name	Label	Volume Name	Size	Free	Percent Free
11/20/2011 12:00:00 AM	WIN7BOOT	RUNCORE SSD	D:\	59.62	31.56	52.93
11/20/2011 12:00:00 AM	WIN7BOOT	DATA	E:\	297.99	34.88	11.70
11/20/2011 12:00:00 AM	WIN7BOOT		C:\	48.00	6.32	13.17
11/20/2011 12:00:00 AM	WIN7BOOT	HP_TOOLS	F:\	0.10	0.09	96.55

BULK INSERT works reasonably well, and it is very simple. However, there are some drawbacks, including:

You need elevated permissions on SQL Server.
BULK INSERT doesn't easily understand text delimiters.
Using the UNC path to files requires an additional setup, as documented under Permissions on the BULK INSERT site.

For these reasons, let's look at some alternate approaches.

Before there was Windows PowerShell, there was LogParser

LogParser is a command-line tool and scripting component that was originally released by Microsoft in the IIS 6.0 Resource Kit. LogParser provides query access to different text-based files and output capability to various data sources including SQL Server. Even though this little tool hasn't been updated since 2005, it has some nice features for loading CSV files into SQL Server.

Setup

Download and install LogParser 2.2.

LogParser can do a few things that we couldn't easily do by using BULK INSERT, including:

Automatically create a table based on the CSV layout
Handle the text delimiter of double quotes

Note: CSV files do not need to be local.

Using LogParser

You can use the LogParser command-line tool or a COM-based scripting interface. Let's look at examples of both.

LogParser command-line tool

LogParser requires some special handling, which is why we use Start-Process. Some switches and arguments are difficult to work with when running directly in Windows PowerShell. Also Windows Powershell_ISE will not display output from LogParser that are run via the command-line tool. Here is the syntax for running a command to generate and load a CSV file:

./get-diskspaceusage.ps1 | export-csv -Path "C:\Users\Public\diskspace.csv" -NoTypeInformation -Force

#Uncomment/comment set-alias for x86 vs. x64 system

#set-alias logparser "C:\Program Files\Log Parser 2.2\LogParser.exe"

set-alias logparser "C:\Program Files (x86)\Log Parser 2.2\LogParser.exe"

start-process -NoNewWindow -FilePath logparser -ArgumentList @"

"SELECT * INTO diskspaceLP FROM C:\Users\Public\diskspace.csv" -i:CSV -o:SQL -server:"Win7boot\sql1" -database:hsg -driver:"SQL Server" -createTable:ON

Looking at SQL Server, we see that our newly created table contains the CSV file:

Filename	Row Number	Usage Date	System Name	Label	Volume Name	Size	Free	Percent Free
C:\Users\Public\diskspace.csv	2	2011-11-20	WIN7BOOT	RUNCORE SSD	D:\	59.62	31.56	52.93
C:\Users\Public\diskspace.csv	3	2011-11-20	WIN7BOOT	DATA	E:\	297.99	34.88	11.7
C:\Users\Public\diskspace.csv	4	2011-11-20	WIN7BOOT		C:\	48	6.32	13.16
C:\Users\Public\diskspace.csv	5	2011-11-20	WIN7BOOT	HP_TOOLS	F:\	0.1	0.09	96.55

The CreateTable switch will create the table if it does not exist; and if it does exist, it will simply append the rows to the existing table. Also notice that we got two new columns: Filename and Row Number, which could come in handy if we are loading a lot of CSV files. You can eliminate the Filename and Row Number columns by specifying the column list in the Select statement as we'll see in a moment.

LogParser COM scripting

Using the COM-based approach to LogParser is an alternative method to using the command line. Although the COM-based approach is a little more verbose, you don't have to worry about wrapping the execution in the Start-Process cmdlet. The COM-based approach also handles the issue with Windows Powershell ISE. Here is code to work with the COM object:

$logQuery = new-object -ComObject "MSUtil.LogQuery"

$inputFormat = new-object -comobject "MSUtil.LogQuery.CSVInputFormat"

$outputFormat = new-object -comobject "MSUtil.LogQuery.SQLOutputFormat"

$outputFormat.server = "Win7boot\sql1"

$outputFormat.database = "hsg"

$outputFormat.driver = "SQL Server"

$outputFormat.createTable = $true

$query = "SELECT UsageDate, SystemName, Label, VolumeName, Size, Free, PercentFree INTO diskspaceLPCOM FROM C:\Users\Public\diskspace.csv"

$null = $logQuery.ExecuteBatch($query,$inputFormat,$outputFormat)

The main drawback to using LogParser is that it requires, well…installing LogParser. For this reason, let's look at one more approach.

Use Windows PowerShell to collect server data and write to SQL Server

In my previous Hey, Scripting Guy! post, Use PowerShell to Collect Server Data and Write to SQL, I demonstrated some utility functions for loading any Windows PowerShell data into SQL Server. Let's revisit this solution using the CSV file example:

Setup

Download the following scripts:

Run the following code to create a CSV file, convert to a data table, create a table in SQL Server, and load the data:

. .\out-datatable.ps1

. .\Add-SqlTable.ps1

. .\write-datatable.ps1

. .\Invoke-SqlCmd2.ps1

$dt = .\Get-DiskSpaceUsage.ps1 | Out-DataTable

Add-SqlTable -ServerInstance "Win7boot\Sql1" -Database "hsg" -TableName diskspaceFunc -DataTable $dt

Write-DataTable -ServerInstance "Win7boot\Sql1" -Database "hsg" -TableName "diskspaceFunc" -Data $dt

invoke-sqlcmd2 -ServerInstance "Win7boot\Sql1" -Database "hsg" -Query "SELECT * FROM diskspaceFunc" | Out-GridView

The following image shows the resulting table in Grid view.

The observant reader will notice that I didn't write the information to a CSV file. Instead, I created an in-memory data table that is stored in my $dt variable. This is because by using this approach, there was not a need to create a CSV file, but for completeness let's apply the solution to our CSV loading use case:

. .\out-datatable.ps1

. .\Add-SqlTable.ps1

. .\write-datatable.ps1

. .\Invoke-SqlCmd2.ps1

./get-diskspaceusage.ps1 | export-csv -Path "C:\Users\Public\diskspace.csv" -NoTypeInformation -Force

$dt = Import-Csv -Path "C:\Users\Public\diskspace.csv" | Out-DataTable

Add-SqlTable -ServerInstance "Win7boot\Sql1" -Database "hsg" -TableName diskspaceFunc -DataTable $dt

Write-DataTable -ServerInstance "Win7boot\Sql1" -Database "hsg" -TableName "diskspaceFunc" -Data $dt

This post demonstrated three approaches to loading CSV files into tables in SQL Server by using a scripted approach. The approaches range from using the very simple T-SQL BULK INSERT command, to using LogParser, to using a Windows PowerShell function-based approach.

Thank you, Chad, for sharing this information with us. It looks like your last four scripts have the makings of an awesome NetAdminCSV module.

Ed Wilson, Microsoft Scripting Guy