I just had a few wearying hours of troubleshooting a Sybase linked server issue on SQL Server 2005. I used most of the troubleshooting techniques that I know, so I figure it's a pretty good illustration of troubleshooting in general.
Basically, I needed to import some data from Sybase into SQL Server. Previously, there was a separate extract done from Sybase to text files, but there were issues with that, so I wanted to create a linked server to the Sybase server, and use that instead.
Installing the Sybase drivers was easy. Creating the ODBC data source went well, I was able to test that the connection worked in the ODBC data source administrator tool.
Setting up the linked server in SQL Server - that's where the problems started. I had numerous issues, and finally found an article with some basic information on Sybase linked server setup in SQL Server, which helped somewhat. But I had all kinds of problems that weren't addressed in the article. The biggest hurdle was the error "The OLE DB provider "MSDASQL" has not been registered". It was tough finding the right way to register the dll, I went down many dead ends. Finally I found the correct tool to download - the 64-Bit OLEDB Provider for ODBC (MSDASQL).
Once I'd gotten past that hurdle, I ran into issue with the Sybase error "Could not load code page for requested charset". I found some information on this fairly quickly here, but the data wasn't complete and I had to do a fair bit of research on which charset was appropriate to use.
Then finally, I figured out the correct way to set up the ODBC DSN to use the appropriate charset and...success. I was finally able to get data from Sybase into SQL Server via the linked server!
So, what pieces of advice can I glean from this experience?
- Persistence is your friend. Keep at the problem, attack it from different angles. I didn't even write about all the dead ends I came across when working on this, but there were lots.
- Research is key. Search for the exact error string on Google, search with more generic terms. I also found it useful to search company websites (i.e. the Sybase knowledge base), because many times the information on these sites is NOT indexed in Google. The totally number of distinct Google searches I did related to this issue was about 30 - and that's just the searches, not all the reading I did.
- Simplify. Get the basics working before adding complexity. I made sure that before I tried connecting via a linked server, I could connect via the ODBC DSN. And before connecting via the ODBC DSN, I tested the connection via the Sybase tools.
- Take a rest from the issue! My breakthrough moment came when I walked around the office to a window that had a view, and relaxed a bit. My mind was working on the problem subconsciously and then WHACK - a moment of insight!
Good luck!
