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).
2009-03-16
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment