Can't drop SQL Mobile subscription at Publisher

I have a program running on Pocket PCs that synchronizes with a desktop SQL database using SQL Mobile. My problem is that I can't delete old subscriptions from the publication; now the publication has 25 subscriptions and won't accept any more (limit of SQL Workgroup edition). I can delete non-SQL Mobile subcriptions just fine.

Running sp_replmonitorhelpsubscription provides this information:

subscriber: LVCONTACTSMERGE - 5FA305234F0C
subscriber_db: Program Files\PocketLVContacts\LVContacts.sdf
publisher_db: LVContacts
publication: LVContactsMerge

So I set up this bit of code:

USE LVContacts
EXEC sp_dropmergesubscription
@.publication = 'LVContactsMerge',
@.subscriber = 'LVCONTACTSMERGE - 5FA305234F0C',
@.subscriber_db = 'Program Files\PocketLVContacts\LVContacts.sdf',
@.subscription_type='all';
GO

No errors are reported, but the subscription doesn't get deleted.

I've tried shutting down SQL Server Agent (mentioned in another thread); makes no difference.

Any ideas?

--Evan

If you query sysmergesubscriptions, do you get the same values returned as|||

If you query sysmergesubscriptions, do you get the same values returned as sp_replmonitorhelpsubscription? I'm just wondering if the subscriber_db name returned is correct or not.

|||

Yes, both sysmergesubscriptions and sp_replmonitorhelpsubscription give the same subscriber_db name. Note that the name returned, i.e. 'Program Files\PocketLVContacts\LVContactsData.sdf', is the path on the mobile device.

I wonder if it is safe to manually delete a row from sysmergesubscriptions. It seems to work, but no Distributor clean-up is done.

|||

I've found where I was going wrong. The code I cited in my original post DOES work, but to see the changes you must use sysmergesubscriptions, NOT sp_replmonitorhelpsubscription. The latter apparently reflects the distribution database, which is not cleaned up when you use sp_dropmergesubscription.

When you use SQL Server Management Studio to delete a subscription on a mobile device running SQL Mobile, the subscription on the Publisher is NOT also deleted; you must do it manually (or wait until the subscription expires). But it is difficult to determine which subscription on the Publisher corresponds to the one you want to delete on the mobile Subscriber. What I did was first query the mobile Subscriber system table __sysMergeSubscriptions, note the SubscriptionID field, then query the sysmergesubscriptions on the Publisher database, and find the row with a SubID that matches. Then you can run sp_dropmergesubscription using the subscriber_server field as @.subscriber and the db_name field as @.subscriber_db.

Can't drop procedure ... Snapshot won't run... Help!

Hi,
I have a server set up for Merge replication. It is the publisher and the
distributer. Has been working fine.
It has two publications.
I wanted the subscribers to get a new copy of the data next time they
replicate, so I went to the first publication, which is stored procs and
view, went to Status tab of the publication properties, and clicked the run
agent now. When it was done, I right clicked on the publication and selected
reinitialize subscriptions.
Then I went to the publication for tables. Right clicked and selected
Re-initialize subscritions. Then I went to the status tab and selected run
agent now. When it came back with a timestamp, red x's appeared on the
replication monitor on the snap shot agents.
The error message it give is :
Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
because it is being used for replication.
As a last resort, I tried to delete the publication, I was going to re do
it. When I try to delete it
it gives me the same error message as above, but with a different sp_sel_
name.
What should I do, or what did I do. I really need it back to working today.
.. .
Any help,
Thanks,
Steve
Well, here is what I did.
I kept trying to delete the publication that I could not get the snapshot
agent to run on, and kept getting the error above. So I delete the stored
proc publication, it went OK. Then I deleted the table publication, and this
time it let me. Then I went to each of the clients, deleted both
subscriptions, and re-created them. Ran fine then, and am able to do manual
pull replications from the subscriber boxes now.
If any one can help me understand what happened, please do. How did I get
in that mess, and what would have been a good way to get out. I was lucky
that I could get to all subscriber boxes and re-create the subscriptions.
Thanks,
Steve
"SteveInBeloit" wrote:

> Hi,
> I have a server set up for Merge replication. It is the publisher and the
> distributer. Has been working fine.
> It has two publications.
> I wanted the subscribers to get a new copy of the data next time they
> replicate, so I went to the first publication, which is stored procs and
> view, went to Status tab of the publication properties, and clicked the run
> agent now. When it was done, I right clicked on the publication and selected
> reinitialize subscriptions.
> Then I went to the publication for tables. Right clicked and selected
> Re-initialize subscritions. Then I went to the status tab and selected run
> agent now. When it came back with a timestamp, red x's appeared on the
> replication monitor on the snap shot agents.
> The error message it give is :
> Cannot drop the procedure 'dbo.sp_sel_B4AC8FE9123F47EDB952DCE5249B4F84_pal'
> because it is being used for replication.
> As a last resort, I tried to delete the publication, I was going to re do
> it. When I try to delete it
> it gives me the same error message as above, but with a different sp_sel_
> name.
> What should I do, or what did I do. I really need it back to working today.
> . .
> Any help,
> Thanks,
> Steve
>
sql

Can't Disable, getting Server Cannot obtain LOCK

Internet merge replication. Need to detach to move to a new box. Deleted
all the publication, now when I try to disable it as the publisher. I tried
to run sp_removedbreplication 'db_name' and get the same message.
Any thoughts?
Thanks
After lots more reading and searching, I found low memory could cause this.
I added another 512 to the box, and it worked.
"SteveInBeloit" wrote:

> Internet merge replication. Need to detach to move to a new box. Deleted
> all the publication, now when I try to disable it as the publisher. I tried
> to run sp_removedbreplication 'db_name' and get the same message.
> Any thoughts?
> Thanks
|||I would have bounced the server and then tried this - this is abnormal.
Hilary Cotter
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
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:17E5CBD0-5D2A-444A-97BD-4629B2AE3559@.microsoft.com...[vbcol=seagreen]
> After lots more reading and searching, I found low memory could cause
> this.
> I added another 512 to the box, and it worked.
>
> "SteveInBeloit" wrote:

Can't disable publishing

I have a server work as publisher and distributor for a database and another
remote server work as subscriber. The remote server downed and reformatted
due to system failure. We setup another remote server with different name.
But when I tried to disable publishing/ delete and recreate subscription at
publisher/distributor server, i received the following error:
The server has been disconnected. Please reconnect it and perform this
action again.
How should i fixed it?
Thank you.
In article <E87D093B-0F0D-4601-9D00-
6988821C47DB@.microsoft.com>, Clarin@.discussions.microsoft.com
says...
> I have a server work as publisher and distributor for a database and another
> remote server work as subscriber. The remote server downed and reformatted
> due to system failure. We setup another remote server with different name.
> But when I tried to disable publishing/ delete and recreate subscription at
> publisher/distributor server, i received the following error:
> The server has been disconnected. Please reconnect it and perform this
> action again.
> How should i fixed it?
> Thank you.
I have had this problem twice. You can do the sp that paul
said to do, but for it work you need to reboot the
publisher. I kept running it, and kept receiving the same
error you were getting. We finally rebooted the publisher
and stopped getting the error.
You can also set a fake subscriber with the same
name/db/tables as the old one (just no data in them), and
then delete it. That worked for us also.

can't delete a row?

I have transaction replication setup between publisher server and subscriber
server.
I tried to delete a row from publisher's table,
but it looks like it's running forever.
delete from sym_type where type_id=1
And I don't know what to troubleshoot. but if I do an insert, then it's
working. i also see the row being replicated to subscriber when I do an
insert.
insert into sym_type values (-3, 'test', null)
scraching my head hard. replication monitor show no errors.
kevin
Kevin,
sounds like a contention issue - you could use sp_who2 and look for blocking
(use dbcc inputbuffer to see the cause of the block).
If not that, then try running select * from sym_type (nolock) where
type_id=1 to see how many rows there are - it might be a big delete?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||no... there is only 10 rows in the table.
and I inserted a row type_id = 1 to test replication, which it works
fine.
So I tried to delete the same row to rollback the change, but then it failed
to delete the row.
I think it maybe has to do with replication.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OgpkHnn1FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Kevin,
> sounds like a contention issue - you could use sp_who2 and look for
> blocking (use dbcc inputbuffer to see the cause of the block).
> If not that, then try running select * from sym_type (nolock) where
> type_id=1 to see how many rows there are - it might be a big delete?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin - what about the blocking results from sp_who2 - see any?
Paul Ibison
|||yes, sp_who2 shows BlockBy - spid 60.
Command delete.
interesting huh? why can't I delete it?
I even did dbcc checktable, nothing wrong.
so i go to spid 60
it's command "delete from sym_type where type_id =1"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGrJB6n1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> Kevin - what about the blocking results from sp_who2 - see any?
> Paul Ibison
>
|||Kevin,
strange - what's the spid/command command that is blocked by spid 60 - are
you saying it is another spid doing exactly the same command?
Paul
|||no.. there is [BlockBy] column in sp_who2 recordset,
there is value 60
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OuuDpro1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Kevin,
> strange - what's the spid/command command that is blocked by spid 60 - are
> you saying it is another spid doing exactly the same command?
> Paul
>
|||I'm still a little confused here You have 2 processes, and one blocks the
other. If I understand properly the one which is causing the block is the
delete, and the other one is blocked by the delete. If so, I agree that this
is strange and is the opposite of what I'd have expected. I suppose you
don't have some cunning triggers at work, or cascade deletes in action? -
worth checking out. DBCC OPENTRAN might also reveal something more but I
doubt it (still, worth a try). In your case I'd kill the delete spid, then
confirm that there's no blocking at all, and no open transactions. After
that, do a corresponding select, or perhaps just run the delete again if
it's ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||oh... I think I do have cascade delete in action.
my primary table is sym_type, foreign key table is portfolio_position ,
symbol_id is the key on both tables.
- it's enforce relationship for replication.
- it's enforce relationship for INSERTS and DELETES
it's cascading update related fields.
it's cascading delete related fields.
That must be it. when I do INSERT statement, there is no problem.
but when I do update or delete statement, query runs forever.
The next question is, why is this happen? I'm inserting a new symbol_id
which doesn't exist in foreign table,
why would this become a problem?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNLDCN2FHA.2444@.TK2MSFTNGP10.phx.gbl...
> I'm still a little confused here You have 2 processes, and one blocks
> the other. If I understand properly the one which is causing the block is
> the delete, and the other one is blocked by the delete. If so, I agree
> that this is strange and is the opposite of what I'd have expected. I
> suppose you don't have some cunning triggers at work, or cascade deletes
> in action? - worth checking out. DBCC OPENTRAN might also reveal something
> more but I doubt it (still, worth a try). In your case I'd kill the delete
> spid, then confirm that there's no blocking at all, and no open
> transactions. After that, do a corresponding select, or perhaps just run
> the delete again if it's ok.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin,
is there a self join, or a circular-relationship between the tables perhaps?
Do you see the same behaviour in the absence of replication (if this is
possible to test). Actually you could DTS the tables to a test server and
test there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Can't connect to Distributor

Hello,
I am trying to set up transactional replication between a server (that is both the distributor and publisher) and a client (msde). The server (sql2000) is configured for mixed mode authentication - windows & sql. Our client subscribers will be using a
nonymous pull subscriptions...so they are not part of our domain.
We have a vb.net application and I am using the code from http://support.microsoft.com/default...;en-us;Q319648 as a starting point. The only changes I made to the sample code was to change the SECURITY_TYPE.NT_AUTHENTICATION to DB_AUTHENTICATI
ON and add my login and password.
I cannot get past the "Process could not connect to Distributor "SERVER" error.
As a quick test...I ran a trace with SQL Profiler...and there isn't even a login attempt. I can ping the server from my client/subscriber.
Is there something else I am missing? I searched through some of the archives and none of the suggestions have worked for me so far.
Thanks
DD,
have you set up an alias in the client network utility on the client
computer? This article may help any other settings:
http://support.microsoft.com/default...22&Product=sql
Regards,
Paul Ibison
|||I did not set up an alias on the client. I didn't think that this step was necessary because we are replicating(well, trying) over the internet via ftp. We will have many clients and I didn't know if we will have to set up this alias.
I read through that article and went through the pieces that had to do with replicating over the internet with ftp. I have everything set according to those documents. One thing..HOWEVER...I actually cannot ping the server. The network admin turned off
or disabled ping on that server. I can go to a command prompt and type "ftp myserver.mydomain.com" and get to the ftp prompt. My question now is...by disabling the ability for machines outside our network and firewall to ping the server...does this me
an that replication won't work even though ftp has been enabled?
"Paul Ibison" wrote:

> DD,
> have you set up an alias in the client network utility on the client
> computer? This article may help any other settings:
> http://support.microsoft.com/default...22&Product=sql
> Regards,
> Paul Ibison
>
>
|||if you can't ping try tracert. or ftp.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DD" <DD@.discussions.microsoft.com> wrote in message
news:0FA8DD44-B9D6-48E9-8AA9-0478322F6B6B@.microsoft.com...
> I did not set up an alias on the client. I didn't think that this step
was necessary because we are replicating(well, trying) over the internet via
ftp. We will have many clients and I didn't know if we will have to set up
this alias.
> I read through that article and went through the pieces that had to do
with replicating over the internet with ftp. I have everything set
according to those documents. One thing..HOWEVER...I actually cannot ping
the server. The network admin turned off or disabled ping on that server.
I can go to a command prompt and type "ftp myserver.mydomain.com" and get to
the ftp prompt. My question now is...by disabling the ability for machines
outside our network and firewall to ping the server...does this mean that
replication won't work even though ftp has been enabled?[vbcol=seagreen]
> "Paul Ibison" wrote:
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
|||Well...I can go to a command prompt and connect to the server with:
ftp MyServer.Domain.com
If this is enough to indicate that a pull subscription should be able to connect to the server...then I am not sure what else to look at to resolve this issue.
I suppose I could remove the publication, disable replication and start over again.
"Hilary Cotter" wrote:

> if you can't ping try tracert. or ftp.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "DD" <DD@.discussions.microsoft.com> wrote in message
> news:0FA8DD44-B9D6-48E9-8AA9-0478322F6B6B@.microsoft.com...
> was necessary because we are replicating(well, trying) over the internet via
> ftp. We will have many clients and I didn't know if we will have to set up
> this alias.
> with replicating over the internet with ftp. I have everything set
> according to those documents. One thing..HOWEVER...I actually cannot ping
> the server. The network admin turned off or disabled ping on that server.
> I can go to a command prompt and type "ftp myserver.mydomain.com" and get to
> the ftp prompt. My question now is...by disabling the ability for machines
> outside our network and firewall to ping the server...does this mean that
> replication won't work even though ftp has been enabled?
> http://support.microsoft.com/default...22&Product=sql
>
>
|||no, no, no, go to your publisher, and in
c:\%windir%\system32\logfiles\MSFTPSVC look for the log file with today's
date stamp and locate entries that correspond to your attempt to connect.
There will be some numbers associated with the attempt, check this link out
to see what the error codes mean
http://www.4d.com/docs/CMU/CMU88906.HTM
Look in the FTP RFC Values section
This will tell you exactly why you are failing.
"DD" <DD@.discussions.microsoft.com> wrote in message
news:EB413FD6-D8AD-4128-80EA-CF7C95C34602@.microsoft.com...
> Well...I can go to a command prompt and connect to the server with:
> ftp MyServer.Domain.com
> If this is enough to indicate that a pull subscription should be able to
connect to the server...then I am not sure what else to look at to resolve
this issue.
> I suppose I could remove the publication, disable replication and start
over again.[vbcol=seagreen]
> "Hilary Cotter" wrote:
step[vbcol=seagreen]
via[vbcol=seagreen]
up[vbcol=seagreen]
ping[vbcol=seagreen]
server.[vbcol=seagreen]
get to[vbcol=seagreen]
machines[vbcol=seagreen]
that[vbcol=seagreen]
http://support.microsoft.com/default...22&Product=sql[vbcol=seagreen]
|||Thanks for the correction. I am still trying to figure this one out...but as a quick test, I changed the settings for the user login and password from the one I had been using (a sql long with limited access) to the "sa" account...and it works great. S
o...there must be some sort of permission problem I need to wrangle with. I have noticed since that server was updated from Win2K server to Win2003 Server...the security access/permissions have been tightened down and many things that use to work...no lo
nger.
"Hilary Cotter" wrote:

> no, no, no, go to your publisher, and in
> c:\%windir%\system32\logfiles\MSFTPSVC look for the log file with today's
> date stamp and locate entries that correspond to your attempt to connect.
> There will be some numbers associated with the attempt, check this link out
> to see what the error codes mean
> http://www.4d.com/docs/CMU/CMU88906.HTM
> Look in the FTP RFC Values section
> This will tell you exactly why you are failing.
> "DD" <DD@.discussions.microsoft.com> wrote in message
> news:EB413FD6-D8AD-4128-80EA-CF7C95C34602@.microsoft.com...
> connect to the server...then I am not sure what else to look at to resolve
> this issue.
> over again.
> step
> via
> up
> ping
> server.
> get to
> machines
> that
> http://support.microsoft.com/default...22&Product=sql
>
>
|||DD,
I am having the same problem here. It's stuck in the Snapshot Agent's connecting attempt to the distributor. It's not even related to Subscription. It just give up during initializing snapshot.
The distributor_admin and sa password are set to the same and mapped in remote_server login.
The only thing I haven't tried is restart the MSSQL and SQLAgent because all my clients are using the databases.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||You shouldn't touch the distributor admin's password. To fix things you
really should disable publishing and reenable it.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:Or5756wiEHA.2448@.TK2MSFTNGP12.phx.gbl...
> DD,
> I am having the same problem here. It's stuck in the Snapshot Agent's
connecting attempt to the distributor. It's not even related to
Subscription. It just give up during initializing snapshot.
> The distributor_admin and sa password are set to the same and mapped in
remote_server login.
> The only thing I haven't tried is restart the MSSQL and SQLAgent because
all my clients are using the databases.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.