Solved

Msg 8152, Level 16, State 14, Line 27

Posted on 2014-02-06
7
2,904 Views
Last Modified: 2014-02-06
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
Comment
Question by:APD_Toronto
  • 4
  • 3
7 Comments
 

Author Comment

by:APD_Toronto
ID: 39840583
I also realized that I do not have the  'C:\PYTHON27\SCRIPTS\pdfileformat.fmt',

Is it important?
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 39840613
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
 

Author Comment

by:APD_Toronto
ID: 39840618
That's what I thought, but how can I pinpoint the row/column that is too long?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 33

Accepted Solution

by:
knightEknight earned 500 total points
ID: 39840623
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
 

Author Comment

by:APD_Toronto
ID: 39840627
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
 
LVL 33

Expert Comment

by:knightEknight
ID: 39840638
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
 

Author Closing Comment

by:APD_Toronto
ID: 39840707
Thank you so much, your idea worked!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now