delete sql syntax for linked notes table

I am hoping this is a quick easy question for someone! :)

I am trying (struggling) with moving data from Sql Server to a Lotus
Notes table.

I am using SQL Server 2000, I have a Lotus Notes linked server (using
NotesSQL), and I wasnt to clear the table (delete all records) and then
reload it from my data on SQL Server.

What is the syntax to delete the records?

My select statement would be like this:
select * from openquery([LinkedServer], 'select * from NotesTable')
where lastName='Smith'

My delete statement ? -- cant quite figure out the syntax of this
one...
select * from openquery([LinkedServer], 'delete from NotesTable') where
lastName='Smith'
(this doesnt work)

Thanks!ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> My delete statement ? -- cant quite figure out the syntax of this
> one...
> select * from openquery([LinkedServer], 'delete from NotesTable') where
> lastName='Smith'
> (this doesnt work)

DELETE LinkedServer...NotesTable

You may need something between the dots as well, but I don't know Lotus
Notes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, I dont think I can use this syntax.

select * from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name]

delete from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name] where
CaseNum='PROD055344'

Returns:

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].|||ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> Yes, I dont think I can use this syntax.
> select * from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name]
> delete from [Notes_DRS_CaseName DEV].[CaseName].[dbo].[case_name] where
> CaseNum='PROD055344'
> Returns:
> 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].

You should certainly not specify dbo for something in Lotus Notes,
as dbo is very SQL Server-specific.

Try one of

select * from [Notes_DRS_CaseName DEV].[CaseName]..[case_name]
select * from [Notes_DRS_CaseName DEV]...[case_name]

You could also try

delete from openquery(LinkedServer, 'SELECT * FROM ...')

although it looks completely crazy!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I tried all different versions of the selects... no luck.
Still the same "no schema exposed message"

Here is the error from the delete...

delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
) where CaseNum='PROD007586'

Server: Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB
provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
returned 0x80004002].|||Hi

Try using two part names for your oracle table by adding the schema that
your oracle table is in.

John

"ProgrammerGal" <carolyn_graf@.ahm.honda.com> wrote in message
news:1145398887.423877.242610@.t31g2000cwb.googlegr oups.com...
>I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].|||John Bell (jbellnewsposts@.hotmail.com) writes:
> Try using two part names for your oracle table by adding the schema that
> your oracle table is in.

ProgrammerGal is using LotusNotes... (Of course, I don't know LotusNotes
at all. Maybe there is an Oracle database in the bottom?)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ProgrammerGal (carolyn_graf@.ahm.honda.com) writes:
> I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].

I'm afraid that I'm out of ideas. Maybe you should try a LotusNotes forum,
to here if anyone in that community has been able to solve this.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland

You are right I confused the ODBC OLE DB Provider MSDASQL with the Oracle
OLE DB Provider MSDAORA!

John

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97AA642F4AEE2Yazorman@.127.0.0.1...
> John Bell (jbellnewsposts@.hotmail.com) writes:
>> Try using two part names for your oracle table by adding the schema that
>> your oracle table is in.
> ProgrammerGal is using LotusNotes... (Of course, I don't know LotusNotes
> at all. Maybe there is an Oracle database in the bottom?)
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi

Check out http://tinyurl.com/kj3ow on how to determine how to use 4 part
naming. I am not sure if you need this now
http://www.databasejournal.com/feat...cle.php/3462011 but you
may want to check it out.

You may also want to check that this is not a read-only interface, also see
if there is a OLEDB interface available so you don't have to go through
ODBC.

John

"ProgrammerGal" <carolyn_graf@.ahm.honda.com> wrote in message
news:1145398887.423877.242610@.t31g2000cwb.googlegr oups.com...
>I tried all different versions of the selects... no luck.
> Still the same "no schema exposed message"
>
> Here is the error from the delete...
> delete from openquery([Notes_DRS_CaseName DEV],'select * from CaseName'
> ) where CaseNum='PROD007586'
> Server: Msg 7390, Level 16, State 1, Line 1
> The requested operation could not be performed because the OLE DB
> provider 'MSDASQL' does not support the required transaction interface.
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface
> returned 0x80004002].