Firstly, I hope this is posted in the right area. Secondly, I have little to no knowledge of how to do this. The only thing I have as of now is a system DSN that I use to connect Access to Sybase. That currently works.
As far as what I have tried: I have a local instance of Sql server 2005 with windows authentication. I have logged into management studio, and choosen:
MyServer>Server Objects>Linked Servers> and right click "add new linked server". Now this is where it gets kinda hazy. I've tried to setup these settings on my own knowing nothing about them and failed. There was already a linked server created before I took on this project, but I tried to use it in a SELECT as below, and it fails.
I got the following messages
OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "Method is not supported by this provider.".
OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[Sybase][ODBC Driver]Driver not capable".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for the linked server "MyLinkedServer" reported an error. The provider does not support the necessary method.
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for the linked server "MyLinkedServer". The provider supports the interface, but returns a failure code when it is used
The Linked server that was created already has the following settings:
Linked Server: MyLinkedServer
Server Type: set to Other data source
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: SQL Anywhere
Data source: set to the name of my system DSN which I use to access Sybase from Access
Provider string: blank
I'm guessing location and catalog don't need to be configured because they are set in the system DSN that I am using. Oh, on a side note, the system DSN has a driver "Sybase SQL Anywhere 5.0". When I click to configure it it has a server name of the SQL Anywhere server that I am trying to connect to, and all the correct settings. It uses a file at startup "C:\sqlany50\win32\dbclient.exe" which is the client that fires up when I use linked tables in Access.
And the other thing is that I have a file called "dbsvmn50.exe" that fires up the database engine for the Sybase Database that I am trying to connect to and it is running.
Any help would be greatly appreciated. I can try and explain more, but as you can probably see from this post, I have no idea of what I am doing.