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.
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.
Hi all, when I create publication with New Publication Wizard. I get the following errors. Did someone meet this before? Can you help me? Thanks!
===================================
SQL Server could not create publication 'JK'. (New Publication Wizard)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------ Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand) at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn) at Microsoft.SqlServer.Replication.ReplicationObject.CommonCreate() at Microsoft.SqlServer.Replication.Publication.Create() at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.CreatePublicationThreadMethod()
===================================
Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction. Changed database context to 'Test'. (.Net SqlClient Data Provider)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=3933&LinkId=20476
------------------------------ Server Name: nxstudio-davidsql2k5_dc Error Number: 3933 Severity: 16 State: 1 Procedure: sp_addmergepublication Line Number: 1034
------------------------------ Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Automatic identity range support is useful only for publications that allow updating subscribers. Changed database context to 'sodimprumde4'. (Microsoft SQL Server, Error: 21231)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=21231&LinkId=20476
Thanks for any help, If anyone know how to resolve this problem.
using publication from server one to server two. On server one, I have several directories with bcp files. Can the older files and directories be deleted?
In the step of creating a merge publication, as I noticed, the snapshot agent is started sucessfully. But very soon later (few seconds), it was shut down with an error message. I checked in the error history, the following message I found:
"... The replication agent encountered a failure. The step failed"
I also went to msdb.dbo.sysjobsteps and found "Detected nonlogged agent shutdown".
I am at my wits end here. For Replication the Books Online clearly state:
"The option to allow transformations is set at the time you create a publication"
However, I cannot find any options that allow me to do this in the Create Publication Wizard.
Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
This article cannot use the 'subscriber_upload_options' feature because the publication compatibility level is less than 90. Use sp_changemergepublication to set the publication_compatibility_level of publication 'testMR' to '90RTM'. Changed database context to 'test'. Publication 'testMR' has the following property: 'compensate_for_errors'. SQL Server subscribers below version '8.00.0858' will ignore this setting. (Microsoft SQL Server, Error: 21522)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21522&LinkId=20476
I found a bug in Management Studio, which can be reproduced with following steps:
1. Create a new publication with the 'New Publication Wizard' 2. Select a database which contains at least 2 tables. 3. Select 'transactional publicatioon' 4. Select at least 2 tables as articles 5. Set article properties for all table articles 6. Change 'Statement Delivery' to 'Insert statement', 'Update statement' and 'Delete statement' and save the article properties. 7. If you now check the properties for a single article you find that the 'Statement Delivery' is set to 'Do not replicate...'. If you generate this publication and create a subscritpion you will see, that actual no replication take place.
It took me a while to find out why my replication doesn't work! :-((
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!
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 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
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.
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.
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?
