How to find bad row in insert

Question

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

Answer 1

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

Answer 2

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 NULL,
[PostalCode] varchar NULL,
[UtilizationType] varchar NULL,
[EventName] varchar NULL,
[AdmissionItem] varchar NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL,
[RegistrationDate] [date] NULL,
[AmountDue] [decimal](8, 2) NULL,
[AmountPaid] [decimal](8, 2) NULL,
[EventRegistrationID] varchar NULL,
[Cancelled] [bit] NULL,
[AttendeeCategories] varchar NULL,
[MemberProductTypeID] [tinyint] NULL,
[NewReturningGuestID] [bit] NULL
) ON [PRIMARY]
GO

Answer 3

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?

Answer 4

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

Answer 5

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.