sql server odbc:
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
