TechTalkz.com Logo Ask the Experts!

Go Back   TechTalkz.com Technology & Computer Troubleshooting Forums > Tech Support Archives > Microsoft > Microsoft Windows Powershell

RE: Run Transact SQL using SMO.

Microsoft Windows Powershell

 
 
Thread Tools Display Modes
Unread 23-10-2007, 07:00 PM   #1
RichS
Guest
 
Posts: n/a
RE: Run Transact SQL using SMO.

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.Connect ionInfo")
$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.ExecutionTyp es]::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.Connect ionInfo")
$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.ExecutionTyp es]::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


"NJC" wrote:

> 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,
>

 
Unread 23-10-2007, 07:02 PM   #2
NJC
Guest
 
Posts: n/a
RE: Run Transact SQL using SMO.

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,


"RichS" wrote:

> 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.Connect ionInfo")
> $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.ExecutionTyp es]::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.Connect ionInfo")
> $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.ExecutionTyp es]::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
>
>
> "NJC" wrote:
>
> > 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,
> >

 
Unread 23-10-2007, 07:02 PM   #3
RichS
Guest
 
Posts: n/a
RE: Run Transact SQL using SMO.

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,
>
>
> "RichS" wrote:
>
> > 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.Connect ionInfo")
> > $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.ExecutionTyp es]::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.Connect ionInfo")
> > $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.ExecutionTyp es]::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
> >
> >
> > "NJC" wrote:
> >
> > > 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,
> > >

 
Unread 23-10-2007, 07:02 PM   #4
urkec
Guest
 
Posts: n/a
RE: Run Transact SQL using SMO.

"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
 
Unread 23-10-2007, 07:03 PM   #5
NJC
Guest
 
Posts: n/a
RE: Run Transact SQL using SMO.

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

Thanks again,

"urkec" wrote:

> "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

 
 

Thread Tools
Display Modes



< Home - Windows Help - MS Office Help - Hardware Support >


New To Site? Need Help?

All times are GMT. The time now is 05:05 PM.


vBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO
Copyright © 2005-2013, TechTalkz.com. All Rights Reserved - Privacy Policy
Valid XHTML 1.0 Transitional