Re: [Q]Problems related to the MySQL linked Server.

Further 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].[table 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@xxxxxxxxxxx> wrote in message
news:ObJWtpObGHA.3992@xxxxxxxxxxxxxxxxxxxxxxx
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






.

Relevant Pages

  • Re: Finding almost duplicate rows in mysql
    ... The query should be like this, ... SELECT userID, COUNT ... > I'm using MySQL 4.1.1 ... > I've inherited a database which has some duplicate rows. ...
    (comp.lang.php)
  • RE: Exporting to Excel
    ... GetUserId ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • Re: Problem with SQL and Recordset
    ... This assumes that the name of the subform control on the parent form is the ... NextSuffix field from the "query" to be entered into the Suffix field on ... Dim mySQL As String ...
    (microsoft.public.access.modulesdaovba)
  • RE: Exporting to Excel
    ... I did make the Criteria for the patientid field as GetUserId() not GetUserId. ... Static Function GetUserIdAs String ... 'Open the specific query with the data to be exported ... You can use this to drive a loop that will export a worksheet per userid. ...
    (microsoft.public.access.externaldata)
  • RE: Conditional display of records ...
    ... So in my query, I will map 'A' and 'B' for flag='Y' and userid (with which ... users logon) with the userid stored in table 'B'. ... The trick is to create a function that calls the username function and pass ... Function CheckFlag(strFlag As String) As Boolean ...
    (microsoft.public.access.formscoding)