question

EckertMark-6159 avatar image
0 Votes"
EckertMark-6159 asked EchoLiu-msft commented

How to find bad row in insert

I have a stored procedure failing on insert into. How can I see what value or row is causing the insert to fail?

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

This depend on your stored procedure and why this failed.

First step you will need to have a table to insert the data into and obviously you will need to have a stored procedure which is failing and an example of exaction which fail.

We need the same for start :-)

Please provide the missing information so we can reproduce the scenario: queries to create the relevant table, the stored procedure, and the data which you tried to use

0 Votes 0 ·

Hi @EckertMark-6159,

Welcome to the microsoft tsql Q&A forum!

Could you please share us your stored procedure, and related table structure (CREATE TABLE …) and some sample data(INSERT INTO …)? So that we’ll get a right direction and make some test.

Regards
Echo

0 Votes 0 ·
EckertMark-6159 avatar image
0 Votes"
EckertMark-6159 answered

I included the sql statement from the stored procedure below. When I execute this statement it fails. It seems to be a data issue but I can't find anything in the data that looks unusual like a character in an integer defined field. Its been running for months without an issue. I was hoping I could somehow see the field or row that it fails on. Is there a method to display the field or row in error?

insert into [CRM].[FactEvents]
select
a.EventID
,a.CampaignID
,case when a.AccountID is NULL
then 0
else a.OpportunityID
end as OpportunityID
,a.AccountID
,a.ContactID
,a.ContactEventID
,row_number() over (partition by a.ContactEventID order by a.ContactEventID) as UniqueID
,a.DateID
,a.EventTypeID
,a.AttendedID
,case when a.MarketSegmentID is NULL
then 0
else a.MarketSegmentID
end as MarketSegmentID
,a.AttendeeCategoryID
,a.ExperienceTypeID
,a.CampaignStatusID
,a.Exhibitor
,a.Staff
,a.Guest
,a.Speaker
,a.Sponsor
,a.Press
,a.CPECredit
,a.Location
,min(b.ZipCode) as PostalCode
,a.UtilizationType
,a.EventName
,a.AdmissionItem
,a.StartDate
,a.EndDate
,case when a.RegistrationDate > a.EndDate
then a.StartDate
else a.RegistrationDate
end as RegistrationDate
,AmountDue
,AmountPaid
,cast(EventTypeID as varchar) + '-' + cast(case when RegistrationDate > EndDate then StartDate else RegistrationDate end as varchar) as EventRegistrationID
,a.Cancelled
,rtrim(replace(
case when a.Exhibitor + a.Staff + a.Guest + a.Speaker + a.Sponsor + a.Press = 0
then 'Attendee/'
when a.Exhibitor + a.Staff + a.Guest + a.Speaker + a.Sponsor + a.Press > 0
then case when a.Exhibitor = 1
then 'Exhibitor/'
else ''
end
+ case when a.Staff = 1
then 'Staff/'
else ''
end
+ case when a.Guest = 1
then 'Guest/'
else ''
end
+ case when a.Speaker = 1
then 'Speaker/'
else ''
end
+ case when a.Sponsor = 1
then 'Sponsor/'
else ''
end
+ case when a.Press = 1
then 'Press/'
else ''
end
else 'Other/'
end
,'/',' ')) as AttendeeCategories
,a.MemberProductTypeID
,NewReturningGuestID = 0
from #FactEventStaging a
left outer join [Shared].[DimUSZipCodes] b
on a.Location = b.City
and b.ZipCodeType = 'Standard'
and b.Decommissioned = 0
and b.CityRank is not NULL
left outer join [Staging].[DimAccount] c
on a.AccountID = c.AccountID
left outer join [CRM].[DimMarketSegment] d
on c.MarketSegmentCode = d.CRMValue
group by a.EventID
,a.CampaignID
,a.OpportunityID
,a.AccountID
,a.ContactID
,a.ContactEventID
,a.DateID
,a.EventTypeID
,a.AttendedID
,a.MarketSegmentID
,a.AttendeeCategoryID
,a.ExperienceTypeID
,a.CampaignStatusID
,a.Exhibitor
,a.Staff
,a.Guest
,a.Speaker
,a.Sponsor
,a.Press
,a.CPECredit
,a.Location
,a.UtilizationType
,a.EventName
,a.AdmissionItem
,a.StartDate
,a.EndDate
,a.RegistrationDate
,a.AmountDue
,a.AmountPaid
,a.Cancelled
,a.MemberProductTypeID

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EckertMark-6159 avatar image
0 Votes"
EckertMark-6159 answered EckertMark-6159 published

here's the table structure of the table I'm inserting into. I don't know that I can post the data due to its nature but what I'm looking for is a method to display the row or a field like accountid when the insert fails to determine which row is causing the failure. CREATE TABLE [CRM].[FactEvents](
[EventID] [int] NOT NULL,
[CampaignID] [int] NULL,
[OpportunityID] [int] NULL,
[AccountID] [int] NULL,
[ContactID] [int] NULL,
[ContactEventID] [int] NULL,
[UniqueID] [int] NULL,
[DateID] [int] NULL,
[EventTypeID] [tinyint] NULL,
[AttendedID] [tinyint] NULL,
[MarketSegmentID] [tinyint] NULL,
[AttendeeCategoryID] [tinyint] NULL,
[ExperienceTypeID] [tinyint] NULL,
[CampaignStatusID] [tinyint] NULL,
[Exhibitor] [bit] NULL,
[Staff] [bit] NULL,
[Guest] [bit] NULL,
[Speaker] [bit] NULL,
[Sponsor] [bit] NULL,
[Press] [bit] NULL,
[CPECredit] [bit] NULL,
[Location] [varchar](150) NULL,
[PostalCode] [varchar](5) NULL,
[UtilizationType] [varchar](30) NULL,
[EventName] [varchar](150) NULL,
[AdmissionItem] [varchar](150) NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL,
[RegistrationDate] [date] NULL,
[AmountDue] [decimal](8, 2) NULL,
[AmountPaid] [decimal](8, 2) NULL,
[EventRegistrationID] [varchar](14) NULL,
[Cancelled] [bit] NULL,
[AttendeeCategories] [varchar](100) NULL,
[MemberProductTypeID] [tinyint] NULL,
[NewReturningGuestID] [bit] NULL
) ON [PRIMARY]
GO


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EckertMark-6159 avatar image
0 Votes"
EckertMark-6159 answered EchoLiu-msft commented

Is there a trace that can be turned on in sql server management studio version 18 so that I can see what record was being processed at the time the insert failed?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Currently sql server does not have this function. But when the insert fails, sql server will return the error type, please share the error message when you insert it.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

I have a stored procedure failing on insert into

And which error message do you get; we can't guess that?

Is there a trace that can be turned on in sql server management studio version 18

The version of SSMS doesn't matter in any way, which version is your database engine? Run this query

 SELECT @@version

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

Is there a trace that can be turned on in sql server management studio version 18 so that I can see what record was being processed at the time the insert failed?

No, there is no such feature.

These situations can indeed be problematic, particularly if the error message does not convey that much information. A long-time problem was the infamous message "String or binary would be truncated" which Microsoft finally improved in SQL 2019 to include which column that is affected. This improvement is available in recent builds of SQL 2016 and SQL 2017 as well, but you need to enable trace flag 470 to get it:

DBCC TRACEON(470)

Exactly what is best way to troubleshoot this situation depends on the error message you are getting, but you have yet to share this with us.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.