September 21, 2017 at 8:18 am
To start with, I used the following link as my base to start: Older Post
I am attempting to load the data from just one table of the Snapshot Replication source for a particular day. My command looks like this:
MASTER.DBO.xp_cmdshell 'bcp "MyDatabase.dbo.Invoices" in "\\UNCPath\SQLBKUP\unc\ReplicationDir_JOBSGROUP\20170916041316\Invoices_5#1.bcp" -SServerName -T -E -n -b 10000'
The BCP errors with the following, where the 10 error messages appear to be related to the 10 DATETIME2 fields in my table:
output
NULL
Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype
NULL
BCP copy in failed
NULL
Since this is a snapshot replication, I used the .SCH file to recreate the structure just as would be done when replication does the load. No change. I've tried using a Format file (.FMT), and changing the command to use that. Errors the same way.
Is there somewhere that BCP logs errors that I'm missing? I've tried using the -e and error file location, but it creates a 0 byte file.
Really hope someone has a lead that will help me out. Ask for more detail as needed. I've checked the things like version, @@LANGUAGE, date formats (as best I know how), and more that other posts mention. Replication completes successfully on this server every night with this same data/bcp source on this same server. I feel like I'm trying to find the 1 switch that Replication services use that make this work.
Thanks in advance.
KD
September 22, 2017 at 7:07 am
Have you validated that the data types in your destination table are a match for the same exact table that is the destination snapshot replica ? Next step is to then look at the data file and see if the actual data in it has a problem. ODBC doesn't support certain data types, so keep that in mind (like datetime2, for example).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 22, 2017 at 7:14 am
Thanks Steve
I've been wondering about the DT2 columns. The columns in the originating and target table are DT2 columns. Any idea how Snapshot Replication is using BCP and bypassing this error then? That's basically what I'm trying to mimic for this one table. You're the first person to mention the DT2 columns and ODBC.
KD
September 22, 2017 at 8:32 am
kDBAjan - Friday, September 22, 2017 7:14 AMThanks SteveI've been wondering about the DT2 columns. The columns in the originating and target table are DT2 columns. Any idea how Snapshot Replication is using BCP and bypassing this error then? That's basically what I'm trying to mimic for this one table. You're the first person to mention the DT2 columns and ODBC.
KD
Can you run a Profiler trace and see exactly what's being executed?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 22, 2017 at 11:01 am
sgmunson - Friday, September 22, 2017 8:32 AMkDBAjan - Friday, September 22, 2017 7:14 AMThanks SteveI've been wondering about the DT2 columns. The columns in the originating and target table are DT2 columns. Any idea how Snapshot Replication is using BCP and bypassing this error then? That's basically what I'm trying to mimic for this one table. You're the first person to mention the DT2 columns and ODBC.
KD
Can you run a Profiler trace and see exactly what's being executed?
That's actually next on my list to do if I didn't find a solution. 🙂 I had one other suggestion to adjust the Distribution Agent to log more, but the instructions given me were for SQL 2000 and SQL 7 Enterprise. Not the best info from Microsoft there.
KD
September 22, 2017 at 11:16 am
Might as well go ahead and do the trace. 🙂 Indeed those older versions don't have much applicability any more.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 26, 2017 at 9:08 am
This looks like the problem: Invalid field size for datatype
You will need to make a comparison check between the field you want to import to and the data that should go into it.
Check the source table and see what the data type and length is for that field.
Something else worth checking is the collation between the two databases. I have had compatibility issues in the past due to this.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply