My technical skills
My favorites
My picture album
Shortcut keys
My code library
VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources
Search my site
Sign my guestbook
Contact information
SQL Server Articles New
Receive & Send money over the net easily!
| Replication
agents are failing with the following error.
What's wrong? Too many arguments were supplied for procedure <A_System_Stored_Procedure_Name> (for example: sp_MSenumschemachange) The most probable reason for this error could be, mismatched service pack versions between the SQL Servers participating in replication. Make sure all the participating SQL Servers are running with the same service pack installed. Click here to learn more about SQL Server service packs |
| Replication
monitor is showing errors (White X mark on a Red
circle), though there are no errors on any
agents. How to get rid of these X marks? In most of the cases, just restarting your SQL Server service should solve this problem. If restarting the server is not an option for you, go ahead and delete the table MSreplication_agent_status in the tempdb database. |
| I have
setup Transactional replication with
"Immediate Updating Subscribers".
Changes on publisher get replicated to the
subscriber, without any errors. But when I try to
UPDATE/INSERT data at the subscriber I am getting
the following error and not able to modify data
at the subscriber: Server: Msg 18456, Level 14, State 1, Line 3 Login failed for user 'sa'. How to correct this problem? Immediate Updating subscribers connect the publisher using dynamic RPC, and this is the default. Dynamic RPC defaults to using the sa login with blank password. This behavior can be changed at the Subscriber using sp_link_publication. See "Security for Immediate-updating Subscribers" and "sp_link_publication (T-SQL)" in Books Online for more information To get rid of this error, execute sp_link_publication on the subscribing database and specify the sa password for the publisher. E.g: sp_link_publication @publisher = 'Your_Publishing_Server_Name', @publisher_db = 'Your_Publishing_Database_Name', @publication = 'Your_Publication_Name', @security_mode = 0, @login = 'sa', @password = 'Your_Publisher_SA_Password' |
| My
Snapshot and Logreader agents are failing with
the following error: The process could not connect to Publisher 'Publishing_Server_Name'. Category: Data source Source: Publishing_Server_Name Number: 18456 Full error message: Login failed for user 'sa' The Snapshot and Logreader agents are running on the same publishing/distributing server, but still they are not able to connect. How to overcome this problem? You may get into this situation, when you configure your replication agents to log into the publisher and/or subscriber using "SQL Server authentication" (where you specify a specific SQL Server login, to connect to the publisher and/or subscriber). It will work fine initially, but when you change that particular login's password, you get the above mentioned errors. To solve this problem, whenever you change that particular login's password, make sure you change the password in the enabled publisher's and/or enabled subscriber's properties on that distribution server. To change the publisher's properties: - Open Enterprise Manager - Connect to the Distribution server - Go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution... - Go to Publishers tab - Click on the properties button (...) against the publisher's name - Enter the new password for the SQL Server login. To change the subscriber's properties, repeat the same steps as above, but in step 3, go to Subscribers tab. Restart your agents, and they should be able to connect to the publisher/subscriber without any errors. |
| How to
find out whether a change to the data is made by
replication or by other users/applications? I
need to do some custom processing, whenever
replication is making changes to my data. I would
prefer to do this from a trigger. Use the APP_NAME function. This function returns the name of the application that is modifying the data. First, find out the output of APP_NAME function when replication is modifying the data. The output of this function will be the distribution agent's name, when replication is modifying the data. Once you know this name, you can write a trigger which uses APP_NAME function to detrmine if replication is making any changes, and proceed with your custom processing. See Books Online for more information about APP_NAME. |
| How to
change the replication working directory on the
distributor? By default every publisher stores it snapshot data in the MSSQL7\Repldate folder on the distributor. If you are running out of disk space on that particular you need to change the working directory to another drive. You need to change this path for each publisher, as it is not a global setting on the distributor. There are two ways to do this: Method #1: - Open Enterprise Manager - Connect to the Distribution server - Go to Tools -> Replication -> Configure Publishing, Subscribers, and Distribution... - Go to Publishers tab - Click on the properties button (...) against the publisher's name - Enter the new path for the Snapshot folder Method #2: Run sp_changedistpublisher from query analyzer. For more information on sp_changedistpublisher see Books Online |
| How to maintain IDENTITY property
of a column on both publishing and subscribing databases in transactional
replication? If you maintain IDENTITY property on the subscribing table, distribution agent fails with the following error even after creating the publisher's IDENTITY property with NOT FOR REPLICATION option: An explicit value for the identity column in table 't1' can only be specified when a column list is used and IDENTITY_INSERT is ON. Category: Data source Source: <Server Name> Number: 8101 Follow these steps to maintain IDENTITY property of a column on both publishing and subscribing databases: 1) Before subscribing, you must create the table at the subscriber (by default, replication will not transfer the IDENTITY property to the subscribers) and don't forget to create the IDENTITY property with NOT FOR REPLICATION option. 2) After creating the publication, go to publication properties window and choose the Articles tab. Click on the properties button (...) against the table that has IDENTITY property. Go to 'Snapshot' tab. Choose 'Keep the existing table unchanged'. 3) By default SQL Server 7.0 applies the transactions to the subscriber using stored procedures (these procedures will get created during the application of initial snapshot). Before you subscribe, you can choose to continue using stored procedures to apply the transactions on the subscriber OR you can choose to apply the transactions to the subscriber using SQL statements. If you choose to use SQL statements, jump to step 6. If you choose to go by the default behavior (stored procedures) follow steps 4 and 5. 4) Go to the publication properties, choose 'Articles' tab, click on the properties button (...) against the table that has IDENTITY property, click on the 'Commands' tab and uncheck 'Create the stored procedures specified above during the initial synchronization of the subscribers'. Using sp_scriptinsproc, sp_scriptmappedupdproc, sp_scriptdelproc (See SQL Server 7.0 Books Online for more information) generate the scripts for these procedures. Add a column list to the INSERT statement in the INSERT stored procedure script. In the UPDATE stored procedure script, remove the line in the UPDATE statement which tries to update the IDENTITY column. Apply these scripts on the subscriber. 5) Push or pull the subscription. 6) If you choose to use SQL statements to apply transactions to the subscriber, go to the publication properties window, choose articles tab, click on the properties button (...) against the table that has IDENTITY property, choose 'Commands' tab. Uncheck the check marks against the procedure calls and check the box against 'Use column names in the SQL statements'. 7) Push or pull the subscription. |
| None of my replication wizards are
coming up. Enterprise Manager lets me click on the replicaiton options,
but nothing happens. What's wrong? Most probable reason for this behavior could be that you have de-selected the 'Replication Support' option while installing SQL Server. Try the following steps to fix this problem - Start Setup - Choose custom install - Check 'Replication Support' under 'Server Components' - Complete the setup |