August 6, 2007 at 7:39 pm
Pardon my ignorance but I've been at this for over a week and finally had to ask for some help.
I'm pretty proficent with SQL and linked servers through different databases, but this Sybase has me stumped! I've tried hundreds of different ODBC and DNS configurations, some of which produce different errors, but none that work. This is my first experience with using linked servers and connecting to a Sybase DB file.
Here's the scenario;
I have a simple Sybase db file that contains 1 table. The db is password protected.
I downloaded the SQL Anywhere 10 developer client, which proceded to embed itself into my Visual Studio (but that's another story). This is installed on the exact same machine as my SQL Server 2005. I can open up Sybase Central and connect to my DB file in SQL Anywhere directly or through an ODBC connection I've created.
Through a lot of searching on the web I found a set of instructions that presented some results that seemed close. I created an ODBC and when creating the DSN I just gave the Data Source Name and User Name/Password (not specifying the DB file) using the Sql Anywhere 10 driver.
When creating my linked server, I specified to use the DSN I created in the Product Name section. I then specified the location of the db file in the Data Source section. Security was set selecting "Be made using this security context" user: sa pass: <pass> . In options ONLY Collation Compatible and Data Access are selected.
When I run a query against this linked server I get the following error
OLE DB provider "SAOLEDB.10" for linked server "B542_MovexSample" returned message "Parse error: DSN 'c:\movex\b542_movexsample.db' does not exist".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SAOLEDB.10" for linked server "B542_MovexSample" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SAOLEDB.10" for linked server "B542_MovexSample".
so, this is just one of MANY different configurations I've used, but one that actually provided something other than a DSN or OLE DB driver error.
Oh yeah and that IS the location of the DB file. Not sure why it says it doesn't exist? Authentication is set in the DSN so I'm not sure why it's failing.
I see a lot of references to Sybase ASE and other providers that I don't have installed. I know the ODBC connection I have configured works, since I can connect and query in SQL Anywhere...so here's the question. Am I missing something? Do I have the proper providers. I mean, all this is a simple .DB file with a table that has maybe 50 records in it.
My main processing needs done in T-SQL and I just want to query the DB file for certain info. It's not rocket science, but this thing sure is giving me a run for my money. ANy help would be appreciated. Like I said, I've tried 200 different configurations and most produce the same driver type errors, especially if I use OLE DB Simple provider instead of SQL Anywhere 10 provider. I see references to DSEDIT out there, but that's nowhere on my machine which makes me ask the question..am I missing something?
Surely connecting to a simple .DB file can't be this hard? Someone please give me a simple solution to creating a Linked server to a .DB that'll work. Explain what drivers are needed and how to configure. Everything is local on my machine, so it's not like I'm working with netowrk issues either
Thanks in advance
August 8, 2007 at 3:26 pm
Its been a few years since I worked with Sybase, so this is really just thinking out load, but ...
When you create a linked server, you're actually connecting to the database engine (of whatever flavor), not the database, the engine then presents the database. If you're trying to link to the local .DB file, what is the engine that is going to present the database?
August 9, 2007 at 5:30 am
SQL Linked servers uses OLEDB.
Create a test.udl file on your box, use that to create an OLEDB connection string. And create a Linked server with that.
Check sybase's documentation for more information on the same.
July 5, 2014 at 11:14 pm
Unfortunately Microsoft requires .
you to have a Ph.D in linked servers
The tweaks they suggest to create a linked server is so convoluted. it is amazing. I an not new to Linked Servers but it is really demanding.
I tried to create a linked server in SQL Server 2012 to SQL Anywhere 16 which is SAP's latetest. But no go.
Follow this link to appreciate my frustration.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply