Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server querying XML Column of MYSQL DB Expand / Collapse
Author
Message
Posted Tuesday, July 07, 2009 2:27 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:13 PM
Points: 132, Visits: 160
Hi

I set up a linked server to MySQL database using @provider=N'MSDASQL etc

The linked server string is

EXEC master.dbo.sp_addlinkedserver

@server = N'MySqLLinkedserver',
@srvproduct=N'mysqldb', @provider=N'MSDASQL',
@provstr=N'Driver={MySQL ODBC 3.51 Driver};
Server=myserver.com;UID=myuser;PWD=mypassword;Database=mydatabase;Option=3;'



The linked server is working fine . I can able to retirieve data and for all columns except xml column

If i specify XML column in the query iam getting errors .Is body experienced this before and have a solution please let me know

--- This query is fine ---

select * from openquery(MySqLLinkedserver,'select id ,county from tblinfo')

1 USA
2 USA
3 UK
4 UK

-----




-- but this query gives an error---

here info is xml column

select * from openquery(MySqLLinkedserver,'select id ,county,info from tblinfo')


Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.


even selecting a xml column also triggers same error

select * from openquery(MySqLLinkedserver,'select info from tblinfo')
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded


I posted this in XML forum .If any DBA here have got guts to answer this please
Post #748364
Posted Thursday, July 09, 2009 9:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:13 PM
Points: 132, Visits: 160
I fixed it

@server = N'MySqLLinkedserver',
@srvproduct=N'mysqldb', @provider=N'MSDASQL',
@provstr=N'Driver={MySQL ODBC 3.51 Driver};
Server=myserver.com;UID=myuser;PWD=mypassword;Database=mydatabase;Option=3;'


By changing option=8

Regards

Reddy
Post #750430
Posted Thursday, July 09, 2009 9:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, March 29, 2014 4:13 PM
Points: 132, Visits: 160
I fixed it

@server = N'MySqLLinkedserver',
@srvproduct=N'mysqldb', @provider=N'MSDASQL',
@provstr=N'Driver={MySQL ODBC 3.51 Driver};
Server=myserver.com;UID=myuser;PWD=mypassword;Database=mydatabase;Option=3;'


By changing option=8

Regards

Reddy
Post #750432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse