Question - what does this do in Snapshot replication?
|Well, this is a "trick question" as I found out recently to my embarrassment. There are no database triggers as in merge to capture DDL changes, and no log reader agent unlike in transactional to capture these changes. So, there is no technology installed which can make this work. The only way of propagating a DDL change in snapshot replication is to reinitialize (or initialize although there is little difference in snapshot). In other words, setting this to true or to false doesn't make a jot of difference :).|
|How can I get the names of my replication agents (after I have changed them frm the original names)?|
Sometimes you'll change the names
of your agents. This might be to standardise them to follow your
naming convention. Afterwards you'll still want to know which
jobs belong to which publications though!
Here is code to do this:
select a.publication, b.name as CurrentJobName
Note that this'll return 'ALL' for the log reader's publicaiton as they don't really belong to a publication.
|Why doesn't logging to an output file work?|
First of all it does work!
However you have to use the command-line method
the MS site. So, why do some people think it doesn't? That's
because the replication agent profiles in SSMS also have the
same output parameters listed but these will either not work or
cause errors. I've done a partial screen-shot below. This is a
merge agent but the same applies to others. I have populated
values for the -OutputMessageFile and -Output parameters, but
When I first looked at this in
SSMS I was confused.
Clearly an irritation we have to deal with and continue with the tried-and-tested command-line method for now.
|My replication monitor incorrectly shows a failed publication - how can I remove it?|
|I have seen this a few times recently. In each case it occurred because of a restored database! Basically, there was an existing published database in the test environment which was correctly configured, worked normally and which showed up correctly in replication monitor. Someone then restored a backup from production on top of this test database without first removing the subscriptions and the publication. Replication monitor at this stage shows the publication still existing but with an error. In this case running sp_removedbreplication, sp_droppublication, restarting the SQL Server service and the like will not solve the issue - still the replication monitor shows an error. I have tried removing some of the system metadata in the distribution database, which also failed to remove the error. Ultimately the only way I found to remove the publication from the replication monitor was to recreate a publication with exactly the same name and then delete it. The dummy publication only needs the same name - the articles can be anything from the database - and once deleted the replication monitor registers the change. No doubt there will be some sort of system proc to do this properly at some stage and I'll update this entry.|
|What should I do if my system is running out of memory when too many agents synchronize?|
|You can limit the number of concurrent synchronizations; in merge this is done on the GUI or by setting the @max_concurrent_merge property of sp_addmergepublication. For other agents you'll need to follow these guidelines to make the necessary edits to the registry.|
|How can I add a "NOT NULL" column to an existing article?|
Basically you need to add a column with a default constraint. After that the column is made nullable and the constraint can be removed. This worked for transactional publications. For merge it worked but I had to rerun the merge agent after it failed once. The code I used is below:
alter table tXXX ADD Salary INT NOT NULL Default 0
alter table tXXX alter column Salary INT NULL
alter table tXXX drop constraint DF__tXXX__Salary__353DDB1D
|How can I have redundancy for the publishing database?|
Database mirroring can be used in conjunction with replication to provide availability for the publication database. There is an article about setting this up
Log shipping can also be used in conjunction with replication.
|How can I see the text for 'sys.sp_MSrepl_helparticlecolumns' or any other such hidden replication system stored procedures?|
Here's a nice trick for you! Some of these procedures aren't accessible using
sp_helptext and they also aren't accessible using the OBJECT_DEFINITION
function. However if you use the Dedicated Admin Connection (DAC), you'll be able to access the real text of the procedure:
The trick is to open up a connection using the DAC (when you open a query window to "yourservername" just use "ADMIN:yourservername" instead).
|What is the cleanest way to setup a Replication Publication such that only the table schema is published and no data ?|
If you are using a SQL Server 2005 distributor, there is an unofficial /NoBcpData switch supported by the snapshot agent.
Remember this is an unofficial switch that comes with no warranty whatsoever!
|How can Replication Alerts be written to Event Viewer ?|
|Open up the replication alerts folder, double click on the alert you are interested, click on the browse button (the three ellipses), click on the edit button, select always write to the Windows Event Log.|
|How does replication work across versions of SQL Server (SQL Server 2005, SQL Server 2000, SQL Server 7.0)?|
|Please take a look at this link (2008 downwards) and this link (2005 downwards) to see what the issues are.|
|How can I avoid I/O errors during synchronization after installing sp4 on SQL Server 2000?|
|My understanding is that sp4 will log some errors as I/O that weren't previously logged that way so it might seem as though sp4 has caused this error (see here). Alternatively it may be of course that it is coincidental and you now do have some hardware I/O issues that will need investigating using these guidelines.|
|How can I ensure that triggers fire during initialization for SQL Server 2005?|
|The setting to enable firing of triggers during the initial bulk load is not exposed in SSMS, but it is configurable through the @fire_triggers_on_snapshot parameter in sp_addarticle and the 'fire_triggers_on_snapshot' property through sp_change_article.|
|What are the differences between 32 and 64 bit replication?|
The Replication features of SQL Server 2000 (64-bit) are nearly identical to the 32-bit version, with the following features being supported:
Immediately updating subscribers
Queued updating subscribers
However, there are a few special cases:
(1) as the Microsoft Jet engine is not supported, Microsoft Jet push subscriptions for merge replication are not supported
(2) unless the subscriber provides a 64-bit ODBC or OLE DB driver, transactional or snapshot push subscriptions for ODBC or OLE DB subscribers are not supported
(3) because of the unavailability of 64-bit Data Transformation Services (DTS), transformable push subscriptions are not supported.
|How can I avoid double quotes being added to the stored procedure names when they're replicated?|
|For a long while, the SQL Replication team had a very strong focus on supporting non-SQL Server subscribers, and since using  for quoting identifiers is not something generally understood\accepted by other DBMSs, someone came up with the idea of converting all the  to " on the fly. So, if you want the original syntax to remain (with or without the square brackets), you have to use sp_addscriptexec instead.|
|How can I prevent the snapshot agent failing with 'Server execution failed'?|
The value of:
should be changed to:
C:\Program Files\Microsoft SQL Server\MSSQL\BINN\SQLREPSS.DLL,C:\Program Files\Microsoft SQL Server\80\COM\SNAPSHOT.EXE,ReplStart,ReplEvent,ReplStop,120
|How can I script out the permissions for use in a post-snapshot script?|
|This script works for straightforward permissions on all articles to the subscriber.|
|How can I find triggers defined without the NOT FOR REPLICATION setting?|
|This script can be used to generate the report.|
|Is it possible to have 2 publications with one table in common?|
In merge replication the same table may be added to each publication. However, one of the publications will need to be set up as a no-sync and if you are using automatic range management you'll receive a PK error like this:
"Violation of PRIMARY KEY constraint 'PK__MSrepl_identity___4D5F7D71'. Cannot insert duplicate key in object 'MSrepl_identity_range'.". So you'll need to use manual range management for your table article.
|How can I change an existing column into an identity one?|
For SQL Server 2000 we had the following code:
sp configure 'allow updates', 1
reconfigure with override
update syscolumns set colstat = colstat | 0x0008 where colstat &
0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
sp configure 'allow updates', 0
For SQL Server 2005 there is a new system stored procedure to do this directly:
|How can I move the distribution database to a new server?|
|It's not easy. If the new server has the same name as the old one, you should be able to detach and reattach, being careful to take the other system databases as well. If the NETBIOS name is different, then there's no easy solution. You have to script out the publications then remove the distributor. Recreate the distributor and edit the scripts before recreating the publications and subscriptions. After that you have to reinitialize.|
|Can I tell if a database is involved in replication?|
The sp_dboption procedure will give you this information. Pass the database name as the parameter. The output will look something like this:
"The following options are set:
trunc. log on chkpt.
auto create statistics
auto update statistics"
select name, databasepropertyex (name,'IsMergePublished') from master..sysdatabases
select name, databasepropertyex (name,'IsPublished') from master..sysdatabases
|How can I alter a procedure, create a snapshot and then merge, so the alterations appear in the subscriber databases?|
|You could drop the article then re-add it or use sp_addscriptexec to replicate and execute a script with the alter procedure statements. Run this on your publisher and it will be replicated to all subscribers of the publishing database you run this script in.|
|How can I change a datatype or length of an existing column?|
|Please take a look in the articles section. There is a separate article for SQL Server 2000 and one for SQL Server 2005.|
|Is it possible remove a rogue red X in replication monitor in SQL Server 2000?|
|For SQL Server 2000 if you run profiler and navigate to the replication monitor, you should see why this is happening. The replication monitor gets its info from tempdb.dbo.MSreplication_agent_status and running sp_MSload_replication_status refreshes this table. As restarting the SQL Server Service causes the tempdb to be recreated from the model database, this also removes the problem. For SQL Server 2005 this is handled differently. The data is no longer held in tempdb and the first thing to try is to run the procedure dbo.sp_replmonitorrefreshjob and check that the job "Replication monitoring refresher for distribution" is scheduled and enabled.|
|How can I prevent the snapshot being applied when I have labelled a subscriber for reinitialization by mistake?|
If the snapshot hasn't yet been run, then you could look at resetting the subscriber status flag in
@publication = 'NorthwindRegion',
@article = 'region',
@subscriber = 'pll-lt-16',
@destination_db = 'Pubs',
@status = 'active'.
|Is it possible to subscribe to specific articles in a publication (ie not all the articles of the publication)?|
According to the documentation, you can only subscribe to a publication. However, assuming you have a publication which consists of several articles (tables) it is possible for each subscriber to subscribe to a subset of the publication. You create the publication and then set up the subscribers as per usual. Then you drop the unnecessary articles on a per subscriber basis. This only works if your subscribers exist on separate servers, but if you need to do it, the procedure to run is:
sp_dropsubscription 'publicationname', 'tablename', 'subscribername'
Or more simply just run sp_addsubscription on a per article basis!
|How can I remove rogue subscriptions? (When I look at the Replication, Subscriptions folder I see lots of subscriptions which shouldn't be there, but right-clicking doesn't give the option to delete them).|
|You can have a look at sp_MSenumsubscriptions to see what is happening when you click on the subscriptions folder. The key tables in each subscribing databases are sysmergesubscriptions and MSreplication_subscriptions. If this database is no longer a subscriber, then run sp_removedbreplication, which removes the redundant system tables. If you want these tables to remain, then simply locate the redundant record in the above system table and delete it.|
|Error Message: 'Could not bulk insert. Bulk data stream was incorrectly specified as sorted. (Source: ... (Data source); Error number: 4819'|
|In SQL Server 2000 all the databases involved in replication must have the same collation. You can use sp_helpsort to view the collation of these databases and after that "Alter database" can be used to reset the collation, but this sounds easier than it is in practice, as ideally all the tables should also have their collation changed as well.|
|Error Message: 'The process could not create file '...\Publication_1.sch' or 'The process cannot access the file because it is being used by another process.'|
|Check to see if there is an automatic virus-scanner set up. If so, disable scanning of the distribution working folder (ie the snapshot share).|
|Error Message: 'the process is running and is waiting for a response from one of the backend connections'|
|Don't worry if you get this message and it appears to hang. In my experience it occurs after the data has been transferred and there are no messages being returned because indexes are being applied to the subscriber. You can verify this looking at the current activity on the subscriber using sp_who2 and DBCC INPUTBUFFER. To avoid any potential problems, just make sure that you have a high value for the QUERYTIMEOUT parameter in the distribution/merge agent profile.|
|Error Message: The schema script '....\test.sql' could not be propagated to the subscriber.|
|If you are using sp_addscriptexec, and the script errors, then the distribution/merge agent will fail. You don't need to reinitialize in this case. To get things working, locate the above script and if possible find and fix the error, alternatively just put in some valid TSQL e.g. "Select 'xxx'" which is sure to work. BTW, you can't leave this file blank, so some valid syntax is necessary.|
|Error Message: "The process could not bulk copy out of table '[dbo].[syncobj_xxxxxxxx]'."|
(1) If this is a snapshot error and additional details show "ODBCBCP/Driver version mismatch" then there are some reports that this problem can be solved by upgrading to a consistent MDAC versions on both machines. To determine the version currently installed, there is a free downloadable tool
here. The following files must have matching file versions:
Where does this originate from? Possibly from a failed/partial installation of MDAC.
(2) If the additional details show "I/O error while writing BCP data file (source:ODBC SQL Server Driver ODBC)" then this normally means a disk error or perhaps a disk full problem.
|Error Message: "Schema replication failed because database '%s' on server '%s' is not the original Publisher of table '%s'"|
|You can get this when you try to add a column to a merge article. In sysmergearticles there is a publisherid column. This ID needs to have a corresponding record in sysmergepublications. However, in sysmergepublications, the 'publisher' column needs to match the servername, which it doesn't in this case as you have restored to another servername. It might be possible to update the 'publisher' column, although I'd advise against it and suggest recreating the publication.|
|Error Message: "Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'"|
|This is usually due to a failed service pack installation. Reapplying the service pack fixes it and to get a bit more info, have a look at sqlsp.log file from the "c:\windows" directory as this might shed some light on any failed actions during the service pack installation.|
|Error Message: "Could not obtain information about Windows NT group/user 'domain\username'."|
There is a good
Microsoft article about troubleshooting these errors and the relevant section explains several potential causes:
(1)The SQL Server service uses an account that does not have sufficient administrative credentials on the Windows domain. In this situation, the xp_logininfo system stored procedure is run by using the security context of the SQL Server service. Because the account does not have sufficient administrative credentials to enumerate the properties of the user in the domain, the xp_logininfo system stored procedure fails, and you receive the 8198 error. To resolve this problem, change the startup account of the SQL Server service to a Windows domain account.
(2) The SQL Server services and the SQL Server Agent services on the Publisher and on the Subscriber use security contexts of different Windows domains. To resolve this problem, use either of the following two methods: use the same Windows domain user account to run the SQL Server services and the SQL Server Agent services or create a Windows domain user account on the Publisher and on the Subscriber that have identical user names and passwords.
(3) If you try the recommended solutions and have no success there is a known issue and hotfix available for SQL Server 2000.
|Error Message: "SQL Server Enterprise Manager could not generate the script. Error 14262: The specified @job_id ('....') does not exist"|
|This message is returned when generating scripts from the replication folder and selecting the option to script the replication jobs. Sometimes the subscription details can be left behind after removing a publication and also the list of jobs is not updated - either issue can cause the error above. To remove this error, delete any orphaned records in the table MSreplication_subscriptions on the subscriber database, refresh the jobs folder and then recreate the script.|
|Error Message: "The process could not read file '...\Orders_1.sch' due to OS error 5" or "the schema script '...\Employees_1.sch' could not be propagated to the subscriber. The step failed."|
|In a pull subscription if you use a default administration share e.g. "c$...", then the subscriber's SQL Server Agent account must be a local administrator on the distribution box. Typically this is not the case, so changing to an explicit share is the best solution. The location is changed by entering distributor properties, publishers tab, publication ellipsis.|
|Error Message: "The process could not connect to Distributor 'PLL-LT-16'. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The step failed"|
|The SQL Server Agent service (SQLServerAgent) at the client should not use the LocalSystem account - it needs to use a standard domain account.|
Error Message: I'm replicating a view or a stored procedure and I get one of these errors:
(a) "Invalid column name 'Column Name'"
(b) "Cannot use empty object or column names. Use a single space if necessary"
(c) "The name 'Object Name' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted"
(d) "The schema script '...proc1' could not be propagated to the subscriber"
|This problem may occur because the Snapshot Agent always sets the QUOTED_IDENTIFIER option to ON, regardless of the actual connection setting. Therefore, if the stored procedures or views use double quotation marks, the Distribution Agent or the Merge Agent assumes the default behaviour of using double quotation marks for identifiers only. To get round this, you can change the object script to refer to literals using single quotes, or use DTS to transfer the objects.|
|Error Message: "The process could not connect to Subscriber 'SQLReporting'"|
|The distribution agent needs to connect to the subscriber to apply the snapshot initially and then possibly for other duties depending on the type of replication. It does this either as a Windows Login (trusted) or as a SQL Login. If the login details are incorrect on the subscriber, the misleading message above is received. If the login is correct but the permissions are not, then you receive the error: "Only members of the sysadmin or db_owner roles can perform this operation". To remove the problem, enter the distributor properties, subscribers tab and click on the ellipsis by the side of the relevant subscriber. Choose to "impersonate the sql server agent account on 'servername' (trusted connection)". Provided the distribution agent account exists as a login on the subscriber and has sysadmin or db_owner rights on the subscription database, the error is removed.|
|Error Message: "The process could not connect to Distributor 'SERVER1'"|
This may be the case if the server has been renamed. You may alternatively see these cryptic errors :
"18483 - could not connect to server 'Newname' because distributor_admin is not defined as a remote login at the server"
"18482 - could not connect to server 'Newname' because 'Oldname' is not defined as a remote server"
To remove for this error, try:
This should return your current server name but if it returns NULL then try:
exec Sp_DropServer 'OldName'
exec Sp_Addserver 'NewName', 'local'
Then stop and Start SQL Services
NB: You can use either the @@SERVERNAME global variable or the SERVERPROPERTY('ServerName') function in SQL Server to find the network name of the computer running SQL Server. The ServerName property of the SERVERPROPERTY function automatically reports the change in the network name of the computer when you restart the computer and the SQL Server service. The @@SERVERNAME global variable retains the original SQL Server computer name until the SQL Server name is manually reset.
|Error Message: On initialization I receive the error number 208 on a specific view. Any ideas how to fix this?|
When the snapshot schema files are applied at the subscriber, the order of articles is carefully chosen. Some objects depend on other objects existing, e.g. a view could depend on the existence of another view or table. Run
sp_depends on the view that errors to check that the dependency is acknowledged by SQL Server. If this shows no dependencies then refresh the view and run it again to check the dependency is recognised, and once this is the case, the snapshot article order should be correct. Alternatively you can use
sp_addscriptexec and add the views in any order, or in
the case of merge you could use @processing_order in
You might find these articles helpful:
BUG: Recreating a Table Causes sysdepends to Become Invalid
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in Sp_depends
Technet article on Displaying Dependencies
|Error Message: "Can not update column as it's used in replication"|
|If this table is no longer involved in replication, there is a stored procedure to update the object's metadata and it is called sp_MSunmarkreplinfo (it takes a tablename as a parameter). Running sp_removedbreplication can be used to remove all traces of replication in the subscriber database, but obviously must only be done if this database is no longer needed as a publisher or subscriber.|
|My database is marked as Suspect, how can I fix it and continue replication?|
|Please take a look at Tibor's link.|
|During the execution of the snapshot agent I receive the following error "The process could not bulk copy out of table '[dbo].[table_name]'".|
|Basically this means the snapshot agent has timed out due to blocking. You should stop and restart the snapshot agent.|
|What is the distributor_admin account used for?|
|The distributor_admin RPC link is needed by replication because most replication administrative functions require processing at both the publisher database as well as the distribution database. For example, when a new publication is added through sp_addpublication, it is necessary to add a row in the syspublications table at the publisher database and the MSpublications table at the distribution database and this process uses the distributor_admin link under the cover.|
Paul Ibison, Copyright © 2013