Linked Server PROBLEM For MySQL Database

I created a ODBC DSN on my Windows Server for a remote UNIX box MySQL database. The TEST button returns the 'successful' message for the MySQL databaser. Also, when I use an ODBC DSNless string I found, that also successfully connects when I use it in some ASP pages.

However, I can not get a successful connection in the Enterprise Manager trying to use the DSN I created nor the DSNless string I used in the ASP pages. Can anyone tell me how to config the Enterprise Manager Linked Server settings listed below to connect to the MySQL database(s).

I added a new linked server, and selected the Microsoft OLEDB Provider for ODBC Drivers as the Provider name.

Does anyone know how do I configure the items below:

On the General tab:" option.
Product Name: xxxxxxxxxxxxxxxxxx
Data Source: xxxxxxx
Provider String: xxxxxxxx
Location: xxxxxxx
Catalog: xxxxx

On the Security tab:" option.
Remote Login: xxxxxx
With password: xxxxxx

Thanks.I found a wonderful solution to this problem...

http://bside.typepad.com/lifebsideben/2003/08/mysql_mssql_lin.html

In case the link is gone, here is the way to create a Linked Server to a MySQL database, in a nutshell...

1) Create a System DSN on the server running MSSQL, connecting to the MySQL database. Let's call it <the_new_mysql_dsn>.

2) Run the following stored procedure, with appropriate arguments...

EXEC sp_addlinkedserver @.server='<linked_server_label>', @.srvproduct='MySQL', @.provider='MSDASQL', @.datasrc='<the_new_mysql_dsn>'

-------

You can then open up ol' Enterprise Manager and see the new linked server added in. Right click on it to see the Properties. On mine, the settings showed up as...

Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: MySQL
Data source: <the_new_mysql_dsn>

Everything else is blank.

-------

Incidentally, just to try it out, I deleted my new linked server, and then added a new one, using the following settings...

Linked server: <linked_server_label>
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Provider string: DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=<mysql_database>;SERVER=<mysql_server>;UID=;PORT=;OPTION=0;STMT=;

And it works as well (the connection string was generated within Visual Interdev). I am using no passwords yet, I'm still in the early development phases, but this should handle the basics of connecting to a MySQL database from SQL Server.

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
KevinAnyone? Buehler?
> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>sql

Linked Server Openquery returns -1 on 2000, works on 2005

We have a query that works fine on SQLServer 2005, but when we run the exact
same query on SQLServer 2000 it fails with a -1. We're not able to get any
other error info at the moment. We're using Windows 2003 Server for both
databases and connecting to Oracle.
Kevin
Anyone? Buehler?

> We have a query that works fine on SQLServer 2005, but when we run the
> exact same query on SQLServer 2000 it fails with a -1. We're not able to
> get any other error info at the moment. We're using Windows 2003 Server
> for both databases and connecting to Oracle.
> Kevin
>

linked server msdb stored procedures

exec [ltd-gln-db2].msdb..sp_help_job
exec [ltd-gln-db2].msdb..sp_help_alert
Neither line fails.
The first returns 0 rows.
The second returns > 0 rows.
Both stored procedures allow 'public' to 'exec'.
Question: Why does sp_help_job return no rows?
Note: SQLServer 2000 SP2 on all db servers.Hi
Are there any jobs on the remote system?
As sp_help_job does not call sp_help_alert therefore I am not sure why you
should think sp_help_job would return anything if sp_help_alert does?
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
>|||
>--Original Message--
>Hi
>Are there any jobs on the remote system?
>As sp_help_job does not call sp_help_alert therefore I am
not sure why you
>should think sp_help_job would return anything if
sp_help_alert does?
>John
>"Larry Storm" <larry.storm@.ltd.org> wrote in message
>news:0c1901c5362c$251ac720$a401280a@.phx.gbl...
>
>.
>When I am connected to the remote system, sp_help_job
displays info for multiple jobs.
The reason I included information for sp_help_alert was to
show the remote server is accessable as a linked server and
permissions on stored procedures allow sp_help_alert to
return results.
My question remains:
Why does sp_help_job return zero rows?|||Hi
Try profiling the procedure and then see what the steps do when you log in
locally as that user.
John
"Larry Storm" <larry.storm@.ltd.org> wrote in message
news:1a0001c5392e$f9f0e4c0$a401280a@.phx.gbl...
>
> not sure why you
> sp_help_alert does?
> displays info for multiple jobs.
> The reason I included information for sp_help_alert was to
> show the remote server is accessable as a linked server and
> permissions on stored procedures allow sp_help_alert to
> return results.
> My question remains:
> Why does sp_help_job return zero rows?
>
>|||Larry Storm wrote:
> exec [ltd-gln-db2].msdb..sp_help_job
> exec [ltd-gln-db2].msdb..sp_help_alert
> Neither line fails.
> The first returns 0 rows.
> The second returns > 0 rows.
> Both stored procedures allow 'public' to 'exec'.
> Question: Why does sp_help_job return no rows?
> Note: SQLServer 2000 SP2 on all db servers.
>
From BOL: "A user who is not a member of the symin fixed role can use sp_
help_job to view only the jobs he/she owns."
HTH,
Igor