August 2009 - Posts

SQLBits V Registration is now open
25 August 09 09:00 AM | MartinBell | with no comments

Today we opened registrations for SQLBits V

image

We have some great artwork by Andrew Fryer and when you are on the website you can click on each of the panels to get more information about the given day.

Don’t forget until 30 September we have an early bird discount, I hope to see you there!

Filed under:
Powershell and SMO scripting – Part 3
22 August 09 05:04 PM | MartinBell | 3 comment(s)

Madhivanan posted a blog entry on how to script table definitions and data the other day. For SQL 2008 this gave a link that showed you how to use the scripting wizard to generate this script.

If you have ever posted a problem on the newsgroups or forums when you can't get a SELECT statement to return the data you want; without posting DDL and sample data your first reply may well have been one asking you to post these and may have also pointed you to Aaron Bertrands web site on how to do it. Taking time to do this, along with showing that you have already put some effort into answering the problem is appreciated by the people (like myself) who answer these posts. It removes the need for them to generate their own tables and test data and reduces the chance of something being mis-interpretted. Posting INSERT statements is far more useful than posting a table of data, which the replier would have to convert into something that could be used. Posting DDL and INSERT statements will not only increase the chance of the post being answered, but it is more likely to be answered correctly (assuming that the question is also correct!).

If you checked out my previous Powershell scripting articles or have used SMO to script table previously you will recognise the similarity between the option available in the Choose Script Options dialog and the properties of the SMO ScriptingOptions object, although looking at the latest version of Books Online the option to script data is not mentioned.

The ScriptingOptions class has a ScriptData property, which if set to true will return the data as insert statements. Unfortunately my previous script which calls the scripting option with a single argument i.e.

$Scripter.Script($Item)

does not support scripting data and you will get the error:

Exception calling "Script" with "1" argument(s): "This method does not support
scripting data."
At line:12 char:17
+ $Scripter.Script( <<<< $Item)


if you try to set the ScriptData property to $True for the scriptingoptions. The solution is not to use the Script method, but instead use the EnumScript method. So the resulting script is:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$True
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.ScriptData=$False;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.EnumScript($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.ScriptData=$True;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.EnumScript($Item)
}

This will produce output like this:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AWBuildVersion_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[AWBuildVersion] DROP CONSTRAINT [DF_AWBuildVersion_ModifiedDate]
END

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AWBuildVersion]') AND type in (N'U'))
DROP TABLE [dbo].[AWBuildVersion]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AWBuildVersion](
    [SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Database Version] [nvarchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
    [VersionDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_AWBuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED
(
    [SystemInformationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[AWBuildVersion] ON

GO
INSERT [dbo].[AWBuildVersion] ([SystemInformationID], [Database Version], [VersionDate], [ModifiedDate]) VALUES (1, N'10.00.80404.00', CAST(0x00009A7400000000 AS DateTime), CAST(0x00009A7400000000 AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[AWBuildVersion] OFF
GO
ALTER TABLE [dbo].[AWBuildVersion] ADD  CONSTRAINT [DF_AWBuildVersion_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

Filed under: , ,
New Version of SQL Server 2008 Books Online - July 2009
22 August 09 04:31 PM | MartinBell | with no comments

On Thursday a new version of Books Online for SQL Server 2008 was released see here

 

Filed under:
SQLBits Competition
20 August 09 06:39 AM | MartinBell | 1 comment(s)

At SQLBits Goes Forth in Manchester we gave out as part of the goody bag a mug.  What we want you to as part of this competition is send us a photo of you and your mug in the most unusual of places. 

Examples might be

  1. You and your mug on the peak of K2
  2. You and your mug enjoying a Schnapps with Angela Merkel
  3. You and your mug on the International Space Station

You can upload your pictures to http://www.sqlbits.com/competition.aspx and the competition will close on 21.09.2009 and be drawn on 23.09.2009.

If you do not have a mug then fear not, go up to the competition area of the site http://www.sqlbits.com/competition.aspx and you can print off a DIY version.  Glue this to a real mug and take a picture, just like Blue Peter.

Even if you don’t win then we would still love to see you at the event.  If you book early for the 2 paid days then you can take advantage of some Early Bird Discounts.   Remember the Saturday is free so failing everything else we hope to see you there.

Terms and Conditions

This prize is only valid for SQLBits 5 "SQL Bits Goes West", 19th-21st November 2009 At Celtic Manor, Newport

There is no monetary equivalent of the prize.  If you can't make the dates then don't enter

The competition is open to everyone except, committee members, their families and tennis partners.

Things not included in the prize

                Travel to and from the venue

                Drinks/Meals - outside of those provided during the days themselves

                Extra Room charges (minibar etc)

The winner is chosen by the SQLBits Organising Committee and the result is considered final

The name of the competition winner will be used on materials promoting the event.

Filed under:
Powershell and SMO scripting – Part 2
15 August 09 06:00 PM | MartinBell | 7 comment(s)

In my last post I created a script that will generate files for each view that contain a drop and create statement for the view. If you wished to script all tables in the database then all you would have to do is change the location you have set:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

I have also changed the way the filename is created by pre-pending the schema name to the filename.

So what if I wanted to only generate scripts for a given schema?

To do that modify the foreach statement so that the returned object is piped through a Where-Object to filter those objects with the schema property set to “Sales”:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem | Where {$_.Schema -eq "Sales" } ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

If you examine the script created e.g.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sales].[SalesPerson]') AND type in (N'U'))
DROP TABLE [Sales].[SalesPerson]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Sales].[SalesPerson](
    [SalesPersonID] [int] NOT NULL,
    [TerritoryID] [int] NULL,
    [SalesQuota] [money] NULL,
    [Bonus] [money] NOT NULL,
    [CommissionPct] [smallmoney] NOT NULL,
    [SalesYTD] [money] NOT NULL,
    [SalesLastYear] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

You may notice that there are no primary keys, foreign keys and other constraint are created in the scripts. This is because the DRIAll property is set to false. Setting this to true will give you a script with all the DRI for the table:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AWBuildVersion_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[AWBuildVersion] DROP CONSTRAINT [DF_AWBuildVersion_ModifiedDate]
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AWBuildVersion]') AND type in (N'U'))
DROP TABLE [dbo].[AWBuildVersion]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AWBuildVersion](
    [SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Database Version] [nvarchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
    [VersionDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_AWBuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED
(
    [SystemInformationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AWBuildVersion] ADD  CONSTRAINT [DF_AWBuildVersion_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

If you are putting these files into source code control having primary keys, foreign keys and constraints may not be what you want. This does present a small problem of how to script indexes, foreign keys etc. separately as they are located below each table and not the database. This can be seen by changing the location to be the database e.g.

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks

and do a Get-Childitem or dir and you can see that the following are listed:

ApplicationRoles
Assemblies
AsymmetricKeys
Certificates
DatabaseAuditSpecifications
Defaults
ExtendedProperties
ExtendedStoredProcedures
FileGroups
FullTextCatalogs
FullTextStopLists
LogFiles
PartitionFunctions
PartitionSchemes
PlanGuides
Roles
Rules
Schemas
ServiceBroker
StoredProcedures
SymmetricKeys
Synonyms
Tables
Triggers
UserDefinedAggregates
UserDefinedDataTypes
UserDefinedFunctions
UserDefinedTableTypes
UserDefinedTypes
Users
Views
XmlSchemaCollections

This does not include indexes or any table constraints. You need to change location to a given table e.g.

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables \person.contact

where Get-Childitem will return:

Checks
Columns
ExtendedProperties
ForeignKeys
FullTextIndex
Indexes
PartitionSchemeParameters
PhysicalPartitions
Statistics
Triggers

So we could get the indexes for a given table by using the previous script by setting the location to the indexes:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables\Person.Contact\Indexes

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Parent.Schema + "_" + $Item.Parent.Name + "_" + $Item.Name + ".sql" $Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

To produce files such like Person_Contact_AK_Contact_rowguid.sql (notice the schema and table names have been pre-pended):

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[Contact]') AND name = N'AK_Contact_rowguid')
DROP INDEX [AK_Contact_rowguid] ON [Person].[Contact] WITH ( ONLINE = OFF )
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Contact_rowguid] ON [Person].[Contact]
(
    [rowguid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

You may notice that primary keys are also scripted, usually these you may wish to script these into a different folder. To exclude these we can again use a Where-Object to filter them out, so the foreach statement becomes:

foreach ($Item in Get-ChildItem | where {$_.IndexKeyType -ne "DriPrimaryKey"} ) {

Ideally if I am scripting a large number of tables I don’t want to have to change location for each table. I can therefore script all the indexes by using a nested loop:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Table in Get-ChildItem ) { foreach ($Item in $Table.Indexes | where {$_.IndexKeyType -ne "DriPrimaryKey"} ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Parent.Schema + "_" + $Item.Parent.Name + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}
}

If you only wanted to script clustered indexes then checking the IsClustered property of the index would include them e.g.

foreach ($Table in Get-ChildItem ) { foreach ($Item in $Table.Indexes | where {$_.IsClustered -eq "True"} ) {

or to script only non-clustered primary keys you could use:

foreach ($Table in Get-ChildItem ) { foreach ($Item in $Table.Indexes | where {$_.IndexKeyType -ne "DriPrimaryKey" -and $_.IsClustered -eq $False} ) {

Now you should have everything to script the entire database!

Filed under: , ,
Powershell and SMO scripting – Part 1
13 August 09 09:18 PM | MartinBell | 2 comment(s)

One of the great things you can do with Powershell is that you can use the classes from SQL Server Management Object (SMO). I’ve only found 3 examples of using Powershell and SMO in Books Online, one scripts all views, one creates a database and one stops and starts the service with WMI ( which could be very handy Smile ).

Let's look at the scripting example from BOL:

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }


All you need to do to run this script is to copy the code, then right click on (say) the server name in object explorer and choose the Start Powershell menu option. Then paste the code into the window by clicking the icon in the top right of the windows and choosing edit and then paste.

If you have never run this before you will probably get the error:


Remove-Item : Cannot find path 'C:\PowerShell\CreateViews.sql' because it does not exist.
At line:1 char:12
+ Remove-Item  <<<< C:\PowerShell\CreateViews.sql

You can suppress this by piping the output of the command to the Out-Null cmdlet i.e.

Remove-Item C:\PowerShell\CreateViews.sql | Out-Null

Remove-Item also has many built-in aliases which include "del", "erase", "rmdir", "rd", "ri", or "rm", so you could use:

del C:\PowerShell\CreateViews.sql | Out-Null

and even through it is a file you can use rmdir:

rmdir C:\PowerShell\CreateViews.sql | Out-Null

The next line:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views

Changes the location to be the views folder in the AdventureWorks database, which is on the default instance of the localhost. cd is an alias you can use instead of typing out Set-Location.

Finally the commands that do all the output is

foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

In my previous Powershell article I showed how to use the foreach to process files in a directory on disc, the power of PowerShell allows you to do this for views in a given database.

Using the SMO Script() method the create statements for each view is then appended to the C:\PowerShell\CreateViews.sql  file specified as a parameter to the Out-File cmdlet.

If you look at the CreateViews,sql you will see CREATE VIEW statements for each view in the database separated by the set statements

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

This is all pretty powerful stuff and very easy to do, but what if I wanted to output each view definition into it’s own file? I did something similar in my previous Powershell article so I could easily change the script to be:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views foreach ($Item in Get-ChildItem) {
$out = "C:\PowerShell\" + $Item.Name + ".sql"
$Item.Script() | Out-File -Filepath $out
}

This script will create a single file named after the view and containing the CREATE statement for that view. SQL Server 2008 Management Studio has a great option on the Object Explore that allows you to script both a drop and create statement for an object. This is wonderful for scripting objects to store in a source code control system, but doing each one individually in Object explorer is not a task you really want to do. It’s also unfortunate that the scripting wizard does not allow you to do this. So I looked around to see how I could and found the SMO Scripter class and the Options Property. This property is a ScriptingOptions type with a hosts of members which includes the ScriptDrops property. So here is the script I came up with:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;

foreach ($Item in Get-ChildItem) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Name + ".sql"
$Scripter.Script($Item)
}

Unfortunately this only scripted statements like:

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[Person].[vAdditionalContactInfo]'))
DROP VIEW [Person].[vAdditionalContactInfo]
GO

i.e. only drop statements, and what I wanted was a combination of both of my scripts i.e. if exists drop the view then create it. So here is what I ended up with:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

In my next article I will look at scripting other objects.

Filed under: , ,
SQLBits V Training Day and SQL 2008 R2 Day pricing
12 August 09 08:00 AM | MartinBell | 1 comment(s)

We’ve had very good feedback about the SQLBits pricing, which was published on Monday. People are please that the early bird discounts will get you a significantly reduced price and signing up for both days will get you another small saving. Of course, the community day will still be free.

 

Cut off date

Pre-Conference Training Day Only

SQL 2008 and R2 Friday

Pre-Conference Training Day and SQL 2008 and R2 Friday

Community Saturday

Early Bird Delegate

30/09/2009

£199.00

£99.00

£285.00

FREE

Standard Delegate

01/11/2009

£249.00

£149.00

£385.00

FREE

Late Delegate

 

£299.00

£199.00

£485.00

FREE

For those wishing to stay overnight at the Celtic Manor we have negotiated a discount for delegates. Information about this will be posted on the website when registration opens.
 

If you want to stay up-to-date will all the SQLBits information register on our website

Filed under:
SQLBits announce Donald Farmer as keynote
11 August 09 06:33 AM | MartinBell | 1 comment(s)

Great news from SQLBits! We can now confirm that Donald Farmer has agreed to do a pre-conference training day and the key note for our SQL Server 2008 and SQL Server 2008 R2 day. As Program Manager for Project Gemini, no-one is better placed to tell you what is going to be in R2 and what is not! More information about the Pre-conference Training Day and SQL 2008 and R2 Friday will be released soon.
 
Filed under:

This Blog

SQL Blogs

Syndication