Kathi K's SQL Server Blog

I'm still here...

I have been soooo busy the last couple of months, mostly with work but some home stuff, too. The biggest news is the birth of my first grandchild, Thomas Indiana Csar-Kellenberger.

I'm trying to get some writing in and have a couple of articles started. I remember the craziness of last summer when I wrote a two chapters for Brian Knight's book "Professional SQL Server 2005 Integration Services". Luckily, I was not involved in quite so many projects at work back then.

I hope that many of you get to PASS in November. I am moderating the Women In Technology Luncheon this year. It is always lots of fun, and we are honored to have Kalen Delaney on our panel. I will also be giving a presentation on beginning T-SQL.

Upcoming presentations

I will be giving a presentation on June 13th for the St. Louis SQL Server Users Group (http://www.stlssug.org/ )on SQL Server Management Studio. This will be 95% demos, and I will cover as much as I can within my alotted 90 minutes. On June 21st I will be giving a talk for the St. Louis Visual Basic.Net group (http://www.stlvbug.net/dnn/Home/tabid/52/Default.aspx) on what DBAs wish developers knew. Both talks will be held at the Microsoft facility in St. Louis. Be sure to check them out if you'll be in town.

DBA work -- both high and low profile

One thing about being a DBA, or at least the flavor of DBA that I am, is that there are many opportunities to impress my customers and boss by automating things or setting up cool reports. But much of what I do is behind the scenes and not too glamorous. Stuff that no one really knows about but that keeps the databases healthy and enterprise applications running.

I have recently been working on moving servers and service accounts to a new domain. Talk about tedious! Because I work so closely with HR and Accounting, I took responsibility for getting all of their servers and service accounts, not just SQLServer, migrated. Luckily, both departments have their own dedicated and talented IS staff so I was able to enlist their help to get each department's servers migrated. I managed to come up with scripts for some of the tasks, but it still is a lot of work and has to be done during non-production hours.

A couple of things that broke when changing the SQL Server 2000 start up accounts was SQL Mail and the SQL Server performance counters.

To fix SQL Mail, I basically had to just set up SQL Mail again by creating a new Outlook profile using the new account. I then chose the new profile for SQL Mail and for SQL Server Agent. Clicking the test button for each indicated everything was fine and doing my own test confirmed it. I didn't have to restart the SQL Server as I have had to in the past for some other SQL Mail problems. I try to avoid using SQL Mail if at all possible but still need it for at least one application.

I didn't think about the problem with the performance counters until a couple of days later when I needed to look at them. When I saw they were gone, I remembered that this had happened to me before and that it was easy to get them back. Unfortunately, the fix requires rebooting the server or at least restarting SQL Server. So, I had to wait until our short maintenance window Saturday morning to fix the counters. To fix this run the following from a command window

unlodctr mssqlserver

lodctr <path to sql server>\bin\sqlctr.ini

Then restart SQL Server. I actually rebooted the server since that is what I found when searching to find the answer to the problem. Today, I found a reference that said just to restart SQL Server http://www.extremeexperts.com/SQL/FAQ/EnablingPerCounters.aspx .

I'm really glad that the web has so many resources for DBAs like me who have to solve so many different problems. I don't know everything about SQL Server, but luckily, I usually can find the answer I'm looking for

Integration Services (SSIS) Import Wizard

I was using SSIS today to import a file with one wide column. I created the table first with a varchar(max) column. When I tried using the Import Wizard (found by right-clicking the database name and clicking Tasks -> Import Data) I kept getting errors about truncating data. It took me awhile to find the problem, on the step of the wizard where you configure the file data source, click the Advanced tab. There, it showed an OutputColumnWidth of 50. You can manually change this or click Suggest Types to have the wizard estimate the width. Clicking the button changed the value to 72. Then I was able to import the file without error.

So, what happens if you are trying to import a file with two columns? There was a definistion for each column on the Advanced tab, but clicking the Suggest Types button corrected both of them.

Reporting Services Goodies

I just upgraded my Reporting Services installation to 2005 a couple of weekends ago. I was happy to see that I could still run and edit my existing reports in VS 2003 so I'll just upgrade the report definitions as I need to tweak them or when I have time. I actually was hoping to do the upgrade on November 8th -- the day after the release, but things don't move that fast in our shop.

I just created my first production RS 2005 report using some of the new features. I was able to use multi-valued parameters, interactive sorting and fixed table headings. These are features that the report users have been requesting for a long time. Unfortunately, these features disappear when a report is delivered via a subscription. (If anyone has a way around this, please post a comment.) So, I am delivering just the link to the report for now.

I also really love that you can manage Reporting Services from SQL Server Management Studio. It seems so much more efficient than the web interface, especially when configuring security. I am also working on getting Report Builder going for some of my customers as well. It is pretty cool, but took a while for me to get it figured out. I hope to write some articles for SSC on it pretty soon.

Troubleshooting

Sometimes we all run into problems with programming or administering SQL Server and need help. When it happens to me I try to figure things out myself. With so much content on the web, usually I can find the answer pretty quickly. Sometimes, I end up posting my question on a forum like SQLServerCentral.com.

A funny thing usually happens. In order to post an intelligent question, I gather and organize all of the information that I know about the problem. Often, I'll think of a place to look, a log file for example, that I didn't think of before. Nine times out of ten, I will solve my own problem by the time I have my question typed out. I need to learn to go through this exercise sooner, before I am desperate enough to ask for help. I need to get over that too, there is no dishonor in asking for help. We're all in this together!

Sometimes, even posting the problem in the forum doesn't get me the answer. I recently called Microsoft support twice in one week! The fix for one of the problems was undocumented, some registry keys that the SQL Server startup account needs if it not in the admin group on Windows 2003. The fix for the other problem was actually documented, but I never found the article on my own. Again it involved giving the startup account permissions on certain registry keys when multiple instances have different startup accounts. It took several days, but once we figured out what was actually going on they had the answer.

I am lucky, since we are an Enterprise customer, the firm I work for already has paid for MS support. But still, I wouldn't call them unless I really tried to solve the problem myself first.

Even Aunt Kathi needs help now and then!