ODBC Linked Server & DSN

[Darren Gosbell [MVP]]

On Fri, 12 Jan 2007 14:11:04 -0800,
***@discussions.microsoft.com wrote:

You will need to setup the driver and DSN on the SQL Server machine.

When you issue a query to a linked server, the client machine only knows
that it is talking to a SQL Server. The SQL Server then makes the request
to the remote data source. So the SQL Server has to have all the required
drivers, DSNs and privileges required to access the remote data source.
--
Darren Gosbell
SQL Server MVP

Hi,
For linked servers to ODBC DSN data sources, does the DSN have to be

setup

on the same windows server as the sql server 2005 instance? Or is only
requirment that the DSN is setup from the workstaing that is luanching

SSMS?

I'm attempting to setup a linked server in SQL Server 2005 to an ODBC
datasource which is Centura SQLbase database.
On my workstation, I have a DSN setup with the odbc drivers to connect to
the SQLbase db. The driver was "Centura SQLBase 3.60 32-bit Driver -NT &
Win95"
I can successfully import that tables from SQLBase using Access.
I'm using the SQL Server Managment Studio from my desktop to connect to

the

SQL 2005 Server on a Windows 2003 Server.
When testing the Linked server by using a query, I use the following

syntax

SELECT LastName, FirstName FROM LINK2SQLBASE.COMPANYDB.dbo.tblEmployee
*OLE DB provider "MSDASQL" for linked server... returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no

default

driver specified".
*Msg 7303, Level 16, State 1, Line 1
*Cannot initialize the data source object of OLE DB provider "MSDASQL"

for

linked server...
Again, i'm using the SSMS from my workstation that has the DSN setup and
works fine when importing from access. I do NOT have the DSN or drivers

on

the actual windwows 2003 Server where the SQL Sever 2005 instance is on.
Is that my promblem? Do I need the DSN on the actual windows 2003 server,

or

should I be albe to use the DSN from my workstation?
Thanks in advanced!

[MOOOOVE]

Great advice. How do I implement this solution?

Post by Darren Gosbell [MVP]
On Fri, 12 Jan 2007 14:11:04 -0800,
You will need to setup the driver and DSN on the SQL Server machine.
When you issue a query to a linked server, the client machine only knows
that it is talking to a SQL Server. The SQL Server then makes the request
to the remote data source. So the SQL Server has to have all the required
drivers, DSNs and privileges required to access the remote data source.
--
Darren Gosbell
SQL Server MVP

Hi,
For linked servers to ODBC DSN data sources, does the DSN have to be

setup

on the same windows server as the sql server 2005 instance? Or is only
requirment that the DSN is setup from the workstaing that is luanching

SSMS?

I'm attempting to setup a linked server in SQL Server 2005 to an ODBC
datasource which is Centura SQLbase database.
On my workstation, I have a DSN setup with the odbc drivers to connect to
the SQLbase db. The driver was "Centura SQLBase 3.60 32-bit Driver -NT &
Win95"
I can successfully import that tables from SQLBase using Access.
I'm using the SQL Server Managment Studio from my desktop to connect to

the

SQL 2005 Server on a Windows 2003 Server.
When testing the Linked server by using a query, I use the following

syntax

SELECT LastName, FirstName FROM LINK2SQLBASE.COMPANYDB.dbo.tblEmployee
*OLE DB provider "MSDASQL" for linked server... returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no

default

driver specified".
*Msg 7303, Level 16, State 1, Line 1
*Cannot initialize the data source object of OLE DB provider "MSDASQL"

for

linked server...
Again, i'm using the SSMS from my workstation that has the DSN setup and
works fine when importing from access. I do NOT have the DSN or drivers

on

the actual windwows 2003 Server where the SQL Sever 2005 instance is on.
Is that my promblem? Do I need the DSN on the actual windows 2003 server,

or

should I be albe to use the DSN from my workstation?
Thanks in advanced!