Linking from SQL server to MySQL

Hi all, hoping someone can help.
I have created an ODBC connection to a remote MySQL database.
I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data.
The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables.
I am so stuck. Any help would be very much appreciated.My first guess would be a permissions issue in the MySQL login used within the DSN, or an IP permission problem in the MySQL login. I don't have any easy way to set up a test case this week, but those would be where I'd start looking.

-PatP|||Really don't know a lot about MySQL permissions so any more help would be great, so I can go back to are website designers with suggestions of what the problem might be on there end.|||The security and permissions for MySQL work a lot different than they do for SQL Server.

The IP address figures prominently into MySQL permissions. A login might have one set of permissions at one ip address or range (say inside the firewall), and drastically different permissions at others. If you ran your DTS package from a machine with an IP address that was "blessed", you could have one set of permissions, but when making a linked server connection on the SQL Server (which would have another address), the same MySQL id might have much less permission.

Another possibility is that the ODBC driver on the SQL Server might not be current. It has been relatively recent that the MySQL team has put any effort into making the ODBC drivers compatible with Windows 2003 Server.

The biggest problem at this point is that there are so many ways for the connection to go bad, and the MySQL ODBC drivers still aren't very good at giving usable diagnostics (as you've noticed!). The problem is almost certainly solvable, but without help from someone pretty technically knowledgable that understands both your NT and your MySQL configuration, it may take many tries to work out the problem and its solution.

-PatP

server to ODBC DSN

i have a DSN created on the SQL Server machine itself. 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 the linked server work? If
any generic 3rd party tool can login and run queries fine, why isn't SQL
Server?
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 what is SQL Server doing wrong?
Here are all the settings for my linked server setup:
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: [blank]
Data source: WC400B Bally CMS Testing
Provider string: [blank]
Location: [blank]
Catalog: [blank]
Provider Options:
Dynamic parameters: Unchecked
Nested queries: Unchecked
Level zero only: Unchecked
Allow InProcess: Checked
Non transacted updates: Unchecked
Index as access path: Unchecked
Disallow adhoc accesses: Unchecked
Be made using this security contect:
Remote Login: [The login]
With password: [blank - the password is empty]
Server Options:
Collation compatibile: Unchecked
Data Access: Checked
RPC: Unchecked
RPC Out: Unchecked
Use remote connection: Checked
Collation Name: [blank]
Connection Timeout: 0
Query Timeout: 0
Everything is defaulted, aside from the OLEDB Provider Name, the DSN, the
login, and password.
Which of these defaults are wrong, and are preventing SQL Server from
connecting to a remote ODBC data source?
Error when i try various things:
Collation Compatible o o x o x o x o x o x o x o x o x
RPC o o o x x o o x x o o x x o o x x
RPC Out o o o o o x x x x o o o o x x x x
Use Remote Connection o o o o o o o o o x x x x x x x x
Data Access o x x x x x x x x x x x x x x x x
================================================== ==========================
=
Error 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1. Error 7411: Server "BALLYS" is not configured for DATA ACCESS
2. Previous Error

server to ODBC DSN

i have a DSN created on the SQL Server machine itself. 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 the linked server work? If
any generic 3rd party tool can login and run queries fine, why isn't SQL
Server?
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 what is SQL Server doing wrong?
Here are all the settings for my linked server setup:
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: [blank]
Data source: WC400B Bally CMS Testing
Provider string: [blank]
Location: [blank]
Catalog: [blank]
Provider Options:
Dynamic parameters: Unchecked
Nested queries: Unchecked
Level zero only: Unchecked
Allow InProcess: Checked
Non transacted updates: Unchecked
Index as access path: Unchecked
Disallow adhoc accesses: Unchecked
Be made using this security contect:
Remote Login: [The login]
With password: [blank - the password is empty]
Server Options:
Collation compatibile: Unchecked
Data Access: Checked
RPC: Unchecked
RPC Out: Unchecked
Use remote connection: Checked
Collation Name: [blank]
Connection Timeout: 0
Query Timeout: 0
Everything is defaulted, aside from the OLEDB Provider Name, the DSN, the
login, and password.
Which of these defaults are wrong, and are preventing SQL Server from
connecting to a remote ODBC data source?Error when i try various things:
Collation Compatible o o x o x o x o x o x o x o x o x
RPC o o o x x o o x x o o x x o o x x
RPC Out o o o o o x x x x o o o o x x x x
Use Remote Connection o o o o o o o o o x x x x x x x x
Data Access o x x x x x x x x x x x x x x x x
=============================================================================Error 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1. Error 7411: Server "BALLYS" is not configured for DATA ACCESS
2. Previous Error

server to ODBC DSN

i have a DSN created on the SQL Server machine itself. 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 the linked server work? If
any generic 3rd party tool can login and run queries fine, why isn't SQL
Server?
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 O
DBC Driver
(32-bit)][DB2/400 SQL]General error.]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access Express O
DBC 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 what is SQL Server doing wrong?
Here are all the settings for my linked server setup:
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: [blank]
Data source: WC400B Bally CMS Testing
Provider string: [blank]
Location: [blank]
Catalog: [blank]
Provider Options:
Dynamic parameters: Unchecked
Nested queries: Unchecked
Level zero only: Unchecked
Allow InProcess: Checked
Non transacted updates: Unchecked
Index as access path: Unchecked
Disallow adhoc accesses: Unchecked
Be made using this security contect:
Remote Login: [The login]
With password: [blank - the password is empty]
Server Options:
Collation compatibile: Unchecked
Data Access: Checked
RPC: Unchecked
RPC Out: Unchecked
Use remote connection: Checked
Collation Name: [blank]
Connection Timeout: 0
Query Timeout: 0
Everything is defaulted, aside from the OLEDB Provider Name, the DSN, the
login, and password.
Which of these defaults are wrong, and are preventing SQL Server from
connecting to a remote ODBC data source?Error when i try various things:
Collation Compatible o o x o x o x o x o x o x o x o x
RPC o o o x x o o x x o o x x o o x x
RPC Out o o o o o x x x x o o o o x x x x
Use Remote Connection o o o o o o o o o x x x x x x x x
Data Access o x x x x x x x x x x x x x x x x
========================================
====================================
=
Error 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1. Error 7411: Server "BALLYS" is not configured for DATA ACCESS
2. Previous Error