Run Transact SQL using SMO.

[NJC]

Hi,

I'm just wondering what is the best method of running a Transact SQL
statement in SMO? I've tried using ExecuteNonQuery with belongs to Namespace
Microsoft.SqlServer.Management.Smo.Database but I can't seem to get it
working.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
| out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.COMMON") | out-null

$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'Home'
$db = new-object ('Microsoft.sqlServer.Management.Smo.Database') ($server,
"MyDatabase")

$sr = new-object System.IO.StreamReader("C:\CreateTableTest.sql")
$script = sr.ReadToEnd

$db.ExecuteNonQuery($script)

I keep on getting an error saying "Exception calling ExecuteNonQuery with 1
argument". I'm a bit confused because the in the documentation it states that
one sql argument can be passed with this function call. I'm not sure if it is
a syntax error or I’m just trying to do something that isn't possible.

Is this best approach? If so can someone please advise me on where I’m going
wrong?

Thanks in Advance,

[RichS]

It doesn't seem able to pass in a script file in the way you want

ExecuteNonQuery() and ExecuteWithResults() are expecting TSQL statements

What is in your CreateTabletest.sql if you have multiple SAQL batches it
may be getting confused

--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk

[NJC]

Hey Rich, thanks for you reply.

CreateTableTest.sql is just a basic script to create a new table with 3
columns:

/****** Object: Table [dbo].[Test] Script Date: 09/26/2007 11:09:47
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test](
[TestID] [int] NOT NULL,
[Col1] [int] NOT NULL,
[Col2] [int] NOT NULL
) ON [PRIMARY]

Ultimately I’ll have a fairly large SQL script which is going to be used to
setup a database with the required entities, triggers etc…

Rich, what would you suggest to be the best method or practice for executing
a SQL script in SMO. I tried the ExecuteNonQuery approach because I found a
code snippet in VB.Net which appeared similar to the functionality I was
looking for:

Dim sr As StreamReader = New StreamReader("C:\script.sql")
Dim script As String = sr.ReadToEnd
Dim SMOServer As Server = New Server
Dim db As Database = SMOServer.Databases("northwind")
db.ExecuteNonQuery(script)

As always any advice would be greatly appreciated,

[RichS]

It looks like you need to use a StringCollection to hold the commands and
pass in some execution type info.

I'll test it tonight and get back to you

[NJC]

Great stuff! Cheers Rich

[urkec]

"RichS" wrote:

> It looks like you need to use a StringCollection to hold the commands and
> pass in some execution type info.
>

I think this line

$script = sr.ReadToEnd

should be like this:

$script = $sr.ReadToEnd ()

I am new around here so I hope I'm not talking nonsense.

--
urkec

[RichS]

OK had a go at this

This will work

$script = New-Object -Type System.Collections.Specialized.StringCollection
$script.Add("SET ANSI_NULLS On")
$script.Add("SET QUOTED_IDENTIFIER ON")
$script.Add("CREATE TABLE [dbo].[Test1]([TestID] [int] NOT NULL, [Col1]
[int] NOT NULL, [Col2] [int] NOT NULL) ON [PRIMARY]")
$null =
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$null =
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Server = new-object Microsoft.SqlServer.Management.Smo.Server("RSSQL1")
$db = $server.Databases["Test"]
$extype =
[Microsoft.SqlServer.Management.Common.ExecutionTypes]::ContinueOnError
$db.ExecuteNonQuery($script, $extype)

If you want it to fail on error change the Execution Type to Default

If you want to read the script from a file then do it this way

$sr = New-Object System.IO.StreamReader("C:\scripts\sqlscript.txt")
$script2 = $sr.ReadToEnd()
$null =
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$null =
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Server = new-object Microsoft.SqlServer.Management.Smo.Server("RSSQL1")
$db = $server.Databases["Test"]
$extype =
[Microsoft.SqlServer.Management.Common.ExecutionTypes]::ContinueOnError
$db.ExecuteNonQuery($script2, $extype)

as urkec said it needs the () on the ReadToEnd

Strangely it didn't work with get-content. Need to have a think about that

--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk

[RichS]

Its something to do with the way get-content reads the file contents. Always
soemthing new to dig into ;-)

--
Richard Siddaway
Please note that all scripts are supplied "as is" and with no warranty
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk

"NJC" wrote:

> Ah fantastic... works like a dream. Big thanks Rich and Urkec for your
> comments, as always very much appreciated.
>
> Rich, I initially tired to use cmdlet get-content because I have used this
> before for parsing an XML file but I couldn't get it working either.
>
> Cheers again,

[urkec]

"RichS" wrote:

> Its something to do with the way get-content reads the file contents. Always
> soemthing new to dig into ;-)

Sorry to barge in again, but I think I figured it out. When you use
Get-Content it returns an array, but $db.ExecuteNonQuery() with one argument
expects a string. Maybe it is possible to use "foreach" to concatenate all
strings in the array, inserting "`n" in between, and pass the resulting
string to ExecuteNonQuery(string) as the argument. (I'm afraid my knowledge
of PowerShell is insufficient to demonstrate this).

But even if it works it is probably easier to use ExecuteNonQuery overload
that accepts StringCollection as an argument, as you showed. Perhaps it would
be convenient to create the script from an existing (test) database using
Smo.Transfer.ScriptTransfer(), which returns a StringCollection, and to pass
it to Smo.Database.ExecuteNonQuery(StringCollection, ExecutionTypes)

--
urkec

[NJC]

Good stuff... i have more than enough info to keep me busy for a while.

Thanks again,