SQLCMD mode and batch separators

I fell for this one this week. If you execute the following code in SQLCMD mode, what would you expect the output to be?





:CONNECT $(PrincipalServer)



:CONNECT $(MirrorServer)



:CONNECT $(WitnessServer)


I'm guessing you might not have expected:




The problem is the lack of a batch separator. What I should have written was this:





:CONNECT $(PrincipalServer)




:CONNECT $(MirrorServer)




:CONNECT $(WitnessServer)




While this may be strictly correct, I can't imagine it's the behaviour anyone would wish for. Do you think that a :CONNECT statement in a SQLCMD batch should also be treated as a batch separator? Does it ever make sense for it not to?


PDCnoggin: The PDC Brain Relay

You've seen the Olympic torch relay. Well PDC has a brain relay with the PDCnoggin. The Regional Directors are an awesome bunch of people that I'm really honoured to be part of. Many get together for the PDC each time it's held. Our RD lead Kevin Schuler sent me the PDCnoggin to start the relay, probably because I'm about as far away as it can start. Here's the PDCnoggin:


And now it's on it's way to the next RD before it makes its way to the PDC in October in LA.

Displaying HTML content in Reporting Services 2008

A friend of mine that works for Microsoft pinged me yesterday about how to strip HTML tags out of text he was trying to display in Reporting Services. He just wanted the text displayed. The typical text looked like this:

<div class=ExternalClassB9A517D3DE254676B8266F6B2D84FD05>
<p>SciTech Software is a software development and consulting company. The company was founded in 1991 with the intention of creating software for scientific instruments, but our focus has shifted towards creating tools for developers.
<p>We have worked in close collaboration  with <a href="http://
http://www.thermometric.com/">Thermometric AB</a> with some of our products, but now we concentrate on our own tool for the .NET Framework: <b><a href="http://http://memprofiler.com/">.NET Memory Profiler</a></b>.
<p>We have extensive experience developing using C++, Java and C#.  Currently, our main development environment is the .NET Framework. </p></div> 

I'd heard this could be done in Reporting Services 2008 so I tried it and it's easy. I presume others might find simple instructions helpful:

1. In the table where I want to display the column, right-click the cell and chose Create Placeholder….

2. On the General tab (of the new placeholder's properties), set the value to the required column.

3. In the Markup type, choose the option for HTML – Interpret HTML tags as styles

and you're done.

Database mirroring requires transaction log backup regardless

I was setting up mirroring at a client site today and started as I usually do by backing up the primary database and restoring it at the mirror. It would not start mirroring and complained that I didn't have the latest transaction log backup: the mirror database …., has insufficient transaction log data to preserve the log backup chain of the principal database.

Somewhere along the way, the need to have a transaction log backup has crept in. So, to start mirroring, I just now backup the primary database to a file, backup the transaction log to the same file and then restore both on the mirror server. Then it starts fine.

This seems pointless behaviour and it didn't used to work that way. I have an RTM SQL Server 2005 in a virtual machine and it works fine without doing this. Anyway, thought that posting it might help someone.

Fascinating demo of surface technology

Fellow RD Tim Huckaby’s company has been really pushing the capabilities of the surface platform and building a really interesting set of apps. Most are still embargoed but this one is now allowed to be shown:




I suspect many of you will find it really interesting. Enjoy!


Modifying the Thesaurus in Full Text Search in SQL Server 2008

While I was prepping my full-text search session for TechEd Australia today, I decided to modify the thesaurus. I found the discussion in books online a bit confusing regarding the locations of the files involved.

What threw me was it said the default location of the thesaurus files was:

SQL_Server_install_path\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\FTDATA\

I went there and couldn't find anything. I worked out that for ENU language, my file should be called tsENU.xml. I searched for that and found it in:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\Templates

I modified that and got nowhere. Turns out that as the name implies, this is where the templates for the files live. I then tried creating the missing folder under the stated default location and also got nowhere. Turns out that it's actually related to your data directory location. Where I needed to put it was:


because I'd used C:\SQLData as my data folder. So it's not the SQL Server install path, it's the data file folder. Anyway, that file contains the following XML:

&lt;XML ID="Microsoft Search Thesaurus"&gt;  &lt;!--  Commented out      &lt;thesaurus xmlns="x-schema:tsSchema.xml"&gt; &lt;diacritics_sensitive&gt;0&lt;/diacritics_sensitive&gt;         &lt;expansion&gt;             &lt;sub&gt;Internet Explorer&lt;/sub&gt;             &lt;sub&gt;IE&lt;/sub&gt;             &lt;sub&gt;IE5&lt;/sub&gt;         &lt;/expansion&gt;         &lt;replacement&gt;             &lt;pat&gt;NT5&lt;/pat&gt;             &lt;pat&gt;W2K&lt;/pat&gt;             &lt;sub&gt;Windows 2000&lt;/sub&gt;         &lt;/replacement&gt;         &lt;expansion&gt;             &lt;sub&gt;run&lt;/sub&gt;             &lt;sub&gt;jog&lt;/sub&gt;         &lt;/expansion&gt;     &lt;/thesaurus&gt; --&gt; &lt;/XML&gt;

The template file has three examples commented out. The diacritics entry relates to whether or not accents matter ie: in words like cafe. Expansion entries are used to provide synonyms. In this case, if you search for any one of "Internet Explorer", "IE" and "IE5", it will search for all of them. Replacement is used to force an alternate lookup. In the example above, if you search for either "NT5" or "W2K", it will search for "Windows 2000" instead.

After modifying the correct file, I then caused it to be loaded by specifying:

EXEC sys.sp_fulltext_load_thesaurus_file 1033 

and then I was in working fine with queries like:

SELECT MessageID,Description

FROM dbo.Messages



Hope this helps someone get started with it.





Data Driven Subscriptions in SQL Server 2005 Standard Edition

Recently, I was working at a client's site where SQL Server 2000 was still in use. They wanted to upgrade to SQL Server 2005. The only reason they were looking at the Enterprise Edition was for data-driven subscriptions in Reporting Services. The CEO certainly wasn't interested in paying the sort of dollars required for that, just to be able to automatically email out a set of monthly reports to about 30 or so folk within the company.

A quick bit of research led me to Jason L. Selburg's article at Code Project on how he achieved this by modifying the subscription details in the ReportServer database. You'll find his original post here: http://www.codeproject.com/KB/database/DataDrivenSubscriptions.aspx

I wanted to tackle a more complete version than Jason provided. There were a couple of key limitations:

1. You'd have to modify the stored procedure every time your parameter list changed. (and potentially have different stored procedures for different reports).

2. He used commands for manipulating the ntext columns that were deprecated. I wanted to avoid these.

3. The code didn't deal with concurrent usage.

Below, you'll find an alternate version of the proc. The concept is still the same. You create a subscription to a report, using tokens for parameters, set the subscription to happen in the past and then execute it using this proc in a scheduled Agent job. The different approach I've taken is:

1. I've provided the ability to deal with a parameter list rather than a single parameter.

2. I found that the ntext columns actually only contained simple xml. While I could have used XQuery to modify it, it's easier to directly manipulate it as varchar data (could have used nvarchar), given all we're doing is replacement of tokens with values. Either way, this avoids the use of UPDATETEXT etc.

3. I've used more of my style of naming conventions..

4. I've used a global temporary table as a flag to limit concurrent use. (Without this, the scheme could fail).

Hope you find it useful.





      Creates the effect of a data driven subscription by replacing the fields in

      an existing subscription with the supplied values, executing the report

      and then replacing the original values.



        @ScheduleID     The Job Name in SQL Server

        @EmailTo      The TO address of the email

        @EmailCC      The Carbon Copy address of the email

        @EmailBCC        The Blind Copy address of the email

        @EmailReplyTo  The Reply TO address of the email

        @EmailBody       Any text that you want in the email body

        @ParameterList The parameters for the report in the format 'Parameter1Token,Parameter1Value,Parameter2Token,Parameter2Value…'

                     Example: '|StartDate|,20071231,|Salesperson|,GE,|Region|,NW'





      Greg Low based on a concept from Jason L. Selburg at CodeProject.com



      ParameterTokens and ParameterValues are limited to 1000 characters

      EmailBody is limited to 8000 characters

      ParameterList is limited to 8000 characters total



CREATE PROCEDURE dbo.ExecuteSubscribedReport

( @ScheduleID uniqueidentifier,

  @EmailTo varchar (1000) = NULL,

  @EmailCC varchar (1000) = NULL,

  @EmailBCC varchar (1000) = NULL,

  @EmailReplyTo varchar (1000) = NULL,

  @EmailBody varchar (8000) = NULL,

  @ParameterList varchar (8000) = NULL




  DECLARE @extensionSettingsPointer binary(16),

          @parametersPointer binary(16),

          @tokenPosition int,

          @tokenLength int,

          @subscriptionID uniqueidentifier,

          @parameterToken varchar(1000),

          @parameterValue varchar(1000),

          @parameterPosition int,

          @numberOfParameters int,

          @parameterCounter int,

          @character varchar(1),

          @parseStatus varchar(1), — 0 ready for another token, 1 in a token, 2 in a value

          @originalExtensionSettings varchar(8000),

          @originalParameters varchar(8000),

          @newExtensionSettings varchar(8000),

          @newParameters varchar(8000);

  DECLARE @parameters TABLE (ParameterID int IDENTITY(1,1),

                             ParameterToken varchar(1000),

                             ParameterValue varchar(1000));


  — first we need to unpack the parameter list

  IF @ParameterList IS NOT NULL BEGIN

    SET @parameterPosition = 1;

    SET @parseStatus = 0;

    SET @parameterToken = ";

    SET @parameterValue = ";

    SET @numberOfParameters = 0;

    WHILE @parameterPosition <= LEN(@ParameterList) BEGIN

      SET @character = SUBSTRING(@ParameterList,@parameterPosition,1);

      IF @character = ',' BEGIN

        IF @parseStatus = 0 BEGIN — we had two commas in a row or the first character was a comma

          PRINT 'ParameterList has incorrect format';

          RETURN 1;


        ELSE IF @parseStatus = 1 BEGIN — we are at the end of the token

          SET @parseStatus = 2;

          SET @parameterValue = ";


        ELSE BEGIN — we are at the end of a value

          INSERT @parameters (ParameterToken,ParameterValue)

            VALUES (@ParameterToken,@ParameterValue);

          SET @numberOfParameters = @numberOfParameters + 1;

          SET @parseStatus = 0;

          SET @parameterToken = ";



        IF @parseStatus = 0 BEGIN — we have the first character of a token

          SET @parseStatus = 1;

          SET @parameterToken = @parameterToken + @character;


        ELSE IF @parseStatus = 1 BEGIN — we have another character in a token

          SET @parameterToken = @parameterToken + @character;


        ELSE BEGIN — we have another character in a value

          SET @parameterValue = @parameterValue + @character;



      SET @parameterPosition = @parameterPosition + 1;


    IF @parseStatus = 2 BEGIN— we were still collecting a value

      INSERT @parameters (ParameterToken,ParameterValue)

        VALUES (@ParameterToken,@ParameterValue);

      SET @numberOfParameters = @numberOfParameters + 1;




  — we need to wait for our turn at using the subscription system

  WHILE EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name = '##ReportInUse')

    WAITFOR DELAY '00:00:30';

  CREATE TABLE ##ReportInUse (ReportID int);


  — once we have the parameters unpacked, we now need to find the subscriptionID

  SELECT @subscriptionID = SubscriptionID

    FROM dbo.ReportSchedule

    WHERE ScheduleID = @ScheduleID;


  — next we save away the original values of ExtensionSettings and Parameters

  — (we use them to make it easy put the values back later)

  — they are actually xml but it'll be easier to work with them as strings


  SELECT @originalExtensionSettings = CAST(ExtensionSettings AS varchar(8000)),

         @originalParameters = CAST(Parameters AS varchar(8000))

    FROM dbo.Subscriptions

    WHERE SubscriptionID = @subscriptionID;


  SET @newExtensionSettings = @originalExtensionSettings;

  SET @newParameters = @originalParameters;


  — if they have supplied arguments ie: not NULL and not blank, process them

  IF COALESCE(@EmailTo,") <> "

    SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|TO|',@EmailTo);

  IF COALESCE(@EmailCC,") <> "

    SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|CC|',@EmailCC);

  IF COALESCE(@EmailBCC,") <> "

    SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BC|',@EmailBC);

  IF COALESCE(@EmailReplyTo,") <> "

    SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|RT|',@EmailReplyTo);

  IF COALESCE(@EmailBody,") <> "

    SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BD|',@EmailBody);


  IF @numberOfParameters > 0 BEGIN

    — process each parameter in turn

    SET @parameterCounter = 1;

    WHILE @parameterCounter <= @numberOfParameters BEGIN

      SELECT @parameterToken = ParameterToken,

             @parameterValue = ParameterValue,

             @tokenLength = LEN(ParameterToken)

        FROM @parameters

        WHERE ParameterID = @parameterCounter;

      SET @newParameters = REPLACE(@newParameters,@ParameterToken,@ParameterValue);

      SET @parameterCounter = @parameterCounter + 1;




  — Temporarily update the values

  UPDATE dbo.Subscriptions

    SET ExtensionSettings = CAST(@newExtensionSettings AS ntext),

        Parameters = CAST(@newParameters AS ntext)

  WHERE SubscriptionID = @subscriptionID;


  — run the job

  EXEC msdb..sp_start_job @job_name = @ScheduleID


  — make enough delay for the report to have started

  WAITFOR DELAY '00:00:30'


  — put the original extensionsettings and parameter values back

  UPDATE dbo.Subscriptions

    SET ExtensionSettings = CAST(@originalExtensionSettings AS ntext),

        Parameters = CAST(@originalParameters AS ntext)

  WHERE SubscriptionID = @subscriptionID;

  — finally we free up the subscription system for another person to use

  DROP TABLE ##ReportInUse;



EXEC dbo.ExecuteSubscribedReport

   @ScheduleID = '4CE38C83-6A03-4780-895A-92FD6F8FD5B0',

   @EmailTo = 'greg@sqldownunder.com',

   @EmailCC = 'ozinfo@sqldownunder.com',

   @EmailBCC = 'info@sqldownunder.com',

   @EmailReplyTo = 'greg@sqldownunder.com',

   @EmailBody = 'Hello Greg',

   @ParameterList = '|StartDate|,20071231,|Salesperson|,GE,|Region|,NW';