Wednesday, June 13, 2012

SQL 2012 Upgrade Gotcha: SQL 2000 Not Supported in Native Client

We're only a few days from our SQL Server 2012 upgrade and a new issue pops up. Apparently, the SQL Server Native Client 11.0 (the one that ships with SQL Server 2012) does not support SQL Server 2000. I found this out when I ran a script to test the processing of all our Analysis Services databases.


We still had two old analysis DBs that query application databases directly (bad idea) and those application databases are on a SQL Server 2000 machine. Here is what SSAS told me when it tried to process:
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Client unable to establish connection; 08001; SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.; 08001.

Now that wasn't very nice and, being so close to Upgrade Day I panicked. I ran off to find out how important the two databases were. It turns out that one was not very important but the other was pretty high profile, despite it's age (or maybe because of it). So then I ran off to find someone that could implement a workaround on short notice. I figured we could stage the data in a SQL 2012 database or something so the analysis database could get to the data somewhere.

I finally came up with a better solution that involved far less work. Due to permissions issue, the developer that was going to help me couldn't do anything. While we were waiting for his access to the source database, the solution fell in my lap. (I vehemently deny that any voices in my head were involved.) It's so simple I was almost too embarrassed to post it in public.

All you have to do is change the data source to use a .Net Provider (SqlClient Data Provider) instead of the Native OLE DB provider. The .Net Provider probably doesn't perform quite as well as the Native OLE DB but it was definitely a lot less work that having to build a bunch of ETL to stage the data and a lot less risky too.

Did this help you? Please let me know in the comments!

We're doing our upgrade this weekend so I probably won't have any new posts until next week. I will continue the series on the SQL Server 2012 Upgrade then! In the meantime, check out Part 1.

10 comments:

  1. No luck with this approach. When the package is deployed, the connection gets updated to use the lastest driver version. Any guidance?

    ReplyDelete
    Replies
    1. The solution I mentioned works for SSAS connections. I haven't tried it with SSIS but it should work. Unfortunately, if you switch your SSIS data source to a .NET source you might have to fix the plumbing between the data source and the first transformation.

      Delete
  2. Thx! You have saved my day.

    ReplyDelete
  3. I ended up using ODBC for the Linked Server connection.

    ReplyDelete
    Replies
    1. ODBC worked for me too

      Delete
  4. Thank you. It worked!

    ReplyDelete
  5. Thanks, it's working perfectly..

    ReplyDelete
  6. Good post. Helped me out. Thank you.

    ReplyDelete
  7. Thank you - this saved me time.

    ReplyDelete
  8. Thanks, solution still works like a charm with legacy SQL Server 2000 connections.

    ReplyDelete