• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3583
  • Last Modified:

Msg 8152, Level 16, State 14, Line 27

Hello Experts,

I'm trying to troubleshoot some one else's SQL code, but I admit that I do not have advances SQL knowledge, nevertheless, I found a few discrepancies in the pd.txt. , such as long data, commas in the address, etc., and I am sure that the error is related to one of two discrepancies in the file opposed to the code. However, for obvious reason, I cannot post pd.txt as it contains confidential medical data.

I keep getting Msg 8152, Level 16, State 14, Line 27, but Line 27 refers to nothing (just a closed parenthesis) and I really cannot know which field and row it is referring to.

I think the error is relating a too long over field, and in excel I have eyeballed the longest data, but they are all under the maximums. I am almost tempted to write a script to give me the highest length of each field data, but before I do, I am sure that there is a quicker way.

How can I find out to what column and/or row that the error is at, or what is the real line number?

below is the code, and right after it is the complete SQL output.

Thank you

GO
Use MAS_BHE
DROP TABLE PDTEST
create table pdtest   ---Create a table to load the patient information data from API into a temporary table in SQL
(

--CustID INT IDENTITY(1,1),
work_phone_extension varchar(6),
name_part varchar(30),
residential_street_address_line_2 varchar(30),
referring_physician_first_name varchar(20),
residential_street_address_line_1 varchar(30),
work_phone varchar(17),
referring_physician_last_name varchar(20),
residential_country_and_province_or_state varchar(10),
residential_postal_or_zip_code varchar(10),
referring_physician_code varchar(10),
residence_phone varchar(17),
health_card varchar(12),
patient_note varchar(40),
patient_email varchar(40),
item_id varchar(30),
cell_phone varchar(12),
residential_city varchar(20),
health_card_version varchar(20),
unique_vendor_id_sequence varchar(20)
)
BULK INSERT pdtest ---Import the from text file ino the temporary table
FROM 'Z:\Site\pd.TXT'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS,
--FORMATFILE = 'C:\PYTHON27\SCRIPTS\pdfileformat.fmt', 
FIRSTROW = 2
)
GO
SELECT * FROM pdtest
DROP TABLE temp_AR_Customer
create table temp_AR_Customer --Create another temporary table to format the raw data from API
(
--CustID INT IDENTITY(1,1),
work_phone_extension varchar(6),
name_part varchar(30),
residential_street_address_line_2 varchar(30),
referring_physician_first_name varchar(15),
residential_street_address_line_1 varchar(30),
work_phone varchar(17),
referring_physician_last_name varchar(15),
residential_country_and_province_or_state varchar(10),
residential_postal_or_zip_code varchar(10),
referring_physician_code varchar(10),
residence_phone varchar(17),
health_card varchar(12),
patient_note varchar(30),
patient_email varchar(40),
item_id varchar(30),
cell_phone varchar(12),
residential_city varchar(20),
health_card_version varchar(20),
unique_vendor_id_sequence varchar(20)
)

INSERT INTO temp_AR_Customer --Insert data into the temporary table 
(
--CustID INT IDENTITY(1,1),
work_phone_extension,
name_part ,
residential_street_address_line_2 ,
referring_physician_first_name ,
residential_street_address_line_1 ,
work_phone ,
referring_physician_last_name ,
residential_country_and_province_or_state ,
residential_postal_or_zip_code ,
referring_physician_code ,
residence_phone ,
health_card ,
patient_note,
patient_email,
item_id ,
cell_phone ,
residential_city ,
health_card_version ,
unique_vendor_id_sequence 
)
SELECT
work_phone_extension,
name_part ,
residential_street_address_line_2 ,
SUBSTRING(referring_physician_first_name,1,15) ,
residential_street_address_line_1 ,
work_phone ,
referring_physician_last_name ,
residential_country_and_province_or_state ,
residential_postal_or_zip_code ,
referring_physician_code ,
residence_phone ,
health_card ,
patient_note,
patient_email,
item_id ,
cell_phone ,
residential_city ,
health_card_version,
unique_vendor_id_sequence 
FROM PDTEST where unique_vendor_id_sequence>=13

--Check the content of the table.
select * from pdtest
SELECT * FROM temp_AR_Customer
GO
--Doing Update/Insert into AR_Customer

MERGE --Add a new patient if she'he doesn't exist in MAS or modify an existing customer
AR_Customer AS target1
USING 
temp_AR_Customer AS source1
ON 
target1.CustomerNo = RIGHT('0000000'+ CONVERT(VARCHAR,source1.unique_vendor_id_sequence),7)
WHEN MATCHED THEN
        UPDATE 
        SET
     
target1.Customerno=RIGHT('0000000'+ CONVERT(VARCHAR,source1.unique_vendor_id_sequence),7), 
target1.DateLastActivity = current_timestamp,
target1.DateUpdated = current_timestamp,
target1.TimeUpdated = Convert(varchar(5), GetDate(), 108),
target1.DateEstablished = current_timestamp,
target1.CustomerName = source1.name_part ,
target1.TelephoneExt = SUBSTRING(source1.work_phone_extension,1,5),
target1.TelephoneNo = source1.work_phone ,
target1.AddressLine1 = source1.residential_street_address_line_1 ,
target1.AddressLine2 = source1.residential_street_address_line_2 ,
target1.City = source1.residential_city ,
target1.EBMEnabled = 'N',
target1.BatchFax = 'N',
target1.Customerstatus = 'A',
target1.OpenItemCustomer = 'Y',
target1.State = SUBSTRING(residential_country_and_province_or_state,len(residential_country_and_province_or_state)-1,2),
target1.ZipCode = source1.residential_postal_or_zip_code ,
target1.CountryCode = 'CAN',
target1.CustomerType = 'P',
target1.TermsCode = '00',
target1.TaxSchedule = 'NONTAX',
target1.UDF_PATIENT_NOTE = source1.patient_note,
target1.EmailAddress = source1.patient_email,
target1.UDF_PHYSICAN_FIRST_NAME = SUBSTRING(source1.referring_physician_first_name,1,15) ,
target1.UDF_PHYSICIAN_LAST_NAME = source1.referring_physician_last_name ,
target1.UDF_PHYSICIAN_CODE = source1.referring_physician_code,
target1.UDF_HEALTH_CARD = source1.health_card ,
target1.UDF_HEALTH_CARD_VERSION = source1.health_card_version,
target1.UDF_CELL_PHONE = source1.cell_phone,
target1.UserCreatedKey= '0000000004',
target1.UserUpdatedKey='0000000004'

WHEN NOT MATCHED THEN

insert 
(
EBMEnabled ,
BatchFax,
Customerstatus,
OpenItemCustomer,
CustomerType,
TermsCOde,
TaxSchedule,
ARDivisionNo,
CustomerNo,
DateLastActivity,
DateLastPayment,
DateLastStatement,
DateLastFinanceChrg,
DateLastAging,
DateCreated,
TimeCreated,
DateUpdated,
TimeUpdated,
SalespersonNo,
DateEstablished,
CustomerName,
TelephoneExt,
TelephoneNo,
AddressLine1,
AddressLine2,
City,
State,
ZipCode,
CountryCode,
UDF_CELL_PHONE,
UDF_PHYSICIAN_CODE,
UDF_HEALTH_CARD,
UDF_HEALTH_CARD_VERSION,
UDF_PHYSICAN_FIRST_NAME,
UDF_PHYSICIAN_LAST_NAME
)
values 
(
'N',
'N',
'A',
'Y',
'P',
'00',
'NONTAX',
'00',
RIGHT('0000000'+ CONVERT(VARCHAR,source1.unique_vendor_id_sequence),7), 
'1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000',
'1753-01-01 00:00:00.000',
CURRENT_TIMESTAMP,
Convert(varchar(5), GetDate(), 108),
CURRENT_TIMESTAMP, 
Convert(varchar(5), GetDate(), 108),
'0000',
CURRENT_TIMESTAMP,
 source1.name_part ,
 source1.work_phone_extension,
 source1.work_phone ,
 source1.residential_street_address_line_1 ,
 source1.residential_street_address_line_2 ,
 source1.residential_city ,
SUBSTRING(source1.residential_country_and_province_or_state,len(source1.residential_country_and_province_or_state)-1,2),
source1.residential_postal_or_zip_code ,
'CAN',
 source1.cell_phone,
 source1.referring_physician_code,
 source1.health_card ,
 source1.health_card_version,
 source1.referring_physician_first_name ,
 source1.referring_physician_last_name
 );
--Doing Update or Insert Shipto customer address
MERGE 
SO_ShipToAddress AS target2
USING 
temp_AR_Customer AS source1
ON 
target2.CustomerNo = RIGHT('0000000'+ CONVERT(VARCHAR,source1.unique_vendor_id_sequence),7)
WHEN MATCHED THEN
        UPDATE 
        SET

target2.CustomerNo = RIGHT('0000000'+ CONVERT(VARCHAR,source1.unique_vendor_id_sequence),7), 
target2.ShipToAddress1 = source1.residential_street_address_line_1,
target2.ShipToState = SUBSTRING(residential_country_and_province_or_state,len(residential_country_and_province_or_state)-1,2),
target2.ShipToZipCode= source1.residential_postal_or_zip_code,
target2.ShipToCountryCode = 'CAN',
target2.TelephoneNo = source1.residence_phone,
target2.DateUpdated = current_timestamp,
target2.TimeUpdated = Convert(varchar(5), GetDate(), 108),
target2.ShiptoName = source1.name_part

WHEN NOT MATCHED THEN
   INSERT 
           (ARDivisionNo
           ,CustomerNo
           ,ShipToCode
           ,ShipToName
           ,ShipToAddress1
           ,ShipToState
           ,ShipToZipCode
           ,ShipToCountryCode
           ,TelephoneNo
           ,SalespersonNo
           ,DateCreated
           ,TimeCreated
           ,DateUpdated
           ,TimeUpdated
                         )
     VALUES
           ( 
           '00'
           ,RIGHT('0000000'+ CONVERT(VARCHAR,source1.unique_vendor_id_sequence),7)
           ,'RES'
           ,source1.name_part
           ,source1.residential_street_address_line_1
           ,SUBSTRING(residential_country_and_province_or_state,len(residential_country_and_province_or_state)-1,2)
           ,source1.residential_postal_or_zip_code
           ,'CAN'
           ,source1.residence_phone 
           ,'0000'
           ,current_timestamp
           ,Convert(varchar(5), GetDate(), 108)
           ,current_timestamp
           ,Convert(varchar(5), GetDate(), 108)
           );
GO
--Getting the physician Info into a temporary table
create table temp_providers
(

first_name varchar(15),
last_name varchar(15),
ohip_no varchar(20),
dob varchar(12),
sex varchar(2),
phone varchar(15),
address varchar(30),
cpso_no varchar(20),
provider_no varchar(20),
email varchar(20)
)

BULK INSERT temp_providers
FROM 'Z:\Site\providers.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
KEEPNULLS,
--FORMATFILE = 'C:\PYTHON27\SCRIPTS\pdfileformat.fmt', 
FIRSTROW = 2
)
GO

--Getting the physician Info into a UDT table in MAS
MERGE 
SO_UDT_PHYSICIAN_DETAILS AS target3
USING 
temp_providers AS source3
ON 
target3.UDF_PHYSICIAN_DETAIL_COD = source3.provider_no

WHEN MATCHED THEN
        UPDATE 
        SET

target3.UDF_PHYSICIAN_DETAIL_COD = source3.provider_no,
target3.UDF_PHYSICIAN_ADDR = source3.address,
target3.UDF_PHYSICIAN_EMAIL = source3.email,
target3.UDF_UDF_PHYSICIAN_PHONE = source3.phone,
target3.UDF_CPSO_NO = source3.CPSO_NO,
target3.UDF_OHIP_NO = source3.OHIP_NO,
target3.UDF_PHYSICIAN_NAME = ((source3.first_name)+ ' ' + (source3.last_name))

WHEN NOT MATCHED THEN
   INSERT 
           (
            UDF_PHYSICIAN_DETAIL_COD,
            UDF_PHYSICIAN_ADDR,
            UDF_PHYSICIAN_EMAIL,
            UDF_UDF_PHYSICIAN_PHONE,
            UDF_CPSO_NO,
            UDF_OHIP_NO,
            UDF_PHYSICIAN_NAME
          
            )
     VALUES
           ( 
           source3.provider_no,
           source3.address,
           source3.email,
           source3.phone,
           source3.CPSO_NO,
           source3.OHIP_NO,
          ((source3.first_name)+ ' ' + (source3.last_name))
           );
           select * from SO_UDT_PHYSICIAN_DETAILS
--Drop the table to clean up database.
--DROP TABLE pdtest
drop table temp_providers
GO


update  AR_Customer set UDF_PHYSICIAN_ADDR=(select UDF_PHYSICIAN_ADDR from SO_UDT_PHYSICIAN_DETAILS where AR_Customer.UDF_PHYSICIAN_CODE = SO_UDT_PHYSICIAN_DETAILS.UDF_UDF_PHYSICIAN_CODE);   
update  AR_Customer set UDF_PHYSICIAN_EMAIL=(select UDF_PHYSICIAN_EMAIL from SO_UDT_PHYSICIAN_DETAILS where AR_Customer.UDF_PHYSICIAN_CODE = SO_UDT_PHYSICIAN_DETAILS.UDF_UDF_PHYSICIAN_CODE);
update  AR_Customer set UDF_PHYSICIAN_PHONE=(select UDF_UDF_PHYSICIAN_PHONE from SO_UDT_PHYSICIAN_DETAILS where AR_Customer.UDF_PHYSICIAN_CODE = SO_UDT_PHYSICIAN_DETAILS.UDF_UDF_PHYSICIAN_CODE);         

Open in new window


(403 row(s) affected)

(403 row(s) affected)
Msg 8152, Level 16, State 14, Line 27
String or binary data would be truncated.
The statement has been terminated.

(403 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(14 row(s) affected)

(14 row(s) affected)

(14 row(s) affected)

(393 row(s) affected)

(393 row(s) affected)

(393 row(s) affected)

Open in new window

0
APD Toronto
Asked:
APD Toronto
  • 4
  • 3
1 Solution
 
APD TorontoAuthor Commented:
I also realized that I do not have the  'C:\PYTHON27\SCRIPTS\pdfileformat.fmt',

Is it important?
0
 
knightEknightCommented:
I believe that message refers to the BULK INSERT statement (the line under the close paren), and it points to a truncation error - meaning the data in the file is longer than the field.

See this for more details:  http://www.sql-server-helper.com/error-messages/msg-8152.aspx

Also here:  http://www.expertsupdates.com/sqlservererrormessage-tutorials/sql-server-error-messages-msg-8152-10.aspx

Also, your missing pdfileformat.fmt file is not a problem here because that line is commented out in your code anyway.
0
 
APD TorontoAuthor Commented:
That's what I thought, but how can I pinpoint the row/column that is too long?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
knightEknightCommented:
Note that line 1 in your code above is a "GO" statement, so the actual count begins below this.  In other words, line 2 of your code is line 1 of what is actually being executed.  After each GO statement, the line count starts over.  This is just FYI.

I recommend increasing the length of one or more fields in the pdtest table that you suspect might have this problem.  Or, create a test file containing only one row of data from the 'Z:\Site\pd.TXT' file and insert that one first.  If it succeeds then you can work your way through the file by breaking it up into smaller chunks.  If it fails, then you have a nice tidy test file to work the issue with.
0
 
APD TorontoAuthor Commented:
WOW, Good advice - Thanks.

What do you think of
http://bytes.com/topic/sql-server/answers/523462-almost-there-bulk-insert

How do I use the BCP feature?
0
 
knightEknightCommented:
Good tips in that article too.

If you want to try this, add this line above the first "GO" in your code above:

   
SET ANSI_WARNINGS OFF

Open in new window

But be prepared to blow away any data it inserts because if there is a column mis-alignment, the data might not be useful after this.

BCP is a legacy command-line tool for doing things similar to this.  The basic help screen can be seen by running this command in a cmd.exe window:  bcp /?
... but you're better off googling it. :)
0
 
APD TorontoAuthor Commented:
Thank you so much, your idea worked!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now