March 12, 2015 at 6:56 am
When I add a variable to my dynamic SQL the entire query is truncated at 4K. Without the variable it allows me to use the entire nvarchar(max) for the SQL. Any suggestions?
March 12, 2015 at 7:26 am
measterbro (3/12/2015)
When I add a variable to my dynamic SQL the entire query is truncated at 4K. Without the variable it allows me to use the entire nvarchar(max) for the SQL. Any suggestions?
How do you know it is truncated? What variable is holding your dynamic sql? Give us some details or share the code and we can give you an answer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 7:28 am
I'd bet that when you concatenate the variable the string is converted to nvarchar(8000) instead of remaining nvarchar(max)
Use sp_executesql and pass the variable as a parameter (if it is in fact a parameter)
*Edited*
p.s. or cast each string/variable to varchar(max) when concatenating
Far away is close at hand in the images of elsewhere.
Anon.
March 12, 2015 at 9:05 am
I know it is being truncated because I get a syntax error at a point about a third of the way in my SQL which I don't get if I don't add the variable. I am using "EXEC sp_executesql @DynamicPivotQuery" where @DynamicPivotQuery is NVARCHAR(MAX). The variable I'm using is @ColumnName NVARCHAR(MAX).
March 12, 2015 at 9:14 am
measterbro (3/12/2015)
I know it is being truncated because I get a syntax error at a point about a third of the way in my SQL which I don't get if I don't add the variable. I am using "EXEC sp_executesql @DynamicPivotQuery" where @DynamicPivotQuery is NVARCHAR(MAX). The variable I'm using is @ColumnName NVARCHAR(MAX).
Well the first parameter to sp_executesql is nvarchar(max) so the truncation must be happening in your code. As I said before, if you want some help you have to provide some details. Show us your code and we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 9:45 am
Sean Lange (3/12/2015)
measterbro (3/12/2015)
I know it is being truncated because I get a syntax error at a point about a third of the way in my SQL which I don't get if I don't add the variable. I am using "EXEC sp_executesql @DynamicPivotQuery" where @DynamicPivotQuery is NVARCHAR(MAX). The variable I'm using is @ColumnName NVARCHAR(MAX).Well the first parameter to sp_executesql is nvarchar(max) so the truncation must be happening in your code. As I said before, if you want some help you have to provide some details. Show us your code and we can help.
+1
nvarchar(max) + nvarchar(max) = nvarchar(max)
nvarchar(8000) + nvarchar(8000) = nvarchar(8000)
Far away is close at hand in the images of elsewhere.
Anon.
March 12, 2015 at 10:06 am
I'm sure I've been bitten by this issue before, but I'm trying to replicate it without success.
Maybe it has something to do with this:
From BOL
The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).
This was my test:
DECLARE @SQL nvarchar(max)
SELECT @SQL='SELECT ''' + CAST( (SELECT ',' + CAST( ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(15))
FROM sys.all_columns
FOR XML PATH('')) AS nvarchar(max)) + ''' x INTO #Dummy;
SELECT x, LEN( x)
FROM #Dummy;
DROP TABLE #Dummy;'
SELECT LEN(@SQL)
EXEC sp_executesql @SQL
March 12, 2015 at 11:36 am
Here is the SQL. If I replace the line 'SUM(ALWins) FOR Division_Name IN (['+@ColumnName2 +'])' with 'SUM(ALWins) FOR Division_Name IN ([East],[Central],[West])' it works correctly. The code is actually a stored procedure; the variable declarations at the beginning are only so I can run it in SSMS manually.
DECLARE @league varchar(10)='8';
DECLARE @conf1 varchar(10)='3';
DECLARE @conf2 varchar(10)='4';
DECLARE @div varchar(10)='13';
DECLARE @season varchar(10)='2012';
DECLARE @TimePeriod varchar(10)='1';
DECLARE @rs varchar(10)='1';
DECLARE @Neutral varchar(10)='0';
DECLARE @DynamicPivotQuery NVARCHAR(MAX);
DECLARE @ColumnName2 NVARCHAR(MAX)='East,Central,West';
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery = N'
DECLARE @league as VARCHAR(10) = '+@league +';
DECLARE @conf1 as VARCHAR(10) = '+@conf1 +';
DECLARE @conf2 as VARCHAR(10) = '+@conf2 +';
DECLARE @div as VARCHAR(10) = '+@div +';
DECLARE @season VARCHAR(10) = '+@Season +';
DECLARE @TimePeriod VARCHAR(10) = '+@TimePeriod +';
DECLARE @rs VARCHAR(10) = '+@RS +';
DECLARE @Neutral VARCHAR(10) = '+@Neutral +';
WITH GamesWon AS(
SELECTGames.HomeTeam AS Team, LeagueName, ShortName, Division_Name, (Games.NumYear / @TimePeriod * @TimePeriod) Season,
CASE WHEN [H_SC] > [A_SC] AND [ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)) THEN 1 ELSE 0 END AS ALwins,
CASE WHEN [H_SC] < [A_SC] AND [ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)) THEN 1 ELSE 0 END AS ALlosses,
CASE WHEN [H_SC] = [A_SC] AND [ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)) THEN 1 ELSE 0 END AS ALties,
CASE WHEN [H_SC] > [A_SC] AND [ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf2)) THEN 1 ELSE 0 END AS NLwins,
CASE WHEN [H_SC] < [A_SC] AND [ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf2)) THEN 1 ELSE 0 END AS NLlosses,
CASE WHEN [H_SC] = [A_SC] AND [ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf2)) THEN 1 ELSE 0 END AS NLties
FROM Games LEFT OUTER JOIN
Conferences ON Games.HConf = Conferences.Conference_ID LEFT OUTER JOIN
Divisions ON Games.ADiv = Divisions.Division_ID LEFT OUTER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Games.HDiv = @div AND Games.NumYear BETWEEN @season AND @season + @TimePeriod - 1 AND Games.RS LIKE @rs AND Neutral LIKE @Neutral)
UNION ALL
SELECTGames.AwayTeam AS Team, LeagueName, ShortName, Division_Name, (Games.NumYear / @TimePeriod * @TimePeriod) Season,
CASE WHEN [H_SC] < [A_SC] AND [HDiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)) THEN 1 ELSE 0 END AS ALwins,
CASE WHEN [H_SC] > [A_SC] AND [HDiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)) THEN 1 ELSE 0 END AS ALlosses,
CASE WHEN [H_SC] = [A_SC] AND [HDiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)) THEN 1 ELSE 0 END AS ALties,
CASE WHEN [H_SC] < [A_SC] AND [HDiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf2)) THEN 1 ELSE 0 END AS NLwins,
CASE WHEN [H_SC] > [A_SC] AND [HDiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf2)) THEN 1 ELSE 0 END AS NLlosses,
CASE WHEN [H_SC] = [A_SC] AND [HDiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf2)) THEN 1 ELSE 0 END AS NLties
FROM Games LEFT OUTER JOIN
Conferences ON Games.AConf = Conferences.Conference_ID LEFT OUTER JOIN
Divisions ON Games.HDiv = Divisions.Division_ID LEFT OUTER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Games.ADiv = @div AND Games.NumYear BETWEEN @season AND @season + @TimePeriod - 1 AND Games.RS LIKE @rs AND Neutral LIKE @Neutral)
),
-------------------------------------------------------------------------------------------------------------------------------------
ALWin AS (
SELECT Team, [East],[Central],[West]
FROM (
SELECT Team,
Division_Name,
ALWins
FROM GamesWon
) x
PIVOT(
SUM(ALWins) FOR Division_Name IN (['+@ColumnName2 +'])
) p ),
ALLoss AS (
SELECT Team, [East],[Central],[West]
FROM (
SELECT Team,
Division_Name,
ALlosses
FROM GamesWon
) x
PIVOT(
SUM(ALlosses) FOR Division_Name IN ([East],[Central],[West])
) p ),
ALTie AS (
SELECT Team, [East],[Central],[West]
FROM (
SELECT Team,
Division_Name,
ALTies
FROM GamesWon
) x
PIVOT(
SUM(ALTies) FOR Division_Name IN ([East],[Central],[West])
) p ),
NLWin AS (
SELECT Team, [East],[Central],[West]
FROM (
SELECT Team,
Division_Name,
NLWins
FROM GamesWon
) x
PIVOT(
SUM(NLWins) FOR Division_Name IN ([East],[Central],[West])
) p ),
NLLoss AS (
SELECT Team, [East],[Central],[West]
FROM (
SELECT Team,
Division_Name,
NLlosses
FROM GamesWon
) x
PIVOT(
SUM(NLlosses) FOR Division_Name IN ([East],[Central],[West])
) p ),
NLTie AS (
SELECT Team, [East],[Central],[West]
FROM (
SELECT Team,
Division_Name,
NLTies
FROM GamesWon
) x
PIVOT(
SUM(NLTies) FOR Division_Name IN ([East],[Central],[West])
) p ),
-------------------------------------------------------------------------------------------------------------------------------------
TeamsWins AS(
SELECT MAX(Team) Team,
MAX(LeagueName) LeagueName,
MAX(ShortName) ShortName,
MAX(Division_Name) Division_Name,
Season Season,
SUM(ALWins) ALWins,
SUM(NLWins) NLWins,
SUM(ALlosses) ALlosses,
SUM(NLlosses) NLlosses,
SUM(ALties) ALties,
SUM(NLties) NLties
FROM GamesWon
GROUP BY Season, Team
)
SELECT
isnull(ALWin.Central,0) ALCentralW,
isnull(ALWin.East,0) ALEastW,
isnull(ALWin.West,0) ALWestW,
isnull(ALLoss.Central,0) ALCentralL,
isnull(ALLoss.East,0) ALEastL,
isnull(ALLoss.West,0) ALWestL,
isnull(ALTie.Central,0) ALCentralT,
isnull(ALTie.East,0) ALEastT,
isnull(ALTie.West,0) ALWestT,
isnull(NLWin.Central,0) NLCentralW,
isnull(NLWin.East,0) NLEastW,
isnull(NLWin.West,0) NLWestW,
isnull(NLLoss.Central,0) NLCentralL,
isnull(NLLoss.East,0) NLEastL,
isnull(NLLoss.West,0) NLWestL,
isnull(NLTie.Central,0) NLCentralT,
isnull(NLTie.East,0) NLEastT,
isnull(NLTie.West,0) NLWestT,
TeamName Team,
Team_ID,
TeamsWins.LeagueName League,
TeamsWins.ShortName Conference,
TeamsWins.Division_Name Division,
ALWins,
ALlosses,
ALties,
NLWins,
NLlosses,
NLties,
CAST(isnull(ALWin.Central,0) as varchar(3)) +''-'' + CAST(isnull(ALLoss.Central,0) as varchar(3)) +''-'' + CAST(isnull(ALTie.Central,0) as varchar(3)) ALCentral,
CAST(isnull(ALWin.East,0) as varchar(3)) +''-'' + CAST(isnull(ALLoss.East,0)as varchar(3)) +''-'' + CAST(isnull(ALTie.East,0) as varchar(3)) ALEast,
CAST(isnull(ALWin.West,0) as varchar(3)) +''-'' + CAST(isnull(ALLoss.West,0) as varchar(3)) +''-'' + CAST(isnull(ALTie.West,0) as varchar(3)) ALWest,
CAST(isnull(NLWin.Central,0) as varchar(3)) +''-'' + CAST(isnull(NLLoss.Central,0) as varchar(3)) +''-'' + CAST(isnull(NLTie.Central,0) as varchar(3)) NLCentral,
CAST(isnull(NLWin.East,0) as varchar(3)) +''-'' + CAST(isnull(NLLoss.East,0)as varchar(3)) +''-'' + CAST(isnull(NLTie.East,0) as varchar(3)) NLEast,
CAST(isnull(NLWin.West,0) as varchar(3)) +''-'' + CAST(isnull(NLLoss.West,0) as varchar(3)) +''-'' + CAST(isnull(NLTie.West,0) as varchar(3)) NLWest,
CAST(ALWins as varchar(3)) +''-'' + CAST(ALlosses as varchar(3)) +''-'' + CAST(ALties as varchar(3)) AL,
REPLACE(LTRIM(REPLACE(CAST(CAST(ALWins as decimal) / (ALWins + ALlosses+ ALties) as decimal(4,3)), ''0'', '' '')), '' '', ''0'') AS ALPct,
CAST(NLWins as varchar(3)) +''-'' + CAST(NLlosses as varchar(3)) +''-'' + CAST(NLties as varchar(3)) NL,
REPLACE(LTRIM(REPLACE(CAST(CAST(NLWins as decimal) / (NLWins + NLlosses+ NLties) as decimal(4,3)), ''0'', '' '')), '' '', ''0'') AS NLPct
FROM TeamsWins
inner join ALWin
on ALWin.Team = TeamsWins.Team
inner join ALLoss
on ALLoss.Team = TeamsWins.Team
inner join ALTie
on ALTie.Team = TeamsWins.Team
inner join NLWin
on NLWin.Team = TeamsWins.Team
inner join NLLoss
on NLLoss.Team = TeamsWins.Team
inner join NLTie
on NLTie.Team = TeamsWins.Team
INNER JOIN
Teams
ON
TeamsWins.Team=Team_ID'
EXEC sp_executesql @DynamicPivotQuery
March 12, 2015 at 11:38 am
The errors I get when I use the variable are here. I don't get any errors when I remove the variable.
Msg 102, Level 15, State 1, Line 67
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 92
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 105
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 117
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 129
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 197
Incorrect syntax near 'o'.
March 12, 2015 at 12:05 pm
measterbro (3/12/2015)
The errors I get when I use the variable are here. I don't get any errors when I remove the variable.Msg 102, Level 15, State 1, Line 67
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 92
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 105
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 117
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 129
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 197
Incorrect syntax near 'o'.
Which variable? There are more than 1 in here.
I didn't pour over this with a fine toothed comb but I don't see why you need to use dynamic sql here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 12:08 pm
Have you evaluated the contents of your dynamic sql? I would do that too. without knowing the exact code you are trying to execute it is difficult to know what the problem might be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 12:16 pm
Why aren't you parametrizing your dynamic query?
Your column list for the pivot is wrong. You set it like this:
DECLARE @ColumnName2 NVARCHAR(MAX)='East,Central,West';
And use it in here:
PIVOT(
SUM(ALWins) FOR Division_Name IN (['+@ColumnName2 +'])
) p ),
That won't generate the correct set of columns. That's a single column named [East,Central,West].
I'm not sure if that would solve the problem, but you should correct this before going any further. You also have your columns hard coded in the string.
March 12, 2015 at 12:17 pm
The variable is "@ColumnName2".
I want to use it in the PIVOT statements so they can be run against any divisions without hard coding them as they are now (where you see "PIVOT(
SUM(xxx) FOR Division_Name IN ([East],[Central],[West])", the "([East],[Central],[West])" would be replaced by "@ColumnName2". Also in "SELECT Team, [East],[Central],[West]" in the same sections.
March 12, 2015 at 12:19 pm
Without the variable it gives me the correct results.
http://citydynasty.com/Opponent/Baseball/MLB/2012-Opponent3.cshtml
March 12, 2015 at 12:22 pm
Try this:
DECLARE @ColumnName2 NVARCHAR(MAX)='East,Central,West';
SET @ColumnName2 = REPLACE(@ColumnName2, ',', '],[');
SELECT @ColumnName2
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply