Thursday, September 29, 2005 - Posts
It’s been a while since I’ve seen Ron speak, so it’s great to have the opportunity again. (He was ever so slightly flat, but he’s just coming off a cold so keeping up the volume was hard.) This session is about SQLCMD, the SQL Server 2005 successor to isql and osql. osql is still around but it uses the ODBC driver and doesn’t support the new data types.
SQLCMD is written in .NET 2.0 and uses the SQL Native Client and ODBC to communicate with SQL Server. It provides what looks to be a pretty rich scripting environment for doing all kinds of complex tasks on the server, with some basic scripting-type error handling.
It has various built-in variables, such as SQLCMDSERVER, referenced as $(SQLCMDSERVER); these are not available if you are running the script in Management Studio in SQLCMD mode. All the built-in variables start with “SQLCMD.” Windows environment variables are readable as $(path). Cool!
This is the order of precedence for variables with the same name, from lowest to highest precedence:
- Environment variables defined in Windows
- Variables defined by -v on the command line
- Variables defined by :SETVAR within the script
SQLCMD has three modes for use:
- Within Management Studio. Here it has an interesting feature where Studio can optionally detect and highlight SQLCMD statements, which are prefixed with a colon. There is a SQLCMD mode button on the toolbar that highlights it and lets you run the script.
- Interactively, as a query client tool.
- As a command line utility. You can pass in parameters to a script from the command line, using the -v switch. -i specifies the file to run.
[Ron uses VMWare instead of Virtual PC. I’ve been going back and forth between the two. I’ll have to ask him why he uses VMWare.]
Some nice features for defining variables that survive batches (delimited with GO) within a single SQLCMD script, although the contents can only be literals, sort of like a one-pass macro expansion. Be careful of the macro expansion: it happens once per batch, so you can easily have one expansion overwrite another because the code doesn’t run until all the macro expansions happen. IOW, they are not dynamically evaluated. Look at SQL Profiler to make sense of all this.
You can connect to multiple servers within a single script, but only one connection can be active at a time.
There are a bunch of internal commands, including :SETVAR to set variables, :CONNECT to connect to a server, :OUT to direct output to file or stderr or stdout, :XML for reading XML output, and others.
Error handling uses “:ON ERROR EXIT|IGNORE.” Lots of osql commands are still available, and with some you have to prefix with a colon, others are optional, others, such as GO, you cannot. There are some consistency issues here, alas.
Some parameters have been dropped: -n, -D, -O. isql and DB-Library are no longer supported. If you copy isql to a SQL Server 2005 box, it will fail. Bring over ntwdblib.dll over as well and put it in System32; no need to register it. (Oooo, the price of the session was just covered in full!)
-z sets a new password for the login, and -Z does the same and exits.
Dedicated Administrator Connection (DAC) is very, very cool, which you can run from the command line using the -A option. Gives you a high priority session so that even if the server is no longer responsive you can access them. You must have sysadmin rights and only one session can be active. Only available through TCP/IP, and it’ll use encryption if that is required. Local DAC is on by default except for clusters; remote DAC must be enabled. So a word to the wise: enable it now if you’re going to use it. No local DAC is available for a cluster. Set using “sp_configure ‘remote admin connections’”. You can register a server with a prefix of “Admin:“ to specify that you always want to open a connection with a DAC. There are several best practices, but I’ll need to absorb the details of DAC before being able to grok them.
Cool! Copying and pasting commands into an interactive SQLCMD command line session runs all the commands.
QUOTED_IDENTIFIER is off by default.
Ron does a nice job setting up his demos, with lots of instructions about how to run stuff, since he’s going back and forth between Management Studio and the command line to show various features. If you have access to conference demo files, check them out.
SQL Agent’s support for SQLCMD scripts “is a bit uneven now.” Supports :SETVAR but not :CONNECT, :ERROR or :ON ERROR.
Nice job, Ron! Thanks for the info. Now go get some rest and finish getting over your cold.
Dave Campbell has started his keynote, talking about moving to SQL Server 2005, answering the why, when, and what. Wow, his first set of features he mention are Trusted Platform, the security features that I see as the most compelling reason to move to the new version. That’s been something that not many people have talked about yet, at least in yesterday’s keynote and the sessions I attended (the SQLCLR mentioned it just in passing).
His Top 10 Reasons to Upgrade was good. Best was “So Kalen and Itzik can publish their ‘Inside’ books”. A very good hint, you guys! He went through them too fast for me to record them all, but I hope they’ll be published somewhere.
Server Status Reports
Oooo, the Configuration Changes Report is nice. Hmm, but probably not for me as a developer. Now a DBA can actually, by default, monitor what I’m doing on a server, without doing any setup ahead of time. The angst between DBAs and devs gets uglier….
Seriously, these are some nice reports you can get in Management Studio to monitor the health of the server, or the database if you select it in the Object Explorer, way better than what you could get in Enterprise Manager. The only downside: it’s going to make those Oracle dweebs more fuel to critisize how SQL Server DBAs are GUI hacks. Nyaa, nyaa! (And it’s going to reduce the need for many third party products for this kind of monitoring, alas. Yet another reason I’m glad I’m not into Microsoft add-on products, since they’ll just add it to the next version.)
Migration
Very gutsy migration demo. Dave found five attendees to be on-stage guinee pigs for upgrading to 2005. They have their laptops on stage and will do a live conversion. It’s all to show off SQL Server 2005 Upgrade Advisor. I hope it’s better than the conversion tools in Visual Studio. (Heh. I suspect that upgrading databases is more straightforward than random code, but we’ll see. And what security state does it leave the server in?) You can download it from somewhere on microsoft.com. It doesn’t require SQL Server 2005 but does require .NET framework 2.0.
The advisor analyzes the server and takes a few minutes to run. (Yikes, one of the people on stage has 12 kids, shown on his desktop. That led to the best joke of the conference so far: If he was an Oracle DBA, he’d only have three kids. LOL!) Anyway, the advisor looks to be well-designed, with lots of feedback about what it does and doesn’t check, issues you need to know about both before and after upgrading. It’s cool that it catches syntax changes that are deprecated, such as outer join operators *= and =*.
Hmm. SQL Server 2005 Agent doesn’t support SQL authentication? I’ll have to think through the implications of that, but I guess it’s more of a DBA issue. Something to be aware of though.
It’s nice that they found five people with vastly different types of applications that use lots of different SQL Server features.
Check out www.sqlserverchopper.com …it’s a site that is loosely based on the OC Choppers show where you might win an OC motorcycle when you migrate from Oracle to SQL Server 2005 and tell Microsoft about it.
Wow, customers have seen 11x speedup in stored procedures converted to CLR??? That is going to be a compelling reason to move more stuff from T-SQL. I’ll have to find out more about this statistic. Were the procs likely candidates for CLR based on what Microsoft has been recommending?
When?
He didn’t spend a huge amount of time on the when question, in this SQL Server-friendly crowd. But it’s telling that Microsoft is eating its proverbial dogfood, having moved to 2005 for many of its critical apps. No report if it’s caused any disasters, but maybe we’ll hear when they file their next SEC reports. Heh.
What?
The first demo of what is cool in 2005 is a demo of using a smart card as the key for data encryption. That’s a great use of encryption in SQL Server, allowing per-user encryption for secure data. Very cool use with the smart key features of Windows. You’d need to carefully consider how to handle lost smart cards and the need to change the smart card key to re-encrypt the data. But that is a small issue given the huge benefit of properly using encryption. Encryption isn’t as easy as Microsoft would have us believe based on simple demos, but the infrastructure is great.
Good keynote. SQL Server 2005 is very cool, ever more a compelling upgrade. On to sessions!
I just found another reason why Google’s Gmail is just too cool. I’m still not using it as my sole email client, since I’m so very dependant on Outlook, particularly it’s disconnected features. Besides, I don’t like the idea of all my mail on Google because of privacy issues.
But whenever I travel outside of the IP range of my mail account servers, I can’t send email using Outlook since no one—rightly—allows relaying. So I have to reply using Web mail, which I hate using since spam is so nasty, and I end up having to copy stuff from Outlook.
But with the POP and SMTP access to Gmail, I can both read and, most importantly, send email through Outlook.
Very, very cool, and a big relief!
It’s Thursday morning at SQL PASS, the second day of the main conference. I’m feeling much more human today than the last couple of days, mostly because my suitcase finally came! American Airlines decided that Dallas just wasn’t a nice fit for my Eagle River bag, and decided to ship it to Oklahoma City instead. And then took way more than 24 hours to get it to me. The only thing that I missed was my power brick for the laptop, so I quickly exhausted computer power. I don’t always put it in my luggage, and now will never again.
Anyway, it’s been fun seeing friends and geeking out on SQL Server. Here are a few observations:
- Since SQL Server 2005 is so close to RTM there haven’t been any earth-shattering announcements of any note. Bill Baker’s keynote yesterday was interesting, but full of marketing stuff and relatively light on technical details. Fun, though, since he has such a wealth of experience.
- Wow, people are still using SQL Server 7. I talked to a guy at lunch yesterday who is looking to migrate directly from 7 to 2005. I wonder how hard that will be? Maybe I’ll stop by the PSS room today to see if they have had any customers do the migration and if there were any issues.
- The choices in the application development track are a little light (but this really shows my bias more than a criticism of the program committe, who overall I think did an amazing job). I’m really geeking out on SQLCLR integration, and have gotten some great information, particularly about changes in the September CTP. Security issues are still a bit confusing, but I think it’s really just because I’m getting my head around it.
- I’m absolutely amazed at how willing we geeks are to wear logowear in the remote hope of winning some cool prize. Apparently today HP is giving some great prize, because the keynote room is a sea of blue with the HP logo on the back. Sheesh. And too many exhibitors are raffling off various iPod models, but you have to be present to win. Bleech.
- It’s been great seeing friends, among them Kent Tegels, Kim Tripp, Kalen Delaney, Roman Rehak, Jean-Rene Roy (who runs the great DevTeach conference in Montreal), and a bunch others, too numerous to remember this early in the morning.
- There is apparently another Fairbanksan here, Jane Vohden, who works at the Fairbanks North Star Borough. It would be fun to meet her, but I haven’t bumped into her yet. Not surprising, since there are some 2,000 attendees here.
- It’s been fun to meet SQL Junkies here, particularly regular readers. Wow…people are reading my stuff? ;-)
My only session is Friday afternoon and I’m ready for it, so it’s been fun to just attend sessions and hang out in the hallways and exhibit hall. One deadline that I finally got done—other than mailing it off, which I’ll do later this morning—is to nominate the Delta River in Interior Alaska as one of 2006 America’s Most Endangered Rivers. It’s a bit distressing that a river in Alaska qualifies, but hopefully it will be nominated and draw attention to the threats it faces from hard rock mining in the area.
Aargh. I’m sitting in the keynote room, and the opening speaker is from HP. I’m sure it’ll be a fascinating talk, but I’m not a hardware guy. Later Dave Campbell from Microsoft will be on. He’s someone I’ve been hoping to meet here, since I rewrote an academic paper of his as a whitepaper for Microsoft. Unfortunately, his session on the topic is during my security session tomorrow. Oh well.