Tacobell777
asked on
Transform Data Task Query won't allow parameter
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_MATRIX_SIN
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_MATRIX_MUL
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_SELECTSING
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_SELECTMULT
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_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.LIFECYCLE
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
--------------------------
-- 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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_MATRIX_SIN
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_MATRIX_MUL
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_SELECTSING
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_ANSWER_SELECTMULT
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
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.LIFECYCLE
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.DISPLAYORD
CAST(QUESTION_LABEL.LABEL AS NVARCHAR(4000)) AS questionLabel,
QUESTION_TYPE.NAME AS questionType,
QUESTION.MASTERQUESTIONDIS
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
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.QUESTIONTYPE
INNER JOIN dbo.WA_S_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.LIFECYCLE
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
you did to.. ooh well at least now you get some points for it ;-)
ASKER
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...
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...
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....
ASKER
I am going nuts, I even tried
EXEC [dbo].[spSurveyExport] ?
I have put the SQL Statement in a stored procedure, but no luck...
EXEC [dbo].[spSurveyExport] ?
I have put the SQL Statement in a stored procedure, but no luck...
ASKER
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
error source: microsoft ole db provider for sql server
error description: invalid parameter number
and it wont allow me to go any further
ASKER
hmm, might be getting somewhere now... why, I don't know..
ASKER
yup, it works now with the EXEC [dbo].[spSurveyExport] ?
why it did not before I don't know.
thanks..
why it did not before I don't know.
thanks..
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.
(on the bright side, DTS in Yukon will blow your mind)....
ASKER
I thought that was what it meant, funny thing is though, there was only 1 parameter ;-)) can't really mess that up..
ASKER