[Q]Problems related to the MySQL linked Server.

Dear everyone.
I have two questions about the linked server for the MySQL.
I need to connect to the mysql DB from the my SQLServer 2000 SP4 on the
Windows 2000 standard Edition(MDAC 2.8)
So, I made a linked server at the SQLServer. I worked well, but I faced with
some problem.
1. Query method
I need to query some data in the mysql with dynamic parameter.
ex) select @.v_intUserNo = uno
from tb_test
where userid = 'testid'
*input of the userid is changed everytime by the user.
so, I cannot use the OPENQUERY or OPENROWSET.
I want to query above like this,
select @.v_intUserNo = uno
from [linked server name].[catalog name].[schema name].[ta
ble name]
where userid = 'testid'
When I used above syntax at the linked server of the SQLServer, it worked
well.
But, it did not worked with the mysql DB.
(unfortunately, I cannot remember the exact error message.T.T)
2. Not killable SPID.
After #1 step, I tried many method to solve the problem.
As one trial, I set the catalog at the linked server's property.
After that, I tried query as followings
---
select @.v_intUserNo = uno
from [linked server name].[catalog name].[schema name].[ta
ble name]
where userid = 'testid'
or select @.v_intUserNo = uno
from OPENQUERY('TEST_DB', 'select * from test_tbl where userid =
''testid''')
---
It doesn't returned any error or result, so, I killed the session.
but it was not cleared promptly, only return the following message.
SPID 70: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
After restarting the SQLServer, they were cleared.
I wish to know the all the experiencies for managing the linked server to
the mysql DB at the SQLServer.
Thank you in advanceFurther information of my environment.
1. I used the mysql ODBC Ver 3.51
2. The error messge when I query data with following SQL.
select @.v_intUserNo = uno
from [linked server name].[catalog name].[schema name].[ta
ble name]
where userid = 'testid'
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
**Following query also doesn't work.
select @.v_intUserNo = uno
from [linked server name].[catalog name].[table name]
where userid = 'testid'
select @.v_intUserNo = uno
from [linked server name].[schema name].[table name]
where userid = 'testid'
select @.v_intUserNo = uno
from [linked server name].[table name]
where userid = 'testid'
Thank you.
"?" <dialup@.nownuri.net> wrote in message
news:ObJWtpObGHA.3992@.TK2MSFTNGP05.phx.gbl...
> Dear everyone.
> I have two questions about the linked server for the MySQL.
> I need to connect to the mysql DB from the my SQLServer 2000 SP4 on the
> Windows 2000 standard Edition(MDAC 2.8)
> So, I made a linked server at the SQLServer. I worked well, but I faced
with
> some problem.
> 1. Query method
> I need to query some data in the mysql with dynamic parameter.
> ex) select @.v_intUserNo = uno
> from tb_test
> where userid = 'testid'
> *input of the userid is changed everytime by the user.
> so, I cannot use the OPENQUERY or OPENROWSET.
> I want to query above like this,
> select @.v_intUserNo = uno
> from [linked server name].[catalog name].[schema name].[
table name]
> where userid = 'testid'
> When I used above syntax at the linked server of the SQLServer, it worked
> well.
> But, it did not worked with the mysql DB.
> (unfortunately, I cannot remember the exact error message.T.T)
> 2. Not killable SPID.
> After #1 step, I tried many method to solve the problem.
> As one trial, I set the catalog at the linked server's property.
> After that, I tried query as followings
> ---
> select @.v_intUserNo = uno
> from [linked server name].[catalog name].[schema name].[
table name]
> where userid = 'testid'
> or select @.v_intUserNo = uno
> from OPENQUERY('TEST_DB', 'select * from test_tbl where userid =
> ''testid''')
> ---
> It doesn't returned any error or result, so, I killed the session.
> but it was not cleared promptly, only return the following message.
> SPID 70: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> After restarting the SQLServer, they were cleared.
> I wish to know the all the experiencies for managing the linked server to
> the mysql DB at the SQLServer.
> Thank you in advance
>
>