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.
Hi to evebody. I'm working with the transactional publication with updatable subscriptions provided by SQL Server 2005. The replication works pretty good from the publisher to the subscriber, but I'm having some problems when the data must go from the subscriber to the publisher.
When I do an update in a subscriptor's table, the database engine shows the following error:
21064 - 16 - The subscription is unavailable for immediate updating because it is marked for reinitialization. Try again after the reinitialization completes.
And rollbacks the transaction.
Does anybody knows what to do to solve this problems.
The publisher is a Windows XP with the SQL Server 2005 Developer edition with SP2 The subscriber is a Windows 2003 Server with SQL Server 2005 Developer edition without SP2 I'm using also the inmediate updating subscriptions. Both operative systems have the MSDTC runing.
Thank you in advance.
Sebastian.-
PS: Sorry about my english, it's been a long time without using it.
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.
I have configured transactional publication where some tables have a simple row filter. And I made a supscription to this publication. When I make snapshot and replication initialization, all rows are updated correctly and passed to subscriber. But when I change some column at a row included by filter at publisher, it is never updated on the subscriber. Other tables and that same table when I remove filter is updated correctly.
What can be problem?
I am using SQL Server 2005 Standard for publisher, and SQL Express 2005 for subscriber, both with SP2.
I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?
I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.
Does it behave differently for small and large number of articles?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Subscription to "Transactional Publication with Updateable Subscriptions" works only one way. Changes take effect on subscriber, but the subcriber is unable to update data on publisher.
I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.' All agents are running at the Distributor (Publishing Server.)
The subscriber is unable to connect to the Distributor using the SQL Server login.
Following is the error message I get:
Creating Subscription(s)...
- Creating subscription for 'SQL3' (Warning)
Messages
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)
I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.
Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.
Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?
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)
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!
I am very new to SQL coming from a SAS background.
I work at a College where I am trying to create a table that basically stores application data so that I can record how many applications we had on a set day.
For example I want to run it say bi-weekly and create a date/time stamp for each row and then the next time it runs it appends to the original table but with the new date/time stamp.
I have managed to create the table and the date/time stamp but I'm not sure how to do the next stage.
This is not the best practice as the table will get "big" quite quickly (although we're only dealing in the hundreds in terms of rows). Ideally I'd have a more transactional database that only appends a record if there is a change i.e. the status of the application has changed from "Received" to "Interview" or similar.
I'm not sure how to copy and paste the code dynamically in this forum but this is what I have got so far (see below).
SELECT CC.cc_name as Academy, SUBSTRING(CC2.cc_reference,1,4) as Cost_Centre, MD2.m_reference as Course, MD2.m_name as Course_Name, CAST(MD2.m_start AS DATE) as Start_Date,
i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?
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'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology? Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
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
