asked on

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

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
Message:
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
DECLARE @MyVar
SET @MyVar = ?
EXEC spAddEtlJobHistory @MyVar

--Test Case 4
DECLARE @MyVar
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
DECLARE @MyVar AS VARCHAR(63)
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.

AmmarR

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
execute-sql-task.png

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

GronkeyKong

ASKER

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 ?

Check.

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.

Ugggg!

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
DECLARE @Now AS DATETIME
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
DECLARE @Now AS DATETIME
SET @Now = GETDATE()
EXEC spAddEtlJobHistory @Now, ?

CREATE PROCEDURE [dbo].[spAddEtlJobHistory] 
	  @EtlJobStart		DATETIME
	, @MachineName		VARCHAR(63)
	
AS
BEGIN
	/*	Full Name:			
		Purpose primary:	Adds an a row to tblEtlJobHistory
		                  and multiple rows to tblEtlJobHistoryDetails			  
	
	*/
	SET NOCOUNT ON;
	--Declarations
    DECLARE @EtlJobHistoryID AS INTEGER

--Add ETL Job History SUCCESS row
  INSERT INTO [AGRC_CSI].[dbo].[tblEtlJobHistory](
      [EtlJobStart]
    , [EtlJobFinish]
    , [EtlJobOutcome]
    , [EtlJobRanAsLogin]
    , [EtlJobExectedFromMachine]
  )
    SELECT
        @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](
      [EtlJobHistoryID]
    , [DataFeedName]
    , [DataFeedType]
    , [RowsAdded]
    , [GrossAmountAdded]
    , [AlteredAmountAdded]
    , [TransDateEarliest]
    , [TransDateLatest]
  )
    SELECT
        @EtlJobHistoryID
      , 'Deltek'
      , 'Billing'
      , COUNT(*)
      , SUM(LABOR_AMOUNT)
      , NULL
      , MIN(Transaction_date)
      , MAX(Transaction_date)
    FROM tblBillingProjects
    WHERE DESCR = 'Deltek'
    UNION
    SELECT
        @EtlJobHistoryID
      , 'Accuate'
      , 'Billing'
      , COUNT(*)
      , SUM(LABOR_AMOUNT)
      , NULL
      , MIN(Transaction_date)
      , MAX(Transaction_date)
    FROM tblBillingProjects
    WHERE DESCR = 'Accuate'
    UNION
    SELECT
        @EtlJobHistoryID
      , 'AGRCPipeline'
      , 'Pipeline'
      , COUNT(*)
      , SUM(GrossRevenue)
      , SUM(GrossRevenue * PctChanceSuccess)
      , MIN(dtProjectStart)
      , MAX(dtProjectEnd)
    FROM tblPipelineProjects
	
  RETURN 0
  															
END

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

GronkeyKong

ASKER

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.

GronkeyKong

ASKER

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.