Solved

Transform Data Task Query won't allow parameter

Posted on 2004-08-19
12
219 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now