delete sql syntax for linked notes table

[ProgrammerGal]

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!

[Erland Sommarskog]

Re: delete sql syntax for linked notes table

ProgrammerGal (carolyn_graf@a hm.honda.com) writes:[color=blue]
> 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)[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[ProgrammerGal]

Re: delete sql syntax for linked notes table

Yes, I dont think I can use this syntax.

select * from [Notes_DRS_CaseN ame DEV].[CaseName].[dbo].[case_name]

delete from [Notes_DRS_CaseN ame DEV].[CaseName].[dbo].[case_name] where
CaseNum='PROD05 5344'

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].

[Erland Sommarskog]

Re: delete sql syntax for linked notes table

ProgrammerGal (carolyn_graf@a hm.honda.com) writes:[color=blue]
> Yes, I dont think I can use this syntax.
>
> select * from [Notes_DRS_CaseN ame DEV].[CaseName].[dbo].[case_name]
>
> delete from [Notes_DRS_CaseN ame DEV].[CaseName].[dbo].[case_name] where
> CaseNum='PROD05 5344'
>
> 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].[/color]

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_CaseN ame DEV].[CaseName]..[case_name]
select * from [Notes_DRS_CaseN ame DEV]...[case_name]

You could also try

delete from openquery(Linke dServer, 'SELECT * FROM ...')

although it looks completely crazy!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[ProgrammerGal]

Re: delete sql syntax for linked notes table

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_CaseN ame DEV],'select * from CaseName'
) where CaseNum='PROD00 7586'

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::Query Interface
returned 0x80004002].

[John Bell]

Re: delete sql syntax for linked notes table

Hi

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

John

"Programmer Gal" <carolyn_graf@a hm.honda.com> wrote in message
news:1145398887 .423877.242610@ t31g2000cwb.goo glegroups.com.. .[color=blue]
>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_CaseN ame DEV],'select * from CaseName'
> ) where CaseNum='PROD00 7586'
>
> 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::Query Interface
> returned 0x80004002].
>[/color]

[Erland Sommarskog]

Re: delete sql syntax for linked notes table

John Bell (jbellnewsposts @hotmail.com) writes:[color=blue]
> Try using two part names for your oracle table by adding the schema that
> your oracle table is in.[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Re: delete sql syntax for linked notes table

ProgrammerGal (carolyn_graf@a hm.honda.com) writes:[color=blue]
> 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_CaseN ame DEV],'select * from CaseName'
> ) where CaseNum='PROD00 7586'
>
> 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::Query Interface
> returned 0x80004002].[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[John Bell]

Re: delete sql syntax for linked notes table

Hi Erland

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

John

"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns97AA642 F4AEE2Yazorman@ 127.0.0.1...[color=blue]
> John Bell (jbellnewsposts @hotmail.com) writes:[color=green]
>> Try using two part names for your oracle table by adding the schema that
>> your oracle table is in.[/color]
>
> 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@sommarsk og.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[/color]

[John Bell]

Re: delete sql syntax for linked notes table

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




"Programmer Gal" <carolyn_graf@a hm.honda.com> wrote in message
news:1145398887 .423877.242610@ t31g2000cwb.goo glegroups.com.. .[color=blue]
>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_CaseN ame DEV],'select * from CaseName'
> ) where CaseNum='PROD00 7586'
>
> 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::Query Interface
> returned 0x80004002].
>[/color]