By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

delete sql syntax for linked notes table

P: n/a
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!

Apr 17 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
ProgrammerGal (ca**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 17 '06 #2

P: n/a
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].

Apr 18 '06 #3

P: n/a
ProgrammerGal (ca**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 18 '06 #4

P: n/a
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].

Apr 18 '06 #5

P: n/a
Hi

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

John

"ProgrammerGal" <ca**********@ahm.honda.com> wrote in message
news:11**********************@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].

Apr 19 '06 #6

P: n/a
John Bell (jb************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 19 '06 #7

P: n/a
ProgrammerGal (ca**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 19 '06 #8

P: n/a
Hi Erland

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

John

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
John Bell (jb************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Apr 19 '06 #9

P: n/a
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/featu...le.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" <ca**********@ahm.honda.com> wrote in message
news:11**********************@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].

Apr 19 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.