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:

Recently, I had a conversation with a friend and former co-worker. He's essentially been running a one-man shop since I left there but the company had recently hired a new developer who claimed to be very experienced in the technologies to which the office is trying to migrate (notably .NET). My friend had been tasked with bringing the new employee up-to-speed and working with the current projects. However, there have apparently been some concerns that the new employee may not have the abilities he claimed in his interview (because it is a small shop, there was no formal technical interview) and that the new employee may not have the necessary soft skills to fit in with his co-workers. My friend asked for my opinion on the situation (since I worked there previously). Apparently, his concern was on how far he should trust the new employee in terms of working on various applications.

I had to stop and think for a little bit because I had never previously considered how others may do things nor on the trust level needed to work with others. I should caveat my commentary here by stating that, up until my current position, I had worked mainly as a cowboy developer in small one or two-man shops. When I had to work with others, there was an implicit level of trust and communication because that is how I deal with everyone. Until you prove to me that you cannot be trusted, I expect that you will do what you say.

That being said, I considered the idea of trust in an IT shop. Everyone has heard stories of developers who work to ensure job security by keeping all information to themselves - even to the detriment of the company as a whole. I do not subscribe to that theory. When my friend was first hired at my previous employer, it was my responsibility to bring him up to speed and to ensure he could do the work. From the outset, I worked to communicate with him constantly on various projects so that, if I were to be hit by a bus, at least someone would know what was happening and could continue. While I am not great at documenting my code, I tried to ensure that the code was at least documented to the point that a beginning developer could read it and have a good idea of what it did. And I made myself available for questions at any time and tried not to show any exasperation if the questions seemed - for lack of a better word - stupid. After all, there is no such thing as a stupid question, just stupid people. And stupid people don't ask questions.

I checked up on my friend's work until I was comfortable with his level of knowledge and understanding that he could do the work independently. As I sensed that he was ready to move up or move on, I allowed him to work on larger or more complicated projects. When I left the company about 2 years after his arrival, I was comfortable that he could take over my responsibilities without any significant performance hits to the department - and made my boss aware that, while she may miss my contributions, my friend was more than able to take them over. Could he do everything I could in the same time frame? No, but that did not mean he could not do it at all. But the key point is that he was able to grow and develop on his own. I learned that mentoring new people was an important aspect of IT as it forced me to understand the process well enough to be able to explain it someone else.

And none of that would have happened without a level of trust. Trust on my part that he could do the job he said he could when he was interviewed. Trust on his part that I would make him not only a part of the team but that I would help him to continue to learn and develop. Trust is not an easy thing to have and it is far too easily broken, but without it, the IT world would not be where it is today.

After all, open-source technology is probably a direct result of trust...

Yesterday my nas died on me, actualy it didn't they I just couldn't connect to it anymore and this thing didn't have a USB port only ethernet.

So I decided to get the disk out and mount it in my desktop. SInce it was Sata that was easy. But I hit a little snag and that is that it was formatted in ext3 and windows does not read ext3

So I googled and found Linux reader 1.1 from diskinternals you find it in the freeware section. Yes freeware. It's very small and it installs in seconds.

You then get a screen with your drives on it, ext 3 drives included.

You then choose the folder you want to recover and click on recover this folder in the sidebar.
After which it starts.

And now I have one extra 250GB drive in my desktop.

And like Ted says, Backups are for sissies.

Still legacy application

I immediatly refactored the I and J to be lowercase.

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