SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create linked server from SQL server 2005 to Sybase SQL Anywhere 5.0


Create linked server from SQL server 2005 to Sybase SQL Anywhere 5.0

Author
Message
loki1049
loki1049
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 301
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.
loki1049
loki1049
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 301
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


ztoddw
ztoddw
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 135
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.
loki1049
loki1049
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 Visits: 301
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.
ztoddw
ztoddw
SSC Veteran
SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)SSC Veteran (282 reputation)

Group: General Forum Members
Points: 282 Visits: 135
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search