Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sqlsrv 2008 replication issues .help needed urgently Expand / Collapse
Author
Message
Posted Sunday, December 15, 2013 5:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 12:46 PM
Points: 38, Visits: 65
I have to replicate 14 tables and out of 14 only one table is refusing to replicate. Its transaction replication on sql server 2008 R2 (SP1) on win 2003 srv 64 bit.

I get the following error and if i run the bcp on cmd prompt it loads the data . I see people reporting similar issues for 2005 and suggestions but none are applicable to SQL SRV 2008.

"

Error messages:
The process could not bulk copy into table '"dbo"."TSR_ACCOUNT_VEHICLE"'. (Source: MSSQL_REPL, Error number:MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
Data conversion failed
To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below. Consult the BOL for more information on the bcp utility and its supported options.
(Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253
bcp "TPDB"."dbo"."TSR_ACCOUNT_VEHICLE" in "\\dotstodb035\ReplData\unc\ORADIST_DISTRIBUTION_PROD_ACCT_VEH\20131215184403\TSR_ACCOUNT_VEHICLE_2.bcp" -e "errorfile" -t"<x$3>" -r"<,@g>" -m10000 -SDOTSTOINFDB101 -T -w (Source: MSSQLServer, Error number: 20253)
Get help: http://help/20253

"

Thanks
P.S : i have also posted this on msdn web site since iam badly in need of an answer quickly , hope its ok.
Post #1523071
Posted Monday, December 16, 2013 1:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:23 PM
Points: 1,281, Visits: 1,761
Check that the TSR_ACCOUNT_VEHICLE table schema is the same between databases.
If it is, drop the article from publisher, drop table on subscriber. Add article, snapshot, should work according to this: http://www.sqlservercentral.com/blogs/jeffrey_yao/2008/06/03/replication-error-quot-field-size-too-large-quot/
Post #1523116
Posted Monday, December 16, 2013 2:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
Andrew G (12/16/2013)
Check that the TSR_ACCOUNT_VEHICLE table schema is the same between databases.
If it is, drop the article from publisher, drop table on subscriber. Add article, snapshot, should work according to this: http://www.sqlservercentral.com/blogs/jeffrey_yao/2008/06/03/replication-error-quot-field-size-too-large-quot/


Or you could just modify the table on the subscriber to match that of the publisher (minus any identity column setting usually).

It seems strange that the schema would be different if it was initialised from a snapshot.
Post #1523126
Posted Monday, December 16, 2013 7:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 12:46 PM
Points: 38, Visits: 65
The table is identicle , I am not using identity columns, and this table replicates perfectly on other subscribers which tells me that it must have something to do with the environment settings.
also if the table structure was different i would not be able to manually run the bcp command which succeeeds if I run it on the DOS prompt.

I also set the article properties to truncate the table and not drop it, no luck.
Post #1523232
Posted Monday, December 16, 2013 8:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 12:46 PM
Points: 38, Visits: 65
when i run the bcp in DOS window it loads the data but gives the following error, i think this might be my issue. but how to interpret this error? it doesnt show what exactly is wrong

#@ Row 11165008, Column 7: Invalid character value for cast specification @#
35632370 2041766 013MEX FL 01 2013-11-30 14:00:00 0000-00-00 00:00:00
Post #1523513
Posted Monday, December 16, 2013 9:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, November 23, 2014 11:23 PM
Points: 1,281, Visits: 1,761
To me, that looks like column 7 has an invalid value "0000-00-00 00:00:00". Is there blank data for that column in the source?
Since it's a datetime, is there some sort of bad conversion going on with the date format? Check the default language of the login being used on that server, is it US_English? Is that the same as the other servers.

Post #1523517
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse