String/Binary data truncated on BULK INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

String/Binary data truncated on BULK INSERT

Author

Topic

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-18 : 10:21:21

Apologies up front for the width on this post. If anyone has a tip on
how I can post this better, I'm all ears.

I am trying to insert a fixed-width text file into a table using bulk
insert with a format file. The table matches the format file, which
appears to match the data, yet I still get the error "String or binary
data would be truncated." every time.

I've attempted to import this both via a bulk insert/format file
combo, and with a data transform. Neither one works. (Though I note
that with the data transform, it won't do an implicit conversion
between the first column's char(10) date field, and the smalldatetime
field in the table.

This has worked in the past. I do not know why it isn't working now.
No new service packs or updates have been done on SQL. (SQL 2000,
SP4)

Here's the table:


CREATE TABLE [medicare_fee_schedule_db] (
	[effective_date] [smalldatetime] NOT NULL ,
	[carrier_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[locality_psro] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[procedure_code] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[modifier] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[mfsdb_description] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_conversion_factor] [float] NULL ,
	[mfsdb_update_factor] [float] NULL ,
	[mfsdb_work_rvu] [float] NULL ,
	[mfsdb_practice_rvu] [float] NULL ,
	[mfsdb_malpractice_rvu] [float] NULL ,
	[mfsdb_work_gpci] [float] NULL ,
	[mfsdb_practice_gpci] [float] NULL ,
	[mfsdb_malpractice_gpci] [float] NULL ,
	[mfsdb_global_surgery_days] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_preop_pct] [float] NULL ,
	[mfsdb_intraop_pct] [float] NULL ,
	[mfsdb_postop_pct] [float] NULL ,
	[mfsdb_pctc_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_multisurg_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_bilat_surg_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_asst_surg_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_two_surg_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_team_surg_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_bill_med_supp_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_site_of_svc_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_cyr_full_fee] [money] NULL ,
	[mfsdb_cyr_sos_full_fee] [money] NULL ,
	[mfsdb_rel_proc_1] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_rel_proc_2] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_rel_proc_3] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_rel_proc_4] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_rel_proc_5] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_rel_proc_6] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_rel_proc_7] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_rel_proc_8] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_cyr_trans_amt] [money] NULL ,
	[mfsdb_cyr_sos_trans_amt] [money] NULL ,
	[mfsdb_cyr_trans_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_ahpb] [money] NULL ,
	[mfsdb_base_endo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_phys_superv_diag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_fac_set_rvu] [float] NULL ,
	[mfsdb_non_fac_set_rvu] [float] NULL ,
	[mfsdb_perf_pmt_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_img_cap_ind] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[mfsdb_fac_img_amt] [money] NULL ,
	[mfsdb_nfac_img_amt] [money] NULL ,
	CONSTRAINT [XPKE_141] PRIMARY KEY  CLUSTERED 
	(
		[effective_date],
		[carrier_code],
		[locality_psro],
		[procedure_code],
		[modifier]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO

Here's the format file:


6.0
49
1       SQLCHAR       0      10       ""        1       effective_date
2       SQLCHAR       0       5       ""        2       carrier_code
3       SQLCHAR       0       2       ""        3       locality_psro
4       SQLCHAR       0       5       ""        4       procedure_code
5       SQLCHAR       0       2       ""        5       modifier
6       SQLCHAR       0      50       ""        6       mfsdb_description
7       SQLCHAR       0       1       ""        7       mfsdb_status
8       SQLCHAR       0      10       ""        8       mfsdb_conversion_factor
9       SQLCHAR       0       8       ""        9       mfsdb_update_factor
10      SQLCHAR       0      10       ""        10      mfsdb_work_rvu
11      SQLCHAR       0      10       ""        11      mfsdb_practice_rvu
12      SQLCHAR       0      10       ""        12      mfsdb_malpractice_rvu
13      SQLCHAR       0       6       ""        13      mfsdb_work_gpci
14      SQLCHAR       0       6       ""        14      mfsdb_practice_gpci
15      SQLCHAR       0       6       ""        15      mfsdb_malpractice_gpci
16      SQLCHAR       0       3       ""        16      mfsdb_global_surgery_days
17      SQLCHAR       0       8       ""        17      mfsdb_preop_pct    
18      SQLCHAR       0       8       ""        18      mfsdb_intraop_pct  
19      SQLCHAR       0       8       ""        19      mfsdb_postop_pct
20      SQLCHAR       0       1       ""        20      mfsdb_pctc_ind
21      SQLCHAR       0       1       ""        21      mfsdb_multisurg_ind       
22      SQLCHAR       0       1       ""        22      mfsdb_bilat_surg_ind
23      SQLCHAR       0       1       ""        23      mfsdb_asst_surg_ind
24      SQLCHAR       0       1       ""        24      mfsdb_two_surg_ind
25      SQLCHAR       0       1       ""        25      mfsdb_team_surg_ind                 
26      SQLCHAR       0       1       ""        26      mfsdb_bill_med_supp_ind
27      SQLCHAR       0       1       ""        27      mfsdb_site_of_svc_ind
28      SQLCHAR       0      11       ""        28      mfsdb_cyr_full_fee
29      SQLCHAR       0      11       ""        29      mfsdb_cyr_sos_full_fee
30      SQLCHAR       0       5       ""        30      mfsdb_rel_proc_1
31      SQLCHAR       0       5       ""        31      mfsdb_rel_proc_2
32      SQLCHAR       0       5       ""        32      mfsdb_rel_proc_3
33      SQLCHAR       0       5       ""        33      mfsdb_rel_proc_4
34      SQLCHAR       0       5       ""        34      mfsdb_rel_proc_5
35      SQLCHAR       0       5       ""        35      mfsdb_rel_proc_6
36      SQLCHAR       0       5       ""        36      mfsdb_rel_proc_7
37      SQLCHAR       0       5       ""        37      mfsdb_rel_proc_8
38      SQLCHAR       0      11       ""        38      mfsdb_cyr_trans_amt
39      SQLCHAR       0      11       ""        39      mfsdb_cyr_sos_trans_amt
40      SQLCHAR       0       1       ""        40      mfsdb_cyr_trans_ind
41	SQLCHAR       0       9       ""        41      mfsdb_ahpb
42      SQLCHAR       0       5       ""        42      mfsdb_base_endo
43      SQLCHAR       0       1       ""        43      mfsdb_phys_superv_diag
44      SQLCHAR       0      10       ""        44      mfsdb_fac_set_rvu
45	SQLCHAR       0      10       ""        45      mfsdb_non_fac_set_rvu
46      SQLCHAR       0       1       ""        46      mfsdb_perf_pmt_int
47      SQLCHAR       0       1       ""        47      mfsdb_img_cap_ind
48      SQLCHAR       0      11       ""        48      mfsdb_fac_img_amt
49      SQLCHAR       0      11       "\r\n"        49      mfsdb_nfac_img_amt

And the first 5 lines of the data. Again, sorry about the width.


01/01/200600510000073T  Delivery, comp imrt                               A00037.8975001.00000000000.000000000.000000000.1301.00000.84600.752XXX00.0000000.0000000.00000300000 100000583.0500000583.05                                        00000583.0500000583.05 000000.00     00000018.070000018.07  00000000.0000000000.00
04/01/200500510000073T  Delivery, comp imrt                               A00037.8975001.01500000000.000000000.000000000.1301.00000.85800.752XXX00.0000000.0000000.00000300000 100000589.6400000589.64                                        00000589.6400000589.64 000000.00     00000018.020000018.02  00000000.0000000000.00
01/01/200600512000073T  Delivery, comp imrt                               A00037.8975001.00000000000.000000000.000000000.1301.00000.83900.722XXX00.0000000.0000000.00000300000 100000578.1100000578.11                                        00000578.1100000578.11 000000.00     00000018.070000018.07  00000000.0000000000.00
04/01/200500512000073T  Delivery, comp imrt                               A00037.8975001.01500000000.000000000.000000000.1301.00000.83800.722XXX00.0000000.0000000.00000300000 100000575.8400000575.84                                        00000575.8400000575.84 000000.00     00000018.020000018.02  00000000.0000000000.00
01/01/200600522000073T  Delivery, comp imrt                               A00037.8975001.00000000000.000000000.000000000.1301.00000.85400.382XXX00.0000000.0000000.00000300000 100000586.7100000586.71                                        00000586.7100000586.71 000000.00     00000018.070000018.07  00000000.0000000000.00

Any ideas or help would be appreciated. Thanks.

mattyblah
Starting Member

49 Posts

Posted - 2007-04-18 : 11:38:06

Have you tried using the MAXERRORS = setting in the bulk insert? It could be a temporary fix. Also, have you tried inserting the fields as just char, instead of float, money, and other data types? Maybe the file format changed.

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-18 : 12:16:30

Sorry, I should have posted the bulk insert statement as well:


BULK INSERT mfsdb_load FROM = '\\a70tmcrpcview01\customview\scftp\mfsdb.dat'
     with (FORMATFILE = '\\a70tmcrpcview01\customview\scftp\sqlload\mfsdb.fmt', MAXERRORS = 10)

I'll go ahead and try changing all the fields to char as a test, but I really can't do that in the production application. :-) I've ordered another data file from the mainframe team, just in case.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-18 : 12:39:26

I'm not sure if it will make a difference but the version you specified in your FMT file is 6.0 (the first line of your format file). For SQL Server 2000, you should use 8.0.

SQL Server Helper
http://www.sql-server-helper.com

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-18 : 12:52:48

Good point, sshelper. It hasn't made a difference before, but I'll go ahead and change it anyway, just to test.

Thanks.

-d.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-18 : 13:04:34

Another thing to check is your destination table. You posted the structure for the medicare_fee_schedule_db table but your BULK INSERT statement is inserting to mfsdb_load table. Most probably your mfsdb_load table does not have the same structure as your medicare_fee_schedule_db table.

SQL Server Helper
http://www.sql-server-helper.com

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-18 : 14:44:13

Actually, it does have the same structure, since I create it with the following:


select * into mfsdb_load from medicare_fee_schedule_db where 1 = 0

It's part of a larger process where I bring everything into a load table, check the dates on the new data, remove those records from the medicare_fee_schedule_db table, and insert the records from the load table into schedule table. Then the load table is dropped.

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-19 : 06:53:51

OK, I tried changing the table fields to all char datatypes. The file imports OK, but I cannot use it in the application that way.

Any further ideas?

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

readysetstop
Posting Yak Master

123 Posts

Posted - 2007-04-19 : 12:42:42

Oddly enough, the issue appears to have resolved itself. It runs now, even with all the original scripts and files. I am at a loss to explain this.

Obvious case of user error, just wish I knew what I did wrong. Thanks to sshelper and mattyblah for their suggestions.

____________________________________________________________________________________
"Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources