dts SQL task executing stored procedure

dts SQL task executing stored procedure

Post by andy westbroo » Thu, 17 Oct 2002 05:47:49



Im using SQL server 2000.  I have a DTS Execute SQL task
which executes a stored procedure by passing the stored
procedute name, and two paramaters as follows

EXEC ?  ?,?

The paramaters are all global variables defined as
strings.  It works as long as all of my paramater values
are 10 characters or less in length.  When I set any of
the gv's to a value longer than 10 characters, they are
truncated to 10 characters.

Any pointers are appreciated.

 
 
 

dts SQL task executing stored procedure

Post by Paul Kohlmille » Thu, 17 Oct 2002 08:44:42


I tried to duplicate what you show here and I get a syntax error. It doesn't
like not knowing the name of the stored procedure at design time. Assuming
you have found some way around this and whatever way that is it isn't
causing the problem, the next thing I would check is the type of the
parameters in the stored procedure itself. Maybe you thought they were typed
varchar(50) but they are varchar(10) instead.
Good luck,
PK

Quote:> Im using SQL server 2000.  I have a DTS Execute SQL task
> which executes a stored procedure by passing the stored
> procedute name, and two paramaters as follows

> EXEC ?  ?,?

> The paramaters are all global variables defined as
> strings.  It works as long as all of my paramater values
> are 10 characters or less in length.  When I set any of
> the gv's to a value longer than 10 characters, they are
> truncated to 10 characters.

> Any pointers are appreciated.


 
 
 

dts SQL task executing stored procedure

Post by linda deng[M » Thu, 17 Oct 2002 14:56:36


Hi Andy,

I have performed some test on this issue, and encountered the same problem
as Paul has received. Would you please paste the steps on how to reproduce
this issue as what you have described here? It helps us to workaround it.
More details are appreciated.

If this stored procedure name isn't transferred to the Execute SQL Task as
a parameter name, there is no limitation for the input parameter's length
according to my test. Please check the stored procedure's definition, or
you can paste this DDL here, too.

If anything is unclear, please let me know.

Sincerely,

Linda Deng
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

dts SQL task executing stored procedure

Post by Allan Mitchel » Sat, 19 Oct 2002 03:06:38


AFAIK

you cannot pass the name of the SP at runtime through a
parameter.

Is this the actual syntax ?

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE, MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community for SQL Server
professionals - http://www.sqlpass.org

 
 
 

dts SQL task executing stored procedure

Post by Andy Westbroo » Sat, 19 Oct 2002 05:29:20


Sorry about the delay in responding, but it took me some
time to figure out how to duplicate the issue.

It appears that the EXEC statement will parse and execute
correctly only when it follows a paramaterized WHERE
clause in another SQL statement.

For example if the SQL Task contains the following ...

UPDATE tblTest
SET Field10 = '0123456789'
WHERE TestKey = ?

EXEC ? ?,?

This example will parse and execute an sproc, however all
three Global Variable parms following the EXEC statement
will be truncated at the leftmost 10 characters.

Hope that clarifies the issue some.  Thanks for your
feedback ...

Andy Westbrook

 
 
 

dts SQL task executing stored procedure

Post by Allan Mitchel » Sat, 19 Oct 2002 15:57:15


OK

Normally this statement would not be allowed in the ExecuteSQL
task

Exec ? ?,?

But because you have a statement before it then you can do it.  
After all the syntax is valid

I ran my own tests in Northwind

make sure the City Col is big Enough

ALTER TABLE  Suppliers ALTER COLUMN City varchar(200)

Gvs are all strings

This is the proc


AS

GO

This is the ExecuteSQL task statement

update employees set firstname = ?
Exec ? ?

Now if I pass a GV 3 the value "The windy town in Southern
England"

In the actual column itself I get

"The windy "

If I do it in QA everything is fine.

However watch what happens when you seperate the statements
using the batch terminator of GO.

In QA

update employees set firstname = 'Allan'
GO
Exec City_U 'The windy town in Southern England'

--91 row(s) affected

In DTS

Step Error Description:Error in source parameter binding.  Verify
that the InputGlobalVariableNames specification matches the query.
(Microsoft OLE DB Provider for SQL Server (80040e21):
Multiple-step OLE DB operation generated errors. Check each OLE
DB status value, if available. No work was done.)

My guess then is the previous method in DTS has showhorned
things in as opposed to DTS enjoying it.  

I will investigate further.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE, MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community for SQL Server
professionals - http://www.sqlpass.org

 
 
 

dts SQL task executing stored procedure

Post by linda deng[M » Sat, 19 Oct 2002 16:00:53


Hi Andy,

I still cannot pass the parse query process, SQL Server always give me a
syntax error. Please try this DTS package on another SQL Server? Can you
still run it?

It is recommended that you specify the procedure name in EXEC statement.

Sincerely,

Linda Deng
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.

 
 
 

dts SQL task executing stored procedure

Post by Allan Mitchel » Sun, 20 Oct 2002 00:51:54



> Hi Andy,

> I still cannot pass the parse query process, SQL Server always give me
a
> syntax error. Please try this DTS package on another SQL Server? Can
you
> still run it?

> It is recommended that you specify the procedure name in EXEC
statement.

> Sincerely,

> Linda Deng
> Microsoft Corporation

> This posting is provided "AS IS" with no warranties, and confers no
rights.

Linda have a look at my explanation from this morning.  I am doing some
more digging.  It does contain a repro script as well.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE, MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community for SQL Server
professionals - http://www.sqlpass.org

 
 
 

dts SQL task executing stored procedure

Post by Bill Hollinshead [MS » Sun, 27 Oct 2002 01:54:59


Hi Alan and Andy,

To get around this bug, use a bogus global variable and map it to the first
input parameter. Then, in the Execute SQL Task, and as the first line, add
a dummy T-SQL line, something like:
{
select * from <some_table> where <some_column>=? and
'0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789A
BCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456
789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF012
3456789ABCDEF0123456789ABCDEF0123456789ABCDEF0123456789ABCDEF'='2'

Quote:}

The above line is a no operation (because the where caluse is always
false), but it should ensure subsequent parameters aren't truncated.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

 
 
 

1. DTS Transform Data Task fails in job where DTS Execute SQL Task does not

I have experienced the following issue in several DTS packages.  I use
Transform Data Task to transfer data from a table on one SQL Server to a
table on another SQL Server.  The package executes successfully when run
manually and fails with the error shown below when run from a job.

However, if I use an insert statement in a Execute SQL Task to accomplish
the same task,  the package executes successfully when run manually and when
run from a job.

Can anyone explain why jobs running with Transform Data Task sometimes fail
as shown below?

 DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnStart:
DTSStep_DTSDataPumpTask_1   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1;
1000 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 2000
Rows have been transformed or copied.; PercentComplete = 0; ProgressCount =
2000   DTSRun OnProgress:  DTSStep_DTSDataPumpTask_1; 3000 Rows have been
transformed or copied.; PercentComplete = 0; ProgressCount = 3000   DTSRun
OnProgress:  DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or
copied.; PercentComplete = 0; ProgressCount = 4000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 5000   DTSRun OnProgress:
DTSStep_DTSDataPumpTask_1; 6000 Rows have been transformed or copied.;
PercentComplete = 0; ProgressCount = 6000   ...  Process Exit Code 1.  The
step failed.

2. shutdown in svrmgrl is hanging

3. Calling a DTS standard task/custom task from a T-SQL Stored procedure

4. Looking for Opportunity...

5. DTS task won't fail when stored procedure executes a RAISERROR

6. A problem in use of Select Command

7. Execute DTS (Analysis Services Process task ) from the store procedure

8. Upgraded from Sol 2.5.1 to 2.6 with Oracle 7.3 running?

9. Returning an error from a DTS Execute SQL Task calling a Stored P rocedure

10. Output parameter not listed for Execute SQL task that calls stored procedure

11. Execute DTS from asp.Net failure because that package contain execute SQL task

12. DTS Log/Executing DTS from stored procedure

13. Executing DTS from stored procedure / missing master db procedures