Average 28000 requests/month











  How to.......
How can I get the text for 'sys.sp_MSrepl_helparticlecolumns' or any other such hidden procedures?
  Here's a nice trick for you! Some of these procedures aren't accessible using sp_helptext. They also aren't accessible using OBJECT_DEFINITION. However if you use the Dedicated Admin Connection (DAC), you'll be able to access the real text of the procedure:

select object_definition(object_id('sys.sp_MSrepl_helparticlecolumns'))

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 Replication Publication such that only table schema is published and no data ?
  If you are using SQL2005 (distributor), there is an unofficial /NoBcpData switch supported by the snapshot agent.
But then again, that 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. (Hilary Cotter)
How can I script the Oracle Publisher using Standard Edition of SQL Server?
  The Oracle Publisher feature is only available in SQL 2005 Enterprise Edition and you will hit errors if you try to setup Oracle Publisher using scripts - Jing Sun, Microsoft Replication Team
(SQL 2005) 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 to see what the issues are.
How to avoid I/O errors during synchronization after installing sp4?
  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 http://groups.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en). 
Alternatively it may be of course that it is coincidental and you now do have some hardware I/O issues that will need investigating.http://support.microsoft.com/?kbid=897284
(SQL 2005) How to.......ensure that triggers fire during initialization?
  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.
How to.......know 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:

Snapshot Replication
Transactional Replication
Merge Replication
Immediately updating subscribers
Queued updating subscribers

However, there are a few special cases:

As the Microsoft Jet engine is not supported, Microsoft Jet push subscriptions for merge replication are not supported.
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. 
Because of the unavailability of 64-bit Data Transformation Services (DTS), transformable push subscriptions are not supported.

 

How to.......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 dbms, 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 to....... prevent the snapshot agent failing with 'Server execution failed'?

Set the value of
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent\Subsystems\
Snapshot

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

from

C:\Program Files\Microsoft SQL Server\MSSQL\BINN\SQLREPSS.DLL,C:\Program
Files\Microsoft SQL
Server\80\COM\SNAPSHOT.EXE,ReplStart,ReplEvent,ReplStop,100

(Hilary)
How to....... script out the permissions for use in a post-snapshot script?
This script works for straightforward permissions on all articles to the subscriber 'yyy'

 
How to....... find triggers defined without the NOT FOR REPLICATION setting?
 

Have a look here.

 
How to....... have 2 publications with one table in common?
 

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, on the upload-only publication you'll just need to disable automatic range management for your table article

 
How to....... increase the snapshot performance for a large snapshot?
 

(a) BCP the tables to textfiles. Compress the files then transfer them. BCP in the files on the subscriber then set up the subscription with no initialization.
(b) alternatively compressing snapshot files is achieved using the alternate snapshot folder.
(c) you could use removable disks if the subscriber isn't too geographically remote.
(d) you could increase the -MaxBCPThreads parameter
(e) and use -UseInProcLoader if you don't manually apply the bcp files or removable disks.

 
How to....... change an existing column into an identity one?
  Here I am setting the identity column for the jobs table to NFR

sp configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat &
0x0001 <> 0 and colstat & 0x0008 = 0 and id=object id('jobs')
GO
sp configure 'allow updates', 0
(Hilary Cotter)

 
How to....... move the distribution database?
  You can't do this. You must disable replication on your publisher, enable it on your distributor, and allow this publisher to publish to this distributor. Then reinstall replication on your publisher and have it use the distributors database. Then rebuild your publications and susbcriptions.

 
How to....... 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:     
-----------------------------------
published
select into/bulkcopy
merge publish
trunc. log on chkpt.
auto create statistics
auto update statistics

Alternatively:

select name, databasepropertyex (name,'IsMergePublished') from master..sysdatabases
select name, databasepropertyex (name,'IsPublished') from master..sysdatabases
How to....... ALTER a procedure, create a snapshot and then merge, so the alterations appear in the subscriber databases ?
  Although sp_refreshsubscriptions 'publication' will add any new articles to the publication which can then be snapshotted, it will not work in this case. Altering an existing stored procedure and running this command will result in the message 'A snapshot was not generated because no subscriber needed initialization'. To force a new snapshot of eg myproc if it is an existing but changed use sp_reinitsubscription - eg

sp_reinitsubscription @publication = 'publication'
, @article = 'myproc'
, @subscriber = 'london'
, @destination_db = 'testrep'
, @for_schema_change = '1'

After that, create a snapshot (it'll just be the new procedure) and then synchronize.

Alternatively, 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 to....... change a datatype or length of an existing column.
  Please see this article for SQL Server 2000 and this article for SQL Server 2005..

 
How to....... remove a rogue red X in replication monitor?
 

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.

 
How to....... 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 syssubscriptions:

exec dbo.sp_changesubstatus
@publication = 'NorthwindRegion',
@article = 'region',
@subscriber = 'pll-lt-16',
@destination_db = 'Pubs',
@status = 'active'.

NB surprisingly I haven't yet been able to have this work once the snapshot has already been created. I'll post more details on this soon.

 
How to....... subscribe to just an article?
  According to the documentation, you can only subscribe to a publication. However, assuming you have a publication which consists of serveral 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 to....... 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 Messages.......
new! Error Messages.......'Could not bulk insert. Bulk data stream was incorrectly specified as sorted. (Source: ... (Data source); Error number: 4819'
All the databases involved in replication must have the same collation. You can use sp_helpsort to view the collation of these databases. Alter database can be used to reset.

 

new! Error Messages.......' The process could not create file
'd:\SQLData\MSSQL\ReplData\unc\SERVER_DataBase_Publication\20041031000029\Publication_1.sch'.
Full Message: 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 repldata folder

 
Error Messages.......'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. Just make sue that you have a high value for the QUERYTIMEOUT parameter in the distribution/merge agent profile.

 
Error Messages.......The schema script '0\\DH1791628\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\20040915121438863\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 eg DBCC INDEXDEFRAG which is sure to work. BTW, you can't leave this file blank, so some valid syntax is necessary.

 
Error Messages.......Snapshot error: The process could not bulk copy out of table '[dbo].[syncobj_xxxxxxxx]'. Error Details: ODBCBCP/Driver version mismatch
 

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:

%SystemRoot%\system32\sqlsrv32.dll
%SystemRoot%\system32\sqlsrv32.rll
%SystemRoot%\system32\odbcbcp.dll

Where does this originate from? Possibly from a failed/partial installation of MDAC qfe(s).

Error Messages.......Snapshot error: The process could not bulk copy out of table '[dbo].[syncobj_xxxxxxxx]'. Error Details: I/O error while writing BCP data file (source:ODBC SQL Server Driver ODBC); Error number:0)
 

This normally means a disk error or perhaps a disk full problem. 

 
Error Messages.......When I try to add a column I have the error: 21260 16 Schema replication failed because database '%s' on server '%s' is not the original Publisher of table '%s'.
  In sysmerge articles there is a publisherid. 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 your 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 Messages.......Error message after applying sp3a and restoring a database: Could not find stored procedure 'dbo_ss.dbo.sp_MSremovedbreplication'.
  This is usually due to a failed sp installation. Reapplying sp3a fixes it and to get a bit more info, have a look at sqlsp.log file from the c:\windows directory this might shed some light on any failed actions during the sp installation.

 
Error Messages.......Why do I get this error: "[298] SQLServer Error: 8198, Could not obtain information about Windows NT group/user 'domain\username'."
  There is a good article about troubleshooting these errors:
http://support.microsoft.com/?kbid=834124 . The relevant section explains:

The following list describes two possible causes for this problem.

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.

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.
Create a Windows domain user account on the Publisher and on the Subscriber that have identical user names and passwords.

If you try the recommended solutions and have no success there is a known issue and hotfix available:
http://support.microsoft.com/default.aspx?kbid=830596.

 
Error Messages.......How do I avoid this error when I try to script replication?:
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 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 create the script.

 
Error Messages ....... I'm initializing replication and I get one of these errors:
(a) The process could not read file '\\PLL-LT-16\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData\unc\PLL-LT-16_Northwind_NorthwindOrders\20040226171940\Orders_1.sch' due to OS error 5.
(b) The schema script '\\PLL-LT-16\ReplData\unc\PLL-LT-16_Northwind_NorthwindEmployees\20040227085540\Employees_1.sch' could not be propagated to the subscriber. The step failed.
  In a pull subscription if you use a default share eg c$, then the agent account must be a local administrator. Typically this is not the case, so changing to a declared share is the best solution. For the error above, the share becomes \\PLL-LT-16\Repldata and is changed by entering distributor properties, publishers tab, publication ellipsis.

 
 Error Messages.......>Initialization error: 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 Messages....... 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 'Path of the .sch file for the stored procedure proc1' could not be propagated to the subscriber.
  This problem occurs because the Snapshot Agent always sets the QUOTED_IDENTIFIER option to ON, regardless of the actual setting. Therefore, if the stored procedures or views use double quotation marks, the Distribution Agent or the Merge Agent assumes the default behavior 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 Messages.......Initialization error: 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 user (trusted) or as a SQL user. If the login is 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 elipsis 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 Messages.......Initialization error: 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.
Try:
Use Master
go
Select @@Servername

This should return your current server name but if it
returns NULL then try:
Use Master 
go 
Sp_DropServer 'OldName' 
GO 
Use Master 
go 
Sp_Addserver 'NewName', 'local' 
GO 
Stop and Start SQL Services

NB: You can use 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 Messages.......Initialization 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, eg a view could depend on the existence of another view or table. It may be the case that the view gets created before the 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.

You might find these articles helpful:

BUG: Recreating a Table Causes sysdepends to Become Invalid http://support.microsoft.com/?id=115333

BUG: Reference to Deferred Object in Stored Procedure Will Not Show in Sp_depends http://support.microsoft.com/?id=201846

Displaying Dependencies http://www.microsoft.com/sql/techinfo/tips/development/displayingdependencies.asp

 
Error Messages.......I want to remove a column but I can't run sp_repldropcolumn
  This is usually because there are dependant objects - constraints, defaults, indexes - which need to be removed first.

 
Error Messages.......I'm trying to change a column but get the message: "Can't update column as it's used in replication". However, this column is NOT used in replication.
  There is a stored procedure to do this called sp_MSunmarkreplinfo which takes a tablename as a parameter. Alternatively, setting replinfo to 0 in sysobjects for the particular table should do it. Finally, 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 not also configured as a publisher.

 
   
  General......
General.......My database is marked as Suspect, how can I fix it and continue replication?
 
General....... 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. As you can probably imagine, sp_addpublication, executed in the context of the publisher database,  uses the distributor_admin link under the cover to perform the insert into the MSpublications table at the distribution database.
 

April 2006