EtienneK

It took me the whole day to troubleshoot the error in the heading.

A client contacted me, yesterday, complaining that he can’t connect remotely to SQL Server 2005 Integration Services. The important word being remotely. When he tries to connect to Integration Services locally, it works. He had this problem with all remote servers.

I thought there was something wrong with his access privileges, so I followed the steps in this article: http://msdn2.microsoft.com/en-us/library/aa337083.aspx. Nope. Still didn’t work.

So I followed the steps in this article: http://mohansmindstorms.spaces.live.com/Blog/cns!69AE1BEA50F1D0E7!213.entry?action=post&wa=wsignin1.0. Nope.

By now I was pretty sure that there was some setting wrong on the client’s PC. The fact that I also could not connect remotely to Integration Services from his PC (even though I could from my PC) supported this claim.

So, using all the knowledge I’ve gained through the day from the hundreds of googles I did, I started comparing settings on my PC with the client’s PC. The problem? One little tick box that wasn’t ticked:

The screen, above, can be found here: Start -> Control Panel -> Administrative Tools -> Component Services -> Expand Component Services -> Expand Computers -> Right Click on My Computer -> Properties -> Default Properties.

After ticking the ‘Enable Distributed COM on this computer’ tick box, click ‘OK’ and restart the computer.

And so, after a whole day of googling, it was only one tick box that needed a tick. 7 hours of my life wasted.

I needed to construct a dynamic HTML document using Transact-SQL the other day. I googled a bit and found that the easiest way to write to a text file in Transact-SQL is to call the command prompt echo command. The problem, though, was that it’s not possible to echo the ‘<’ and ‘>’ characters with the echo command. Well, that’s not 100% true. To be more precise, it’s not possible to do something like the following:

echo <b>Hello World</b> > hello.html

Command prompt doesn’t like you using the ‘<’ and ‘>’ characters, because it uses these characters to redirect the standard input and output to other sources (like our hello.html file in the example, above).

This is how to make it work:

echo ^<b^>Hello World^</b^> > hello.html

Wasn’t that easy? Just use the ‘^’ escape character in front of the ‘<’ and ‘>’ characters when echoing in the command prompt. Obviously, we don’t want to escape the last ‘>’ character, because we want to send our string, outputted by echo, to hello.html.

And this is how it’ll look in Transact-SQL:

exec master..xp_cmdshell 'echo ^<b^>Hello World^</b^> > hello.html'

Easy.

As a final note, the ‘^’ escape character only works in the command prompt of the Windows NT family of operating systems.