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! 9 7313
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 (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
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" <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].
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
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
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]. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: NickBlooruk |
last post by:
Hello,
I have successfully linked a Lotus Notes server to our SQL Server
database using an ODBC connection.
This works fine when wanting to select records
eg openquery(LOTUSNOTES2, 'select *...
|
by: teddysnips |
last post by:
In the script below is the DDL to create some tables and a UDF.
What I'm interested in is the UDF at the end. Specifically, these few
lines:
--CLOSE OTRate
--DEALLOCATE OTRate
ELSE --...
|
by: LaurenW |
last post by:
After many years of working with Access, I am FINALLY required to
delete a single field from a table in a linked back-end data base and
I must do it fro the FRONT end! There MUST be a way to do...
|
by: Smartin |
last post by:
I have tblEvents which includes a Memo field for "Notes". I have a main
form to display various data from tblEvents, including a small space to
display the Notes field.
I'm trying to create a...
|
by: Neil |
last post by:
I am getting time-out errors when I try to perform a simple delete on a
linked server. The command is:
Delete From MyTable Where PKID=12345
I have tried executing this command directly from...
| |
by: snoonan |
last post by:
The company in quesiton does construction work. Tables look like this:
***Job Table***
JobNumberID*
JobName
***JobNote Table***
JobNoteID*
JobNumberID*
JobNoteCreateDate
|
by: nma |
last post by:
Hi all,
Could someone help me how to delete the rows for the listed row below? How do I write the codes at this line? I want to click on 'Delete' to delete the specific row.
<td colspan="2"...
|
by: scott.tang |
last post by:
I'm experiencing a very strange problem. My application is MS Access
front-end and MS SQL server back-end database. I have a SQL statement
that deletes records from a table after an export...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |