March 29, 2016 at 7:59 am
Hi i am getting error when i am running this query.
Msg 102, Level 15, State 1, Line 7
March 29, 2016 at 8:02 am
what's the query?
- Damian
March 29, 2016 at 8:03 am
and what's the error message?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2016 at 8:06 am
There are 1400 record into KLA_steps table if i ran for 10 it works bit 1400 it dont work
DECLARE
@v_sql AS NVARCHAR (MAX) = '' ,
@v_statusdec_in_sql AS NVARCHAR (MAX) = '',
@v_statusdec_pivot_in_sql AS NVARCHAR (MAX) = '',
@v_statusdec AS NVARCHAR (MAX) = ''
DECLARE Status_cursor CURSOR FOR
SELECT StepDesc FROM KLA_Steps
WHERE summarystep = 0
OPEN Status_cursor;
-- Perform the first fetch.
FETCH NEXT FROM Status_cursor INTO @v_statusdec ;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
IF @v_statusdec_in_sql = ''
BEGIN
SET @v_statusdec_in_sql = N'''' + @v_statusdec + N''''
SET @v_statusdec_pivot_in_sql = N'[' + @v_statusdec + N']'
END
ELSE
BEGIN
SET @v_statusdec_in_sql = @v_statusdec_in_sql + ',' + N'''' + @v_statusdec + N''''
SET @v_statusdec_pivot_in_sql = @v_statusdec_pivot_in_sql + ',' + N'[' + @v_statusdec + N']'
END
FETCH NEXT FROM Status_cursor INTO @v_statusdec ;
END
CLOSE Status_cursor;
DEALLOCATE Status_cursor;
--PRINT @v_statusdec_in_sql;
--PRINT @v_statusdec_pivot_in_sql;
BEGIN
SET @v_sql = N'SELECT * FROM
( SELECT a.AssigneeID, b.ProjectID, c.StepID,d.StepDesc, d.statusid FROM KLA_ProjectAssigneeXRef a INNER JOIN
KLA_PROJECT b ON a.projectid = b.projectid INNER JOIN
KLA_ProjectStep c ON b.projectid = c.projectid INNER JOIN
KLA_Steps d ON c.stepid = d.stepid
WHERE d.summarystep = 0
AND d.StepDesc IN (' + @v_statusdec_in_sql
SET @v_sql = @v_sql + N') Group by a.AssigneeID, b.ProjectID,b.ProjectID, c.StepID,d.StepDesc, d.statusid ) AS SOURCE_TABLE
PIVOT (MAX(statusid)
FOR StepDesc IN (' + @v_statusdec_pivot_in_sql
SET @v_sql = @v_sql + N' ) ) AS PivotProject'
PRINT @v_sql;
EXECUTE sp_executesql @v_sql ;
END
Error is
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'Tax'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ','.
Msg 132, Level 15, State 1, Line 7
The label 'Incomplete' has already been declared. Label names must be unique within a query batch or stored procedure.
March 29, 2016 at 8:10 am
And what are the results from this?
PRINT @v_sql;
March 29, 2016 at 8:17 am
SELECT * FROM
( SELECT a.AssigneeID, b.ProjectID, c.StepID,d.StepDesc, d.statusid FROM KLA_ProjectAssigneeXRef a INNER JOIN
KLA_PROJECT b ON a.projectid = b.projectid INNER JOIN
KLA_ProjectStep c ON b.projectid = c.projectid INNER JOIN
KLA_Steps d ON c.stepid = d.stepid
WHERE d.summarystep = 0
AND d.StepDesc IN ('1 Hour Annual Tax Advis
like total of all of 3987 char
March 29, 2016 at 8:28 am
Can you try running it with simpler metadata in the table it's building the dynamic SQL from? If possible only 1 return row, that should make it easier to trouble shoot the generated code.
March 29, 2016 at 8:41 am
It work when i have 10 record in my KLA_Steps there are 1400 records there where it get fail. I think it is length giving problem but i have declare as NVARCHAR(MAX) i don’t know if dynamic SQL hold more than 4000 character.
March 29, 2016 at 8:55 am
Here is your query with some formatting so it is legible.
DECLARE @v_sql AS NVARCHAR (MAX) = '',
@v_statusdec_in_sql AS NVARCHAR (MAX) = '',
@v_statusdec_pivot_in_sql AS NVARCHAR (MAX) = '',
@v_statusdec AS NVARCHAR (MAX) = ''
DECLARE Status_cursor CURSOR FOR
SELECT StepDesc
FROM KLA_Steps
WHERE summarystep = 0
OPEN Status_cursor;
-- Perform the first fetch.
FETCH NEXT FROM Status_cursor INTO @v_statusdec;
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
IF @v_statusdec_in_sql = ''
BEGIN
SET @v_statusdec_in_sql = N'''' + @v_statusdec + N''''
SET @v_statusdec_pivot_in_sql = N'[' + @v_statusdec + N']'
END
ELSE
BEGIN
SET @v_statusdec_in_sql = @v_statusdec_in_sql + ',' + N'''' + @v_statusdec
+ N''''
SET @v_statusdec_pivot_in_sql = @v_statusdec_pivot_in_sql + ',' + N'['
+ @v_statusdec + N']'
END
FETCH NEXT FROM Status_cursor INTO @v_statusdec;
END
CLOSE Status_cursor;
DEALLOCATE Status_cursor;
--PRINT @v_statusdec_in_sql;
--PRINT @v_statusdec_pivot_in_sql;
BEGIN
SET @v_sql = N'SELECT * FROM
( SELECT a.AssigneeID, b.ProjectID, c.StepID,d.StepDesc, d.statusid FROM KLA_ProjectAssigneeXRef a INNER JOIN
KLA_PROJECT b ON a.projectid = b.projectid INNER JOIN
KLA_ProjectStep c ON b.projectid = c.projectid INNER JOIN
KLA_Steps d ON c.stepid = d.stepid
WHERE d.summarystep = 0
AND d.StepDesc IN (' + @v_statusdec_in_sql
SET @v_sql = @v_sql + N') Group by a.AssigneeID, b.ProjectID,b.ProjectID, c.StepID,d.StepDesc, d.statusid ) AS SOURCE_TABLE
PIVOT (MAX(statusid)
FOR StepDesc IN ('
+ @v_statusdec_pivot_in_sql
SET @v_sql = @v_sql + N' ) ) AS PivotProject'
PRINT @v_sql;
EXECUTE Sp_executesql
@v_sql;
END
I would suggest using a dynamic crosstab instead of a dynamic pivot. The syntax is far less obtuse and it will even perform slightly better most of the time. Take a look at the links in my signature about converting rows to columns. You will need the dynamic version.
_______________________________________________________________
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 29, 2016 at 8:58 am
sp_executesql should be able to handle a NVARCHAR(MAX) so that shouldn't be the issue, it's likely one(or more) of the values you're trying to use to generate the sql has something in it that's breaking the SQL syntax since it works on a smaller result set.
You can try to find the offending record and either fix it or scrub the data before generating the SQL, or you could try to parameterize the input but you might start running into limitations there as well just based on the number of parameters you would be need.
March 29, 2016 at 9:07 am
A couple of things to consider.
1) Change the PRINT @<sql_variable> to SELECT @<sql_variable>, since SELECT can display many more chars than SELECT.
2) Explicitly cast the unicode literal in the SET statement(s) to prevent SQL from truncating to 4000 bytes. For example:
SET @v_sql = CAST(N'SELECT * FROM
( SELECT a.AssigneeID, b.ProjectID, c.StepID,d.StepDesc, d.statusid FROM KLA_ProjectAssigneeXRef a INNER JOIN
KLA_PROJECT b ON a.projectid = b.projectid INNER JOIN
KLA_ProjectStep c ON b.projectid = c.projectid INNER JOIN
KLA_Steps d ON c.stepid = d.stepid
WHERE d.summarystep = 0
AND d.StepDesc IN (' AS nvarchar(MAX)) + @v_statusdec_in_sql
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 29, 2016 at 1:37 pm
Thanks but i still have issue after 43667 char.
March 29, 2016 at 7:45 pm
1. Restore PRINT commands for "in sql" variables and see what do they return.
Look especially for single quotes or square brackets.
2. Get rid of the cursor and replace it with proper concatenation:
SELECT v_statusdec_in_sql = STUFF((
SELECT N',' + QUOTENAME(StepDesc, '''')
FROM KLA_Steps
WHERE summarystep = 0
FOR XML PATH ('')
), 1,1,'')
SELECT @v_statusdec_pivot_in_sql = STUFF((
SELECT N',' + QUOTENAME(StepDesc)
FROM KLA_Steps
WHERE summarystep = 0
FOR XML PATH ('')
), 1,1,'')
_____________
Code for TallyGenerator
March 29, 2016 at 7:47 pm
sks_989 (3/29/2016)
Thanks but i still have issue after 43667 char.
3. 43667 char is way beyond 8000 bytes limitation.
What would be the character 43668?
_____________
Code for TallyGenerator
March 29, 2016 at 11:08 pm
Check out the following article hope this will help you: http://www.sqlmdfviewer.org/fix-error-sql-syntax.html
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply