The Bit Bucket

Hi Folks,

The summit program committee have confirmed I'll be doing two sessions at the PASS Summit in Seattle in November. I've got upgraded versions of both "Avoiding Stored Proc Recompiles" and "A SQL Server DBA's Guide To CLR Integration" to cover SQL 2k8 changes. The first session also has a little more time allocated as a spotlight session.

I'm really looking forward to the summit this year and hope to see you all there. Summit details are here: http://summit2008.sqlpass.org/

Regards,

Greg

Fellow RD and colleague Ken Spencer posted a little while back that he was having good success loading VPC images off flash drives. I thought it was time to try it myself.

The first concept is that not all flash drives are created equal. I went and found fast ones. I ended up with Corsair Flash Voyager GT drives in 16G, which was just big enough to hold the VPC images I was working with. These are extra fast drives that have matched controllers and memory and are rated up to 34 MB/sec. In Australia, they are $119 AUD but I've seen them on US sites for about $63 USD recently.

Next concept is that I needed to reformat the drives as the VPC images were too large for the default FAT32 formatting. As I bought two drives, I tried one with NTFS and a cluster size of 4k and the other also NTFS but with 64k.

The last concept is that these drives are fast for reading but not writing. Virtual PC 2007 seems to insist that the undo disk is in the same folder as the vmc file so I placed the vmc file on my internal eSata 7200rpm 200G drive. That meant the undo disk was there too.

What can I say? The results show it's worth doing.

Operation	Internal Drive	4k Cluster	64k Cluster
Boot To Login	1:27	0:51	0:50
Desktop Appears	2:09	1:07	1:06
SSMS Usable	3:09	1:30	1:28

I haven't done extensive testing of it in use yet but booting twice as fast has to be a good thing 🙂 The cluster size made only the very slightest difference.

SQL Down Under show 39 featuring Kent Tegels is now available for download from www.sqldownunder.com.

In the show, Kent discusses the spatial data type in SQL Server 2008.

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

:SETVAR PrincipalServer WINSTD2K8BASE

:SETVAR MirrorServer WINSTD2K8BASE\SQLDEV02

:SETVAR WitnessServer WINSTD2K8BASE\SQLDEV03

 

:CONNECT $(PrincipalServer)

SELECT @@SERVERNAME;

 

:CONNECT $(MirrorServer)

SELECT @@SERVERNAME;

 

:CONNECT $(WitnessServer)

SELECT @@SERVERNAME;

I'm guessing you might not have expected:

WINSTD2K8BASE\SQLDEV03

WINSTD2K8BASE\SQLDEV03

WINSTD2K8BASE\SQLDEV03

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

:SETVAR PrincipalServer WINSTD2K8BASE

:SETVAR MirrorServer WINSTD2K8BASE\SQLDEV02

:SETVAR WitnessServer WINSTD2K8BASE\SQLDEV03

 

:CONNECT $(PrincipalServer)

SELECT @@SERVERNAME;

GO

 

:CONNECT $(MirrorServer)

SELECT @@SERVERNAME;

GO

 

:CONNECT $(WitnessServer)

SELECT @@SERVERNAME;

GO

 

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?

 

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.

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.

Rob Sanders pointed me today at the Political Compass. I wondered where I sat in the spectrum. Well this is where. I felt quite a cringe reading some of the questions. I wasn't surprised that I was more libertarian in my views than authoritarian but I was surprised to see myself slightly more to the left than the right.

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.

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:

 

http://silverlight.interknowlogy.com/Videos/VitruView/default.html

 

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

 

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:

C:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\FTData 

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:

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

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

WHERE CONTAINS(Description,'FORMSOF(THESAURUS,punter)')

ORDER BY MessageID;

Hope this helps someone get started with it.