2009-03-16

Executing a T-SQL script file

I would like to execute a developers T-SQL script file automated, so that I can deploy without having to press the button every single time.
To do this I'm working on a PowerShell function, that uses both the Windows Shell (cmd.exe) end the SQL Server sqlcmd (SQLCMD.EXE) utility.

function Invoke-SqlCmd_cmd {
param(
 [string]$WindowsName = $( throw "Windows host name required as parameter." ),

 [string]$SqlInstanceName = $( throw "SQL Server database instance name required as parameter." ),

 [string]$DatabaseName = $( throw "Database name required as parameter." ),

 [string]$SqlScriptFileName = $( throw "T-SQL script file name required as parameter." ) )


 if( $SqlInstanceName -eq '.' )

 { $DbInstanceName = $WindowsName }

 else

 { $DbInstanceName = $WindowsName + '\' + $SqlInstanceName }


 & cmd.exe /c "SQLCMD.EXE -E -S `"$DbInstanceName`" -d `"$DatabaseName`" -i `"$SqlScriptFileName`" -u -b -m-1"

}


The function is used like this:
Invoke-SqlCmd_cmd 'Sandbox' '.' 'master' 'C:\SQLAdmin\SqlCmd.sql'

I use '.' (dot) to identify a default database instance in my repository, but any other identification can be motivated.

I haven't found a way round the Windows Shell, which I think is a indicator that PowerShell is still some way from complete. Maybe the PowerShell V2 CmdLet Invoke-Command does a better job...

The documentation is at MSDN Library on sqlcmd and at TechNet Library on Windows Shell (cmd.exe).

No comments: