Date 8/29/2012 3:38:06 PM Log Job History (DEVWINSQL4-AbraHRMS_Live-snapshot_repl_AbraHRMS_Live-3)
Step ID 3 Server DEVWINSQL4 Job Name DEVWINSQL4-AbraHRMS_Live-snapshot_repl_AbraHRMS_Live-3 Step Name Detect nonlogged agent shutdown. Duration 00:00:00 Sql Severity 16 Sql Message ID 8152 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message Executed as user: NT AUTHORITY\NETWORK SERVICE. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152). The step failed.
Here is the details: -Snapshot Replication -Over 10,000 articles -Source production database replicates to reporting database on different server -Runs under SQL Server agent account
I have run redgate SQL Compare and it shows 22723 identical objects, therefore both databases have exact same schema, but I am getting string or binary data would be truncated when trying to run the snapshot job and it fails! I would say maybe permissions, but it seems odd to me as well to get that error for a permission issue...And another thing, using same servers, I also have snapshot replication set up on a different database to replicate to the same reporting server under the same permissions and I have had no issues with it whatsoever!
Can someone enlighten me? Should I change the agent security with a different user/password?
I'm also getting this error, using Transactional Replication on SQL 2012. But I've got a question on the proposed solution to compare schemas. Isn't the scope of the snapshot agent just to copy the publisher data to the distribution server as text files in the "ReplData" folder? It would be the distribution agent that would notice schema mismatches.
It seems to me that the only data insert or update happening during a Snapshot agent run would be to the system tables in Distribution, and possibly on the publisher.