January 23, 2013 at 1:43 pm
I have the following and is getting the attached error. I am trying to loop through a table and change the rows to field names. I do not know in advance hows many rows will be returned for each ID (using a for each loop container to pass the id to this sql task). I am getting the attached error when I try to execute the @query . 8175 in the query is replace with the ? is the SSIS package
Any assistance will be greatly appreciated as I know very little SSIS
DECLARE @cols NVARCHAR(MAX)
SELECT @cols = isnull(@cols + ',', '') + '[' + Col_Name + ']'
FROM (SELECT DISTINCT top 5 dbo.tblQuestions.Question AS Col_Name
FROM dbo.tblQuestions INNER JOIN
dbo.tblResponses ON dbo.tblQuestions.ID = dbo.CSAT_tblResponses.Question_ID
WHERE (dbo.tblResponses.Survey_ID = 8175)) as tb1
ORDER BY Col_Name
DECLARE @query NVARCHAR(max)
SET @query = N'SELECT dbo.tblResponses.Question_ID, dbo.tblQuestions.Question, ' + @cols + ' FROM
(SELECT t1.Col_Name, dbo.tblResponses.Question_ID,
t2.dbo.tblQuestions.Question,
FROM dbo.tblQuestions AS t1 Inner
JOIN dbo.tblResponses AS t2 ON t1.dbo.tblQuestions.ID = t2.dbo.tblResponses.Question_ID
) as p
PIVOT
(
max(dbo.tblQuestions.Question)
FOR Col_Name IN
( '+ @cols +' )
) AS pvt '
execute @query
-------------error-----------
Msg 203, Level 16, State 2, Line 23
The name 'SELECT dbo.tblResponses.Question_ID, dbo.tblQuestions.Question, [Company_Name],[Customer_Name],[Response Time],[Survey ID ],[Survey Response ID] FROM
(SELECT t1.Col_Name, dbo.tblResponses.Question_ID,
t2.dbo.tblQuestions.Question,
FROM dbo.tblQuestions AS t1 Inner
JOIN dbo.tblResponses AS t2 ON t1.dbo.tblQuestions.ID = t2.dbo.tblResponses.Question_ID
) as p
PIVOT
(
max(dbo.tblQuestions.Question)
FOR Col_Name IN
( [Company_Name],[Customer_Name],[Response Time],[Survey ID ],[Survey Response ID] )
) AS pvt ' is not a valid identifier.
January 24, 2013 at 6:47 am
Can you run your generated SQL in SSMS? It looks like there is a problem with the SQL from the error.
HTH,
Rob
January 24, 2013 at 8:45 am
I did run the SQL in SSMS and got the error above. I am sorry for not including that in the post. I am trying to determine what I am missing in the script why it is erroring in SSMS. ( I am using that script in SSIS after testing in SSMS.)
January 25, 2013 at 6:21 am
You can't use the T-SQL in SSIS until you get it working in SSMS (as I'm sure you know). I don't have the DDL for your tables, but double click on the 'Msg 203, Level 16, State 2, Line 23' error message (in red). This will move your cursor to the offending T-SQL line in SSMS.
HTH,
Rob
January 26, 2013 at 11:55 am
@crbnldy, if you provide some DDL so to create your tables, some DML so we can create some test data and the shape of the expected resultset I am confident we can help you out.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 28, 2013 at 4:10 am
I echo others' comments about DDL ...
But also noticed that your SQL has a spurious comma between
't2.dbo.tblQuestions.Question'
and 'FROM'
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply