|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > MYSQL Linked Server
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
MYSQL Linked Server
|
|
| Andrew Walsh 2002-10-05, 8:48 pm |
| I have managed to link a mysql server under MS SQL server 2000, but I can't
query it.
Normally to query a linked server:
select * from linkedserver.schema.catalog.table
But with the linked mysql server I get an error message:
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema.
Has anyone got any ideas about how to get around this.
I'm using mysql ODBC driver version 3.51.
| |
| Vikrant V Dalwale [MS] 2002-10-05, 8:49 pm |
|
Hello Andrew,
Looks like the Driver is not capable of exposing the rowset thru 4 part
naming ...Try using OPENQUERY
e.g.
SELECT * FROM OPENQUERY (<linked ssrv name>,'SELECT * FROM
pubs.dbo.authors')
If that fails try,
DBCC TRACEON ( 7300,3604)
GO
<run your query>
GO
Please Post the Error message.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure ! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--------------------
>Reply-To: "Andrew Walsh" <andrew.walsh@burohappold.com>
>From: "Andrew Walsh" <andrew.walsh@burohappold.com>
>Subject: MYSQL Linked Server
>Date: Fri, 16 Aug 2002 15:38:24 +0100
>Lines: 17
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3663.0
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3663.0
>Message-ID: <#zRKyLTRCHA.2560@tkmsftngp09>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 195.152.62.104
>Path: cpmsftngxa06!tkmsftngxs01!tkms
ftngp01!tkmsftngp09
>Xref: cpmsftngxa06 microsoft.public.sqlserver.server:227694
>X-Tomcat-NG: microsoft.public.sqlserver.server
>
>I have managed to link a mysql server under MS SQL server 2000, but I can't
>query it.
>
>Normally to query a linked server:
>select * from linkedserver.schema.catalog.table
>
>But with the linked mysql server I get an error message:
>
>Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
>four-part name was supplied, but the provider does not expose the necessary
>interfaces to use a catalog and/or schema.
>
>Has anyone got any ideas about how to get around this.
>
>I'm using mysql ODBC driver version 3.51.
>
>
>
| |
| Andrew Walsh 2002-10-05, 8:49 pm |
| Vikrant,
Thanks for that. Using OPENQUERY worked fine for select statements, but if I
try and perform an update statement it brings back the below message:
Could not process object 'update recruitment.tbl_login set login_UserName =
'test''. The OLE DB provider 'MSDASQL' indicates that the object has no
columns.
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='MSDASQL',
Query=update
recruitment.tbl_login set login_UserName = 'test''].
If using the the schema and catalog method is get the below message:
DBCC TRACEON (7300,3604)
GO
select * from hades..recruitment.tbl_login
go
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 7312, Level 16, State 1, Line 2
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
OLE DB error trace [Non-interface error].
Mnay thanks for you help.
Andy
"Vikrant V Dalwale [MS]" <vikrantd@online.microsoft.com> wrote in message
news:gJ$4vfYRCHA.2616@cpmsftngxa06...
>
> Hello Andrew,
>
> Looks like the Driver is not capable of exposing the rowset thru 4 part
> naming ...Try using OPENQUERY
>
> e.g.
>
> SELECT * FROM OPENQUERY (<linked ssrv name>,'SELECT * FROM
> pubs.dbo.authors')
>
> If that fails try,
>
> DBCC TRACEON ( 7300,3604)
> GO
> <run your query>
> GO
>
> Please Post the Error message.
>
> Thanks,
>
> Vikrant Dalwale
>
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Get secure ! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
>
> --------------------
> >Reply-To: "Andrew Walsh" <andrew.walsh@burohappold.com>
> >From: "Andrew Walsh" <andrew.walsh@burohappold.com>
> >Subject: MYSQL Linked Server
> >Date: Fri, 16 Aug 2002 15:38:24 +0100
> >Lines: 17
> >X-Priority: 3
> >X-MSMail-Priority: Normal
> >X-Newsreader: Microsoft Outlook Express 6.00.3663.0
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3663.0
> >Message-ID: <#zRKyLTRCHA.2560@tkmsftngp09>
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: 195.152.62.104
> >Path: cpmsftngxa06!tkmsftngxs01!tkms
ftngp01!tkmsftngp09
> >Xref: cpmsftngxa06 microsoft.public.sqlserver.server:227694
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >I have managed to link a mysql server under MS SQL server 2000, but I
can't
> >query it.
> >
> >Normally to query a linked server:
> >select * from linkedserver.schema.catalog.table
> >
> >But with the linked mysql server I get an error message:
> >
> >Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> >four-part name was supplied, but the provider does not expose the
necessary
> >interfaces to use a catalog and/or schema.
> >
> >Has anyone got any ideas about how to get around this.
> >
> >I'm using mysql ODBC driver version 3.51.
> >
> >
> >
>
| |
| Vikrant V Dalwale [MS] 2002-10-05, 8:50 pm |
|
Hello Andy,
Based on the Error,
"OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
OLE DB error trace [Non-interface error]. "
its a problem of incompatibility of SQL Server with the ODBC Drivers
provided for the MySQL.
I would recommed to contact the Provider for the MySQL ODBC Driver for
further information.
Thanks,
Vikrant Dalwale
Microsoft SQL Server Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.
Get secure ! For info, please visit http://www.microsoft.com/security.
Please reply to Newsgroups only.
--------------------
>Reply-To: "Andrew Walsh" <andrew.walsh@burohappold.com>
>From: "Andrew Walsh" <andrew.walsh@burohappold.com>
>References: <#zRKyLTRCHA.2560@tkmsftngp09> <gJ$4vfYRCHA.2616@cpmsftngxa06>
>Subject: Re: MYSQL Linked Server
>Date: Mon, 19 Aug 2002 10:57:20 +0100
>Lines: 110
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3663.0
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3663.0
>Message-ID: <etY1xc2RCHA.3700@tkmsftngp08>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: 195.152.62.56
>Path: cpmsftngxa07!tkmsftngp01!tkmsf
tngp08
>Xref: cpmsftngxa07 microsoft.public.sqlserver.server:226335
>X-Tomcat-NG: microsoft.public.sqlserver.server
>
>Vikrant,
>
>Thanks for that. Using OPENQUERY worked fine for select statements, but if
I
>try and perform an update statement it brings back the below message:
>
>Could not process object 'update recruitment.tbl_login set login_UserName =
>'test''. The OLE DB provider 'MSDASQL' indicates that the object has no
>columns.
>OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
>an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
>DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
>OLE DB error trace [Non-interface error: OLE DB provider unable to process
>object, since the object has no columnsProviderName='MSDASQL',
Query=update
>recruitment.tbl_login set login_UserName = 'test''].
>
>If using the the schema and catalog method is get the below message:
>
>DBCC TRACEON (7300,3604)
>GO
>select * from hades..recruitment.tbl_login
>go
>
>DBCC execution completed. If DBCC printed error messages, contact your
>system administrator.
>Server: Msg 7312, Level 16, State 1, Line 2
>Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
>four-part name was supplied, but the provider does not expose the necessary
>interfaces to use a catalog and/or schema.
>OLE DB error trace [Non-interface error: OLE DB provider MSDASQL returned
>an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
>DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
>OLE DB error trace [Non-interface error].
>
>Mnay thanks for you help.
>
>Andy
>
>"Vikrant V Dalwale [MS]" <vikrantd@online.microsoft.com> wrote in message
>news:gJ$4vfYRCHA.2616@cpmsftngxa06...
>>
>> Hello Andrew,
>>
>> Looks like the Driver is not capable of exposing the rowset thru 4 part
>> naming ...Try using OPENQUERY
>>
>> e.g.
>>
>> SELECT * FROM OPENQUERY (<linked ssrv name>,'SELECT * FROM
>> pubs.dbo.authors')
>>
>> If that fails try,
>>
>> DBCC TRACEON ( 7300,3604)
>> GO
>> <run your query>
>> GO
>>
>> Please Post the Error message.
>>
>> Thanks,
>>
>> Vikrant Dalwale
>>
>> Microsoft SQL Server Support Professional
>>
>> This posting is provided "AS IS" with no warranties, and confers no
>rights.
>> Get secure ! For info, please visit http://www.microsoft.com/security.
>> Please reply to Newsgroups only.
>>
>> --------------------
>> >Reply-To: "Andrew Walsh" <andrew.walsh@burohappold.com>
>> >From: "Andrew Walsh" <andrew.walsh@burohappold.com>
>> >Subject: MYSQL Linked Server
>> >Date: Fri, 16 Aug 2002 15:38:24 +0100
>> >Lines: 17
>> >X-Priority: 3
>> >X-MSMail-Priority: Normal
>> >X-Newsreader: Microsoft Outlook Express 6.00.3663.0
>> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3663.0
>> >Message-ID: <#zRKyLTRCHA.2560@tkmsftngp09>
>> >Newsgroups: microsoft.public.sqlserver.server
>> >NNTP-Posting-Host: 195.152.62.104
>> >Path: cpmsftngxa06!tkmsftngxs01!tkms
ftngp01!tkmsftngp09
>> >Xref: cpmsftngxa06 microsoft.public.sqlserver.server:227694
>> >X-Tomcat-NG: microsoft.public.sqlserver.server
>> >
>> >I have managed to link a mysql server under MS SQL server 2000, but I
> can't
>> >query it.
>> >
>> >Normally to query a linked server:
>> >select * from linkedserver.schema.catalog.table
>> >
>> >But with the linked mysql server I get an error message:
>> >
>> >Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
>> >four-part name was supplied, but the provider does not expose the
> necessary
>> >interfaces to use a catalog and/or schema.
>> >
>> >Has anyone got any ideas about how to get around this.
>> >
>> >I'm using mysql ODBC driver version 3.51.
>> >
>> >
>> >
>>
>
>
>
| |
|
| Hallo,
Its ODBC compatibility among SQL Server with MySQL?
Please information...
thank's a lot
betty.
"Vikrant V Dalwale [MS]" <vikrantd@online.microsoft.com> wrote in message
news:BeRetR$RCHA.2188@cpmsftngxa10...
>
>
> Hello Andy,
>
> Based on the Error,
>
> "OLE DB error trace [Non-interface error: OLE DB provider MSDASQL
returned
> an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
> DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
> OLE DB error trace [Non-interface error]. "
>
> its a problem of incompatibility of SQL Server with the ODBC Drivers
> provided for the MySQL.
> I would recommed to contact the Provider for the MySQL ODBC Driver for
> further information.
>
> Thanks,
>
> Vikrant Dalwale
>
> Microsoft SQL Server Support Professional
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Get secure ! For info, please visit http://www.microsoft.com/security.
> Please reply to Newsgroups only.
>
> --------------------
> >Reply-To: "Andrew Walsh" <andrew.walsh@burohappold.com>
> >From: "Andrew Walsh" <andrew.walsh@burohappold.com>
> >References: <#zRKyLTRCHA.2560@tkmsftngp09>
<gJ$4vfYRCHA.2616@cpmsftngxa06>
> >Subject: Re: MYSQL Linked Server
> >Date: Mon, 19 Aug 2002 10:57:20 +0100
> >Lines: 110
> >X-Priority: 3
> >X-MSMail-Priority: Normal
> >X-Newsreader: Microsoft Outlook Express 6.00.3663.0
> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3663.0
> >Message-ID: <etY1xc2RCHA.3700@tkmsftngp08>
> >Newsgroups: microsoft.public.sqlserver.server
> >NNTP-Posting-Host: 195.152.62.56
> >Path: cpmsftngxa07!tkmsftngp01!tkmsf
tngp08
> >Xref: cpmsftngxa07 microsoft.public.sqlserver.server:226335
> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >
> >Vikrant,
> >
> >Thanks for that. Using OPENQUERY worked fine for select statements, but
if
> I
> >try and perform an update statement it brings back the below message:
> >
> >Could not process object 'update recruitment.tbl_login set login_UserName
=
> >'test''. The OLE DB provider 'MSDASQL' indicates that the object has no
> >columns.
> >OLE DB error trace [Non-interface error: OLE DB provider MSDASQL
returned
> >an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
> >DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
> >OLE DB error trace [Non-interface error: OLE DB provider unable to
process
> >object, since the object has no columnsProviderName='MSDASQL',
Query=update[color
=green]
> >recruitment.tbl_login set login_UserName = 'test''].
> >
> >If using the the schema and catalog method is get the below message:
> >
> >DBCC TRACEON (7300,3604)
> >GO
> >select * from hades..recruitment.tbl_login
> >go
> >
> >DBCC execution completed. If DBCC printed error messages, contact your
> >system administrator.
> >Server: Msg 7312, Level 16, State 1, Line 2
> >Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> >four-part name was supplied, but the provider does not expose the[/color]
necessary
> >interfaces to use a catalog and/or schema.
> >OLE DB error trace [Non-interface error: OLE DB provider MSDASQL
returned
> >an incorrect value for DBPROP_CONCATNULLBEHAVIOR which should be either
> >DBPROPVAL_CB_NULL or DBPROPVAL_CB_NON_NULL].
> >OLE DB error trace [Non-interface error].
> >
> >Mnay thanks for you help.
> >
> >Andy
> >
> >"Vikrant V Dalwale [MS]" <vikrantd@online.microsoft.com> wrote in message
> >news:gJ$4vfYRCHA.2616@cpmsftngxa06...
> >>
> >> Hello Andrew,
> >>
> >> Looks like the Driver is not capable of exposing the rowset thru 4 part
> >> naming ...Try using OPENQUERY
> >>
> >> e.g.
> >>
> >> SELECT * FROM OPENQUERY (<linked ssrv name>,'SELECT * FROM
> >> pubs.dbo.authors')
> >>
> >> If that fails try,
> >>
> >> DBCC TRACEON ( 7300,3604)
> >> GO
> >> <run your query>
> >> GO
> >>
> >> Please Post the Error message.
> >>
> >> Thanks,
> >>
> >> Vikrant Dalwale
> >>
> >> Microsoft SQL Server Support Professional
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >rights.
> >> Get secure ! For info, please visit http://www.microsoft.com/security.
> >> Please reply to Newsgroups only.
> >>
> >> --------------------
> >> >Reply-To: "Andrew Walsh" <andrew.walsh@burohappold.com>
> >> >From: "Andrew Walsh" <andrew.walsh@burohappold.com>
> >> >Subject: MYSQL Linked Server
> >> >Date: Fri, 16 Aug 2002 15:38:24 +0100
> >> >Lines: 17
> >> >X-Priority: 3
> >> >X-MSMail-Priority: Normal
> >> >X-Newsreader: Microsoft Outlook Express 6.00.3663.0
> >> >X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3663.0
> >> >Message-ID: <#zRKyLTRCHA.2560@tkmsftngp09>
> >> >Newsgroups: microsoft.public.sqlserver.server
> >> >NNTP-Posting-Host: 195.152.62.104
> >> >Path: cpmsftngxa06!tkmsftngxs01!tkms
ftngp01!tkmsftngp09
> >> >Xref: cpmsftngxa06 microsoft.public.sqlserver.server:227694
> >> >X-Tomcat-NG: microsoft.public.sqlserver.server
> >> >
> >> >I have managed to link a mysql server under MS SQL server 2000, but I
> >can't
> >> >query it.
> >> >
> >> >Normally to query a linked server:
> >> >select * from linkedserver.schema.catalog.table
> >> >
> >> >But with the linked mysql server I get an error message:
> >> >
> >> >Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> >> >four-part name was supplied, but the provider does not expose the
> >necessary
> >> >interfaces to use a catalog and/or schema.
> >> >
> >> >Has anyone got any ideas about how to get around this.
> >> >
> >> >I'm using mysql ODBC driver version 3.51.
> >> >
> >> >
> >> >
> >>
> >
> >
> >
>
|
|
|
|
|