Question:
I have some code that loads a script to a variable, and then I pass the variable to an SMO object. I get the following exception:
Exception calling “ExecuteWithResults” with “1” argument(s): “Execute
with results failed for Database ‘Russell_Test’. “
- $serverName is the server name.
- $databaseName is the database name.
- $createScript is the script that was read.
How can I fix this problem?
Below is the relevant portion of the code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# Load Smo and referenced assemblies. [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo'); [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Management.Sdk.Sfc'); [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO'); [void][System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended'); Try{ $server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName; $db = $server.Databases.Item($databaseName); $result = $db.ExecuteWithResults($createScript); $result | Out-File -Append -FilePath $outputFile; } Catch { [system.exception] $_.Exception | Out-File -Append -FilePath $outputFile } |
Answer:
Thanks for the help. Ultimately the SMO option would not work so I did this solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
# Deploy table update scripts $createScript = Get-Content $scriptFile.FullName | Out-String ################# Script execution to capture errors/messages/warnings ################## $createScriptList = [regex]::Split($createScript, '\bGO') $cn2 = new-object system.data.SqlClient.SQLConnection("Data Source=$serverName;Integrated Security=SSPI;Initial Catalog=$databaseName;Connection Timeout=600;Max Pool Size=10"); $cn2.Open(); foreach ($cSL in $createScriptList) { Try{ $cmd = new-object system.data.sqlclient.sqlcommand($cSL, $cn2); $cmd.ExecuteScalar() | Out-File -Append -FilePath $outputFile; } Catch { [system.exception] $_.Exception | Out-File -Append -FilePath $outputFile } } $cn2.Close(); ############################################################################################### |