ODBC Linked Server & DSN

Hi,
Short version of question:
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?
Longer Background for question:
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
in a query in SSMS:
SELECT LastName, FirstName FROM LINK2SQLBASE.COMPANYDB.dbo.tblEmployee
I receive an Error:
*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!On Fri, 12 Jan 2007 14:11:04 -0800,
labsRcoolcommunitynospan@.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,
> Short version of question:
> 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?
>
> Longer Background for question:
> 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
> in a query in SSMS:
> SELECT LastName, FirstName FROM LINK2SQLBASE.COMPANYDB.dbo.tblEmployee
> I receive an Error:
> *OLE DB provider "MSDASQL" for linked server... returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and no[/vbcol
]
default[vbcol=seagreen]
> 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!