I've seen this question numerous times in the forums I like to frequent. The idea is, when you create a new database, you may want that database to have several tables, some of which are automatically populated with data (thinking about relatively static lookup tables here).

The process mentioned here may not be useful for everyone, but it is certainly something to consider.

There is a system database named Model. This database is used when you create a new user database. In fact, any object (table, view, stored procedures, functions, etc…) that exists in the model database will be copied to your newly created database. This may be a blessing and a curse, so use this suggestion wisely. If you have a SQL Instance where you need to set up a new database for each customer, and that is all the instance is used for, then it makes sense to create your objects in the Model database. However, if you have a general purpose instance that you are using for various databases, you probably won’t want to put your objects in the Model database.

Enough of the chatter, let's see how this can be done.

First, create a table in the Model database.

Now, let’s create a new database.

Now, let's make sure the table (and it's data) exist in the new database.

As you can see, the table that was created in Model exists in the newly created database. It even has all the data that was loaded in to it.

Now, let’s clean up after ourselves:

I had no choice but to do work with creating dynamic column headings and dynamically determine what field in my dataset should go where in a report today. Sense this is the second time I’ve gone through this exercise and knowing the lack of information out there on really how to do it, I thought it deserves a blog entry.

So here is the basis of the requirements. You have a query that uses PIVOT but thrown into the mix is the need for dynamic columns in the PIVOT. This is usually a task when you are going after things like current week plus the last 52 weeks. That was the case in this situation. I needed to bring in a dynamic set of columns to be used in PIVOT. The matrix in 2005 did not give me what I needed in the end result so this is the path I took.

First task is to write the procedure to use PIVOT with dynamic column headers. I’m not going to go into that method sense it’s well documented out there and out of scope. I will point you to Pivots with Dynamic Columns in SQL Server 2005 as it explains the way to accomplish this well.

I wrote something in AdventureWorks to for this example so if you have AdventureWorks floating around you should be able to read this and run through step for step with success.

Here is our procedure. I’m sure my methods will take great notice from my local TSQL friends The dynamic SQL more so than anything...

The results shows us the PIVOT results of each account number and the sales for the week of the year

The problem with all of this is the dynamic nature of the column names. In reporting services we’re used to handling column names as static entities. So here is how we’ll build our report given the fact these column names can and will change over time.

So create a new report in your solution and add a new DataSet. Make it a text call with the following statement

Run the DataSet to verify everything comes in ok.

Now add a new table to your empty report. Add the account number and product number as you would normally. Next we need to figure out what week is actually first. To do this we’re going to write a function in the code section of SSRS

In the layout tab go to Report and select Report Properties. This will give you the properties for the entire report. Select the Code tab. Copy and paste the following code into the window

This code was written by our own gmmastros. Thanks to him for this and the help it gave me when I needed it. Gets the job done and it does it quickly.

Final results should look like this

Hit OK to save.

Now in the field next to the Product Number go ahead and enter an expression for the heading like this

="W" & Code.GetColumnHeading(0)

Recall in our procedure we return each week as Wnn for the week number. So in our code we created an ArrayList filled up with the order we want. The same order we based the procedure off of. Now by using the index of the ArrayList we can simply call for the heading that should be all the way to the left (-51 weeks from the current) by means of index of 0. In the details textbox we can then simply do the following as well given the same guidelines

=Fields("W" & Code.GetColumnHeading(0)).Value

Most developers don’t know they can reference the fields by name in this manner. Usually it just isn’t required and that is the case. It can be useful to note that you can dynamically fill the object name in though and get the same results as Fields!name.Value

I went ahead and put a few more columns and increased the index requested from the ArrayList. Running that results in the following.

Now you have your customer matrix in a sense by means of dynamic column and field referencing. You also a nice example of PIVOT by means of dynamic column names.

This blog probably has about 12 pages worth of explanations but I’d like to leave those to the forums so please follow the directions below if you want to discuss this method further. Otherwise, have fun with it!

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

There seems to be a little bit of confusion on how to script out the database. The correct answer is of course: just run all the scripts you have in source control :-)

So for those who do not use/have source control I will show you how to do it

Logically you would think that this would be the way: Right click on the database --> Script Database As --> CREATE To and then pick your choice. See also image below

What that will do is just create the database and not much else, here is what the script might look like

What you have to do is actually this: Right click on your database-->Tasks-->Generate Scripts

Select the database you want to script and make sure you check the option at the bottom that says Script all objects in the selected database. See image below

That is all, click next a couple of times and review your options and you are set

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

I answered this question today and thought it would be useful to create a little post

If you have a varchar value like this '2009-06-26 14:30:00.000Z+4:30' you want to take 4 hours and 30 minutes and then subtract that from the date itself so in this case you will get 2009-06-26 11:00:00.000. The reason you subtract is because the +4:30 means that this was generated in a zone that is 4:30 ahead of utc

So first we need to figure out a couple of things
1) where are the minutes?
2) where is the hour?
3) is it positive or negative?

Here are the answers
1) where are the minutes?
The minutes are the last 2 characters

2) where is the hour?
The hour starts after the Z and last for 2 or 3 characters including the sign, we will just grab 3 and replace : with an empty string

+4

-4

+14

3) is it positive or negative?
That we already grabbed above for the hour, for the minute we need to do something like this

We also need to convert the stuff we did above to integers in order to add

So here is the complete code

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum

This will not work on SQL Server 2000 since the sp_refreshsqlmodule does not exists on that version!

A while back in the What is deferred name resolution and why do you need to care? blogpost I showed you that sp_depens is not reliable because you can create procedures that reference objects that have not been created yet.

You can use sp_refreshsqlmodule to help 'fix' that
let's take a look at how that works

First create this awesome stored procedure

Now execute sp_depends

Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 25
The object 'Blah' does not exist in database 'tempdb' or is invalid for this operation.

So that tells us that the table Blah does not exist. Fine, what happens if we run sp_depends for the proc?

Object does not reference any object, and no objects reference it.

That makes sense since the table does not exist. Let's create this table

Now run sp_depends again for the table and the project

Object does not reference any object, and no objects reference it.

Object does not reference any object, and no objects reference it.

Okay so SQL server knows that the table Blah has been created but it still does not know that it is beeing used in the proc

Will executing the proc change that perhaps?

Object does not reference any object, and no objects reference it.

Object does not reference any object, and no objects reference it.

Nope, no such luck, that didn't do anything
Now execute the sp_refreshsqlmodule proc

Execute sp_depends again

In the current database, the specified object is referenced by the following:

name		type
dbo.prBla	stored procedure

Yep, now it is showing us that table Blah is used by the stored proc prBla
Will it work when we run sp_depends for the stored procedure?

In the current database, the specified object references the following:

name		type		updated	selected	column
dbo.Blah	user table	no	yes		SomeCol

And as you can see it also shows that the table is used..like Borat would say "Very Nice"

Clean up by dropping these sample objects

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum