The Birth of YUKONJACK

By Bill Wunder

I was disappointed that I didn’t get to attend the SQLPASS Community gathering this year. Guess I should feel lucky that I made it to the previous two annual conferences and hopefully I’ll have the privilege to make it next year when I’m sure the RTM version of YUKON will be the talk of show. One fellow I work with found a way to get out there and he came home with the YUKON PASS Preview - aka beta 1. He’s a nice guy and willingly shared his copy. I had to see what all the fuss was about after all I had read from the PDC, the PASS Community meeting, in SQL Server Magazine, on the SQL Server community web sites.

First thing I did was try to make a copy of his CD so I wouldn’t have to keep bugging him about it. Unfortunately, when I tried to install from the copy, the install failed well into the process with an error that it couldn’t find the file:

\Setup\COM\Microsoft.SQLServer.Replication.NativeResourceStringLoader.netmodule

on the CD (say that three time real fast). I checked and sure enough, the CD burning software that had come with laptop had changed the file name to: \Setup\COM\Microsoft.SQLServer.Replication.NativeResourceStringLo.netmodule

The CD copy would not work so I just copied the original to my hard drive and was able to install from there. I almost missed the fact that I needed to complete a separate step to apply a .NET Framework v1.1 patch included on the CD for proper operation of the SQL Workbench. Since the first thing the install did was apply v1.2 of the .NET Framework I’m still not sure applying the patch is necessary, but I did it anyway.

Just like a kid on a rampage in a candy store I installed the beta on my laptop. Sure enough I found piles of candy everywhere, and quickly realized my eyes were bigger than my binary stomach. Within minutes of the install I had to do some real work. When I went to my SQL 2000 Enterprise Manager to check on a SQLAgent job the sweet YUKON overdose became immediately apparent. As soon as I clicked to expand the Manage folder on my production server Enterprise Manager shut down. I opened it back up, navigated back to that Manage folder, clicked, and Enterprise Manager disappeared. All I can say is it’s a good thing I have a KVM and old workstation under my desk.

Not to be deterred by a small obstacle like not being able to do my job after the beta install, I was able to came back and play around a little more. I opened the SQL Server Workbench and clicked around a bit. No problem figuring out what to do because I’ve been playing with Visual Studio .NET for quite a while and the interface is very similar. I could tell already that "Whidbey" wouldn’t have far to go to bring SQL Server and Visual Studio together. Only for a moment I remembered how little it appeals to me to put the administration tools on every developers desktop.

I cleared the negativity from my head and went back to the playground. Hmmm, still a few things that have “not been implemented”. That’s cool. That’s the difference between beta 1 and RTM, right. I opened up the SQL Server “YUKON” Books OnLine to try to get a better understanding of all the default tool bars since it wasn’t exactly like the Visual Studio .NET UI. Right away I discovered that the SQL Server Workbench and the Business Intelligence Workbench stepped into each other’s territory as compared to the SQL Server 2000 Enterprise Manager and Analysis Server Manager.

"If you are implementing a solution that uses SQL Server database services, or if you are administering an existing solution that uses SQL Server, Analysis Services, Data Transformation Services, or Reporting Services, you should use the SQL Server Workbench. If you are developing a solution that uses Analysis Services, Data Transformation Services, or Reporting Services, you should use the Business Intelligence Workbench"

I read about the SQL Server Workbench for a couple of minutes and then my attention drifted to the SQL Server Language reference. The first thing I looked at was Try-Catch. Perhaps my expectations were too much and as well it appears that

Try-Catch may not be fully implemented just yet. BOL remarked, “”Microsoft® SQL Server™ "Yukon" Beta 1 can only handle transaction abort errors.” This was the first time I’d realized that I had to be in a transaction to use this exquisite error handling mechanism. It will be sweeter if the final implementation can do Try-Catch for any batch or even better for any session without having to be in a transactional container. We’ll see.

Then, sure enough I had another “real work” issue to check out. I needed to open Profiler to see what was happening on a SQL 2000 machine. It took quite a while for Profiler to open and when it did, the “SQL Server YUKON beta 1” logo popup hit the middle of my screen and nothing else happened with profiler after that. After a little struggle - and a few unprintable thoughts – I did get Profiler closed down, but that logo popup stayed right there…, right in the middle of my screen…, always on top.

I realized I’d better try to get my laptop back to a functional state and reluctantly accepted that that meant uninstalling SQL Server YUKON beta 1. System performance was so slow that even before the un-install It took a little doing to reboot so I could get rid of that logo pop-up. The worst was it was just the right size so I couldn’t access the SQL Sever 2000 Query Analyzer save dialog. I had to loose a little work.

Once I rebooted, the un-install went smoothly with no immediate pain. When I opened up Enterprise Manager after the un-install I got only message about SQLDMO not being registered rather than the EM GUI I had hoped for. I rebooted again and still had the error so I reinstalled SP3a and then EM seemed happy. To date, the only thing I haven’t been able to recover from my SQL Server 2000 client tools is the color coding in my Query Analyzer. After all these years I still haven’t learned that too much candy will always give you a belly ache.

After getting my laptop almost back to normal my next plan was to resurrected the old P2-266 workstation in my basement with a build of Windows 2000 Advanced server and then install only the YUKON beta 1 on it. It did try my patience but finally I was able to again snoop around the YUKON beta a little more on this test setup I christened YUKONJACK.

I started again by opening the SQL Server Workbench but soon became distracted by the SQLCMD command line utility. WOW! SQLCMD, the OLE DB replacement for the isql and osql, includes the ability to pass parameters to scripts as command line variables or even as environment variables. The command line switches populate environment variables so there is an opportunity to write scripts more generically and use the environment variables specified when the SQLCMD is invoked INSIDE the scripts. SQLCMD can do more tricks than osql for sure.

For starters, SQLCMD’s -L switch lists the servers on the network. Very cool! Noticed that it cannot be used with any other switches and it leaves you in SQLCMD interactive mode (much like isql and osql interactive mode) even though the BOL remarks suggest that in the final implementation -L can be expected to automatically exit SQLCMD mode. Oops,

SQLDMD can be associated with a startup script. When you invoke SQLCMD a T-SQL script file will always be executed provide you have created the file and you have set it as the cleverly named environment variable sqlcmdini: an .ini file with T-SQL commands in it. Also, you can specify and launch a text editor for script files directly from SQLCMD. It defaults to the cumbersome Edit DOS utility, but again you can set and environment variable and make that the text editor of your choice. I'm thinking these features have some interesting possibilities when it is necessary to manage a SQL Server or a batch process over a slow WAN connection.

SQLCMD has a collection of internal commands that are awesome! You can change the server connection from inside the script or script file, retry if an error occurs, and change the file/output stream where results, errors, and performance trace results are sent. One T-SQL script could easily produce multiple output files and could throw any errors to the screen or what ever stdout stream you have defined.

Already I'm excited about the .NET based command line utility that will replace SQLCMD in what ever comes after YUKON.

Another command line utility that will be welcomed by many of us is scm. The scm utility is a command line tool to start, stop and pause a SQL Server service. Looks like you can even check to see if a SQL Server service is running with this little gem.

Next I plan to poke around a little more in the SQL Workbench, and then I want to check out the new DTS and the Business Intelligence Workbench. If the two WorkBenchs are as fun as SQLCMD I’ll be sure to tell you all about it.

Bill