?
Solved

Transform Data Task Query won't allow parameter

Posted on 2004-08-19
12
Medium Priority
?
254 Views
Last Modified: 2008-02-01
The query is a bit long, but basically the where clause at the end is giving me problems, as soon as I replace the language id with a ? parameter it starts to complain that "Syntax Error or Access Violation". Anyone any ideas?

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Author: Taco Fleur (taco@coldfusionist.com)
-- Function: gets all surveys and their associated questions and their associated answers
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SET NOCOUNT ON

SELECT                  SURVEY.SURVEYID AS pkIDsurvey,
                  SURVEY.[NAME] AS surveyName,
                  LIFECYCLE.LIFECYCLE AS surveyLifeCycle,
                  SURVEYTYPE.[NAME] AS surveyType,
                  SURVEY.TITLE AS surveyTitle,
                  SURVEY.[DESCRIPTION] AS surveyDescription,
                  SURVEY.OBJECTIVES AS surveyObjective,
                  SURVEY.STATUSID AS pkIDSurveyStatus,
                  STATUS.[NAME] AS surveyStatus,
                  SURVEY.DIVISIONID AS pkIDdivision,
                  DIVISION.[NAME] AS divisonName,
                  QUESTION.QUESTIONID AS pkIDQuestion,
                  QUESTION.REQUIRED AS questionRequired,
                  SURVEY_QUESTION.DISPLAYORDER AS questionDisplayOrder,
                  CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
                  QUESTION_TYPE.NAME AS questionType,
                  QUESTION.MASTERQUESTIONDISPLAYORDER AS questionMasterDisplayOrder,
                  QUESTION_LABEL.LANGUAGEID AS pkIDQuestionLanguage,
                  LANGUAGE_.[NAME] AS questionLanguage,
                  QUESTION.SUFFIX AS questionSuffix,
                  ASSET.[NAME] AS questionAssetName,
                  ASSET.CODE AS questionAssetCode,
                  QUESTION.SEQUENCENUM AS questionSequence,
                  ANSWER.MATRIXSINGLEID AS pkIDAnswer, -- ANSWER MATRIX SINGLE
                  CAST(LABEL.LABEL AS NVARCHAR(4000)) AS answer,
                  ANSWER.REPORTINGVALUE AS answerValue,
                  ANSWER.RANDOMORDER AS answerRandomOrder,
                  ANSWER.DISPLAYORDER AS answerDisplayOrder,
                  NULL AS answerDefaultSelected,
                  ANSWER.IS_COLUMN_HEADER AS isColumnHeader,
                  CASE ANSWER.IS_COLUMN_HEADER
                        WHEN 1 THEN ANSWER.DISPLAYORDER
                        ELSE 0
                  END AS depth
FROM                  dbo.AIO_ASSET ASSET
INNER JOIN            dbo.WA_S_SURVEY SURVEY
INNER JOIN            dbo.AIO_DIVISION DIVISION
ON                   SURVEY.DIVISIONID = DIVISION.DIVISIONID
INNER JOIN            dbo.WA_S_QUESTION2SURVEY SURVEY_QUESTION
ON                   SURVEY.SURVEYID = SURVEY_QUESTION.SURVEYID
INNER JOIN            dbo.WA_S_QUESTION QUESTION
ON                   SURVEY_QUESTION.QUESTIONID = QUESTION.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL QUESTION_LABEL
ON                   QUESTION.QUESTIONID = QUESTION_LABEL.QUESTIONID
INNER JOIN            dbo.WA_S_QUESTIONTYPE QUESTION_TYPE
ON                   QUESTION.QUESTIONTYPEID = QUESTION_TYPE.QUESTIONTYPEID
INNER JOIN            dbo.WA_S_ANSWER_MATRIX_SINGLE ANSWER -- ANSWER MATRIX SINGLE
ON                   QUESTION.QUESTIONID = ANSWER.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL LABEL
ON                   ANSWER.MATRIXSINGLEID = LABEL.MATRIXSINGLEID -- ANSWER MATRIX SINGLE
INNER JOIN            dbo.WA_S_LANGUAGE LANGUAGE_
ON                   QUESTION_LABEL.LANGUAGEID = LANGUAGE_.LANGUAGEID
ON                   ASSET.ASSETID = QUESTION.ASSETID
INNER JOIN            dbo.WA_S_STATUS STATUS
ON                   SURVEY.STATUSID = STATUS.STATUSID
LEFT OUTER JOIN            dbo.AIO_LIFECYCLE LIFECYCLE
INNER JOIN            dbo.AIO_SURVEY2LIFECYCLE SURVEY2LIFECYCLE
ON                   LIFECYCLE.LIFECYCLEID = SURVEY2LIFECYCLE.LIFECYCLEID
ON                   SURVEY.SURVEYID = SURVEY2LIFECYCLE.SURVEYID
LEFT OUTER JOIN            dbo.WA_S_SURVEYTYPE SURVEYTYPE
ON                   SURVEY.SURVEYTYPEID = SURVEYTYPE.SURVEYTYPEID

UNION

SELECT                  SURVEY.SURVEYID AS pkIDsurvey,
                  SURVEY.[NAME] AS surveyName,
                  LIFECYCLE.LIFECYCLE AS surveyLifeCycle,
                  SURVEYTYPE.[NAME] AS surveyType,
                  SURVEY.TITLE AS surveyTitle,
                  SURVEY.[DESCRIPTION] AS surveyDescription,
                  SURVEY.OBJECTIVES AS surveyObjective,
                  SURVEY.STATUSID AS pkIDSurveyStatus,
                  STATUS.[NAME] AS surveyStatus,
                  SURVEY.DIVISIONID AS pkIDdivision,
                  DIVISION.[NAME] AS divisonName,
                  QUESTION.QUESTIONID AS pkIDQuestion,
                  QUESTION.REQUIRED AS questionRequired,
                  SURVEY_QUESTION.DISPLAYORDER AS questionDisplayOrder,
                  CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
                  QUESTION_TYPE.NAME AS questionType,
                  QUESTION.MASTERQUESTIONDISPLAYORDER AS questionMasterDisplayOrder,
                  QUESTION_LABEL.LANGUAGEID AS pkIDQuestionLanguage,
                  LANGUAGE_.[NAME] AS questionLanguage,
                  QUESTION.SUFFIX AS questionSuffix,
                  ASSET.[NAME] AS questionAssetName,
                  ASSET.CODE AS questionAssetCode,
                  QUESTION.SEQUENCENUM AS questionSequence,
                  ANSWER.MATRIXMULTIPLEID AS pkIDAnswer, -- ANSWER MATRIX MULTIPLE
                  CAST(LABEL.LABEL AS NVARCHAR(4000)) AS answer,
                  ANSWER.REPORTINGVALUE AS answerValue,
                  ANSWER.RANDOMORDER AS answerRandomOrder,
                  ANSWER.DISPLAYORDER AS answerDisplayOrder,
                  NULL AS answerDefaultSelected,
                  ANSWER.IS_COLUMN_HEADER AS isColumnHeader,
                  CASE ANSWER.IS_COLUMN_HEADER
                        WHEN 1 THEN ANSWER.DISPLAYORDER
                        ELSE 0
                  END AS depth
FROM                  dbo.AIO_ASSET ASSET
INNER JOIN            dbo.WA_S_SURVEY SURVEY
INNER JOIN            dbo.AIO_DIVISION DIVISION
ON                   SURVEY.DIVISIONID = DIVISION.DIVISIONID
INNER JOIN            dbo.WA_S_QUESTION2SURVEY SURVEY_QUESTION
ON                   SURVEY.SURVEYID = SURVEY_QUESTION.SURVEYID
INNER JOIN            dbo.WA_S_QUESTION QUESTION
ON                   SURVEY_QUESTION.QUESTIONID = QUESTION.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL QUESTION_LABEL
ON                   QUESTION.QUESTIONID = QUESTION_LABEL.QUESTIONID
INNER JOIN            dbo.WA_S_QUESTIONTYPE QUESTION_TYPE
ON                   QUESTION.QUESTIONTYPEID = QUESTION_TYPE.QUESTIONTYPEID
INNER JOIN            dbo.WA_S_ANSWER_MATRIX_MULTIPLE ANSWER -- ANSWER MATRIX MULTIPLE
ON                   QUESTION.QUESTIONID = ANSWER.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL LABEL
ON                   ANSWER.MATRIXMULTIPLEID = LABEL.MATRIXMULTIPLEID -- ANSWER MATRIX MULTIPLE
INNER JOIN            dbo.WA_S_LANGUAGE LANGUAGE_
ON                   QUESTION_LABEL.LANGUAGEID = LANGUAGE_.LANGUAGEID
ON                   ASSET.ASSETID = QUESTION.ASSETID
INNER JOIN            dbo.WA_S_STATUS STATUS
ON                   SURVEY.STATUSID = STATUS.STATUSID
LEFT OUTER JOIN            dbo.AIO_LIFECYCLE LIFECYCLE
INNER JOIN            dbo.AIO_SURVEY2LIFECYCLE SURVEY2LIFECYCLE
ON                   LIFECYCLE.LIFECYCLEID = SURVEY2LIFECYCLE.LIFECYCLEID
ON                   SURVEY.SURVEYID = SURVEY2LIFECYCLE.SURVEYID
LEFT OUTER JOIN            dbo.WA_S_SURVEYTYPE SURVEYTYPE
ON                   SURVEY.SURVEYTYPEID = SURVEYTYPE.SURVEYTYPEID

UNION

SELECT                  SURVEY.SURVEYID AS pkIDsurvey,
                  SURVEY.[NAME] AS surveyName,
                  LIFECYCLE.LIFECYCLE AS surveyLifeCycle,
                  SURVEYTYPE.[NAME] AS surveyType,
                  SURVEY.TITLE AS surveyTitle,
                  SURVEY.[DESCRIPTION] AS surveyDescription,
                  SURVEY.OBJECTIVES AS surveyObjective,
                  SURVEY.STATUSID AS pkIDSurveyStatus,
                  STATUS.[NAME] AS surveyStatus,
                  SURVEY.DIVISIONID AS pkIDdivision,
                  DIVISION.[NAME] AS divisonName,
                  QUESTION.QUESTIONID AS pkIDQuestion,
                  QUESTION.REQUIRED AS questionRequired,
                  SURVEY_QUESTION.DISPLAYORDER AS questionDisplayOrder,
                  CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
                  QUESTION_TYPE.NAME AS questionType,
                  QUESTION.MASTERQUESTIONDISPLAYORDER AS questionMasterDisplayOrder,
                  QUESTION_LABEL.LANGUAGEID AS pkIDQuestionLanguage,
                  LANGUAGE_.[NAME] AS questionLanguage,
                  QUESTION.SUFFIX AS questionSuffix,
                  ASSET.[NAME] AS questionAssetName,
                  ASSET.CODE AS questionAssetCode,
                  QUESTION.SEQUENCENUM AS questionSequence,
                  ANSWER.SELECTSINGLEID AS pkIDAnswer, -- ANSWER SELECT SINGLE
                  CAST(LABEL.LABEL AS NVARCHAR(4000)) AS answer,
                  ANSWER.REPORTINGVALUE AS answerValue,
                  ANSWER.RANDOMORDER AS answerRandomOrder,
                  ANSWER.DISPLAYORDER AS answerDisplayOrder,
                  ANSWER.DEFAULTSELECTED AS answerDefaultSelected,
                  0 AS isColumnHeader,
                  0 AS depth
FROM                  dbo.AIO_ASSET ASSET
INNER JOIN            dbo.WA_S_SURVEY SURVEY
INNER JOIN            dbo.AIO_DIVISION DIVISION
ON                   SURVEY.DIVISIONID = DIVISION.DIVISIONID
INNER JOIN            dbo.WA_S_QUESTION2SURVEY SURVEY_QUESTION
ON                   SURVEY.SURVEYID = SURVEY_QUESTION.SURVEYID
INNER JOIN            dbo.WA_S_QUESTION QUESTION
ON                   SURVEY_QUESTION.QUESTIONID = QUESTION.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL QUESTION_LABEL
ON                   QUESTION.QUESTIONID = QUESTION_LABEL.QUESTIONID
INNER JOIN            dbo.WA_S_QUESTIONTYPE QUESTION_TYPE
ON                   QUESTION.QUESTIONTYPEID = QUESTION_TYPE.QUESTIONTYPEID
INNER JOIN            dbo.WA_S_ANSWER_SELECTSINGLE ANSWER -- ANSWER SELECT SINGLE
ON                   QUESTION.QUESTIONID = ANSWER.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL LABEL
ON                   ANSWER.SELECTSINGLEID = LABEL.SELECTSINGLEID -- ANSWER SELECT SINGLE
INNER JOIN            dbo.WA_S_LANGUAGE LANGUAGE_
ON                   QUESTION_LABEL.LANGUAGEID = LANGUAGE_.LANGUAGEID
ON                   ASSET.ASSETID = QUESTION.ASSETID
INNER JOIN            dbo.WA_S_STATUS STATUS
ON                   SURVEY.STATUSID = STATUS.STATUSID
LEFT OUTER JOIN            dbo.AIO_LIFECYCLE LIFECYCLE
INNER JOIN            dbo.AIO_SURVEY2LIFECYCLE SURVEY2LIFECYCLE
ON                   LIFECYCLE.LIFECYCLEID = SURVEY2LIFECYCLE.LIFECYCLEID
ON                   SURVEY.SURVEYID = SURVEY2LIFECYCLE.SURVEYID
LEFT OUTER JOIN            dbo.WA_S_SURVEYTYPE SURVEYTYPE
ON                   SURVEY.SURVEYTYPEID = SURVEYTYPE.SURVEYTYPEID

UNION

SELECT                  SURVEY.SURVEYID AS pkIDsurvey,
                  SURVEY.[NAME] AS surveyName,
                  LIFECYCLE.LIFECYCLE AS surveyLifeCycle,
                  SURVEYTYPE.[NAME] AS surveyType,
                  SURVEY.TITLE AS surveyTitle,
                  SURVEY.[DESCRIPTION] AS surveyDescription,
                  SURVEY.OBJECTIVES AS surveyObjective,
                  SURVEY.STATUSID AS pkIDSurveyStatus,
                  STATUS.[NAME] AS surveyStatus,
                  SURVEY.DIVISIONID AS pkIDdivision,
                  DIVISION.[NAME] AS divisonName,
                  QUESTION.QUESTIONID AS pkIDQuestion,
                  QUESTION.REQUIRED AS questionRequired,
                  SURVEY_QUESTION.DISPLAYORDER AS questionDisplayOrder,
                  CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
                  QUESTION_TYPE.NAME AS questionType,
                  QUESTION.MASTERQUESTIONDISPLAYORDER AS questionMasterDisplayOrder,
                  QUESTION_LABEL.LANGUAGEID AS pkIDQuestionLanguage,
                  LANGUAGE_.[NAME] AS questionLanguage,
                  QUESTION.SUFFIX AS questionSuffix,
                  ASSET.[NAME] AS questionAssetName,
                  ASSET.CODE AS questionAssetCode,
                  QUESTION.SEQUENCENUM AS questionSequence,
                  ANSWER.SELECTMULTIPLEID AS pkIDAnswer, -- ANSWER SELECT MULTIPLE
                  CAST(LABEL.LABEL AS NVARCHAR(4000)) AS answer,
                  ANSWER.REPORTINGVALUE AS answerValue,
                  ANSWER.RANDOMORDER AS answerRandomOrder,
                  ANSWER.DISPLAYORDER AS answerDisplayOrder,
                  ANSWER.DEFAULTSELECTED AS answerDefaultSelected,
                  0 AS isColumnHeader,
                  0 AS depth
FROM                  dbo.AIO_ASSET ASSET
INNER JOIN            dbo.WA_S_SURVEY SURVEY
INNER JOIN            dbo.AIO_DIVISION DIVISION
ON                   SURVEY.DIVISIONID = DIVISION.DIVISIONID
INNER JOIN            dbo.WA_S_QUESTION2SURVEY SURVEY_QUESTION
ON                   SURVEY.SURVEYID = SURVEY_QUESTION.SURVEYID
INNER JOIN            dbo.WA_S_QUESTION QUESTION
ON                   SURVEY_QUESTION.QUESTIONID = QUESTION.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL QUESTION_LABEL
ON                   QUESTION.QUESTIONID = QUESTION_LABEL.QUESTIONID
INNER JOIN            dbo.WA_S_QUESTIONTYPE QUESTION_TYPE
ON                   QUESTION.QUESTIONTYPEID = QUESTION_TYPE.QUESTIONTYPEID
INNER JOIN            dbo.WA_S_ANSWER_SELECTMULTIPLE ANSWER -- ANSWER SELECT MULTIPLE
ON                   QUESTION.QUESTIONID = ANSWER.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL LABEL
ON                   ANSWER.SELECTMULTIPLEID = LABEL.SELECTMULTIPLEID -- ANSWER SELECT MULTIPLE
INNER JOIN            dbo.WA_S_LANGUAGE LANGUAGE_
ON                   QUESTION_LABEL.LANGUAGEID = LANGUAGE_.LANGUAGEID
ON                   ASSET.ASSETID = QUESTION.ASSETID
INNER JOIN            dbo.WA_S_STATUS STATUS
ON                   SURVEY.STATUSID = STATUS.STATUSID
LEFT OUTER JOIN            dbo.AIO_LIFECYCLE LIFECYCLE
INNER JOIN            dbo.AIO_SURVEY2LIFECYCLE SURVEY2LIFECYCLE
ON                   LIFECYCLE.LIFECYCLEID = SURVEY2LIFECYCLE.LIFECYCLEID
ON                   SURVEY.SURVEYID = SURVEY2LIFECYCLE.SURVEYID
LEFT OUTER JOIN            dbo.WA_S_SURVEYTYPE SURVEYTYPE
ON                   SURVEY.SURVEYTYPEID = SURVEYTYPE.SURVEYTYPEID

UNION

SELECT                  SURVEY.SURVEYID AS pkIDsurvey,
                  SURVEY.[NAME] AS surveyName,
                  LIFECYCLE.LIFECYCLE AS surveyLifeCycle,
                  SURVEYTYPE.[NAME] AS surveyType,
                  SURVEY.TITLE AS surveyTitle,
                  SURVEY.[DESCRIPTION] AS surveyDescription,
                  SURVEY.OBJECTIVES AS surveyObjective,
                  SURVEY.STATUSID AS pkIDSurveyStatus,
                  STATUS.[NAME] AS surveyStatus,
                  SURVEY.DIVISIONID AS pkIDdivision,
                  DIVISION.[NAME] AS divisonName,
                  QUESTION.QUESTIONID AS pkIDQuestion,
                  QUESTION.REQUIRED AS questionRequired,
                  SURVEY_QUESTION.DISPLAYORDER AS questionDisplayOrder,
                  CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
                  QUESTION_TYPE.NAME AS questionType,
                  QUESTION.MASTERQUESTIONDISPLAYORDER AS questionMasterDisplayOrder,
                  QUESTION_LABEL.LANGUAGEID AS pkIDQuestionLanguage,
                  LANGUAGE_.[NAME] AS questionLanguage,
                  QUESTION.SUFFIX AS questionSuffix,
                  ASSET.[NAME] AS questionAssetName,
                  ASSET.CODE AS questionAssetCode,
                  QUESTION.SEQUENCENUM AS questionSequence,
                  ANSWER.RATINGID AS pkIDAnswer, -- ANSWER RATING
                  CAST(LABEL.LABEL AS NVARCHAR(4000)) AS answer,
                  ANSWER.REPORTINGVALUE AS answerValue,
                  ANSWER.RANDOMORDER AS answerRandomOrder,
                  ANSWER.DISPLAYORDER AS answerDisplayOrder,
                  ANSWER.DEFAULTSELECTED AS answerDefaultSelected,
                  0 AS isColumnHeader,
                  0 AS depth
FROM                  dbo.AIO_ASSET ASSET
INNER JOIN            dbo.WA_S_SURVEY SURVEY
INNER JOIN            dbo.AIO_DIVISION DIVISION
ON                   SURVEY.DIVISIONID = DIVISION.DIVISIONID
INNER JOIN            dbo.WA_S_QUESTION2SURVEY SURVEY_QUESTION
ON                   SURVEY.SURVEYID = SURVEY_QUESTION.SURVEYID
INNER JOIN            dbo.WA_S_QUESTION QUESTION
ON                   SURVEY_QUESTION.QUESTIONID = QUESTION.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL QUESTION_LABEL
ON                   QUESTION.QUESTIONID = QUESTION_LABEL.QUESTIONID
INNER JOIN            dbo.WA_S_QUESTIONTYPE QUESTION_TYPE
ON                   QUESTION.QUESTIONTYPEID = QUESTION_TYPE.QUESTIONTYPEID
INNER JOIN            dbo.WA_S_ANSWER_RATING ANSWER -- ANSWER RATING
ON                   QUESTION.QUESTIONID = ANSWER.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL LABEL
ON                   ANSWER.RATINGID = LABEL.RATINGID -- ANSWER RATING
INNER JOIN            dbo.WA_S_LANGUAGE LANGUAGE_
ON                   QUESTION_LABEL.LANGUAGEID = LANGUAGE_.LANGUAGEID
ON                   ASSET.ASSETID = QUESTION.ASSETID
INNER JOIN            dbo.WA_S_STATUS STATUS
ON                   SURVEY.STATUSID = STATUS.STATUSID
LEFT OUTER JOIN            dbo.AIO_LIFECYCLE LIFECYCLE
INNER JOIN            dbo.AIO_SURVEY2LIFECYCLE SURVEY2LIFECYCLE
ON                   LIFECYCLE.LIFECYCLEID = SURVEY2LIFECYCLE.LIFECYCLEID
ON                   SURVEY.SURVEYID = SURVEY2LIFECYCLE.SURVEYID
LEFT OUTER JOIN            dbo.WA_S_SURVEYTYPE SURVEYTYPE
ON                   SURVEY.SURVEYTYPEID = SURVEYTYPE.SURVEYTYPEID

UNION

SELECT                  SURVEY.SURVEYID AS pkIDsurvey,
                  SURVEY.[NAME] AS surveyName,
                  LIFECYCLE.LIFECYCLE AS surveyLifeCycle,
                  SURVEYTYPE.[NAME] AS surveyType,
                  SURVEY.TITLE AS surveyTitle,
                  SURVEY.[DESCRIPTION] AS surveyDescription,
                  SURVEY.OBJECTIVES AS surveyObjective,
                  SURVEY.STATUSID AS pkIDSurveyStatus,
                  STATUS.[NAME] AS surveyStatus,
                  SURVEY.DIVISIONID AS pkIDdivision,
                  DIVISION.[NAME] AS divisonName,
                  QUESTION.QUESTIONID AS pkIDQuestion,
                  QUESTION.REQUIRED AS questionRequired,
                  SURVEY_QUESTION.DISPLAYORDER AS questionDisplayOrder,
                  CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
                  QUESTION_TYPE.NAME AS questionType,
                  QUESTION.MASTERQUESTIONDISPLAYORDER AS questionMasterDisplayOrder,
                  QUESTION_LABEL.LANGUAGEID AS pkIDQuestionLanguage,
                  LANGUAGE_.[NAME] AS questionLanguage,
                  QUESTION.SUFFIX AS questionSuffix,
                  ASSET.[NAME] AS questionAssetName,
                  ASSET.CODE AS questionAssetCode,
                  QUESTION.SEQUENCENUM AS questionSequence,
                  ANSWER.RANKORDERID AS pkIDAnswer, -- ANSWER RANK ORDER
                  CAST(LABEL.LABEL AS NVARCHAR(4000)) AS answer,
                  ANSWER.REPORTINGVALUE AS answerValue,
                  ANSWER.RANDOMORDER AS answerRandomOrder,
                  ANSWER.DISPLAYORDER AS answerDisplayOrder,
                  ANSWER.DEFAULTSELECTED AS answerDefaultSelected,
                  0 AS isColumnHeader,
                  0 AS depth
FROM                  dbo.AIO_ASSET ASSET
INNER JOIN            dbo.WA_S_SURVEY SURVEY
INNER JOIN            dbo.AIO_DIVISION DIVISION
ON                   SURVEY.DIVISIONID = DIVISION.DIVISIONID
INNER JOIN            dbo.WA_S_QUESTION2SURVEY SURVEY_QUESTION
ON                   SURVEY.SURVEYID = SURVEY_QUESTION.SURVEYID
INNER JOIN            dbo.WA_S_QUESTION QUESTION
ON                   SURVEY_QUESTION.QUESTIONID = QUESTION.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL QUESTION_LABEL
ON                   QUESTION.QUESTIONID = QUESTION_LABEL.QUESTIONID
INNER JOIN            dbo.WA_S_QUESTIONTYPE QUESTION_TYPE
ON                   QUESTION.QUESTIONTYPEID = QUESTION_TYPE.QUESTIONTYPEID
INNER JOIN            dbo.WA_S_ANSWER_RANK_ORDER ANSWER -- ANSWER RANK ORDER
ON                   QUESTION.QUESTIONID = ANSWER.QUESTIONID
INNER JOIN            dbo.WA_S_LABEL LABEL
ON                   ANSWER.RANKORDERID = LABEL.RANKORDERID -- ANSWER RANK ORDER
INNER JOIN            dbo.WA_S_LANGUAGE LANGUAGE_
ON                   QUESTION_LABEL.LANGUAGEID = LANGUAGE_.LANGUAGEID
ON                   ASSET.ASSETID = QUESTION.ASSETID
INNER JOIN            dbo.WA_S_STATUS STATUS
ON                   SURVEY.STATUSID = STATUS.STATUSID
LEFT OUTER JOIN            dbo.AIO_LIFECYCLE LIFECYCLE
INNER JOIN            dbo.AIO_SURVEY2LIFECYCLE SURVEY2LIFECYCLE
ON                   LIFECYCLE.LIFECYCLEID = SURVEY2LIFECYCLE.LIFECYCLEID
ON                   SURVEY.SURVEYID = SURVEY2LIFECYCLE.SURVEYID
LEFT OUTER JOIN            dbo.WA_S_SURVEYTYPE SURVEYTYPE
ON                   SURVEY.SURVEYTYPEID = SURVEYTYPE.SURVEYTYPEID

WHERE                  (QUESTION_LABEL.LANGUAGEID = ?)

ORDER BY             pkIDSurvey, pkIDQuestion, questionDisplayOrder, answerDisplayOrder, depth
0
Comment
Question by:Tacobell777
  • 8
  • 4
12 Comments
 
LVL 17

Author Comment

by:Tacobell777
ID: 11848238
PS. this is in an Data Transformation Task
0
 
LVL 34

Accepted Solution

by:
arbert earned 1000 total points
ID: 11848670
I noted this in a question you posted just yesterday--there is a "parse bug" in DTS:

 http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20909916.html

Basically, you enter a "good" statement that will allow you to choose your parameter, then you paste the "bad" statement back.

So, you could do something like

select * from question_label where languageid=?

Select your parm, and then paste your above query back in....

it sucks I know, but that's the way it works right now...
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 11856283
you did to.. ooh well at least now you get some points for it ;-)
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 17

Author Comment

by:Tacobell777
ID: 11888863
OK, I tried this, and it won't work, what am I doing wrong?

I remove the big sql statement, replace it with something like SELECT * from table where 1 = ?
then I select the param, save it and close it. Open it and paste the above statement in it with the ?, but it keeps giving me the error...
0
 
LVL 34

Expert Comment

by:arbert
ID: 11888927
what's the error?  The parse error?  you will always get the parse error, but as long as you know the sql statement is valid and your ? match the parms you selected 1 for 1, it should work fine....
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 11888997
I am going nuts, I even tried
EXEC [dbo].[spSurveyExport]  ?

I have put the SQL Statement in a stored procedure, but no luck...
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 11889008
This is what I get
error source: microsoft ole db provider for sql server
error description: invalid parameter number
and it wont allow me to go any further
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 11889023
hmm, might be getting somewhere now... why, I don't know..
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 11889080
yup, it works now with the EXEC [dbo].[spSurveyExport]  ?
why it did not before I don't know.

thanks..
0
 
LVL 34

Expert Comment

by:arbert
ID: 11889148
ya, invalid parameter number error usually means that you have a mismatch between the number of selected parms and the number of ? in the exec statement.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11889151
(on the bright side, DTS in Yukon will blow your mind)....
0
 
LVL 17

Author Comment

by:Tacobell777
ID: 11889846
I thought that was what it meant, funny thing is though, there was only 1 parameter ;-)) can't really mess that up..
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question