Category Archives: SQL 22005 / 2008

Tips for SQL Server 2005 and 2008

Compiling CLR without Visual Studio

To Compile a VB or C# module as a CLR routine.

In a dos prompt

csc  /out:UserDefinedFunctions.dll /target:library userdefinedsourcefile.cs

vbc  /out:UserDefinedFunctions.dll /target:library userdefinedsourcefile.vb

Not that this doesn’t give you the benefits of tracing that you get using visual studio but there are cases where a clr routine is faster to write (I’m thinking string manipulation here) than the equivalent inline code

Static Data Tables

If you have static data tables then consider the following option – for low volume static data.

You can set up a DTS package to export the tables to an excel workbook very easily. Just use the export wizard and schedule the DTS package to run at a convenient time.
But…Make sure
 ODBC drivers are configured on the server – 2000 installed by default but not SQL server 7
 The sql server agent account has permissions to write to the folder for the spreadsheet.
 That the option to delete the data first is set.

Don’t forget to set up a DTS import package and….test, retest and test again.

Getting column list into a variable

The easiest way of getting a column list into a variable is to assign the contents of syscolumns (or sys.columns for SQL 2005).

Create a variable to hold the column list and then reassign it in the select statement.
To get the column key use the object id of the object

Select @variable = @variable + coalesce(@variable + ‘,’,”) from sys.columns where object_id = object_id(‘tablename’)

We are using the fact that coalesce returns the first non null value here so that we get a ‘,’ for each of the subsequent field names but just the column name for the first field.
Much easier than using a cursor on sys.columns to get each field.

If you don’t use coalesce for the first item in the array, you will also need to initialise the variable before assigning it since null + anything = null

create table temptable(id int identity, scol1 char(30), scol2 char(30) 

Declare @myvar varchar(max);
select @myvar = coalesce(@myvar + ‘,’,”) from sys.columns where object_id = object_id(‘temptable’) order by column_id

select @myvar