There is a push subscription to an immediate-updating transactional publication with a queued failover. The publisher was also the distributor.
The publisher melted down and has been removed from service. The subscriber continues to queue up transactions for the now nonexistent publisher.
How do you remove the push subscription directly from the subscribing machine?
Thanks for your help,
Jim Schweitzer
Anobi Technology
Hi All,
Can push and pull subscriptions be configured on the publisher/distributer server?
We have server A running SQL Server 2000 configured as Publisher/Distributor and also a Push subscription (transactional replication) to Server B configured as Subscriber.
The idea was to have Server B as backup server if Server A goes down. Users will continue to update data in server B till server A is up.
Once server A is up, I want to synchronise the up-to-minuit date from Server B to Server A in the form of Transactional replication configured as "Pull Subscription" from Server A.
Is that possible?
Thanks
Sri
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 FilesPocketLVContactsLVContacts.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 FilesPocketLVContactsLVContacts.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
As I understand replication in Sql2K the only difference in push or pull subscriptions is where the agent runs. If I wanted changes in the publisher to be sent to the subscribers immediately after the change then I thought push would be better. But, if I am equally interested in changes made at the subscriber then what should I use? Or does the agent monitor both the publisher and subscriber at the same time?
View 1 Replies View RelatedHi all!
Is there an option to monitor current state of Merge Push Subscription from the Subscriber, without connecting to the Publisher Server? I have examined many SPs and system tables at Subscriber, but didn't find any reliable method...
Hi all,
I've been looking for the right stored procedure to create a push subscription for publication but i haven't had much luck so far. So I was wondering if you guys know which SP is appropriate, that'd be super great!! thanks guys!
note. the sp should run on the subscriber database.
thanks again
Hi There,
I wonder if anyone has run into this problem before.
I have inherited a SQL Server 2000 box with what appears to be a push subscription set up on it. However, the original Subscription database has long since been removed, leaving that box with what is essentially an orphaned push subscription.
We are trying to remove the subscription, but do not understand how. All documentation I have found keeps insisting that this be done at the subscriber database (which is for all effective purposes, destroyed).
How do I get rid of it?
Any ideas would be dearly appreciated.
Thanks.
Hi,
We have created replication from one production server to another backup server by using SQL scripts. These scripts have been generated by SQL Server after manual initial setup. When I ran these scripts they did not add the subscription for the backup server because a stored procedure were missing named something like sp_addsubscription. Therefore I ran the script on the production server and created the subscription manually on the backup server. But I made an error, I created a push subscription first but I should have created a pull subscription. Now I can't delete the push subscription. I don't even get a error message. I can't press delete and when right clicking on it I can only choose Help. The subscription has the following properties:
Type: push
Status: retrying
Last action: The process could not connect to distributor 'PSERV'
Replication type: Snapshot
Anyone know how I can delete this push subscription on my backup server?
Best,
/M
I need a way to push create index statements drop to the subscribers. If I can use replication to distribute index creation that would be great. We don't allow our clients to make any schema changes directly. We are using transaction and merge replication.
View 4 Replies View RelatedI am using SQL 2000. How can I get my transactional replication reinitialized after it has failed with several attempts.
I know one way of doing it through enterprise manager and specifying the subscription to reinitialize. But this will apply the snapshot and will take long time.
Is there a fast way of doing it?
-Nipul
in the clustering lab I was setting up transact replication - created the publication ok, but when I setup the subscription, wasn't sure what account it needed to use :/Â tried various formats of Administrator and Password;1 etc - but no dice. what account I need to be using?
btw: im trying to set this up on single server.
ERROR MESSAGE:
"Message: Incorrect syntax near 'NoteID'."
Here is a snipet from the output file:
2006-03-27 13:01:49.820 Skipping file 'NotesHistory_21.sch' because it has already been delivered for a previous article or by a previously interrupted snapshot.
2006-03-27 13:01:49.867 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON
2006-03-27 13:01:49.898 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:49.945 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON
2006-03-27 13:01:50.008 [17%] OLE DB Subscriber 'LUZERNV4-PDOSQL4': CREATE TABLE [dbo].[MSmerge_conflict_EFILE_MERGE_NotesHistory](
[NoteID] [int] NOT NULL,
[CaseRefNum] [int] NOT NULL,
[KeyField] [varchar](500) NOT NULL,
[KeyValue] [varchar](500) NOT NULL,
[Note] [varchar](800) NULL,
[NoteField] [varchar](100) NULL,
[ScanDocID] [int] NULL,
[PDF_ID] [int] NULL,
[RescanCode] [int] NULL,
[RescanNote] [varchar](100) NULL,
[AuditID] [bigint] NOT NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL
)
ꜽśã¹? êœ?Å›B 27 13:01î–”Å›è›ªç —êœ?Å›é”?Ä€Percent Complete: 17
2006-03-27 13:01:50.086 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET ANSI_NULLS ON
2006-03-27 13:01:50.117 Preparing table 'NotesHistory' for merge replication
2006-03-27 13:01:50.164 OLE DB Subscriber 'LUZERNV4-PDOSQL4': SET QUOTED_IDENTIFIER ON
2006-03-27 13:01:50.195 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.258 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL
alter table [MSmerge_conflict_EFILE_MERGE_NotesHistory] add origin_datasource_id uniqueidentifier NULL
2006-03-27 13:01:50.351 OLE DB Subscriber 'LUZERNV4-PDOSQL4': if object_id('MSmerge_conflicts_info') is not NULL
create nonclustered index [ncMSmerge_conflict_EFILE_MERGE_NotesHistory] on [MSmerge_conflict_EFILE_MERGE_NotesHistory] ([rowguid], origin_datasource_id)
2006-03-27 13:01:50.383 OLE DB Subscriber 'LUZERNV4-PDOSQL4': exec sp_MSsetconflicttable @article = N'NotesHistory', @conflict_table = N'MSmerge_conflict_EFILE_MERGE_NotesHistory'
2006-03-27 13:01:50.430 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.476 [17%] The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.Percent Complete: 17
2006-03-27 13:01:50.50é”?Ä€
2006-03-27 13:01:50.539 Applied script 'NotesHistory_21.cft'
2006-03-27 13:01:50.586 OLE DB Subscriber 'LUZERNV4-PDOSQL4': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.617 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.711 Percent Complete: 17
2006-03-27 13:01:50.742 {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')}
2006-03-27 13:01:50.773 OLE DB Distributor 'LARGOS00-SDI': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2006-03-27 13:01:50.867 Percent Complete: 0
2006-03-27 13:01:50.898 Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSsetconflicttable (N'NotesHistory', N'MSmerge_conflict_EFILE_MERGE_NotesHistory', N'LARGOS00-SDI', N'EFILE', N'EFILE_MERGE')}
2006-03-27 13:01:50.961 Percent Complete: 0
2006-03-27 13:01:50.992 Category:SQLSERVER
Source: LUZERNV4-PDOSQL4
Number: 102
Message: Incorrect syntax near 'NoteID'.
2006-03-27 13:01:51.039 Percent Complete: 0
2006-03-27 13:01:51.070 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
2006-03-27 13:01:51.133 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.180 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.226 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.258 Disconnecting from OLE DB Subscriber 'LUZERNV4-PDOSQL4'
2006-03-27 13:01:51.305 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.336 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.383 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.414 Disconnecting from OLE DB Publisher 'LARGOS00-SDI'
2006-03-27 13:01:51.445 Disconnecting from OLE DB Distributor 'LARGOS00-SDI'
2006-03-27 13:01:51.492 Disconnecting from OLE DB Distributor 'LARGOS00-SDI'
2006-03-27 13:01:51.523 The merge process will restart after waiting 60 second(s)...
I keep getting this following error when trying to set-up an updateable subscription on SQL 2005 (latest service packs). Server is set for mixed mode auth.
At the final stage of the new subscription wizard I get:
Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)
The user is not associated with a trusted SQL Server connection
Login failed for user ''. The user is not associated with a trusted SQL Server connection
Error Number: 18456, Severity: 14, State: 1,Line Number: 1
Can anyone recommend a possible way of debugging this? Is it some issue with RPC security?
I am executing sp_dropmergesubscription, but the rows are still in dbo.msmerge_subscriptions, and are still shown in the replication monitor as expired; the last synch dates were in april (expiration is set to 10 days). The 'expired subscription clean up' job appears to be running okay. I need to remove these subscriptions from the publisher as the subscribers are mobile devices, which sometimes are lost.
Hi all,
I'm a rather newbie, not only to this forum but also to sql server having a question to the following issue:
Is it possible to drop/remove/delete the "orphan" of a merge subscription on one instance of sql server without having the (former) distributor/publisher (on other instance) available?
The background is: I had a small replication infrastructure with two instances (on two machines), one the publisher and distributor, the other the subscriber. Now it happened that the publisher/distributor machine was completely set up new without having the replication dropped in advance, what remained on the subscriber is now a database with all the merge/replication tables and the guid columns in the user tables.
Moreover this, an entry remained in the subscription saying that a subscription with the former publisher exists...
Can I remove these "orphans" without having to setup the instance again?
Thank you,
Andy
Hi All,
The following is the report from the SQL Server Mobile Subscription wizzard, Any Ideas?
New Subscription Wizard
- Beginning Synchronization (Success)
- Synchronizing Data (100%) (Error)
Messages
A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit
HRESULT 0x80045647 (0)
Invalid parameter @subid specified for sys.sp_MSmerge_log_idrange_alloc_on_distributor.
HRESULT 0x0000523F (0)
The operation could not be completed.
- Finalizing Synchronization (Stopped)
- Saving Subscription Properties (Stopped)
Initially i thought it might be that some of the articles had primary keys that were of type nvarchar rather then Int thus resulting in no identity range being able to be assigned to those articles.
Test 1: I tried removing all articles that had nvarchar primary keys and left only one table that had an identity Int primary key colum. I then ran the snapshot agent. I then run through the Subscription Wizzard again and the error was the same.
Test 2: Then reading the error message again i tried those tables that didnt use identity columns and the wizzard completed successfully. Any idea what would be wrong with my articles that have identity columns. The article properties for the identity columns use the Identity Range Management defaults.
Any help would be appreciated.
From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.
How can I achieve these operations programmatically?
In particular, how do I initialize a single merge subscription from the publisher?
I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time
I have a large number of merge subscribers and want to reinitialize all except one
aero1
I have setup Transnational replication with updatable subscription.
There were around 7000 rows update at subscriber from one query which was executed at subscriber side.
But this update has not taken place at publisher yet and so to other subscribers.
Is there any way i can monitor pending log or where is the issue?
I find so many ways to see pending logs or statistics for - "Publisher to Subscriber" but not for "subscriber to publisher"
What is the way to see subscriber to publisher pending and issues?
Hi.
First of all, I apologize for my english
I have two publications. Some of the data are the same on the two publications. Both are configured as follow : The identity range management is set to "automatic" and the tracking-level is set to "Column-level tracking". Until there, every things works fine.
But, if i'm deleting one of the publication and if i'm deleting one of the rows that were replicated on the two publications i'm getting the following SQL Exception : "Invalid object name 'dbo.MSmerge_repl_view_1CAD32C4FF904A3CA27518B0C4BFF716_70308DE2261C4EC784C56131902E7D1C'"
If i'm watching the status of the leftover replication through the replication monitor, i get this error message :
"Error messages:
The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation. If a republishing Subscriber has run out of identity ranges, synchronize the republishing Subscriber to obtain more identity ranges before restarting the synchronization. If a Publisher runs out of identit (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199417)
Get help: http://help/MSSQL_REPL-2147199417
The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. (Source: MSSQLServer, Error number: 20663)
Get help: http://help/20663"
I checked the given links but they're useless.
So I tried to reinitialize the subscription with the "use a new snapshot" option enabled without any success either. I did only obtain a new error message :
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Failed to pr"
I didnt have any idea to correct this issue, so I would appreciate any help.
Thanks.
David.
is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)
View 3 Replies View RelatedI need to drop and recreate few subscriptions in transactional publication
Do I need to worry about log marker issues ?
Do I need to set the primary and replicate databases in 'DBO use only'
The Primary and Replicate databases are being accessed all the time.
We need to configure a way to publish from development to testing and from testing to production. An easy, automated process would be best.
View 1 Replies View RelatedI have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
View 2 Replies View RelatedHi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View Related
It seems to me that if a scheduled SSRS report subscription fails ( Status Message - An error has occurred during report processing) , that I actually need to delete the subscription and reconfigure it from scratch. The scheduled job doesn't try to run again automatically (say the next Monday on a weekly Monday schedule).
Is there a way to "reset" a failed subscription without have to recreate the entire subscription?
Thank you
Devon Kyle
28638
SSCE_M_DUPLICATETABLE
Existing subscription already contains table included in the new subscription.
What are the possible causes of this merge replication error?
Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?
Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?
Dear all,,
I used a code that import data from an excel file into a dataset,
now I want to insert the dataset into a table in my database(SQLserver database) using a VB.NET code
Could you help me?
Thanks in advance,,
Here is my code:
Imports System.Data.OleDbPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ClickDim connString As String = ConfigurationManager.ConnectionStrings("xls").ConnectionString
' Create the connection object Dim oledbConn As OleDbConnection = New OleDbConnection(connString)
Try
' Open connection
oledbConn.Open()
' Create OleDbCommand object and select data from worksheet Sheet1Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)
' Create new OleDbDataAdapter Dim oleda As OleDbDataAdapter = New OleDbDataAdapter()
oleda.SelectCommand = cmd
' Create a DataSet which will hold the data extracted from the worksheet.Dim ds As Data.DataSet = New Data.DataSet()
' Fill the DataSet from the data extracted from the worksheet.oleda.Fill(ds, "Sheet1")
' Bind the data to the GridView
GridView1.DataSource = ds.Tables(0).DefaultView
GridView1.DataBind()
Catch
Finally
' Close connection
oledbConn.Close()
End TryEnd Sub
End Class
I'm trying to replicate two very big databases with about 10 million
of 4000 characters each. The publisher is SQL 2000, subscriber
is SQL 7.0
The subscriber will also perform full text searches.
I'm trying to decide wheter I should use PULL or PUSH.
The publisher is operating on a very low quality/speed internet
connection, where the subscriber is enjoying a T1.
Help, anyone?
Thanks,
-Michael.
Hi to all.
How can we identify that a transactional replication is configured in pull or push subscription..
where we have to check it out.
While trying to push a tracked table using RDA.push, I get the following error:
Error Code: 80004005
The message cannot be built. The make message failed.
Minor Err: 28581
Source: Microsoft SQL server 2005 Mobile Edition.
All other tables in the database are getting pulled and pushed correctly. This table is different only in the larger number of columns, around 150. It has a primary key, no other constraints.
Any help to find the reason for this error will be greatly appreciated.
- Paul
Hi All,
I am developing an application in which i have to send data from local Sql Server compact edition database[Which is in a Windows Mobile Device,] to central server[SQL Server 2005]. I am using RDA method for communication
Can i use push method to send data from local DB to Central DB?
Is it must to use PULL method before using PUSH method?
Thanks in Advance..
Im using the rda.push to push the data back to the SQL SERVER.
My SQL table is OrderDetail with a blank data inside?
My push was unseccessful due to the follow error, can any one help?
[code]
rda.Push("OrderDetail", rdaOleDbConnectString, RdaTrackOption.TrackingOffWithIndexes)
[/code]
Error message:The table is not a tracked table
Hello all. Please excuse my ignorance, as this is not my territory. I administer a website which is hosted remotely. This site has SQL7 running the data to dynamically build the site. Every Sunday our hosting service runs a DTS package to push the data they have down to us, so we can run reports and analyze it. We recently upgraded to SQL2000, while our host has stayed with SQL7. Now our DTS is failing. They say it is because 7 cannot push to 2000. But they think that we could pull from them. How do I go about setting that up? Will the DTS wizzard walk me through most of it?
Thanks in advace
Adrian Miller