Table Creation with SMO

In a recent post http://richardsiddaway.spaces.live.com/blog/cns!43CFA46A74CF3E96!727.entry  I showed how to use SMO to create a database table.  The drawback to the approach used in that post was that the columns had to be added separately rather than using the usual CREATE TABLE command.

A recent post in the newsgroups asked about using the ability of SMO to run TSQL queries to create a table.

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)

The script can be broken down into a number of areas.  Firstly we create our TSQL script.  We create a StringCollection and then add the various TSQL commands into it.  In this case we set a couple of configuration settings and then add the CREATE TABLE command.

The second block loads the usual SMO assemblies (put those lines in your profile if you are doing a lot of SMO scripting!) and connects to the appropriate SQL Server and database instances.

We then set how we want execution to proceed.  In this case to continue if there is an error.  If you want execution to halt on an error then use Default instead of ContinueOnError.

We can then use the ExecuteNonQuery() method with the script and the execution type as parameters.

If you have a TSQL script already in a file then the code above can be modified as follows

$sr = New-Object System.IO.StreamReader("C:scriptssqlscript.txt")
$script = $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($script, $extype)

where we read in the contents of the script to create the StringCollection.   I would have thought that get-content would work here but it didn’t in my initial testing.  Need to look at that in more detail.

It is possible to use SMO to return results from a query but that it is a post fro another day.

Technorati Tags: , ,
Advertisements
This entry was posted in PowerShell and SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s