Linked server to ODBC dsn.....

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ian Boyd (admin_at_SWIFTPA.NET)
Date: 07/09/04 

Date: Thu, 8 Jul 2004 20:38:10 -0400

i have a DSN created on the SQL Server machine.

Using my favorite ODBC sql query tool, i can select the System DSN, specify
a login and password, and type a select statement and it all works.

i go into SQL Server, and want to create a linked server to this same ODBC
DSN

Linked server = "BALLYS"
Provider = "Microsoft OLE DB Provider for ODBC Driver"
Data Source="WC400B Bally CMS Testing"
Security-Be made using this security context:
    Remote login= [Login Name]
    With Password= [Password is blank]

Then i connect to the SQL Server using QA, and try to run my query (that
works in my favorite generic ODBC query tool):

SELECT * FROM OPENQUERY(BALLYS, 'select * from cspcm')

And i get an error message. Now, the error message really shouldn't matter.
My question is, why does it not work? Why didn't Microsoft get ODBC linked
servers right? If any generic stupid 3rd party tool can login and run
queries fine, why can't SQL Server get it right?

But if you care, the error message is:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]General error.]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. Comm RC=4 - CWB0999 -
Unexpected error: unexpected return code 4]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].

Now obviously the DB2 ODBC driver works fine, since i can use it fine with
ADO in my own programs, as well as any other program that knows how to use
ODBC.

So why can SQL Server figure out how to use ODBC?

Relevant Pages

  • Re: How to have SQL_ERROR returned from RAISERROR within IF-BLOCK
    ... The problem has been fixed in SQL Server 2005. ... even modify the sequence of ODBC function calls and checking of the return ... I want to return SQL_ERROR to my application with a custom error message ... I get it returned fine when I execute the following statement using ...
    (microsoft.public.data.odbc)
  • SQL Server ODBC Driver Ignores Authentication Setting
    ... set their ODBC connections with SQL Server Authentication, ... Microsoft Data Access Components 2.6 RTM, ... authentication to log into the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Linking tables access - sql server 2005
    ... Another advantage of this method is that you don't need an ODBC setting on the local computer, ... Create a linked table to SQL Server without using a DSN ... Name of the table that you are linking to on the SQL Server database ... Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As ...
    (microsoft.public.access.adp.sqlserver)
  • Re: sql server + ODBC: error suppressed
    ... I have a program that connects to SQL Server through ODBC, one of my clients ... no error message is displayed either (which is strange and it works fine on ... are there any configuration parameters on sql server or ODBC ...
    (microsoft.public.sqlserver.server)
  • RE: [ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()
    ... the ODBC source using named pipe, but I don't know how to enable named pipes ... If it is not working I am going to use ado to connect to sql server. ... Microsoft Online Community Support ...
    (microsoft.public.sqlserver.connect)