server to ODBC dsn.....

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?
Have you tried with the latest version of Client access express odbc driver from IBM?
What is the level of service pack on SQL?
"Ian Boyd" wrote:

> 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?
>
>