Error executing SQL script via SMO in PowerShell

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.

# 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
    }

Could you show the content of variable $createScript, please? — Oleksandr Fedorenko
– Oleksandr Fedorenko, Commented Oct 30, 2013 at 19:08
Here is the code in the database script read in: USE Russell_Test GO create table scripttest (a VARCHAR(1)) GO The actual code that reads this in is: $createScript = Get-Content $scriptFile.FullName | Out-String — Russ960
– Russ960, Commented Oct 30, 2013 at 19:14
Try this simple CREATE statement: create table scripttest (a VARCHAR(1)) — Oleksandr Fedorenko
– Oleksandr Fedorenko, Commented Oct 30, 2013 at 19:21
If you're using the Russsell_Test in $databaseName then skip that and the go. Just run the create table statement. Leave the final go off as well. If $results still comes back empty try "$result.Tables |Out-File ..." and see what you get. — Bruce
– Bruce, Commented Oct 30, 2013 at 19:23
SMO does not and never did handle GO. The GO command is not a SQL command it is a Management Studio (SSMS) command and is implemented by it and SQLCMD (it's command-line interface) only. If you want to handle GO, then you will have to implement the code (powershell or otherwise) to cut up the script into separate batches on every GO yourself. — RBarryYoung
– RBarryYoung, Commented Oct 30, 2013 at 20:14

Eris · Accepted Answer · 2013-10-30 21:44:03Z

5

Here's how to do it:

# If we cast $Content as a [String], all the newlines are lost
# If we don't cast it, it becomes an array and batching breaks
$Content = (Get-Content $SqlScript) -join [Environment]::NewLine

# When using GO, we must set it up as a StringCollection, not a List or Array
$Batch = New-Object -TypeName:Collections.Specialized.StringCollection
$Batch.AddRange($Content)
$result = $Database.ExecuteWithResults($Batch)

answered Oct 30, 2013 at 21:44

Eris

7,7281 gold badge33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Russ960 · Accepted Answer · 2013-11-05 21:24:25Z

2

Thanks for the help. Ultimately the SMO option would not work so I did this solution:

# 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();
###############################################################################################

answered Nov 5, 2013 at 21:24

Russ960

1,3592 gold badges22 silver badges36 bronze badges

1 Comment

Serg Vorontsov Over a year ago

Really excellent sample. May be suggest a little improvement: $createScriptList = [regex]::Split($createScript, '\bGO[^\S]') for string like with values 'GOAHead' or something like that.

Oleksandr Fedorenko · Accepted Answer · 2013-11-06 08:37:16Z

1

Try to use Invoke-SqlCmd cmdlet. This cmdlet allows you to run T-SQL code or commands supported be the SQLCMD utility.

Try{
    Invoke-SqlCmd `
    -Query $createScript `
    -ServerInstance $serverName `
    -Database $databaseName `
    -ErrorAction Stop `
    }
    Catch
    {
        [system.exception]
        $_.Exception | Out-File -Append -FilePath $outputFile
    }

edited Nov 6, 2013 at 8:37

answered Nov 1, 2013 at 13:06

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

2 Comments

Russ960 Over a year ago

I'm not sure it would capture all the errors based on my past research. That said using Invoke-SqlCmd is a no go as I've had issues loading the sqlps module in all the environments the script are started from.

Oleksandr Fedorenko Over a year ago

The -ErrorAction Stop ` parameter will help you to catch errors. The answer has been updated

Collectives™ on Stack Overflow

Error executing SQL script via SMO in PowerShell

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related