Hi All

Happy New Year.

Inside a DTS package I have to implement a functionality that will enable me
create and entry in a table and then collect the identity column generated.
I have the following statements in the SQL Task.


DECLARE @newID
EXEC @newID = ps_AddDemoDTS03Control ?, ?
SELECT @newID

The idea is to set the two arguments from global variables and collect the
return value in gobal varuable.

However, when I click on the parameters button I get the error message:

Package Error
Error source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax Error or access violation

Parsing SQL Statement

An error occurred while parsing SQL Statement for parameters. Please check
the syntax of teh SQL statement and verify that it is valid for the
connection selected.

The code of the stored procedure is

CREATE PROCEDURE ps_AddDemoDTS03Control (@dataloadid, @status varchar(50)
AS
SET NOCOUNT ON
DECLARE @newid int
insert into table1(dataloadid, status) select @dataloadid, @status
SELECT @newid = @@IDENTITY
RETURN @newid

I could use a third paramter as output in the stored procedure but I prefer
using the return method, specially when used in a VB program.

Can someone shed some light on this and show how to collect return
parameters from a stored procedure.

Thanks

Raj

try:

DECLARE @newID
EXEC @newID = ps_AddDemoDTS03Control ?, ?
SELECT @newID as ValueReturned


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Hemraj Julha" <hjulha (AT) intnet (DOT) mu> wrote

Thanks for the quick reply Allan. But ...
I still get the error message. I have SQL 2000 with service pack 3. Any
clues ?

Raj

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

I did this

CREATE PROCEDURE ReturnValueSimple
AS
SET NOCOUNT ON
RETURN 10
go

In my ExecuteSQL task I did

DECLARE @val int
Exec @val = ReturnValueSimple
SELECT @Val as Value

I cliked the parameters button and assigned the value to a Row Value output.

SQL Server 2000 SP3 + Sec hotfixes.


Now to take two input values and add a return value

CREATE PROCEDURE ReturnValueSimpleWithInputs @input1 int, @input2 int
AS
SET NOCOUNT ON
RETURN @input1 + @input2
go


Here we are going to cheat.

The design time parser is not as advanced or forgiving as its runtime
counterpart so what are going to do is fool it by creating the same metadata
but not the same statement.

I enter into the ExecuteSQL task the following

SELECT id AS OutputTest FROM sysobjects where id = ? OR id = ?I can now map
my parametersI go to Disconnected edit (Or the object model in an Active
Script task) and enter my real statement. DECLARE @val intExec @val =
ReturnValueSimpleWithInputs ?,?select @val as OutputTestWorks like a
charm.We discuss it more hereGlobal Variables and Stored Procedure
Parameters(http://www.sqldts.com/Default.aspx?234)





--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Hemraj Julha" <hjulha (AT) intnet (DOT) mu> wrote

Thanks, it works now.

Raj




"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote