?
Solved

Transform Data Task Query won't allow parameter

Posted on 2004-08-19
12
Medium Priority
?
233 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

777 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