July 23, 2013 at 9:52 am
Hey guys,
So I have setup replication between our source production database to replicate to our reporting database so we are not hitting our production database for reporting...
I set this up 2 months ago and everything was running fine...Until about 5 days ago, it started failing every night, when I go to look at the job history and see the big red X and look at the error, it gives me this:
Message
Executed as user: TRH\mssql. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152). The step failed.
For some reason I was under the impression it was failing during the distribution step of replication...I know what that error means but I started digging...This was yesterday - I created a backup of the source, and restored it onto reporting...Was going to see if that helped the issue...I check this morning and nope same failure, same message...So I have this redgate tool SQL Compare...It compares the schema's of the two databases! Once it finished, it showed no differences and 22723 identical objects!
When looking back at the error, the light bulb came on and was like this is failing on the snapshot portion of replication! Now it makes no sense to me why I would get that error when trying to create a snapshot??? Its not pushing data anywhere so I dont know why this error would be thrown...
Looking at the job history and expanding down on the today's date with big red X, I noticed some of the lines had a blue curved arrow and some of the lines have a the successful green play button, but looking at the blue curved arrow line, I noticed this:
2013-07-23 07:17:43.55 [8%] The replication agent had encountered an exception.
2013-07-23 07:17:43.55 Source: Replication
2013-07-23 07:17:43.55 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException
2013-07-23 07:17:43.55 Exception Message: Failed to read column data
2013-07-23 07:17:43.55 Message Code: 0
2013-07-23 07:17:43.55
2013-07-23 07:17:43.55 [8%] The replication agent had encountered an exception.
2013-07-23 07:17:43.55 Source: Replication
2013-07-23 07:17:43.55 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException
2013-07-23 07:17:43.55 Exception Message: Failed to read column data
2013-07-23 07:17:43.55 Message Code: 0
2013-07-23 07:17:43.55
Now let me say this again, there was not a red X on this line, just the blue curved arrow! and the line above it is a successful green one...Then it 2 lines above it is when I see the red X errors with the String or Binary data would be truncated...
Details of the Snapshot Replication:
Source to Reporting
A bunch of articles - like at least 10,000
Security Settings - Run under the SQL Server Agent service account , Connect to the publisher by impersonating the process account
Runs every morning around 3 am
Can anyone give me any insight as to what might be going on here?
July 23, 2013 at 9:55 am
After doing some more diggin, I stumbled across this:
http://go4answers.webhost4life.com/Example/error-8152-during-snapshot-creation-73067.aspx
This is saying it has something to do with permissions...
I am using the SQL Server Agent account to do this...And I also have another database on the same server that I am replicating to reporting server and I have not had a single issue with it...And it runs under the same account! So would that matter?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply