servers: Invalid schema

Hi
This may not be much help but....I'm having the same
problem with MySQL. (1) Have you put a trace on the ODBC
call (do 'DBCC Traceon(7300)'in query analyser)- that gets
me some crud about 'nulls'. Do you get the same?. (2)
Anyway, as far as I am aware, catalog refers to the
database you are trying to connect to and schema refers to
the owner of the object you're trying to get at. My
question is, is the object owner in your other database
called something other than 'dbo' which is the name in
SQL2000 and are you using 'dbo' as the third part of the 4
part string?
I wouldn't normally ask questions in response to a
question but your posting seems to indicate that this is
not uncommon but no-one's posted an answer:-(
Malcolm
>--Original Message--
>I am having a problem referencing tables via a linked
>server in SQL queries using the
>syntax as exampled in the following simple illustration:
> SELECT * FROM LinkedServerName.Catalog.Schema.TableName
>The connection string I am using seems to be correct
>because I can see the table names when I expand the
>server in Enterprise Manager and I have also used it
>elsewhere (to generate a recordset using ADO and VBScript
>in a DTS package).
>When the above query is executed (in Query Analyser, for
>example) I get the following error message:
> 'OLE DB provider 'MSDASQL' returned an invalid schema
> definition. OLE DB error trace [Non-interface error:
> OLE/DB provider returned an invalid schema finition.].'
>The linked server is actually a Recital database
>connected using OLEDB for ODBC using the Recital ODBC
>driver. It does not have a direct equivalent of schema
>and catalogues and does not expose the interface which
>MSSQL Server seems to require.
>I have searched the internet (including various forums,
>e.g. dbforum) and have found quite a number of similar
>cases logged for a variety of other databases, but no one
>has replied with a solution.
>I really want to be able to write update queries using a
>mixture of SQL and Recital data and the linked server
>would be an attractive solution - if it worked!!!.
>Any suggestions.
>N.b. I have managed to use OPENROWSET, but not for update.
>.
>i'm having the same problem with linking mysql. i get the error
Server: Msg 7312, Level 16, State 1, Line 1
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].
i can see all the tables in the linked mysql server, but the schema
column is blank whereas it's usually dbo for linked sqlserver servers.
Malcolm wrote:
[vbcol=seagreen]
> Hi
> This may not be much help but....I'm having the same
> problem with MySQL. (1) Have you put a trace on the ODBC
> call (do 'DBCC Traceon(7300)'in query analyser)- that gets
> me some crud about 'nulls'. Do you get the same?. (2)
> Anyway, as far as I am aware, catalog refers to the
> database you are trying to connect to and schema refers to
> the owner of the object you're trying to get at. My
> question is, is the object owner in your other database
> called something other than 'dbo' which is the name in
> SQL2000 and are you using 'dbo' as the third part of the 4
> part string?
> I wouldn't normally ask questions in response to a
> question but your posting seems to indicate that this is
> not uncommon but no-one's posted an answer:-(
> Malcolm