The Bit Bucket

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?

2008-08-29

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:

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.  

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>.  

We have extensive experience developing using C++, Java and C#. Currently, our main development environment is the .NET Framework.

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:

2008-08-25

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.

2008-08-18

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.

2008-08-13

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:

2008-08-13

I’ve been doing another performance tuning job today and it highlighted to me once again that problem that a lack of indexes on foreign key columns can bring.

By adding indexes on the foreign keys on three tables, we saw a reduction of 87% in total I/O load on the server. There are other aspects of the system that I’m now working on but it really struck me that having SQL Server do this by default would avoid a lot of apparent performance problems. It would have to be one of the most common indexing issues that I see in my work.

2008-07-28

I’ve also been looking around for a new phone. It became much more urgent last week after I dropped my iMate.

After trying lots of phones, I ended up opting for an iPhone 3G. And after using it for a few days, couldn’t be happier with it. Well, not quite true, if it worked as a NextG modem as well, it would be even better as the data plans are quite costly here in Australia.

2008-07-22

Hi Folks,

SQL Down Under show 38 with guest SQL Server MVP Andy Leonard discussing database testing is now available for download from https://sqldownunder.com .

The show also includes a competition.

Enjoy !

Regards,

Greg

2008-07-19

I caught up with James Luetkehoelter at the PASS Summit in Germany a few months back. He sent me a copy of his new book from APress: Pro SQL Server Disaster Recovery .

I managed to finish reading it while heading back from CodeCampSA in Adelaide today (which was a good solid event again - excellent work Peter Griffiths!). I quite enjoyed the book and I like James’ writing style. It’s quite conversational and I could hear him talking to me as I read it.

2008-07-13

There are a number of key books that I’ve missed reading over the years, in areas that interest me. Recently, I’ve been fixing that. One that is always discussed is The Microsoft Data Warehouse Toolkit by Joy Mundy and Warren Thornthwaite from the Kimball Group.

I would have to say I enjoyed reading it. It is a large book at over 700 pages and a couple of inches thick so it took a while to get through.

2008-07-09