Delete Subscription

I am still struggling to delete a subscription so I can set another one to
that server
I believe that normally that would be listed in the Properties Page of the
Publisher, but it doesn't
How can I still delete it
Thank you,
Samuel
Did you try to issue a sp_dropsubscription 'Publicationname','articlename/or
all','Subscribername','subscriptiondatabase'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:ur04P1y4GHA.1252@.TK2MSFTNGP04.phx.gbl...
>I am still struggling to delete a subscription so I can set another one to
>that server
> I believe that normally that would be listed in the Properties Page of the
> Publisher, but it doesn't
> How can I still delete it
> Thank you,
> Samuel
>

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

Delete records older than a certain period from the subscriber

Hi,
I am struggling with this for some time now:
I want all records older than a certain period (eg. one month) to be
deleted from the subscriber's database.
I have created sample database with only one table and one datetime
column, just for testing this. The table is filtered:
SELECT <published_columns> FROM [dbo].[table] WHERE DateField >=
DateAdd(month,-1,GetDate()) - so subscriber should only have records
entered last month.
So, if subscriber enters one record in the subscription db with
today's date and synchronizes immediatelly, record will remain in the
subscriber's database which is OK, but I want this record to be
removed from the subscriber's database when user will synchronize
someday in the future and this record will be older than a month.
However, this does not happen. I know that record won't be sent to the
publisher as a part of merge replication, if it was not changed
between synchronizations. For that reason, an update to the same value
is always performed on the subscriber's table before the
synchronization, eg. update table set datefield = datefield.
I can see in the merge agent history that this update is sent to the
publisher, but record still remains in the subscriber's db. It seems
to me that filter is not evaluated correctly or not evaluated at all.
If I specify reinitialization on the subscription, the record is
removed from the subscribers database, but I do not want to
reinitialize at each sync.
I have read numerous posts and noticed that this scenario should
work?!
Any idea what might be wrong?
I am using SQL 2000 with SP3.
Janez
I think you will have to run a job on the subscriber which will delete rows
which are older than a month.
The merge filter only filters modified/deleted/inserted rows. Not rows which
are not touched.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Janez" <janezcas@.yahoo.com> wrote in message
news:c697ea9c.0412080703.1135f892@.posting.google.c om...
> Hi,
> I am struggling with this for some time now:
> I want all records older than a certain period (eg. one month) to be
> deleted from the subscriber's database.
> I have created sample database with only one table and one datetime
> column, just for testing this. The table is filtered:
> SELECT <published_columns> FROM [dbo].[table] WHERE DateField >=
> DateAdd(month,-1,GetDate()) - so subscriber should only have records
> entered last month.
> So, if subscriber enters one record in the subscription db with
> today's date and synchronizes immediatelly, record will remain in the
> subscriber's database which is OK, but I want this record to be
> removed from the subscriber's database when user will synchronize
> someday in the future and this record will be older than a month.
> However, this does not happen. I know that record won't be sent to the
> publisher as a part of merge replication, if it was not changed
> between synchronizations. For that reason, an update to the same value
> is always performed on the subscriber's table before the
> synchronization, eg. update table set datefield = datefield.
> I can see in the merge agent history that this update is sent to the
> publisher, but record still remains in the subscriber's db. It seems
> to me that filter is not evaluated correctly or not evaluated at all.
> If I specify reinitialization on the subscription, the record is
> removed from the subscribers database, but I do not want to
> reinitialize at each sync.
> I have read numerous posts and noticed that this scenario should
> work?!
> Any idea what might be wrong?
> I am using SQL 2000 with SP3.
> Janez
|||Hi Hilary,
thanks for your response.
About your suggestion: I have thought about this too, but I believe that
this would delete the records also from the publisher at next sync which
is not what I want. I want only one month old records in the
subscriber's database, but publisher should have all records not just
one month old.
Another thing:
What do you mean by 'not touched'?
I always update records at the subscriber before each sync with the
dummy update of the date field to the same value, but the filter is not
evaluated.
Should this dummy update be enough to trigger filter evaluation?!
Regards
Janez
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||For that form of logic I normally use bi-directional transactional
replication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Janez Cas" <janezcas@.yahoo.com> wrote in message
news:eUUJG8U3EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for your response.
> About your suggestion: I have thought about this too, but I believe that
> this would delete the records also from the publisher at next sync which
> is not what I want. I want only one month old records in the
> subscriber's database, but publisher should have all records not just
> one month old.
> Another thing:
> What do you mean by 'not touched'?
> I always update records at the subscriber before each sync with the
> dummy update of the date field to the same value, but the filter is not
> evaluated.
> Should this dummy update be enough to trigger filter evaluation?!
> Regards
> Janez
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!