Solved

Transform Data Task Query won't allow parameter

Posted on 2004-08-19
12
222 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 250 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 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