Focusing on setup and development using SQL Server products from 2000 to 2008 and beyond. Also about anything geeky that compels me to write a blog entry.

Wednesday, September 30, 2009

Handling Layered Transitions

We've all been there. The company is adding a new domain and the PCs and servers must be moved to the domain. A new file server is replacing the old one, and everyone must be remapped to use the new server. The servers are being moved to an off-site data center, and all the external IPs must be rerouted. The company is moving offices.

Any of these transitions can cause stress in the office. In my case, we are going through all of the above.

So how do you keep your sanity and avoid self-destructing during these stressful times? Here are a few tips I am trying to follow.

1) Always remember the pressure that everyone else is feeling. This means you can't take things personally if emotions run high, as they are bound to do. Try to be the calm voice of reason.

2) Find out how you can help rather than complaining about mistakes. When everyone is overwhelmed by the pressures of the transition, it is inevitable that some details will be overlooked and some mistakes will be made. What is important is that you contribute to the solutions.

3) Put in the hours that are needed to get the job done. I like my home life as much as anyone, but these transition times are time limited, they don't go on forever. If you put in extra time to make it go more smoothly, and your co-workers do the same, you will all have a much smoother transition.

The bottom line to me is that major transitions are always tough, and sometimes you just have to gut it out.

Sunday, September 20, 2009

Logmein.com Ups and Downs

I have been using Logmein.com for about three weeks now to access my PC. As a quick overview, logmein.com allows you to remotely access your PC from any computer with an internet connection using a web browser. You install a local service on the PC that you are logging into which talks to the logmein server, thereby resolving your IP address even if you have a floating IP on a home internet connection like DSL.

This isn't anything new, and GoToMyPC.com seems to be the most heavily marketed player at this point. However, logmein.com offers a free option with limited functionality. Basically, you can login and use your PC but you can not transfer files or print locally.

You can register multiple PCs in your one website account, thereby simplifying your remote access needs.

Overall I have to say I am very impressed. Typically I have used Windows remote desktop to connect to my PC in conjunction with a VPN connection. Remote desktop is great but I hate dealing with the slow speed of the VPN, and the VPN drop outs.

With logmein, when I first login the screen resolution is sized to fit the browser and it maintains the remote PC's aspect ratio. This can be very handy for quick access to do something and then log back out. A complaint I have always had about remote desktop is that the screen will resize if you are logging in from a PC that uses smaller screen resolution than the remote PC. This can be a slow and sometimes messy process.

On the flip side, my beloved shortcut keys don't work through Logmein, while they do work with remote desktop. Honestly though, this is the main shortcoming that I have found so far. Overall, I'm a fan, and I think I will continue to be a user.

Thursday, September 17, 2009

Windows Shortcut Keys

Sometimes when I use Windows shortcut keys, it reminds me of the good old days of Wordperfect. Ok, I take that back. Trying to remember those hotkeys was miserable. That doesn't change the fact that there are a few Windows shortcut keys that I use every day and don't want to live without.

Here are a few of my favorites.

WindowsKey + E
I hate digging for Windows Explorer in the Start Menu or right-clicking the Start Menu to explore. Give me a quick Win+E and I'm in business.

WindowsKey + R
Quick access to the Run... box makes for a quick "cmd" or "regedit" or whatever the occasion calls for.

WindowsKey + L
I usually Lock my PC when I wander away for lunch or at the end of the day. While you can get there from CTRL + ALT + DEL, this is even faster.

CTRL + SHIFT + ESC
Fast access to the Task Manager so I can see which program is hammering the CPU or eating all the memory.

CTRL + ALT + DEL
The old classic which I mainly use for a password change.

Feel free to comment with any of your favorite shortcut keys.

Monday, September 14, 2009

Domain Move Part 2

After the first attempt at moving a SQL 2005 Server from one domain to another, I learned several lessons to help me make the permanent transition to the new domain. Here are a few of the key things I checked and/or changed.

- Verify that the new DNS server has entries for the short names of all servers that SQL Server needs to access. I found that several servers would resolve to [servername].[full domain name] but not to simply [servername]. I had to request CNAME entries for all the resources that required (due to extensive existing code) the ability to access the server through the short name.
- Make sure all services are running under local accounts.
- Make sure the Reporting Services execution account is running under a local account.
- If any objects are owned by domain level accounts, change ownership to local accounts.
- If any SQL Agent Jobs are scheduled to run under domain accounts, change them to local accounts.
- Check SSIS packages for any domain account ownership and change to local account.

I still had a few issues, but for the most part I was able to change one SQL 2005 Server from one domain to another and add a SQL 2005 and a SQL 2000 server to the domain from a workgroup with little trouble after checking the previous list and making changes as needed.

If you have any more tips, please feel free to leave comments to flesh out the topic.

Thursday, September 10, 2009

FILESTREAM Article on SQLServerCentral.com

One week ago, an article I wrote on FILESTREAM in SQL 2008 was published on SQLServerCentral.com. This was the first time I had written for a professional publication, online or in print, and I want to thank Steve Jones for giving me the opportunity.

In case you missed it, here is a link to my article.
http://www.sqlservercentral.com/articles/FILESTREAM/67668/

I highly recommend that those of you with any inclination toward documentation or training start writing articles. It not only forces you to dig deeper on a subject than you may have done otherwise, but it also helps to establish you as a professional in the field. I know we all have busy lives, but I think it is worth it. Only time will tell.

And speaking of Steve Jones, check out his recent blog entry on Technical Article Writing where he lists several good places to submit your work.

Wednesday, September 9, 2009

T-Mobile's Cheap Internet and Mobile Blogging

I am using a 30 day trial of T-Mobile's web plan for cell phones. This plan includes unlimited web access but does not include downloads or a text plan. The up side... it only costs $10/Month.

So far I am very pleased. In fact I am writing this blog on my Shadow phone at a time that would usually be lost to simple waiting. Since I am used to the two letters per key typing method, I can crank out the words at about 60% of keyboard typing speed.

The strange thing is that T-Mobile limits this cheap web plan to phones that it considers non-smart phones. If you have a G1 you have to use at least the basic $25/Month data plan. Even stranger is the fact that they don't consider a phone like the Shadow, which runs Windows Mobile 6, a smart phone. But hey, I'm not complaining.

This little phone is still not as efficient as the ever popular netbooks, but on the flip side I always have my phone. Mostly I think it fulfills a role that I require within the ever broadening array of Internet enabled devices.

Thursday, September 3, 2009

SSIS Packages fail on ODBC connections after .NET Framework 3.5 SP1 or 2.0 SP2

If you work with ODBC connections through third-party ODBC drivers such as MySQL, you may have already experienced the pain of this Windows Critical Update. This is my tale of woe.

I was innocently applying critical Windows Updates to one of my SQL Servers, one is the .NET Framework 3.5 SP1. I read the details and saw that it also includes SP2 for both .NET 2.0 and 3.0, but still it is a critical update. As always, I there may be some bumps in the road but I figure I will just work through them. Updates applied. Basic database and reporting all test fine. So far so good.

The next morning, all of my SSIS packages that pull data through ODBC connections have failed, and as a result all of my subsequent scheduled reporting has also failed. That is what I call having Monday on Wednesday.

I know what happened due to my daily email report of all jobs from the last 24 hours, sorted by the errors first. In checking my SSIS log files, I find this error:

OnError,[Server],[UserAccount],[TableName],{GUID1},{GUID2},9/1/2009 11:15:48
PM,9/1/2009 11:15:48 PM,-1071607767,0x,SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source"
(31)" failed because error code 0x80131937 occurred, and the error row
disposition on "output column "[ColumnName]" (1848)" specifies failure on error.
An error occurred on the specified object of the specified component.
There may be error messages posted before this with more information about the
failure.


After some research, I find that this is a known issue on an msdn blog. It seems that .NET Framework 2.0 SP2 changes the way that the OdbcDataReader checks for Nulls, resulting in compatibility issues with some ODBC drivers. It impacted both the MySQL and CVIEW (for ISAM connections) ODBC drivers that I was using to pull in all my data.

As explained in the excellent blog post, the preferred option is to add error output to your SSIS packages, thereby handling the bogus errors. Unfortunately I have six or seven packages with twenty or more DataReaders in each package, so it would not be a quick fix. The blog mentions option #2 is to roll back your updates, which for me was the only option. I had production people waiting.

Due to the 3.5 constraints, I had to remove .Net 3.5 SP1, then .Net 3.0 SP2, and finally .Net 2.0 SP2. Then, because 3.0 and 2.0 updates are slipstreamed into the 3.5 SP1 update, at that point I had zero .Net frameworks installed. I had to reinstall .Net 2.0 and SP1, which is all I need for my SQL 2005 and related functionality. It took roughly 20-30 minutes to uninstall and reinstall along with reboots, not including any download requirements.

Afterward, I started playing with the website and realized the IIS default page was not even available. I looked at properties in the default website, looked at the ASP.NET tab... bingo, the ASP.NET version is set to... blank. I click the drop-down and see .Net 2.0 is available, but knowing that the scope of the settings goes far beyond this setting, I went back to the command line.

I knew I needed to run regiis, but I don't do this frequently so I searched, found a refresher, and ran the following command:

C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\Aspnet_Regiis -i

Because I am on a 64-bit server, I was wroking in the Frameword64 subdirectory, but if you are on 32-bit, it is the same but you browse to the Framework subdirectory.

Now my baseline ASP.NET is configured in IIS. I browse to the "/reportserver" directory in IE8 and get a (403) Forbidden error. That's an improvement.

I looked around in various places at various settings for a bit, just trying to help me think. In the Reporting Services Configuration Manager, it hit me that I had just made a mess of IIS and in the process the virtual directories had been through the ringer. So under Report Server Virtual Directory and Report Manager Virtual Directory, I checked the "Apply default settings" checkbox and applied the changes.

Boom, back in business. Reports were up and running and I was able to run my SSIS package to generate all my scheduled reports. Then I remembered one other little ASP.NET application that I wrote for managing the report schedules. Tried to pull it up in the browser and it didn't open.

After looking at IIS settings for a while, I finally found the problem. So I right-click on the Virtual Directory, go to Properties-->Documents tab and default.aspx is not in the list. In the process of resets, apparently the default content page list had been reset on my virtual directory. I added default.aspx to the list and boom, everything is back up and running.

I hope that at some point Microsoft makes allowances for this issue in their .Net updates but in the meantime, doing without .Net 2.0 SP2 is fine by me.