August 10, 2012 at 8:03 am
I had a dynamic query in which exceeds 8000 charactes adn will have approraxmately 15000 characters how can the same be done
August 10, 2012 at 8:04 am
Use VARCHAR(MAX), which will store upto 2GB worth of data.
August 17, 2012 at 5:24 am
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
August 17, 2012 at 5:35 am
If your function returns more than 8000, then change its returning type to varchar(max) too.
August 17, 2012 at 5:44 am
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
August 17, 2012 at 7:18 am
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.
August 17, 2012 at 7:28 am
Change the function to return varchar(max) and have a str of varchar(max)
August 17, 2012 at 8:02 am
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).
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
August 18, 2012 at 2:03 am
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.
August 19, 2012 at 9:21 pm
pawan.boyina (8/18/2012)
If we take a subset of dates then the same is workingIf 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?
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply