Solved

Msg 8152, Level 16, State 14, Line 27

Posted on 2014-02-06
7
2,998 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Setting variables in a stored procedure 5 51
online  environment for testing sql queries 5 31
SQL Syntax 6 41
Macro problems with Excel file 6 27
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

726 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