Friday, December 2, 2011

Multi Row Inserts in T-SQL

A new feature in SQL Server 2008 is the ability to insert multiple rows with one insert statement!

In previous versions, anytime you wanted to insert multiple rows into a table with a T-SQL script, you would have done something like this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123)
Insert into MyTable (Field1, Field2)
Values ('DEF',456)
However, with SQL Server 2008, you can now do this:

Insert into MyTable (Field1, Field2)
Values ('ABC',123),('DEF',456)

Working with a growing data mart, I do a lot of scripted inserts, so this definitely makes my life a little simpler!


Tuesday, October 18, 2011

Unattended Visual Source Safe 2005 installation

To install Visual Source Safe 2005 silently, you must first create an install ini file using Setup.exe.
The setup in the root of the CD will not do this, you have to navigate to the setup folder on the CD.
If you are using a download from MSDN, there is only one setup.exe and it’s the one to use.
The machine that you use to create this ini will be the template for all installations. You cannot create the ini file from a machine that already has VSS installed. You must create it from a machine that does not yet have VSS installed.

From a command line, execute the following:
\\(servername)\(sharename)\(VSS Path)\setup.exe /CreateUnattend \\(servername)\(sharename)\(Path to INI)\filename.ini

I create my ini file in the same folder as the setup process.
For example, my command would look like this:
\\VSSServer\VSS\setup.exe /CreateUnattend \\VSSServer\VSS\FullInstall.ini

Again, the machine that you are running this process on should not have VSS installed yet. If it does have VSS installed, the ini file will be used to upgrade or add/remove features and will not install VSS on the target machine.

To install on the target machine, run this command:

\\(servername)\(sharename)\(VSS Path)\setup.exe /UnattendFile \\(servername)\(sharename)\(Path to INI)\filename.ini

Tuesday, September 13, 2011

Repeat Table Header on Each Page in SSRS 2008R2

I ran into an issue with the tablix in SSRS 2008R2 where I set the tablix property “RepeatColumnHeaders” to True. However, this did not cause the table header to repeat.

After a little experimentation, and a little help from Paul Turley, I figured out how to consistently get the table header to repeat on every page.

The setting of the “RepeatColumnHeaders” property on the tablix seems to have no effect on this behavior. Instead, we have to set it on a static member of the row groups.

To see the static members of the row groups, click on the small drop down arrow on the far right of the row groups/columns groups header. Then select Advanced Mode.

With the static members of the row group shown, select the first one in the list. The top left cell of the tablix should be selected in the designer.

Now open the property pane and find the property “RepeatOnNewPage” and set it to True.

Then find the property “KeepWithGroup” and make sure its set to After. If its not, set it to After.

Now your table header will repeat on every page.

BTW, check out Paul’s book “Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports” It has been a good resource for SSRS.