SQL Server DTS: DTSGlobalVariables visibility to Exectue SQL Task using question mark (?) and click Parameters... method?  Syntax error or accee violation / An error occured while parsing the SQL

Posted on 2011-10-29
Last Modified: 2013-11-30
I have transformations in my DTS package that successfully use the Question Mark (?) parameter mapping feature.  For example I have multiple Transformations that include WHERE clauses such as the following in the SQL query portion of the Source tab:
WHERE ProjectTransactions.TransDate >= ?

All I do to connect the question mark (?) to one of the DTSGlobalParameters is click on the [Parameters...] button and I am able to select and assign from an appropriate DTSGlobalParameters.  Also executing the DTS package meets all expectations.

But within the same DTS package I have been unable to use the Question Mark (?) / Parameters... feature for any Execute SQL Tasks.  For example, the following  meets all expectations.  Lets' call this Test Case 1...

--Test Case 1
EXEC spAddEtlJobHistory 'TEST'

When I execute the DTS package the stored proc is called successfully and results in expected results.

But if I (and I'll call this Test Case 2) ...
1.   Change to...EXEC spAddEtlJobHistory ?
2.   Click [Parameters...]
3.   I immediately get the following two message prompts one after the other...

Prompt 1
Title:Package Error
     Error Source: Microsoft OLE DB Provider for SQL Server
     Error Description: Syntax error or access violation

Prompt 2
Title: Parsing SQL Statement
Message: An error occured while parsing the SQL statement for parameters.  Please check the syntax of the SQL statement and verify that it is valid for the connection selected

BTW, I've already tried the following test cases (Test Case 3 and Test Case 4) and they raise the same error prompts as Test Case 2 above...

--Test Case 3
SET @MyVar = ?
EXEC spAddEtlJobHistory @MyVar

--Test Case 4
SELECT @MyVar = ?
EXEC spAddEtlJobHistory @MyVar

BTW, I already tried creating an entirely new DTS package with one Connection, one DTSGlobalVariable, and one  Execute SQL Task with the following SQL:

--Test Case 5
SET @MyVar = ?

...but I get the same error prompts.

And I also did the same logged in as a sysadmin and I get the same error prompts (I'll call this Test Case 6).

And to be thorough, yes, there was always at least one DTSGlobalVariable to pick from in all Test Cases.  

Also if I leave out any question mark (?) as in Test Case 1 and click [Parameters..]  I get the expected prompt..
Prompt 3
Title: Parsing SQL Statement
Message: The SQL statement does not contain any parameters.

Question by:GronkeyKong
    LVL 15

    Expert Comment

    hi dear

    Lets keep  test cases ( Test Case 3 and Test Case 4) on a side because they wont work anyway

    i tried your test case 1 and case 2 and it did work without any errors

    i thing most propably your error is because of your connection.

    what is you Execute SQL task connected to is it to SQL directly or are you using ODBC or what.

    Check the image below see i am using a simple connection with an Execute SQL task and i wrote the script

    Exec dbname..Spname ?

    and then clicked on parameters and the screen appeared normally and i chose my variable and then clicked on parse query and didnt get any error

    So i guess look into your connection

    or start a new DTS package and do these simple steps and try
    LVL 75

    Expert Comment

    by:Anthony Perkins
    This is what I stated in your previous thread on the subject:
    First make sure that the Stored Procedure compiles and runs from SSMS.
    Next make sure the correct server/database is selected in the DTS package.
    Finally make sure you can execute the Stored Procedure with the same DTS user.  In other words if they are not the database owner than at least you have done:
    GRANT EXECUTE ON usp_YourStoredProcedure TO YourDTSUser
    LVL 1

    Author Comment

    I get the same error prompts when I do what you did and create an entirely new DTS package with the bare minimum for testing.

    Stored Procedure compiles and runs from SSMS.  Check.
    The correct server/database is selected in the DTS package.  Check.
    I have two logins that can both execute the stored proc and execute DTS packages.  One login is a dbo of the database referenced in the connection, the other login is a sysadmin on the server.  Check.
    I added a DTSGlobalParameter via the Package Properties interface.  Check.
    DTS package executes successfully (executes the stored proc) when I use...
         EXEC spAddEtlJobHistory 'TESTTHIS2'

         ...instead of ...
         EXEC spAddEtlJobHistory ?


    What's going to on here?  This is so frustrating.  I don't want to resort to VBScript everytime I need a DTSGLobalParameter to be visible to an Execute SQL Task.


    I've attached as Code the stored proc I'm using here.  BTW, stored proc is not a result set returning stored proc.  It just inserts records into two tables and does a RETURN 0.  Also, you'll notice that there are two parameters.  I wasn't listing the other parameter for simplicity's sake.  But for the record the DTS job executes the following Execute SQL Task successfully...
    --Test Case 11
    SET @Now = GETDATE()
    EXEC spAddEtlJobHistory @Now, 'Test'

    But I get those God blessed be darn error prompts as soon as hit hit the [Parameters...] button (after replacing 'Test' with question mark ? of course)...
    --Test Case 12
    SET @Now = GETDATE()
    EXEC spAddEtlJobHistory @Now, ?

    CREATE PROCEDURE [dbo].[spAddEtlJobHistory] 
    	  @EtlJobStart		DATETIME
    	, @MachineName		VARCHAR(63)
    	/*	Full Name:			
    		Purpose primary:	Adds an a row to tblEtlJobHistory
    		                  and multiple rows to tblEtlJobHistoryDetails			  
        DECLARE @EtlJobHistoryID AS INTEGER
    --Add ETL Job History SUCCESS row
      INSERT INTO [AGRC_CSI].[dbo].[tblEtlJobHistory](
        , [EtlJobFinish]
        , [EtlJobOutcome]
        , [EtlJobRanAsLogin]
        , [EtlJobExectedFromMachine]
            @EtlJobStart --(SELECT EtlJobStart FROM ##AGRC_CSI__Temp_01)
          , GETDATE()
          , 'SUCCESS'
          , SUSER_SNAME()
          , @MachineName
    --Get ETL Job History ID just created
      SET @EtlJobHistoryID = SCOPE_IDENTITY()
    --Add ETL Job History Detail rows for each Data Feed
      INSERT INTO [AGRC_CSI].[dbo].[tblEtlJobHistoryDetails](
        , [DataFeedName]
        , [DataFeedType]
        , [RowsAdded]
        , [GrossAmountAdded]
        , [AlteredAmountAdded]
        , [TransDateEarliest]
        , [TransDateLatest]
          , 'Deltek'
          , 'Billing'
          , COUNT(*)
          , SUM(LABOR_AMOUNT)
          , NULL
          , MIN(Transaction_date)
          , MAX(Transaction_date)
        FROM tblBillingProjects
        WHERE DESCR = 'Deltek'
          , 'Accuate'
          , 'Billing'
          , COUNT(*)
          , SUM(LABOR_AMOUNT)
          , NULL
          , MIN(Transaction_date)
          , MAX(Transaction_date)
        FROM tblBillingProjects
        WHERE DESCR = 'Accuate'
          , 'AGRCPipeline'
          , 'Pipeline'
          , COUNT(*)
          , SUM(GrossRevenue)
          , SUM(GrossRevenue * PctChanceSuccess)
          , MIN(dtProjectStart)
          , MAX(dtProjectEnd)
        FROM tblPipelineProjects
      RETURN 0

    Open in new window

    LVL 75

    Accepted Solution

    I have placed in bold the problem:
    SET @Now = GETDATE()

    EXEC spAddEtlJobHistory @Now, ?

    To make my point obvious test it this way and you will be able to assign the parameter without any problem:
    EXEC spAddEtlJobHistory '20111030 17:00', ?
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Since obviously you do not want to hard code the current date, there are at least a couple of workarounds:
    1.  Create a Global Variable in your DTS and assign it the current date and time using a ActiveX script.
    2.  Don't pass in a parameter and instead assign the teh GETDATE() value in the Stored Procedure.

    Let me know if this is not clear and I can elaborate on either method.
    LVL 1

    Author Closing Comment

    Yep, so there you have it.  I guess I learned three things from this:
    1) I need to post what I'm actually executing (in my "wisdom" I withheld the fact that I had two params one of which I was sending to the stored proc with a @... .  Sry about that.
    2) I need to test down to the simplest level (I should have created a test stored proc that did nothing that only had one input param a long time ago, duh?
    3)) It appears the Execute SQL Task can not always (and perhaps can never) handle a combination of question marks (?) and the typical declared variables like @MyVariable.  I'd like to test this some day but I just don't have time.

    On to my next headache.  Thanks so much acperkins for you responsiveness.
    LVL 1

    Author Comment

    So it ends up (and I almost forgot) that I DO need to pass a particular DATETIME value.  And its NOT the value of GETDATE().  Its actually the Start Time (a DATETIME datatype) of the DTS Package that I defined as my first step of the package.  So far its available via a global temp table (which of course I will change to a local once I move into more of a QA mode).  I guess this means I need to dump this DATETIME value into a String type DTSGlobalVarialbe and end up calling...
    EXEC spAddEtlJobHistory ?, ?

    Not too difficult but I guess now I know one of the limitations of DTS and how to work around it.  Boy what a painful (though rewarding) experience this has been.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now