Production issue with varchar(max)

Question

Production issue with varchar(max)

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

The data was probably there, but the query output in Management Studio can show a maximum of 8192 characters per line, and defaults to much less.

You can verify that the data is really there by using the DATALENGTH function:

select DATALENGTH(@MyParam)

ramadesai108 SSChampion Points: 10609 More actions · Answer 2

Michael is right. The issue is completely because of SSMS limitations. Thanks for your time.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ramadesai108 (6/26/2012)
Also, I forgot to mention that it is a dynamic query and here is the SP:
Create PROCEDURE [dbo].[MySP]
(
@OrgId int,
@DivId int = null,
@EmpId nVarChar(MAX) = NULL,
@year int
)
AS
DECLARE @sql nVARCHAR(MAX)
DECLARE @cnts int
DECLARE @ParmDefinition nvarchar(50);
SET @sql = N'
SELECT @cnt =COUNT(DISTINCT e.OverallScore)
FROM Evals ev INNER JOIN
eval
ON ev.Id = e.Id INNER JOIN
orgs o ON e.EmpId = orgs.Id INNER JOIN
Divis d ON o.id = d.Id
INNER JOIN Lead l On o.Id = l.Id
WHERE (orgs.Id = '+ CAST(@OrgId as VARCHAR) +')
AND o.IsActive = 1
AND ((e.Year = '+ CAST(@year as VARCHAR) +'))'
IF (@DivId <> null)
BEGIN
SET @sql = @sql + ' AND (d.Id = '+ CAST(@DivisionId AS VARCHAR)+' )'
end
IF (@EmpId IS NOT NULL)
BEGIN
SET @sql = @sql + ' AND (e.EmpId IN ('+@EmpId+') )'
END
SET @ParmDefinition = N'@cnt int OUTPUT'
EXECUTE sp_executesql @sql, @ParmDefinition, @cnt=@cnts OUTPUT

Why don't you resolve the string into a #temp table using something like Jeff Moden's SPLIT function, and join to it? You would no longer need dynamic sql.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 4

@michael-2 Valentine Jones "max char 8192" this may be the case for SQL2000 but its not for SQL2012 wich i was using. I also used profiler trace to check what was executed this showed the same result 🙂

***The first step is always the hardest *******

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

ramadesai108

SSChampion

Points: 10609

June 28, 2012 at 6:44 am

#1506568

I am using SQL Server 2008.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 6

ramadesai108 (6/28/2012)
I am using SQL Server 2008.

go to tools then options in there in the query options you can see what the character limit is 🙂

***The first step is always the hardest *******

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 7

SGT_squeequal (6/26/2012)
i had the same issue with varchar(max) when using it to create dynamic SQL, when executing my SP i relieved syntax errors, i run the SP with select (@SQL) and noticed that the query was cut short. to over come this i just split up the bits of query in to sections then combined then together to execute hey presto worked fine.
I didn't take much notice of how much data there was but there was not 2gb 🙂

I've had the same problem and fixed it by ensuring that everything used in the construction of the dynamic SQL (including any variables or parameters) where all delcare as the MAX datatype ESPECIALLY if you use COALESCE anywhere in the construction of the dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ramadesai108 SSChampion Points: 10609 More actions · Answer 8

Thanks Jeff, it is a good idea to use max for all vars. I will try that.

clouwaerts Grasshopper Points: 16 More actions · Answer 9

clouwaerts

Grasshopper

Points: 16

June 29, 2012 at 6:47 am

#1507098

a