Rajib Bahar's Blog

Rajib blogs here about topics of his interest.

Combining SMO and Powershell to Generate SQL Database Schema

December 08
by Rajib Bahar 8. December 2009 10:49

There are times we find the need to generate the database schema. In SQL Server, it can be easily done using the graphical wizards in the Management Studio. I haven't found a way to script it to this day.

 

However, one alternative solution to this is to combine .NET programmability feature in powershell, and SMO. With this approach you can setup a powershell script job to automate your team's database build process.

 

Here are some basic assumptions before reading this post:

1. SQL Server 2008 is installed

2. Powershell is installed

3. SMO is in the GAC (Global Assembly Cache) or you know how to register it there

4. AdventureWorks is loaded in the database

 

Here are the steps I took to generate script against AdventureWorks database:

 

First of all, I went to management studio and right clicked on the AdventureWorks database to "Start Powershell"

 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

GAC    Version        Location
---    -------        --------
True   v2.0.50727     C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\1...

 

At this point, the SMO object was loaded using .NET reflection technology. Next we declare $srv variable and assign the local SQL Server 2008 instance to it. Then we assign the AdventureWorks database to the $db variable. The database object has an overloaded method namely Script(). We need to invoke that method to generate the database script. See the output below as the script is run.

 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $srv = new-object("Microsoft.SqlServer.Management.Smo.Server") "(local)\sql2k8"
PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db = $srv.Databases["AdventureWorks"]

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db

WARNING: column "Owner" does not fit into the display and was removed.

Name                        Status          Recovery Model   CompatLvl        Collation
----                           ------          --------------           ---------            ---------
AdventureWorks       Normal          Simple                 100            SQL_Latin1_General_CP1_CI_AS


PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $db.Script()

CREATE DATABASE [AdventureWorks] ON  PRIMARY
( NAME = N'AdventureWorks_Data', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Data.mdf' , SIZE = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH =16384KB )
 LOG ON
( NAME = N'AdventureWorks_Log', FILENAME = N'C:\data\MSSQL10.SQL2K8\MSSQL\DATA\AdventureWorks_Log.ldf' , SIZE = 18432KB , MAXSIZE = 2048GB , FILEGROWTH = 1638
4KB ) COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE [AdventureWorks] SET COMPATIBILITY_LEVEL = 100
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
    EXEC [AdventureWorks].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [AdventureWorks] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [AdventureWorks] SET ANSI_NULLS ON
ALTER DATABASE [AdventureWorks] SET ANSI_PADDING ON
.
.
.

 

The limitation we run with the above approach is that it doesn't include the objects such as tables, stored procedures, and functions in the script. That's why we have to write a loop to iterate all the tables, procedures, checks, primary, functions, etc. Each of those classes have the Script() method and we can invoke them as we need it. 

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> for ($i=0; $i -lt $db.Tables.Count; $i++) {$db.Tables[$i].Script()}
.
.
.
 

Yes, there are more gotchas. :(

 

So far, we have looked into resolving this issue using 1 of the overloaded Script() method. The 2nd version of the overloaded method expects ScriptingOptions as one of the parameter. Here is how we would declare them and the options they give us.

PS SQLSERVER:\SQL\OVERLORD\SQL2K8\Databases\AdventureWorks> $sc = new-object("Microsoft.SqlServer.Management.Smo.ScriptingOptions")

Here is a quick list of properties we can set on the $sc (ScriptingOptions) object.


            $sc.AppendToFile = 0;
            $sc.Bindings = 1;
            $sc.Default = 1;
            $sc.DdlBodyOnly = 1;
            $sc.DriAll = 1;
            $sc.DriAllConstraints = 1;
            $sc.DriAllKeys = 1;
            $sc.DriPrimaryKey = 1;
            $sc.IncludeDatabaseContext = 1;
            $sc.IncludeDatabaseRoleMemberships = 1;
            $sc.IncludeHeaders = 1;
            $sc.IncludeIfNotExists = 1;
            $sc.Indexes = 1;
            $sc.LoginSid = 1;
            $sc.PrimaryObject = 1;
            $sc.Permissions = 1;

 

 Depending upon your need, you can set the target server version, and the output file properties as well.

Some funny interview or pre-interview experience

November 28
by Rajib Bahar 28. November 2009 09:41

I have been through my fair share of technical interviews in various roles such .NET/SQL/BI developer. Thanks to putting on many hats in past/present consulting days. Most went well and few are worth a good smile. Please don't think I'm admonishing or looking down upon the people who asked these type of questions. It could happen to any of us (including me). My message is not meant to hurt anyone's feeling/thought/reputation/experience. 

Anyways, some time product-experience-requirement-type-questions can be crafted in funny way. For example, a recruiter may ask you for experience in product that is longer than even the product itself. For example, I was asked about the experience on "SQL Server 2008". I was literally asked "Do you have 10 years of experience on SQL Server 2008?" I felt like I dropped from the sky. At best if I recall correctly, I have used "SQL Server 2008" in 2007 (or later 2006) to try their Community Technology Preview or CTPs (as they are best known). I did correct that recruiter that it's not possible to have that many years of experience unless you were part of the team that developed the product itself or have access to confidential information. Needless to say he and I are good friends. I enjoy bringing him up to speed in my world, and he helps me learn about the business in general. 

Another type of questions involve the GUI. Yes, the dreaded GUI questions. I'll explain it shortly. It was a interview for a "BI Developer Role". They opened the interview by asking me where I can find the subreport button and under which section. I know the general area where that button is. It's on the right hand side on the "Business Development Studio." The 2nd part of that question involving "under which section" annoyed me. This question doesn't take into account "what if I have custom controls?" It'll surely make them rethink as those dynamically compiled controls will appear above and change the ordering of the section. I started considering whether this position will keep me happy. As the IDE itself is dockable so this kind of questions are not as relevant as one deems it to be. It shows that you are more excited about bringing in someone who may be unbalanced on business side of things (while a great technologist in heart). Once in a while I get invited to interview candidates. These are the type of questions I tend to skip. I'd not ask you about Crossword puzzle (though it could serve to show your intellect), philosophy (though it could reveal your personal ethics), or other completely unrelated skill that is not relevant to the job at hand. It may change depending upon the priority and the culture of the organization.

The most interesting type of interviews can happen when both the interviewer and the interviewee were misinformed about the subject area of the interview. In one interview I was instructed to prepare exteremely well for SSIS. So I went well prepared for the SSIS interview, and looked at the basic information on other areas. When the interviewer started, I realized it was a SSRS interview with focus on technologies that was not disclosed to me ahead of the interview. Was I surprised? 

These kind of scenarios may appear outrageous on it's face, but, I usually left those interviews with a smile in my heart and in my person.   

I am interested in learning what others faced. There is plenty of room for all of us to learn.   

 

Agile Philosophy

November 22
by Rajib Bahar 22. November 2009 18:07

This one quote has been in my mind for a while. I must have seen in someone's blog or update. The quote is “The illiterate of the 21st century will not be those who cannot read and write, but those who cannot learn, unlearn, and relearn.” Now, I remember... It was Michael Coates aka "The Pragmatic Evangelist", who wrote a blog entry on "Learning, Unlearning, and Relearning". That post helped me find the origin of this quote [http://blog.opsan.com/archive/2005/09/21/1588.aspx]. I learned Alvin Toffler is credited with it. He is a writer, journalist, and furturist. Compared to his journey, I feel like, I'm the opposite of an intellectual or outtellectual (is that even a word?) I think I just invented Rajib-ism.

One Guru in the techspace (Farhan Muhammad) told me technology changes every 6-12 months or so. Working as a techie dude, I realized/continue to realize every bit of what he said. I faced it when Visual studio 98, 2002, 2005, 2008, SQL Server 7.0, 2000, 2005, 2008 came out. One may want to point out, how is that 6-12 months gap? Well, these products also affect other dependent products which have to rush out there to make sure their tools of the trade are compatible or have some workaround. In my humble opinion, it's a reasonable generalization. The rule of the game is simple, we must be on the notice and at the edge of the cliff constantly. While most of the products are backward compatible, some times they are not forward compatible. That tends to make the job challenging, risky and rewarding. A solution that I may have written in .NET 1.0 world, will be completely different in the .NET 2.0 or it's successors. Similar thing happens in the SQL Server world. Either the previous solutions/methods/apis are outdated, deprecated, or we have more ways to do the same thing. This is where the learning of new tricks, unlearning of what you knew before, and relearning of how you solve a particular problem happens. A simple analogy would be some one who eats with chop stick. All of a sudden, the fork technology gets released. Now this person has to learn how to eat with the fork with proper balance, as opposed to the chop sticks. When he gets used to the forks, he has no need of the chop sticks. Anyways, I hope I'm not misusing this Toffler quote, like the way Darwin's "survival of the fittest" quote is. I did have few fun and engaging discussion with my college life biology professor on this. Interestingly enough, he's a George Harrison fan, but, I digress. We have to figure out how flexible we are as it relates to the changes.

One of my mentors, once admonished me, "Whatever you do, you must be consistent". I took that lesson to the heart, but, discipling myself to that principle is an ongoing challenge. Sometimes, I follow it quite well, and some times I have room for improvement. I wish I was a better mentee. Some time facts of life gets in the way and things get complicated. These days I try to be consistent by being agile to the challenge or issue at hand. We don't know what kind of facts we will face. As long as we work fairly and in good spirit, everything should move along fine. 

DLL Dilemma

November 20
by Rajib Bahar 20. November 2009 06:10

We all have some form of inner-gadget-freak inside. Sometime that entity manages to go overboard by installing every sort of interesting application on the laptop/desktop. Yes, I admit having multiple database servers running off of my laptop, and few open source servers as well. It's all good until the system is bloated and can not handle anymore. Does it ring a bell? Then you start noticing your laptop is the loudest voice in important meetings, lectures, or other related gatherings. That's when we get the motivation to start unloading every application on the system.  Along the way we manage to get rid of important system dll files. Then we probably crash our system or buy a new laptop. Now, the question is does it repeat for you every 6 to 12 months?

 

Here is a site I have used in the past in resolving DLL issues [http://www.dll-files.com/]. Hope it helps and please be extra careful in utilizing it (check for version, publisher, and other critical details). It's possible instead of fixing your issue, you may end up building new ones.

Search for data on any given database and on any column

November 18
by Rajib Bahar 18. November 2009 15:47
In some of my previous project, I had to find out whether a particular data existed on anywhere in any database. By that I mean, if I wanted to find out whether a particular data, for example, "manager" made in to any table. It's a trivial issue if you know the database table structure and column names. What if you did not have that benefit and wanted to do a massive manhunt for the data? I have written similar script such as the one below, and forgot to keep track of it in my library. Finally, my slowly deteriorating and dull memory served as a motivation for this post. The way it works is a) by getting list of all available databases, b) using dynamic sql to capture tables and column information, c) comparing sought after data against any column that can be converted to varchar type. If you need more explanation then comment please. 
 
Here is the snippet:
 

SET NOCOUNT ON

 

DECLARE @SoughtAfterValue VARCHAR(8000)

 

SET @SoughtAfterValue ='Manager'

 

DECLARE @Tmp TABLE

(

      ID INT IDENTITY(1,1)

    , DBName VARCHAR(255)

)

 

INSERT INTO @Tmp(DBName)

SELECT name

FROM sys.sysdatabases

 

DECLARE @i INT

DECLARE @total INT

 

SET @i = 1

 

SELECT @total = COUNT(*)

FROM @Tmp

 

DECLARE @sql Nvarchar(max)

 

WHILE @i <= @total

BEGIN

 

            set @sql= N'

            use ' +(

                SELECTDBName FROM @Tmp WHEREID = @i

            ) + '

           

            DECLARE @TmpQry TABLE

            (

                    ID INT IDENTITY(1, 1)

                  , Qry VARCHAR(8000)

            )

           

       

        if exists

                  ( SELECT

                  *

        FROM INFORMATION_SCHEMA.COLUMNS

            WHERE TABLE_CATALOG = '''

        +

            (

                SELECTDBName FROM @Tmp WHEREID = @i

            )

        + '''

            )

            BEGIN

 

            INSERT INTO @TmpQry (Qry)

        SELECT

                  ''SELECT CAST('' +COLUMN_NAME + '' AS VARCHAR(8000))[Result] '' +

                ''FROM '

            +

            (

                SELECTDBName FROM @Tmp WHEREID = @i

            )

                  + '..' + ''' + TABLE_NAME  

                  + '' WHERE CAST('' +COLUMN_NAME + '' AS VARCHAR(8000)) like ''''%' +@SoughtAfterValue + '%''''''

                   

        FROM INFORMATION_SCHEMA.COLUMNS

            WHERE DATA_TYPE NOT IN

            (

                  ''binary'',''varbinary'', ''image'', ''geography'', ''geometry'', ''timestamp'',

                  ''xml'',''hierarchyid'', ''sql_variant''

            )

 

            END

 

            DECLARE @i INT

            DECLARE @Total INT

            DECLARE @CurrentQryVARCHAR(8000)

            DECLARE @CurrentQry2VARCHAR(8000)

           

            SET @i = 1

 

            SELECT @Total = COUNT(*) FROM@TmpQry

           

            WHILE @i <= @Total

            BEGIN

                 

                  SELECT @CurrentQry = Qry     

                  FROM @TmpQry WHERE ID =@i

           

                  SET @CurrentQry2 = ''ifexists('' + @CurrentQry +  '') begin select ''''''+ REPLACE(@CurrentQry,'''''''', '''''''''''') + '''''' [Query Ran] '' + @CurrentQry + '' end''

 

                  exec(@CurrentQry2)

 

                  SET @i = @i + 1

 

                  CONTINUE

            END

           

      '

 

exec(@sql)

 

      SET @i = @i + 1

      CONTINUE   

END

  

AdventureWorks 2008R2 November CTP Beta is out

November 18
by Rajib Bahar 18. November 2009 06:06

Two weeks ago I struggled to get the "AdventureWorks 2008" to work on my "SQL 2008 R2" server. I ended up building a 2005 version of it. It appears they have released a beta version of it for the R2 release at CodePlex. Help yourself at your own learning venture.

http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=24854

 I'll update this entry and/or have a followup entry as time permits to share my experience.

Trend

October 11
by Rajib Bahar 11. October 2009 10:37
It's a challenge to keep track of things as they are constantly changing (just like my tools of the trade). Don't you wish you are always on top of all things, but, other things in life get in the way... Some time I get immersed in work and study that I forget the rest of the world. 

Now, that I have this challenge at hand, the question is how am I going to pull myself through this one? I found a tool on it's face seemed quite interesting. My work involves working with SQL server. If I want to learn the noise around sql server then it will show what everyone in the world is thinking about it. I believe it utilizes the RSS feed from twitter. The people at codemunch.com wrote this very simple and useful tool (depending upon your value on it). I found their site while trying to find the trend on sql server. 

How to use it?

Here is what the URL for the trend on "SQL Server" would look like. 


Simply change the "q=" part to your preferred query. For example, you want find the latest on sql then the query string would look like "q=sql". If you want to use words with spaces in it then place a %20 between each word. It's as simple as that.

Other trends that I may use for myself include:

I am sure other Gurus may have different opionion how to find trend. After all, there are other reliable methods such as reading the book on latest tools, online articles, blogs, visiting focus groups that provide training in similar area. My intention with this entry is to talk about what other tool is out there to complement such effort. Anyways, I do find the existing methods useful from time to time. I have been to the local sql server user group many times. Most of the times, I went their for the swags, and free softwares. Most recently, I went to the "SQL Server Summit" hosted in minnesota with sponsorship from Digineer. I attended one of the reporting services session to learn about the challenges the MVP had in his project. Toward the end, I asked a question about SSRS and sharepoint and got good feedback from several peers. The presenter moved on to take other closing questions. In the meantime, I had this urge to tinker. I took out a yo-yo which I picked up from Digineer's booth prior to that presentation. I thought I'd play with it while the presentation ends. Unfortunatley, I did not know that the yo-yo's extra feature included very loud sound, and shiny lights. Yes, it was an awkwardly funny moment and all eyes were on me. I was in a panic mode trying to silence the alarm. Luckily for me, I did not get bounced out as I worked with some of the people in there running the show. I had an occassion to collaborate with Mark V. from digineer in the past. He was sitting next to me and cracked open. He did not know that the yo-yo would do that. I am making a note to avoid yo-yo's next time. BTW, I transferred the yo-yo to my nephew, but, I digress.

ConnectionStrings - a tribute

September 29
by Rajib Bahar 29. September 2009 07:25

I use the ConnectionStrings.com site many times. Whehter it's MS SQL server or Oracle or MySql, they have the useful snippet of code for the connection string. It usually saves me time from looking up the manual. I thought I would dedicate this entry just for them.

 

If you don't know what connection string is then you are lucky to be reading this entry. In short, connection string is what defines how we connect to data sources and destinations. It enables application to talk to the database so that information is persisted in the RDBMS or target data structure. 

 

A sample connection string may look like this... (Yes... I borrowed the connection string below from their site :) )

 

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

TSQL Cheatsheet - Need more to build on this list

September 01
by Rajib Bahar 1. September 2009 07:31

I use these cheatsheets related  to TSQL and/or SQL server from time to time.

 

I can not count on my memory, but, it would be nice to have a photographic one.

 

Enough of my rambling, here is the list:

TSQL Cheatsheet found at Scribd - http://www.scribd.com/doc/399147/TSQL-Cheat-Sheet

SQL Server Cheatsheet found at Scribd - http://www.scribd.com/doc/15235350/SQL-Server-TSQL-Cheatsheet

Dave Pinal or SQL Authority's Cheatsheet - http://www.pinaldave.com/sql-download/SQLServerCheatSheet.pdf 

 

Did I miss any good ones? I'll update this 

Tags: , ,

SQL

My extra-curricular activities with KFAI radio

July 23
by Rajib Bahar 23. July 2009 02:01
Hello, Friends,

This post deviates from my usual postings on SQL and various geeky topics...

After volunteering my time for various IT interest groups... I also volunteer my time at KFAI radio whenever time permits. KFAI radio recently asked me to create multiple videos for their fund raising effort.

I had a lot of fun building them as a humble volunteer.

Here is the link to the 1st video which is about what KFAI symbolizes.

http://www.youtube.com/watch?v=vZrpQ8QfZDc


The 2nd video falls under comedy category and will be released some time friday.



Please visit KFAI radio http://www.kfai.org or http://www.youtube.com/kfairadio and add them as your friend and/or subscribe if you like the content.

Regards,
Rajib Bahar