Solved

Transform Data Task Query won't allow parameter

Posted on 2004-08-19
12
227 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 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

690 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