Have you ever wondered how to build a string with values for an error message? One would think that RASIERROR could simply take a string that was concatenated together with variables. Something like RAISERROR ('My Error' + @myErrorString, 16,1)<< MORE >>

Have you ever wanted to change the export options for Reporting Services? Have you seen issues with report page headers being exported to Excel as non headers? Other times displaying all export options on the web is not the best idea. An end user trying to figure out what a TIF is can be a more user support time then is desired. In other cases the export might need to be renamed, instead of TIF call it Image. << MORE >>

Have you ever noticed that if a Total or Sub-Total line floats until to the last page by itself? Worse, table headers do not copy over when only a Sub-Total or Total is all by itself on the last page. To remedy this situation there is a table option that can be set called Fit to Page. << MORE >>

A great new feature of SQL Server 2005 is the ROW_NUMBER function. In previous versions of SQL to get a numbered row would pretty much require moving the data into a temp table with an identity column, placing an identity column on the original table, or looping through data with a cursor. SQL Server 2005 makes adding a row number to a query a very simple task with the ROW_NUMBER function.<< MORE >>

Unfortunately, subreports within a table or matrix cannot be exported to Excel. I am not sure why MS did not allow this with Reporting Services. << MORE >>

One issue that I have noticed with SSRS is that headers and footers do not contain query data that is derived from a hidden text field. << MORE >>

A new addition to the DELETE command in SQL Server 2005 is the TOP statement. The DELETE TOP does the same thing as a SELECT TOP WHERE only the TOP number of rows are deleted. << MORE >>

I have seen a lot of is queries written without using the JOIN syntax. Instead the developer chose to write the query using an IN clause in the WHERE clause with a SELECT statement; a Nested Query. While many people blame nested queries on developers that don't understand joins, they can actually be beneficial. << MORE >>

Sometimes identity columns must be reseeded. A good example is when merging data from a couple of different data sources. There is a DBCC command that makes this task easy. DBCC CHECKIDENT (, RESEED, )<< MORE >>

Posted by Adam Rink at 10/26/2007 | Add Comment

Selecting a Comma Delimited List with TSQL

Sometimes instead of bringing back a table set of information, a query needs to return a comma delimited list. I have seen some crazy stuff to do this like UDFs and Cursors. There is a really simple way to do this in a select statement though.<< MORE >>

Posted by Adam Rink at 9/25/2007 | View Comments (10) | Add Comment

Older Entries

Copyright 2007. All rights reserved.