SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Varchar Exceeding 8000 characters


Varchar Exceeding 8000 characters

Author
Message
pawan.boyina
pawan.boyina
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 247
I had a dynamic query in which exceeds 8000 charactes adn will have approraxmately 15000 characters how can the same be done
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10121 Visits: 6367
Use VARCHAR(MAX), which will store upto 2GB worth of data.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


pawan.boyina
pawan.boyina
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 247
DECLARE @query VARCHAR(max)
declare @cols VARCHAR(max)
set @cols=dbo.[getAllDaysInYear]()
print @cols
SET @query = N'SELECT userid, '+@cols +'


FROM
( select userId,CONVERT(VARCHAR(10), loggedDate, 120) as loggedDate,logCount from loginStats
)
AS Q1
PIVOT
(
sum( logCount) FOR loggedDate IN ('+@cols +')
) AS Q2'
print @query
EXECUTE(@query)



when trying to execute the above Query I'm unable to execute the Query,


Below is the funtion used in the Query.


alter FUNCTION [dbo].[getAllDaysInYear] ( )
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
set @str='';
with x (dy, yr) as ( select dy, year (dy) yr from
( select getdate () - datepart (dy, getdate ()) + 1 dy
-- the first date of the current year
) tmp1 union all select dateadd (dd, 1, dy), yr from x where year (dateadd (dd, 1, dy)) = yr )
SELECT @str=@str+','+'['+ LEFT(CONVERT(VARCHAR,x.dy, 120), 10)+']' from x option (maxrecursion 400)
RETURN LTRIM(RTRIM(substring(@str,2,len(@str))))

END
SomewhereSomehow
SomewhereSomehow
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 469
If your function returns more than 8000, then change its returning type to varchar(max) too.


I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1347 Visits: 1515
Hi.

What is the exact error message you're receiving?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
pawan.boyina
pawan.boyina
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 247
my dynamic Query which will be in variable @query is not taking values greater than 8000, even though i have declared it as varchar(max)

Print @query is returning only 8000 charcters and execute(@query) is not working.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10121 Visits: 6367
Change the function to return varchar(max) and have a str of varchar(max)



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16352 Visits: 19554
anthony.green (8/17/2012)
Change the function to return varchar(max) and have a str of varchar(max)


The function returns a maximum of 4757 characters (for a leap year). It's inefficient but works ok.

Try measuring the stringlengths:

declare @cols VARCHAR(8000)
set @cols = dbo.[getAllDaysInYear]()
SELECT LEN(@cols) -- should be 4757

DECLARE @query VARCHAR(max)
SET @query = N'SELECT userid, '+@cols +'
FROM
( select userId,CONVERT(VARCHAR(10), loggedDate, 120) as loggedDate,logCount from loginStats
)
AS Q1
PIVOT
(
sum( logCount) FOR loggedDate IN ('+@cols +')
) AS Q2'

SELECT LEN(@query) -- should be 4757 * 2 plus 50 or so



Also, try running the code with a subset of dates, like this:

declare @cols VARCHAR(8000)
set @cols = '[2012-01-07],[2012-01-08],[2012-01-09],[2012-01-10],[2012-01-11],[2012-01-12],[2012-01-13],[2012-01-14],[2012-01-15],[2012-01-16]'

DECLARE @query VARCHAR(max)
SET @query = N'SELECT userid, '+@cols +'
FROM
( select userId,CONVERT(VARCHAR(10), loggedDate, 120) as loggedDate,logCount from loginStats
)
AS Q1
PIVOT
(
sum( logCount) FOR loggedDate IN ('+@cols +')
) AS Q2'

print @query
EXECUTE(@query)



This from BOL, regarding PRINT:
A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).


“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
Exploring Recursive CTEs by Example Dwain Camps
pawan.boyina
pawan.boyina
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 247
If we take a subset of dates then the same is working
If we need all the dates of a Year to be taken in that query, is it possible as print @query is not giving me the result, how to handle such situations.
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10446 Visits: 11961
pawan.boyina (8/18/2012)
If we take a subset of dates then the same is working
If we need all the dates of a Year to be taken in that query, is it possible as print @query is not giving me the result, how to handle such situations.

As Chris pointed out,
A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).


So, the string you see when PRINTing does not reall matter.

Try to do this:
print substring(@query, LEN(@query) - 100, 120)


This should confirm that your @query has all the characters.

What is the error message returning by the EXECUTE statement?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search