Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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
7
Medium Priority
?
644 Views
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
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.


0
Comment
Question by:GronkeyKong
  • 3
  • 3
7 Comments
 
LVL 15

Expert Comment

by:AmmarR
ID: 37052616
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37053856
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
0
 
LVL 1

Author Comment

by:GronkeyKong
ID: 37053969
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 37054139
I have placed in bold the problem:
DECLARE @Now AS DATETIME
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', ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37054147
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.
0
 
LVL 1

Author Closing Comment

by:GronkeyKong
ID: 37054198
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.
0
 
LVL 1

Author Comment

by:GronkeyKong
ID: 37054254
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.  
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

571 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