problem while creating a linked server from SQL-Server2000 to an Access97 database

I was attempting to create a linked server from SQL-Server2000 to an Access97 mdb file using the following scripts

EXEC sp_addlinkedserver

@.server='REMOTE_OFFICE',

@.srvproduct='Jet 4.0',

@.provider='Microsoft.Jet.OLEDB.4.0',

@.datasrc='F:\RealEstate_Office1.mdb'

and

EXEC sp_addlinkedsrvlogin

@.rmtsrvname='REMOTE_OFFICE',

@.useself='false',

@.locallogin='sa',

@.rmtuser='Admin',

@.rmtpassword=NULL

And while querying the linked server from the query analyzer using the following select command

SELECT *

FROM REMOTE_OFFICE.RealEstate_Office1.dbo.E_GOV_RE_OK

I got the following error

Server: Msg 7312, Level 16, State 1, Line 1

Invalid use of schema and/or catalog for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. 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].

could you explain why this happen

The error indicates that Access does not support database or schema names. Try leaving those parts empty. SELECT * FROM REMOTE_OFFICE...E_GOV_RE_OK.

This post belongs in another forum.
|||Moving to the SQL Server Data Access Forum.|||

Thanks JayH,

after a little search I found that it was an article describes how to use a Microsoft SQL Server distributed query to retrieve data from a secured Microsoft Access database and its link is as follows

http://support.microsoft.com/?kbid=246255

Article ID : 246255
Last Review : February 12, 2007
Revision : 4.3

Thanks all.

problem using ntext data type in OPENXML

Hi,
I've got problems using ntext data in a stored procedure (SQL-Server
2000):
create procedure testproc
@.xmldata ntext
as
declare @.Param ntext
exec sp_xml_preparedocument @.idoc OUTPUT, @.xmldata
select @.Param=Param
from openxml(@.idoc, '//ROOT/Parameters')
with (
Param ntext
)
But if I pass "Param" I'll receive an internal error. If "Param" and
"@.Param" are declared as "varchar(2000)" everything works fine.
Any hints?
xpost & f'up"Tom" <me@.privacy.net> wrote in message
news:6vqit05cnuomoouvpi9279b7bl4smbbmne@.
4ax.com...
> Hi,
> I've got problems using ntext data in a stored procedure (SQL-Server
> 2000):
> create procedure testproc
> @.xmldata ntext
> as
> declare @.Param ntext
> exec sp_xml_preparedocument @.idoc OUTPUT, @.xmldata
> select @.Param=Param
> from openxml(@.idoc, '//ROOT/Parameters')
> with (
> Param ntext
> )
>
> But if I pass "Param" I'll receive an internal error. If "Param" and
> "@.Param" are declared as "varchar(2000)" everything works fine.
> Any hints?
> xpost & f'up
From a pure TSQL point of view, the obvious problem is that you cannot
declare ntext variables - see DECLARE in Books Online. I'm not sure how you
would best address this in the context of your problem, so hopefully someone
from the XML newsgroup will be able to suggest something.
Simon|||Can you use a temp table or an output parameter instead?
HTH
Michael
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41d99274$1_3@.news.bluewin.ch...
> "Tom" <me@.privacy.net> wrote in message
> news:6vqit05cnuomoouvpi9279b7bl4smbbmne@.
4ax.com...
> From a pure TSQL point of view, the obvious problem is that you cannot
> declare ntext variables - see DECLARE in Books Online. I'm not sure how
> you would best address this in the context of your problem, so hopefully
> someone from the XML newsgroup will be able to suggest something.
> Simon
>|||Tom (me@.privacy.net) writes:
> I've got problems using ntext data in a stored procedure (SQL-Server
> 2000):
> create procedure testproc
> @.xmldata ntext
> as
> declare @.Param ntext
> exec sp_xml_preparedocument @.idoc OUTPUT, @.xmldata
> select @.Param=Param
> from openxml(@.idoc, '//ROOT/Parameters')
> with (
> Param ntext
> )
>
> But if I pass "Param" I'll receive an internal error. If "Param" and
> "@.Param" are declared as "varchar(2000)" everything works fine.
If you get "Internal error", that's a bug. However, you cannot assign to
ntext parameters anyway, so you are unfortunately out of luck.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

problem using ntext data type in OPENXML

Hi,
I've got problems using ntext data in a stored procedure (SQL-Server
2000):
create procedure testproc
@.xmldata ntext
as
declare @.Paramntext
exec sp_xml_preparedocument @.idoc OUTPUT, @.xmldata
select @.Param=Param
from openxml(@.idoc, '//ROOT/Parameters')
with (
Paramntext
)
But if I pass "Param" I'll receive an internal error. If "Param" and
"@.Param" are declared as "varchar(2000)" everything works fine.
Any hints?
xpost & f'up
"Tom" <me@.privacy.net> wrote in message
news:6vqit05cnuomoouvpi9279b7bl4smbbmne@.4ax.com...
> Hi,
> I've got problems using ntext data in a stored procedure (SQL-Server
> 2000):
> create procedure testproc
> @.xmldata ntext
> as
> declare @.Param ntext
> exec sp_xml_preparedocument @.idoc OUTPUT, @.xmldata
> select @.Param=Param
> from openxml(@.idoc, '//ROOT/Parameters')
> with (
> Param ntext
> )
>
> But if I pass "Param" I'll receive an internal error. If "Param" and
> "@.Param" are declared as "varchar(2000)" everything works fine.
> Any hints?
> xpost & f'up
From a pure TSQL point of view, the obvious problem is that you cannot
declare ntext variables - see DECLARE in Books Online. I'm not sure how you
would best address this in the context of your problem, so hopefully someone
from the XML newsgroup will be able to suggest something.
Simon
|||Can you use a temp table or an output parameter instead?
HTH
Michael
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41d99274$1_3@.news.bluewin.ch...
> "Tom" <me@.privacy.net> wrote in message
> news:6vqit05cnuomoouvpi9279b7bl4smbbmne@.4ax.com...
> From a pure TSQL point of view, the obvious problem is that you cannot
> declare ntext variables - see DECLARE in Books Online. I'm not sure how
> you would best address this in the context of your problem, so hopefully
> someone from the XML newsgroup will be able to suggest something.
> Simon
>
|||Tom (me@.privacy.net) writes:
> I've got problems using ntext data in a stored procedure (SQL-Server
> 2000):
> create procedure testproc
> @.xmldata ntext
> as
> declare @.Param ntext
> exec sp_xml_preparedocument @.idoc OUTPUT, @.xmldata
> select @.Param=Param
> from openxml(@.idoc, '//ROOT/Parameters')
> with (
> Param ntext
> )
>
> But if I pass "Param" I'll receive an internal error. If "Param" and
> "@.Param" are declared as "varchar(2000)" everything works fine.
If you get "Internal error", that's a bug. However, you cannot assign to
ntext parameters anyway, so you are unfortunately out of luck.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp