We're still not ready to support sql 2008 (Company Infrastructure, vendors and DBA Group are a little slow), maybe this June or July we'll do the first sql 2008 implementation).

For me, most SQL Server migrations have been easy.  No mess, no fuss, hit the "easy button".  We even do the migrations the "hard" way...new servers, named instance, usually to a new clustered or consolidated environment using Polyserve or VM, and we separate all DTS (ssis) and User jobs out to an application (ETL) server.  As a DBA with 100's of servers it's a lot easier to let each application group manage their own jobs, off the sql server, than for us to handle the requests.  Usually the most difficult issue is connectivity, firewall and connection strings.  Of 170 instances we support very rarely is there a difficult issue, we've dealt with some odd performance issues here and there, but for the most part it's been smooth sailing (hah, I remember being up for several days straight monitoring a system with 5K tps and watching it crash every day, but that's the extreme end of the bell curve).

Finally we couldn't put it off any more, and it was time to tackle the elephant in the room. 

It's not a big Instance (5 databases, 60gb), but the challenges are big.  We're dealing with 88 DTS Packages and 45 sql agent jobs, Connectivity to multiple Oracle databases, AS400 and other SQL Servers, 60+ downstream consumers of data.  Excel Flat Files, Text Files, Web services, 3rd party vendor imports and exports.  Let the games begin.

I knew I was screwed when I de-attached the databases from SQL 2000 and attached them to sql 2005 and got the following errors:  "Attach database failed for Server x.  An exception occurred while executing a T-SQL Statement or batch.  Converting database x from version 539 to 611.  Microsoft SQL Server, Error 195. fn_convertdatetoint_notime is not a recognized function name.  Incorrect syntax near the keyword 'left'".  Ultimately we determined that this issues was caused by user functions in SQL 2000 setup in the master database, NOT Supported in sql 2005.  We also ran into isolated issues where there were tsql syntax errors in sql 2000 that did not throw any errors, but would not compile in 2005!

Linked Server Issues were also encountered.
   Setting up a linked server to the AS400 on 64 Bit windows
      Required a new version of the IBM iSeries driver
      Required a patch to the IBM iseries driver
      Required patching windows to install 64 bit odbc drivers
   Setting up a Linked server to SQL 2000 SP4
      Required patching sql 2000, http://support.microsoft.com/kb/906954
   Setting up a linked server to Oracle

Over-all the process took about a month (with 4 DBA's and other work), bulk of the work was the DTS to SSIS packages.  We did look at some 3rd party utilities, but they still left several hours work for each package, so we just did the brute force method and did them one at a time.  Currently the process will spend 2-3 months running in parallel and being QA'd, than onto production.

Hit the "next" button.

Recently ran into an issue with a linked server between sql server and an as400, using the iSeries access driver via ODBC. 

The linked server was not returning all the rows and it was not throwing any errors either.

There is an advanced settings option on the iSeries as400 setup that needs to be "unchecked", by default the option is checked and can lead to missing records.

Once it is unchecked, the query returns the correct counts or rows.

SQL Server 2005 to Oracle via Linked Server - OpenQuery Syntax with Datefields

For some reason the Oracle Linked Server in SQL Server 2005 is more "type sensitive" when it comes to data types.  We've had to resort to using OpenQuery instead of the 4 part linked server syntax and using conversions of the datatypes.  The unique part of using openquery is to get as much of the query to resolve on the host (oracle) side as possible, to do this you will need to become familiar with Oracle's functions

Between on two hard coded date fields:

Between on hard coded date field with System Date.

Message
Executed as user: {removed}. Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "{removed}". [SQLSTATE 42000] (Error 7346)  OLE DB provider "OraOLEDB.Oracle" for linked server "{removed}" returned message "ORA-01403: no data found". [SQLSTATE 01000] (Error 7412).  The step failed.

Once again, my most favorite.

This is from a sql agent job, in sql server 2005, that calls a stored procedure that uses a Oracle Linked server.  Now what really is strange, is that i can take that stored procedure and run it from a query window and it runs fine !  BUT NOT AS A JOB !

Stopped SQL Agent, still Fails.

Finally had to stop and start the ENTIRE INSTANCE.  I think this is overkill, but I didn't have hours to research and I was still in the maintenance window.  Not sure if this is related to the MS DTC Service or an Oracle issue.

This is on an HP 585, Windows 2003, 64 bit, Oracle 10g client.

%^&$#$#%$%

Burned again by the famous combination of Oracle and SQL Server linked servers, and a poorly configured SQL Agent job.

Scenario:

1. Job Runs every 5 minutes
2. Job Runs a standard T-SQL Stored Procedure
3. Stored Procedure makes use of Oracle Linked Server (full 4 part naming and Open query used)
4. Oracle goes down for scheduled maintenance
5. SQL DBA's forget to stop job
6. Job pages on-call (me)
7. Disable job until Oracle maintenance complete
8. Oracle maintenance complete.
9. Connectivity of linked server verified
10. Re-enable job
11. JOB Fails !
12. T-SQL Stored procedure runs from SSMS fine, but won't run as a job

Here is the poorly configured sql agent.  1st off the job output file is set to over-write, so know as I write this I can't quote the exact error, 2nd the system was set at the default job history retention, so I can't go into the job history to grab the error either.

Basically the error was something about reading a row from sysoledbservers or something.

The solution was to Stop and Start SQLAgent; now why SQLAgent, running tsql using a linked server would not work, when the sqm tsql would run just fine from SSMS is beyond me, which is why I wish i had the exact error, as I was going to call pss support, which i didn't do at 1am in the morning.