Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sqlsrv 2008 replication issues .help needed urgently


sqlsrv 2008 replication issues .help needed urgently

Author
Message
sahmad43
sahmad43
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
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.
Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2003 Visits: 2227
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/
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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.
sahmad43
sahmad43
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
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.
sahmad43
sahmad43
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
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
Andrew G
Andrew G
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2003 Visits: 2227
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search