May 28 2013

SMO vs Invoke-Sqlcmd in Azure

I’m working on a series of Powershell scripts for the pre-conference seminars on Windows Azure SQL Database that I’m going to be giving at TechEd and the PASS Summit. One of the things I want to do is script out a full backup process (one that’s consistent with transactions) which means I need to create a database copy. There’s a nice neat T-SQL command that does this:

CREATE DATABASE ADW_COPY
AS COPY OF nmhdhskcew.AdventureWorks2012;

So, being the lazy Powershell freak that I am, I figured I’d just pass it in with Invoke-sqlcmd as a single line like this:

Invoke-Sqlcmd -ServerInstance $server -Database $db -Username $user -Password $password -Query $sql

Which resulted in an unexpected error:

Invoke-Sqlcmd : The CREATE DATABASE statement must be the only statement in the batch.

Well, now I’m curious. I’m passing in a single command. It’s not part of any other commands or statements or batches. What if I did the same thing in SMO?

$cn = 'Server=tcp:server.database.windows.net;User ID=CloudAdmin;Password=@password;Trusted_Connection=False;'
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $cn

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $conn

$db = $srv.Databases["master"]

$db.ExecuteNonQuery($sql)

It worked flawlessly. Hmmm… Maybe there’s an extra command being passed in from Invoke-Sqlcmd? Not sure. But it’s interesting.

3 Comments

  • By Eli Weinstock-Herman, May 28, 2013 @ 11:47 am

    Try the powershell one again without the semicolon in the SQL statement. I found that including trailing semi-colons w/ Invoke-Sqlcmd was one of the consistent ways to get that error.

  • By Grant Fritchey, May 28, 2013 @ 12:37 pm

    You’re kidding me. Hang on…

    Ha!

    It works. So, in short, we shouldn’t be using the correct T-SQL terminator within PowerShell? That’s marvelous.

  • By John Miner, June 24, 2013 @ 2:43 pm

    I will have to double check my article on managing a windows Azure database.

    I found some commands like (if db exist, drop db) did not work.

    However, wrapping them in a Exec(”) worked fine. See below.

    The cloud, a new set of issues and opportunities!

    — Delete existing database
    IF EXISTS (SELECT name FROM sys.databases WHERE name = N’AUTOS’)
    EXEC (‘DROP DATABASE [AUTOS]’);
    GO

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment