April 28, 2009 at 3:18 pm
SQL Server 2000.8.00.2282
Transactional Replication
Distributor Agent Issue
I have an issue where everytime a record is inserted into the subscriber from a certain table I get a replication failure. It tells me that it failed because of "Error converting data type nvarchar to int".
However, the replicated tables are identicle in both the publisher and subscriber.
The only thing I notice is that in the string that shows the update, it appears to be cutoff. I don't know if that is because it only shows so many characters in the Session Details GUI.
Any ideas? Anyone else have a similar issue? I regenerated the stored procs from the publisher and created new ones in the subscriber, but the issue is still the same.
The string shows up like this:
{CALL sp_MSins_BvSurvey (16197, 0, N'Cell_Cycle5', N'Cell_Cycle5', NULL, 16549, 0, 1, NULL, NULL, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1000000259, 0, 0, 16195, 0, 0, N'', 1, N'SU_ID', 0, N'SU_ID', N'LCS', N'ENTRY_LCS', N'PROTS_NUM', N'NIS_OUTCOME', N'S_NUMB', N'TIS_UNDR18', N'NIS_FINAL', N'TEEN_FINAL', N'FINAL', N'ENTRY_DIALMODE', 0, N'', N'ACIDACRLACPRACTCACTEACSTACRCAIIIAIHIAIPHAIRSAITIAIITAIPTAILCAIPSAACNAATCAATEPCIDPCRLPCPRPCTCPCTEPCSTPCRCPIIIPIHIPIPHPIRSPITIPIITPIPTPILCPIPSPACNPATCPATESIIISIHISIPHSIRSSITIS
Transaction sequence number and command ID of last execution batch are 0x0003C83C0020EA18000200000000 and 2.
April 29, 2009 at 1:43 am
You can view the full command by running sp_browsereplcmds on your distribution server since the GUI probably does truncate the command. Something like this should work:
USE distribution
GO
DECLARE@iPublisherIdint,
@iPublisherDatabaseIdint
SELECT@iPublisherId = srvid
FROMmaster..sysservers
WHEREsrvname= '' -- put your publisher server name in here
SELECT@iPublisherDatabaseId= id
FROM mspublisher_databases
WHEREpublisher_id = @iPublisherId
exec sp_browsereplcmds@publisher_database_id= @iPublisherDatabaseId,
@xact_seqno_start= N'0x0003C83C0020EA180002',
@xact_seqno_end= N'0x0003C83C0020EA180002',
@command_id= 2
Given the error message, I'd say there has to be a mismatch somewhere but if you've regenerated the stored procedures then all should be well. I don't use SQL Server generated stored procedures, I write them myself so I'm assuming the code within the stored procedure specifies the column names in the insert statement. If it doesn't and the subscriber table has the columns in a different order from the publisher then that will cause problems. I'm sure this won't be the case though. That's about all I can think of at the moment. Not much I'm afraid.
Mike
April 29, 2009 at 9:30 am
Thanks, I'll take a look at that.
There is a stored proc that generates the stored procdure objects for the insert, updates and deletes for replication based on the table schemas for all of the articles in the publication.
I did a side by side and the tables match. I looked over the replication objects and they look fine. All of the sp params are correct.
Never had an issue like this in 5 years of running replication. Weird.
Thanks again.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply