SET ANSI_WARNINGS OFF - SQL 2005

I would like some advice on the SET ANSI_WARNINGS OFF.

I had a pretty complex query that was returning the "String or binary data would be truncated" error message so I dropped in SET ANSI_WARNINGS OFF. What (if any) are my issues with using this statement? Is there a more simple way of identifying the "String or binary data would be truncated" error message? Is it possible to see the truncated records to pin point what was the problem? I know there are alot of SQL gurus on this board ... so what I'm hoping for is a shortcut to get me past this message and advice on the pro's and con's to using the SET ANSI_WARNINGS OFF.

LVL 7
Jeff SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
>What (if any) are my issues with using this statement?

Mainly that it might mask some soft errors that you really do care about.  Personally, I would NEVER turn it OFF permanently.  I might consider toggling it ON/OFF for the duration of a query if I fully understood the implications and accepted the risk.

>Is there a more simple way of identifying the "String or binary data would be truncated" error message?
No.  You need to find the source of the problem and modify the code so that it doesn't happen.  You may need to increase a column or variable size or you may need to explicitly truncate some data so that it fits.  

Often, you can isolate the problem by inspecting the SQL with a little common sense and a big understanding of your data and datatypes.  Or at least you can find the likely candidates.  Other than that, start simplifying the SQL until the problem disappears.  Then, fix what's causing it.

If you post your query, we can probably speed you along.

>Is it possible to see the truncated records to pin point what was the problem?
Hard to say without seeing your query.  Certainly you could modify your query to capture the results in such a way that they are viewable.




0
Jeff SAuthor Commented:
Here is the SQL, I historically took it piece by piece until I found the issue. I guess I was hoping for a way to step past this alittle faster or easier. If you can spot anything out of whack let me know. The statements in the "WHERE" clause will look odd to you, but rest assured this is how the third party application handles them. Strange ... but it works.
/*Patient Ledger*/
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
 
DECLARE @DOBFrom datetime, @DOBTo datetime
 
IF 1 = 1
   BEGIN
	SET @DOBFrom = getdate() - (120 * 365.25)
	IF 1 = 1 
	      BEGIN	
		SET @DOBTo = getdate() - ((1-1) * 365.25)
	      END
	ELSE
	      BEGIN
		SET @DOBTo = getdate() - (1 * 365.25)
	      END
   END
ELSE
   BEGIN
                IF NULL = NULL
                        BEGIN
		SET @DOBFrom = getdate() - (365.25*110)
		SET @DOBTo = getdate()
	        END
	ELSE
	        BEGIN
		SET @DOBFrom = NULL
		SET @DOBTo = NULL
	        END
   END
 
CREATE TABLE	#Visit
		(
			PatientVisitId int,
			PatientProfileId int
		)
 
CREATE TABLE	#Ledger
		(	
			PatientProfileId int,
			Type smallint,
			PatientId varchar(15) NULL,
			PatientMRN VARCHAR(15) NULL,
			Birthdate datetime NULL,
			PatientAge INT NULL,
			PatientSex VARCHAR(1)NULL,
			PatientSSN VARCHAR(15)NULL,
			PatientName varchar(110) NULL,
			PatientAddress1 varchar(50) NULL,
			PatientAddress2 varchar(50) NULL,
			PatientCity varchar(30) NULL,
			PatientState varchar(3) NULL,
			PatientZip varchar(10) NULL,
			PatientPhone1 varchar(15) NULL,
			PatientPhone1Type varchar(25) NULL,
			PatientPhone2 varchar(15) NULL,
			PatientPhone2Type varchar(25) NULL,
			PatientVisitId int NULL,
			VisitDateOfService datetime NULL,
			VisitDateOfEntry datetime NULL,
			DoctorId int NULL,
			DoctorName varchar(60) NULL,
			FacilityId int NULL,
			FacilityName varchar(60) NULL,
			CompanyId int NULL,
			CompanyName varchar(60) NULL,
			TicketNumber varchar(20) NULL,
			PrimaryCarrier varchar(50) NULL,
			CurrentCarrier varchar(50) NULL,
			PatientVisitProcsId int NULL,
			TransactionDateOfServiceFrom datetime NULL,
			TransactionDateOfServiceTo datetime NULL,
			TransactionDateOfEntry datetime NULL,
			InternalCode varchar(10) NULL,
			ExternalCode varchar(10) NULL,
			Modifier1 varchar(4) NULL,
			Modifier2 varchar(4) NULL,
			Modifier3 varchar(4) NULL,
			Modifier4 varchar(4) NULL,
			Description varchar(255) NULL,
			Fee money NULL,
			Units float NULL,
			PatAmount money NULL,
			InsAmount money NULL,
			Action varchar(1) NULL,
			Payer varchar(255) NULL,
			Notes text NULL,
			PatBalance money NULL,
			InsBalance money NULL,
			Charges money NULL,
			Payments money NULL,
			Adjustments money NULL
		)				
			
/* Get the subset of visits for this report */
 
INSERT	#Visit
SELECT	pv.PatientVisitId, 
	pv.PatientProfileId
 
FROM	PatientVisit pv 
	INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
 
WHERE  --Filter on Patient
	(
	(NULL IS NOT NULL AND pp.PatientProfileId in (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on date type and range
	(
	('1' = '1' AND pv.Visit >= ISNULL(NULL,'1/1/1900') AND pv.Visit < dateadd(d, 1, ISNULL(NULL,'1/1/3000'))) OR
	('1' = '2' AND pv.Entered >= ISNULL(NULL,'1/1/1900') AND pv.Entered < dateadd(d,1,ISNULL(NULL,'1/1/3000')))
	)
	AND  --Filter on doctor
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on patient sex
	(
	(Null IS NOT NULL AND pp.sex in (Null)) OR
	(Null IS NULL)
	)
	AND  --Filter on DOB Date
	(
	pp.Birthdate >= @DOBFrom  AND pp.Birthdate <= @DOBTo
	)
 
/* Get demographics for the patient */
 
INSERT	#Ledger
		(
		PatientProfileId, Type, PatientId, PatientMRN, Birthdate, PatientAge, PatientSex, PatientSSN,
		PatientName, PatientAddress1, PatientAddress2, PatientCity, PatientState, PatientZip,
		PatientPhone1, PatientPhone1Type, PatientPhone2, PatientPhone2Type,
		PatBalance, InsBalance, Charges, Payments, Adjustments
	      	)	
 
SELECT DISTINCT	pp.PatientProfileId, 
		1, 
		pp.PatientId, 
		ISNULL(pp.MedicalRecordNumber,'') AS PatientMRN,
		pp.Birthdate,
		datediff(YYYY, isnull(pp.birthdate, getdate()), getdate())AS PatientAge,
		ISNULL(pp.Sex,'') AS PatientSex,
		ISNULL(SUBSTRING(pp.SSN,1,3) + '-' + SUBSTRING(pp.SSN,4,2) + '-' + SUBSTRING(pp.SSN,6,4),'') AS PatientSSN,
		RTRIM(RTRIM(RTRIM(ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) + ' ' + pp.Last) + ' ' + ISNULL(pp.Suffix, '')) AS PatientName, 
		pp.Address1, 
		pp.Address2, 
		pp.City, 
		pp.State, 
		pp.Zip,
		ISNULL(dbo.formatphone(pp.phone1,1),'') AS Phone1, 
		pp.Phone1Type, 
		ISNULL(dbo.formatphone(pp.phone2,1),'') AS Phone2,  
		pp.Phone2Type,
		PatBalance = (SELECT SUM(pva.PatBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
		InsBalance = (SELECT SUM(pva.InsBalance) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
		Charges = (SELECT SUM(pva.OrigInsAllocation + pva.OrigPatAllocation) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
		Payments = (SELECT SUM(pva.InsPayment + pva.PatPayment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId),
		Adjustments = (SELECT SUM(pva.InsAdjustment + pva.PatAdjustment) FROM #Visit pv INNER JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId WHERE pv.PatientProfileId = pp.PatientProfileId)
 
FROM	PatientProfile pp 
	INNER JOIN #Visit tv ON pp.PatientProfileId = tv.PatientProfileId
 
/* Get visit information for the patient */
 
INSERT	#Ledger
		(
		PatientProfileId, Type, PatientVisitId, VisitDateOfService, VisitDateOfEntry, 
		DoctorId, DoctorName, FacilityId, FacilityName, CompanyId, CompanyName,
		TicketNumber, PrimaryCarrier, CurrentCarrier
	       	)	
 
SELECT	pv.PatientProfileId, 
	2, 
	pv.PatientVisitId, 
	pv.Visit, 
	pv.Entered,
	pv.DoctorId, 
	d.ListName AS DoctorName,
	pv.FacilityId, 
	f.ListName AS FacilityName,
	pv.CompanyId, 
	c.ListName AS CompanyName,
	pv.TicketNumber,
	ic.ListName AS PrimaryCarrier,
	ic2.ListName AS CurrentCarrier
 
FROM	#Visit tv 
	INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
	INNER JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
	INNER JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
	INNER JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
	LEFT JOIN InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
	LEFT JOIN InsuranceCarriers ic2 ON pv.CurrentInsuranceCarriersId = ic2.InsuranceCarriersId
 
WHERE   --Filter on Insurance Carrier
 	(
 	('30' IS NOT NULL AND pv.PrimaryInsuranceCarriersId IN (30)) OR
 	('30' IS NULL)
 	)
 
/* Get diagnosis information for the patient's visits */
 
INSERT	#Ledger
		(
		PatientProfileId, Type, PatientVisitId, 
		InternalCode, ExternalCode,
		Description
	       	)	
 
SELECT	pv.PatientProfileId, 
	3, 
	pv.PatientVisitId, 
	pvd.Code, 
	pvd.ICD9Code,
	pvd.Description
 
FROM	#Visit tv
	INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
	INNER JOIN PatientVisitDiags pvd ON pv.PatientVisitId = pvd.PatientVisitId
 
/* Get procedure information for the patient's visits */
 
INSERT	#Ledger
		(
		PatientProfileId, Type, PatientVisitId, PatientVisitProcsId,
		TransactionDateOfServiceFrom, TransactionDateOfServiceTo, TransactionDateOfEntry,
		InternalCode, ExternalCode, Modifier1, Modifier2, Modifier3, Modifier4,
		Description, Fee, Units, PatAmount, InsAmount
	       	)
	
SELECT	pv.PatientProfileId, 
	4, 
	pv.PatientVisitId, 
	pvp.PatientVisitProcsId,
	pvp.DateOfServiceFrom, 
	pvp.DateOfServiceTo, 
	b.Entry,
	pvp.Code, 
	pvp.CPTCode,
	m1.Code AS Modifier1, 
	m2.Code AS Modifier2, 
	m3.Code AS Modifier3, 
	m4.Code AS Modifier4,
	pvp.Description, 
	pvp.TotalFee, 
	pvp.Units, 
	pvpa.OrigPatAllocation, 
	pvpa.OrigInsAllocation
 
FROM	#Visit tv 
	INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
	INNER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
	INNER JOIN Batch b ON pvp.BatchId = b.BatchId
	INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
	LEFT JOIN MedLists m1 ON pvp.Modifier1MId = m1.MedListsId
	LEFT JOIN MedLists m2 ON pvp.Modifier2MId = m2.MedListsId
	LEFT JOIN MedLists m3 ON pvp.Modifier3MId = m3.MedListsId
	LEFT JOIN MedLists m4 ON pvp.Modifier4MId = m4.MedListsId
 
/* Get transaction information for the patient's visits */
 
INSERT	#Ledger
		(
		PatientProfileId, Type, PatientVisitId, PatientVisitProcsId,
		TransactionDateOfServiceFrom, TransactionDateOfEntry,
		Description, Payer, PatAmount, InsAmount, Action, Notes
	       	)
	
SELECT	pv.PatientProfileId, 
	5, 
	pv.PatientVisitId, 
	NULL,
	ISNULL(pm.CheckDate, b.Entry), 
	b.Entry,
	CASE WHEN t.Action = 'T' THEN CASE WHEN pm.Source = 1 THEN 'Transfer from Patient' ELSE 'Transfer from Insurance' END ELSE at.Description END,
	pm.PayerName, 	
	CASE WHEN pm.Source = 1 THEN -t.Amount ELSE CASE WHEN t.Action = 'T' THEN t.Amount ELSE 0 END END, 
	CASE WHEN pm.Source = 2 THEN -t.Amount ELSE CASE WHEN t.Action = 'T' THEN t.Amount ELSE 0 END END,
	t.Action, 
	CASE WHEN ISNULL(t.ShowOnStatement, 0) <> 0 THEN t.Note ELSE NULL END
 
FROM	#Visit tv 
	INNER JOIN PatientVisit pv ON tv.PatientVisitId = pv.PatientVisitId
	INNER JOIN VisitTransactions vt ON pv.PatientVisitId = vt.PatientVisitId
	INNER JOIN Transactions t ON vt.VisitTransactionsId = t.VisitTransactionsId AND t.Action <> 'N' 
	INNER JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId AND ISNULL(pm.InsuranceTransfer, 0) = 0
	INNER JOIN Batch b ON pm.BatchId = b.BatchId
	LEFT JOIN MedLists at ON t.ActionTypeMId = at.MedListsId
 
SELECT		tl.* 
FROM		#Ledger tl 
ORDER BY	tl.PatientProfileId, tl.PatientVisitId, tl.PatientVisitProcsId, tl.TYPE
 
DROP TABLE #Visit
DROP TABLE #Ledger

Open in new window

0
dqmqCommented:
To my knowledge, you have to isolate the problem step by step.  

I'd simply just remove one SQL statement at a time until the problem disappears.  Then you've found the culprit SQL.  Then look for the places where truncation is likely to occur.  If need be, remove them one at a time until you get to the bottom of it.  

But it sounds, like you've arlready done that...I have no additional magic

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff SAuthor Commented:
Thanks ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.