June 30, 2010 at 11:03 am
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.
SELECT *
FROM MyLinkedServer.DatabaseName..TableName
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
Location: blank
Catalog: 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.
June 30, 2010 at 11:27 am
Hmm, well it seems to be working now. I simply changed the way I called the select statement. I read here that SQL Anywhere doesnt support the catalog name, i.e.
SELECT * FROM MyLinkedServer.CatologName.TableOwner.Table
Here, CatologName would be the database name. I just had to log into Sybase Central, look at the table and figure out the name for the owner and use
SELECT * FROM MyLinkedServer..TableOwner.Table
May 4, 2012 at 1:37 pm
Are you still around? Or does somebody else know how to solve this? I'm trying to do the same thing, also using SQL Server 2005. I'm getting this error-
OLE DB provider "MSDASQL" for linked server "ABS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "ABS" returned message "[Sybase][ODBC Driver][SQL Anywhere]Unable to start specified database: Access is denied.".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "ABS".
I'm wondering what other settings you had set up.
May 10, 2012 at 12:27 pm
Hey, I'm still around. I can't say that I know too much about this process as I got it working quite a while ago. From what I can see from the errors, it seems that the MSDASQL provider does allow you to talk to the SyBase database, so it seems like the ODBC connection process is OK. My guess is that "Access Denied" statement that this is an issue of credentials. Are you getting this issue when trying to query the linked server, or when trying to create the linked server? Maybe try to do this with the SQL server SA account. Or make sure that the user you are logged in as is also in a windows group that has a login to the sybase account.
May 11, 2012 at 8:21 pm
Thanks for the reply- you're right, it was a file permissions issue with the actual database data file that the odbc was using. I didn't know which windows user SSMS was using to call the odbc, so I gave full permission to the database data file and log file to "Everyone". We have no worries about that- it's just a daily backup copy of the database. My windows user and the System user already had full permission, so I still am curious which user it was using... The sql server database is actually on a different server than the one I was running SSMS from, so maybe the System user that had full permission was actually not the right System user...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply