I have a database with two publications, one for SQL CE Clients with filters that send each client its data and keeps it database with "low weight" filtering data not older than a week, and the other publications for SQL Servers.
The publication for SQL CE Clients works excelent, the other one has this problem:
In the publisher i can insert data that should be replicated to subscribers, but subscribers don't receive it. And if I try to insert both in the publisher and the subscriber, the subscriber throws a Primari Key exception.
In the other way, if i insert the data in the subscriber, when it synchronizes, the publisher pulls this data to the table and keeps it, but deletes the subscriber table due to a "conflict". And it never sends the data back to the subscriber, keeping the subscriber's table always clear.
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 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 are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."
This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.
Hello Everybody, If any one have solution then please help me. Thanks in Advance. I do the following steps:
I have created two publications on my SQL SERVER for merge replication Publication A €“ which returns all rows from the Table1 Publication B €“ which returns all rows from the Table1 where the field MANAGER =€™ABC€™ I have two clients who have MSDE Client 1 is subscribed to Publication A and Client 2 is subscribed to Publication B All works fine till now and I am able to make transfers from the two clients and get all the changed data However, now If I change the filter rules for Publication B and set that it should return all rows from the Table1 where the field MANAGER = €˜DEF€™ , SQL Server tells me that I have to reinitialize all snapshots for all subscriptions. If I don€™t do this it doesn€™t allow me to make a transfer from my Client 1. As you can see in my example I have not made any changes to Publication A to which Client 1 is subscribed. So this seems to be illogical. In our case it would not be possible for us to reinitialize the subscriptions for all Publications when the rules for only one Publication are changed because we may have a lot of clients connected to our Server and if we reinitialize the subscription then all the data is sent again. If anybody know that how to restrict the other publications re-initialization then please tell me how we can do.
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.
I have added a new table to a database (existing publication) using T-SQL, I then opened up publisher properties, and ticked the new table/article so that it would be added to the subscriber. It did not show up.
I did not use a snapshot to initialize the subscription.
Immediate Synch is 0. allow_anonymous is 0.
I mark the subscription to be reinitialized. When I start the snapshot agent I get '0% A snapshot was not generated because no subscriptions needed initialization'.
What could I be doing wrong, or missed out? Do I need to drop and recreate the subscription to get the article to show up?
I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:
Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'. Msg 574, Level 16, State 1, Server SQLSERVER, Line 2 CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.
Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!
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 .?
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 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 .
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.
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.
Hi All,I have a problem, a user deleted some tables from a couple db's dealingwith replication. I now get this error on those db's.The process could not execute 'sp_repldone/sp_replcounters onservernameI have deleted one of the database's and neither are listed underpublications but they still show up under replication monitor.Any ideas on how to delete them would be great. When I try I getError 208: Invalid object name 'sysarticles'Invalid object name 'sysschemaarticles'Could not use view or function 'sysextendedarticlesview' because ofbinding errors.TIADave
I had a publication that I removed from the Local Publications folder but it still shows up in Replication Monitor (with a red 'X'). No jobs exist related to this publication either.
Everything is working ok, but I can't get rid of this from the Replication monitor.
I want to create one more publication on a database (SQL Server 2005) that is already a publisher and has subscribers. The subscribers of the second publication will not be inserting new records so I will mark the articles (tables) as download-only.
Is there anything extra that I should take into consideration? Are there any side effects of this process?
After upgrading SQL Server 2005 with the recent service pack, I am unable to create a publication. When I right-click on [INSTANCE]/Replication/Local Publications and select "New Publication...," the mouse cursor turns into an hourglass, but the wizard never shows up, nor is any error displayed.
There is a logged error that's been showing up that may or may not be related. The error message is "Replication-(null): agent (null) scheduled for retry. Could not clean up the distribution history tables."
Any ideas on how we can restore replication functionality on our SQL Server instance?
Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?
I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?
Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?
