Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Varchar Exceeding 8000 characters Expand / Collapse
Author
Message
Posted Friday, August 10, 2012 8:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:07 AM
Points: 29, Visits: 246
I had a dynamic query in which exceeds 8000 charactes adn will have approraxmately 15000 characters how can the same be done
Post #1343413
Posted Friday, August 10, 2012 8:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 5,216, Visits: 5,109
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
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

Post #1343414
Posted Friday, August 17, 2012 5:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:07 AM
Points: 29, Visits: 246
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
Post #1346477
Posted Friday, August 17, 2012 5:35 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
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
Post #1346486
Posted Friday, August 17, 2012 5:44 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:04 PM
Points: 900, Visits: 1,490
Hi.

What is the exact error message you're receiving?


Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2
Post #1346490
Posted Friday, August 17, 2012 7:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:07 AM
Points: 29, Visits: 246
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.
Post #1346534
Posted Friday, August 17, 2012 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:19 AM
Points: 5,216, Visits: 5,109
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
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

Post #1346538
Posted Friday, August 17, 2012 8:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:03 AM
Points: 6,748, Visits: 13,891
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
Post #1346558
Posted Saturday, August 18, 2012 2:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 6:07 AM
Points: 29, Visits: 246
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.
Post #1346821
Posted Sunday, August 19, 2012 9:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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?
Post #1346992
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse