Patrick Purviance – SQL Server DBA Blog
Blogging impactful solutions for SQL Server DBAs worldwide

Archive for the ‘Replication’ Category

The distribution agent failed to create temporary files in ‘C:\Program Files\Microsoft SQL Server\110\COM’ directory.

Wednesday, August 7th, 2013

Shortly after configuring a new SQL Server 2012 server as a pull subscriber to a publication on a SQL Server 2008 Publisher/Distributor, we began receiving the following error message from the replication agents/jobs:

SQL Server Replication Error 14151

Replication-Replication Distribution Subsystem: agent PublisherNameDBNamePublicationName-62 failed. The distribution agent failed to create temporary files in ‘C:\Program Files\Microsoft SQL Server\110\COM’ directory. System returned errorcode 5.

The root cause, after much searching and testing theories, was permissions on the subscriber COM directory. 

The fix was to simply grant the account that the distribution agent was running on appropriate write/modify permissions on the COM folder in question ON THE SUBSCRIBER, NOT THE PUBLISHER/DISTRIBUTOR

Why this was not something done automatically by the replication setup process is beyond me, but once we granted the write permission to the COM folder on the subscriber (likely not necessary, but we also restarted the SQL Agent Job responsible for the distrubution), it all worked.

Hope this helps someone!

         -Patrick

Procedure to Validate SQL Server Replication Convergence

Friday, July 22nd, 2011

Today I’m posting a turn-key solution for those of you out there supporting SQL Server replication. The purpose of the stored procedure created by the script provided below is simply to leverage a little-known, but very useful, tool provided by Microsoft for the sake of convergence validation (validate that all of your table based articles are identical between the publisher and subscriber(s)).

The execution of the SP (once compiled) simply generates syntax in text format that can be saved as a batch file or run as-is (carefully) from a command prompt to check for convergence issues between a published DB and its subscriber’s DB tables.

The underlying tool, called TableDiff.exe, again, is provided by Microsoft and can be found with the SQL Server binaries. Because of this, the SP should be executed on the server upon which you will be executing the resulting script so that the correct binary location will be provided.

I typically compile the SP in a DBA “tools” database, execute it providing all of the required parameters, save the resulting text as .bat file on the desktop of the publisher’s host server, and then execute the .bat. The batch file will create a log report and, optionally, a fix-it T-SQL script for each article to bring the table(s) back into convergence if differences are found.

Note: There currently is NO error checking done for the the location of TableDiff.exe. It can be added if needed.


Example Usage: EXEC DBA_TOOLS.dbo.Repl_Gen_TableDiff_Code_usp @Pub_SvrName = @@servername , @Sub_SvrName = 'SERVER_A' , @Pub_DBName = 'CUSTOMER' , @Sub_DBName = 'CUSTOMER' , @Provide_Fix = 1 , @Outputdir = 'C:\' Where: @Pub_SvrName is the name of the publisher server @Sub_SvrName is the name of the subscriber server @Pub_DBName is the name of the published database @Sub_DBName is the name of the subscriber database

This has been tested on SQL Server 2005/2008/2008R2, but only with Transactional & Snapshot Replication. Merge Replication uses some alternative tables for configuration and lookups and often includes parameterized filters for subscribers, so I won’t claim this solution is appropriate for Merge or Peer-to-Peer replication, but am open to comments on the topic.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
USE [DBA_Tools]
GO
 
IF OBJECT_ID('Repl_Gen_TableDiff_Code_usp') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.Repl_Gen_TableDiff_Code_usp
END
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[Repl_Gen_TableDiff_Code_usp] 
	  @Pub_SvrName	SYSNAME
	, @Sub_SvrName	SYSNAME
	, @Pub_DBName	SYSNAME
	, @Sub_DBName	SYSNAME
	, @Provide_Fix  BIT = 0
	, @OutputDir	VARCHAR(4000)
	, @DebugFlag	BIT = 0
AS
--================================================================================
--==  Procedure:		dbo.Repl_Gen_TableDiff_Code_usp  
--==  Author:			Patrick Purviance
--==
--==  Description:		Simple SP to generate syntax that can be saved as a batch
--==					file or run as-is (carefully) from a command prompt to 
--==					check for convergence issues between a published DB and
--==					its subscriber's DB tables.  TableDiff.exe is provided
--==					with the SQL Server binaries and therefore this SP should
--==					from the server upon which you will be executing the 
--==					resulting script so that the correct binary location will
--==					be selected.  
--==
--==
--==					Note:  There currently is NO error checking done for the 
--==					the location of TableDiff.exe.  It can be added if needed.
--==
--==
--==  Usage:			EXEC DBA_TOOLS.dbo.Repl_Gen_TableDiff_Code_usp 
--==  								@Pub_SvrName	= @@servername
--==  								, @Sub_SvrName	= 'SERVER_A'
--==  								, @Pub_DBName	= 'CUSTOMER'
--==  								, @Sub_DBName	= 'CUSTOMER'
--==								, @Provide_Fix = 1
--==								, @Outputdir = 'C:\'
--==
--==					Where @Pub_SvrName is the name of the publisher server,
--==							@Sub_SvrName is the name of the subscriber server,
--==							@Pub_DBName is the name of the published database
--==							@Sub_DBName is the name of the subscriber database
--==
--==  Version history:
--==					v1.0 Created, PPurviance, 11/29/2010
--==					v1.1 Modified to use replication database syspublications to be sure 
--==						 to pick up all articles. I found a bad publication_id in 
--==						 distribution..mspublications causing some articles not to 
--==						 get picked up for convergence checking.
--================================================================================
SET NOCOUNT ON 
 
DECLARE @MSG VARCHAR(2000)
		, @SQL VARCHAR(4000)
		, @LogDate VARCHAR(13)
		, @Version VARCHAR(4)
		, @TablediffLoc VARCHAR(256)
		, @FixFile VARCHAR(256)
 
DECLARE @SysArticles TABLE (pubid int, artid int, artname SYSNAME)
DECLARE @Publications TABLE (pubname sysname, pubid int)
 
 
--==========================================
--== Determine the current SQL version:
--==========================================
 
Select @version = left(convert(varchar, SERVERPROPERTY('ProductVersion')), 2)
 
Select @version = 
	Case @version 
		When '7.' then '7'
		When '8.' then '2000'
		When '9.' then '2005'
		When '10' then '2008'
	End
 
 
--==========================================
--== Check all supplied values:
--==========================================
 
--== First check the publisher server name. This must be run on the publisher.
IF rtrim(@Pub_SvrName) <> @@servername
BEGIN
	SET @MSG = 'This procedure must be run on the publisher to work correctly.  The servername provided does not match the local server name.'
	RAISERROR(@MSG,0,1)	WITH NOWAIT;
 
	RETURN
END
 
IF NOT EXISTS(SELECT 1 FROM master.sys.databases where name = rtrim(@Pub_DBName))
BEGIN
	SET @MSG = 'Supplied Publisher Database cannot be found.'
	RAISERROR(@MSG,0,1)	WITH NOWAIT;
 
	RETURN
END
 
--==========================================
--==  End check all supplied values
--==========================================
 
SET @SQL = 	'SELECT pubid, artid, name FROM [' + rtrim(@Pub_DBName) + '].[dbo].[sysarticles]'
 
INSERT @SysArticles	
EXEC(@SQL)
 
IF @DebugFlag = 1
BEGIN
	PRINT @SQL
	RETURN
END
 
SET @SQL = 	'SELECT name, pubid FROM [' + rtrim(@Pub_DBName) + '].[dbo].[syspublications]'
 
INSERT @Publications
EXEC(@SQL)
 
IF @DebugFlag = 1
BEGIN
	PRINT @SQL
	RETURN
END
 
--==Build the date and time and tablediff location portion for the log name
SET @LogDate = CONVERT(VARCHAR, (DATEPART(YYYY, GETDATE()))) + CONVERT(VARCHAR, (DATEPART(MM, GETDATE()))) + CONVERT(VARCHAR, (DATEPART(DD, GETDATE()))) + '_' + CONVERT(VARCHAR, (DATEPART(HH, GETDATE()))) + CONVERT(VARCHAR, (DATEPART(MI, GETDATE())))
 
SELECT @TablediffLoc = CASE @version 
	WHEN '2000' THEN '"C:\Program Files\Microsoft SQL Server\80\COM\tablediff" '
	WHEN '2005' THEN '"C:\Program Files\Microsoft SQL Server\90\COM\tablediff" '
	WHEN '2008' THEN '"C:\Program Files\Microsoft SQL Server\100\COM\tablediff" '
END
 
SET @FixFile = ''
 
--==Now build the statement
		SELECT @TablediffLoc 
			+ '-sourceserver "' + rtrim(@Pub_SvrName)
			+ '" -sourcedatabase "' + rtrim(@Pub_DBName)
			+ '" -sourcetable "' +  sa.artname
			+ '" -destinationserver "' + rtrim(@Sub_SvrName)
			+ '" -destinationdatabase "' + rtrim(@Sub_DBName)
			+ '" -destinationtable "' + sa.artname 
			+ CASE WHEN @Provide_Fix <> 0
				THEN '" -f "' + RTRIM(@OutputDir) + '\Make_' + RTRIM(@Sub_SvrName) + '_' + RTRIM(@Sub_DBName) + '_LookLike_' + RTRIM(@Pub_SvrName) + '_' + RTRIM(@Pub_DBName) + '_For_' + RTRIM(sa.artname) + '.sql"'
			  ELSE '"'
			  END   
			+ ' >> "' + RTRIM(@OutputDir) + '\Tablediff_Log_' + RTRIM(@Sub_SvrName) + '_' + RTRIM(@Sub_DBName) + '_vs_' + RTRIM(@Pub_SvrName) + '_' + RTRIM(@Pub_DBName) +  '_' + rtrim(@LogDate) +  '.txt"'
		FROM @SysArticles sa inner join
			@Publications pub on pub.pubid = sa.pubid
		ORDER BY sa.artname 
RETURN

-Patrick

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0×800

Friday, January 28th, 2011

If you’re experiencing this issue on a Windows 2003 Server, make sure you read UPDATE 3!

So today I’m posting about an issue with SQL Server replication.   This particular posting is with regards to a SQL Server 2008 Enterprise 64-bit platform and transactional replication.

The story goes like this:

I was asked to add some new tables in a published DB to the transactional publication on the publisher to get replicated to the subscriber servers.  Simple request, right?  Right.  Wait.  Wrong.  Yes, the request should have been simple to action.  But upon attempting to generate the publication snapshot for the subscribers to consume, the snapshot fails with the following error:

“Failed to initialize the Common Language Runtime (CLR) v2.0.50727  with HRESULT 0x80070005. You need to restart SQL server to use CLR integration features.”

Upon checking the status of the CLR (mind you we don’t have/use any CLR assemblies here and CLR is disabled on all of the servers here), it states “CLR initialization permanently failed“.  I’m checking that status with:

SELECT * FROM sys.dm_clr_properties

Ok, but who cares…we don’t need CLR, right?  Right.  Wait.  Wrong again.  Microsoft is using internal CLR assemblies for replication.
How’d I fix it?  Like the original error stated, restarted SQL Server.  I hate that that’s the solution.  Especially in a production environment.  But that’s the solution.  As soon as SQL starts up, and before any CLR integration is necessary, querying the state of CLR again, it’s now “loaded CLR version with mscoree“. 

Ok, it’s loaded again and not “permanently failed”.  So we try the snapshot again, monitoring the CLR state through the snapshot generation.   Guess what…..near the end of the snapshot generation the CLR state changed from “loaded CLR version with mscoree” to “CLR is initialized” and listed the directory (C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\) and version (v2.0.50727).  So it’s now allowing the internal CLR for replication to run again.

Could it be that our .NET 2.0 installation is corrupt?  Possibly.  The other rationale behind this behaviour, at least in many similar posts (not exactly like our situation) is that memory pressure causes the problem. 

Will post back with updates as they become available. 

                  -Patrick

Quick script to find Published Articles in Transactional Replication

Wednesday, September 22nd, 2010

Quick script to list out all of your published articles for Transaction and Snapshot Replication in SQL Server 2005+. This is based on the dbo.sysextendedarticlesview view which unions together the dbo.sysarticles (articles) and dbo.sysschemaarticles (schema only based articles…no data is replicated for these objects) tables.

Primarily posting here because it took me a while to track this information down and maybe it’ll get picked up by search engines and save you some time someday.

1
2
3
4
5
6
SELECT seav.artid as [Article ID], 
           seav.name as [Article Name], 
           sp.name as [Publication Name]
FROM dbo.sysextendedarticlesview seav inner join
        dbo.syspublications sp on sp.pubid = seav.pubid
ORDER BY 2