I have been quite busy and inactive in my blog. It was a challenging year. My New Year’s Resolution is to do more blogging compared to 2010.

• IntelliTrace is one of the new feature within the Visual Studio 2010 family of products. I believe visual studio 2010 ultimate edition contains this enhancement. This tool brings automated testing capabilities. I asked Mark few basic questions, and he was very kind in response. Just like other debugger in the microsoft environment, this one can start right within the IDE. We can write automated test scripts depicting certain scenarios, or use the recording option as if you are recording a VBA macro. This in turn generates the code files.
• One can build a lab within the test manager. In the lab, you can have multiple machines. As per Mark, we can have up to 60 machines (or depending upon the environment) tested at the same time.

Two things that happened today, which motivated me to write this post: 

1. I learned a new trick using set-operation

2. Also today is the day some of my SQL Server colleagues having #TSQL2sDay party

Here is the entry where I learned the trick [http://ask.sqlteam.com/questions/1206/insert-date-value-for-1-year]. 

I have been participating at AskSqlTeam.com lately. One of the recent question was about how to generate date for 1 year. TG had an interesting solution and I did not realize it was set-operation until Kristen set me straight. :) As usual, I was coming up with a iterative solution as opposed to set-based one. Here is the snippet I modified off of TG's code.

create table #myTable 

(dateCol datetime)

go

declare @i int

SET @i = 0

WHILE 

(

 datediff(year, dateadd(day, @i,'2010-01-01'),'2010-01-01')=0

)

begin

    insert #myTable (dateCol)

    select dateadd(day, @i, '2010-01-01')

    SET @i = @i + 1

    continue

end

go

select * from #myTable

go

The script above will create about 365 entries containing everyday of the year 2010. 

TG's answer to that problem was:

create table #myTable 

(dateCol datetime)

go

insert #myTable (dateCol)

select dateadd(day, number, '2010-01-01')

from   master..spt_values

where  type = 'P'and    number < 365

order by number

go

select * from #myTable

go

Anyways, my approach above is not the most ideal solution and it will be slow because of the looping. TG had the right idea. Many DB professional create a reference database for their tasks. In this database one may have scripts that can be applied on scheduled jobs and other artifacts that don't belong anywhere else. The solution above can be improved if we create a reference table containing all integers. Let's say we call that table IntValues. I started building that table today and it took more than 6 hours to enter 2 billion numbers. Here is the final draft of what that script would look like.

create table #myTable 

(dateCol datetime)

go

insert #myTable (dateCol)

select dateadd(day, number, '2010-01-01')

from   DbReference.dbo.IntValues

where  number < 365

order by number

go

select * from #myTable

go

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.

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. 

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.

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

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.