I have two SQL 2000 Server running on Windows 2003 Standard Edtion. I am using merge option to replicate database as Transactional option is excluding some tables. Both server are not in Active Directory and I am trying to replicate over the internet. I got following error message while using Merge option:
Error Message:
The subscription to publication 'XXX' is invalid.
Error details:
The subscription to publication 'XXX' is invalid.
(Source: Merge Replication Provider (Agent); Error number: -2147201019)
---------------------------------------------------------------------------------------------------------------
The remote server is not defined as a subscription server.
(Source: x.x.x.x (Data source); Error number: 14010)
---------------------------------------------------------------------------------------------------------------
Any help regarding the same will be appricated. Thanks.
Because of problems trying to alter databases used for replication my software would need to find out if a database is a publisher or was repliated (using T-SQL). Is this possible?
I am using sql 2005 replication in my application. But how can i log the transactions that are happening in the server and client databases please help me...
I've a merge rep running with 4 subscribers for a while. Now I wanted to add another subscriber and when I open the publisher in the pull subscription assistant dialog, there are no publications in it. (even not if i switch to "allow anonymous sub") But every- thing seems to be alright wirh the publication itself! It even doesn't work to select the publication in a pull subs. from an other db on the same server! Any ideas?
I had a merge subscription expire and now need to synchronize two disparate databases. How do I do so without overwriting the subscriber database with the publication snapshot? Thanks for any help you can give.
Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)??
I want to set up monitoring to make sure I am aware if something for some reason does not replicate
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.
Occasionally I have this error happening in some of my subscriptions: Subscription contains invalid parameters.
What is actually happenign, one of the parameters has lost the value on which it was previously running without problems. If I open subscription the first parameter, of more, is having no value. The value that is usually lost is first option in drop down list, being for ALL of bellow values. When the error happens it has no value, and that option normally doesn't exists as a parameter value. (If you go creating new subscription that same parameter doesn't have empty values as options to be selected. I usually return ALL and then other individual values.)
For example, one report is having paramteres that returns values from the stored procedure, and the return dataset would be:
Value Label ----------------- ----- 'SP1','SP2','SP3' ALL 'SP1' SP1 'SP2' SP2 'SP3' SP3
So, when the error happens, in no reason (no changes to proc, to report design, to subscription,...) usually those subscriptions that had ALL for this parameter have lost the value, the option is BLANK, and is treated as invalid value, which is correct, as my query also doesn't return BLANK value. I have to manually go into subscription and set up the value back to ALL and then it's OK, untill next time.
Has anyone experienced this problem?
Does anyone now the fix for this? We thought that hot fix on SP2 may solve this, we applied on dev server but it didn't.
(NOTE: FROM a NON-IT PERSON) We've implented SQL 2005 and Reporting Services, but our subscriptions only work internally: when we try to send reports via subscriptions to valid external email addresses, we get a message: invalid email address and the entire subscription does not work.. PLEASE ADVISE. IT WILL BE GREATLY APPRECIATED!
I've set up a data-driven subscription on a report but it's not sending and the error log says it's because:
Default value or value provided for the report parameter 'CSRFaxID' is not a valid value.
For the particular dataset record causing the error I'm passing the value Null to the report parameter. The report parameter CSRFaxID is set up with "Allow Null" and with a default value of Null. I can run the report in my browser with CSRFaxID set to null, but the scheduled subscription is not able to produce the report with the same parameter value. Does anyone have any idea what the problem is and how I can fix it?
Hi, everyone, I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
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?
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?
I have a strange error, when I try to create publication
Msg 3933, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1222 Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
What is it?
P. S. The error arise with all types of replication.
I have a problem with a publication which is a transactional replication on SQL Server 7.0 . I want to add stored procedure to the existing publication along with the tables . But the Enterprise manager does not allow to select or check the box against the required stored procedure although it displays when i right click on the publication and select proeprties .
Can i have some help in this regard ? Does anybody knows to add a stored procedure by means of a query .
Is there any other way to modify publication in SQL Server 7.0 although it is possible in SQL Server 2000 .?
i had configured a merge replication betwen 2 server and i was running fine. but due to network problems my network admin has changed the ip address and name of the publisher, distributor and subscriber.
what i did. i just drop the publication and now i m trying to create a new one. but i cannt' able to create. i dont' know y.. this is the error i m facing..
Msg 208, Level 16, State 1, Procedure sp_addmergepublication, Line 239
Invalid object name 'dbo.sysmergepublications'.
Msg 21040, Level 11, State 1, Procedure sp_MSaddmergepub_snapshot, Line 65
Publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 20026, Level 16, State 1, Procedure sp_grant_publication_access, Line 176
The publication 'DIS_MASTER_Merge' does not exist.
Msg 208, Level 16, State 1, Procedure sp_changemergepublication, Line 205
I have been wrestling with this one all day and cannot find anything on MSDN, google, etc.... I have configured replication, but cannot create a new publication either programmatically or using the wizard. I programmatically did the following: 1. I set up my local server as a distributor, and created a distribution database (@distributor set to server sysname) execute sp_adddistributor @distributor = @distributor execute sp_adddistributiondb @database = @distributionDB, @security_mode=1; 2. I registered my local server also as a Publisher (@publisher set to server sysname, @distribution = 'distributor') SELECT @sql = 'sp_adddistpublisher @publisher = ''' + @publisher + ''', @distribution_db = ''' + @distributionDB + ''', @working_directory = ''' + @directory + ''', @security_mode = 1' SELECT @sp_executesql = quotename(@distributionDB) + '..sp_executesql ' EXEC @sp_executesql @sql 3. I enabled my local database for transactional replication (@replicationDB) exec sp_replicationdboption @dbname = @replicationDB, @optname = 'publish', @value = 'true 4. I added a logReaderAgent on the ReplicationDB SELECT @sql = 'sp_addlogreader_agent @publisher_security_mode = 1' SELECT @sp_executesql = quotename(@replicationDB) + '..sp_executesql EXEC @sp_executesql @sql
All is well up to this point, no problems, all looks great. When I check the status of things using sp_help repl stored procedures to check on publisher, distributor all looks healthy. Also looks good when I use the SSMS replication interface to check on distributor, publisher, etc. . HOWEVER...... I cannot create any publications.
I tried programmatically, and also by using the wizard. I keep getting the same error :'Cannot insert the value NULL into column 'pubid', table'. This is the sql I try to run: EXEC sp_addpublication @publication = 'test3333', @status = N'active', @allow_push = N'true', @allow_pull = N'true' This is the error: Msg 515, Level 16, State 2, Procedure sp_MSrepl_addpublication, Line 1320 Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 14018, Level 16, State 1, Procedure sp_MSrepl_addpublication, Line 1348 Could not create the publication. If I use the wizard instead of trying to create the publication programmatically, I get this error (just about the same content, but a little different wording): Creating Publication - Creating Publication 'NewPublication' (Error) Messages * SQL Server could not create publication 'NewPublication'. (New Publication Wizard)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot insert the value NULL into column 'pubid', table 'CypressMaster.dbo.IHpublications'; column does not allow nulls. INSERT fails.
Could not create the publication. Object 'NewPublication' does not exist or is not a valid object for this operation. Changed database context to 'CypressMaster'. The statement has been terminated. (Microsoft SQL Server, Error: 515)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=515&LinkId=20476
hi I was following the walkthrough "Creating a Mobile Application with SQL Server Mobile" and when I got to the point where you create a "local publication" I couldn't find the link "Local Publication" in my Object Explorer.
I read all the help in books online however id did not tell me how to bring that link there.
I did install the replication component using the CD installation. I have SQL Server 2005 Standard Edition and Visual Studio 2005
I also found the help "Using the Publication Wizard to Create a Publication" but did not know where to locate or start the wizard.
When go to set up a new publication I get the below error:
TITLE: New Publication Wizard ------------------------------
SQL Server is unable to connect to server 'LEICS-DC1'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ''. (Replication.Utilities)
------------------------------ BUTTONS:
OK ------------------------------
I am specfing the server name but it still brings back the same error, the message at the bottom also stats to try and connect to '' which of course is impossible. This is a domain controller if that makes any difference, SQL 2005 SP2, Win 2003 STD.
Hi, I've come across this one before in one of the threads, unfortunately for me the author managed to figure it out for his/her self and there for no answer was posted, so can anybody help PLEASE???
Creating Publication
- Creating Publication 'SQLMobile' (Error) Messages * SQL Server could not create publication 'SQLMobile'. (New Publication Wizard)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid object name 'dbo.sysmergepublications'. Publication 'SQLMobile' does not exist. Changed database context to 'SQLMobile'. (Microsoft SQL Server, Error: 208)
Its been created via UI on SQL2005 the distributor is correctly installed i also have not tried creating a publication on any other database.....this is the first.
I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration .
Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified . The table is configured for transactional replication .
I will have to drop the entire publication and create all the subcribers again .
We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.
Hi, I disabled a merge publication on my server. I then ran sp_removedbreplication to clean the system tables. Thats fine. When I recreate a new publication on my server now, I still get the red x on my publisher node in my replcation monitor from the error I was getting before. Evenm though this publication is error free. Is it saving an srror flag in one of the sytem tabbles
Hi, I'm using DMO (SQLOLE65.dll) to programmatically replicate selected publications. The Publication object supports a property called Enabled, which can be set to FALSE. I'm setting the enabled property to TRUE for those publications that need be replicated, and make the others FALSE. Still, all publications get replicated. [I give DoAlter to commit the changes i made.] Any solution,Please mail me ASAP to venkateswaranb@synectics.soft.net
Hi, I tried to run the following script from SQL2005 server A (publisher) from Database 'TestDB' to SQL2005 server B (subscriber) database 'TestDB' ------------------------------------------- exec sp_addsubscription @publication = N'publish_TestDB_FromCA', @subscriber = N'subscriberTest', @destination_db = N'TestDB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
with following error:
Msg 14013, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 252 This database is not enabled for publication.
Hi, I would like to know the procedure to be followed to add a table to the existing publication. The subcriber doesn't have the object yet. I have data in the source table and I want to add this table in a existing publication for transactional replication. Any advise is appreciated.
We have to add some hardware and I was wondering if someone could provide a stepwise tip to do it .
It is a 3 Server Environment Pub/Dist/Sub All three running under SQL2K Here is what I am planning.
a. Uncheck the Enable in Distributed Agent for each replication b. Wait for all replications to complete c. Shut down Publisher ? Or do I have to do something else before that.
I have a case of SQL Server 2005 production db's publication which does not have either Log reader, snapshot or distribution agents. I need to modify primary key in one of the tables, and existing publication does not allow this. When I try to start snapshot job in GUI I am getting of course error: "Could not find the regular snapshot job for the spicified publication. Supply either @job_id or @job_name to identify the job, error 20678."
Is there any way to erase it with system st procs or SQL? Help is greatly appreciated.
