Re: Execute Stored Procedure for ever record when importing?

#1

Hi Darren, is there any way of returning a value without using a resultset?
The issue is that the stored procedure increments a key, so I would have to
write a select statement to retrieve that key, which works. however I'm
concerned that if someone else happens to run the same stored procedure,
that the select statement mgiht not return the key that I'm after?

ie. I do an exec and increment the key to 49
someone else increments the key to 50
is it possible that the select statement returns 50 in both cases?

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Quote:

To return a value from your stored procedure, just use a resultset (SELECT
statement) in the procedure. Make sure you add SET NOCOUNT ON to the top
of
the procedure.

Some more articles on lookups-

Data Transformation Services (DTS)
(http://www.databasejournal.com/featu...le.php/1459171)

Looking at Lookups

(http://msdn.microsoft.com/library/de...-us/dnsqlpro2k
/html/sql00l5.asp)

--
Darren Green
http://www.sqldts.com

"Gregor Anton" <nospamplease (AT) hotmail (DOT) com> wrote in message
news:e8BIGIvBEHA.1236 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
I've got it working now, although I havent figured out yet how to
retrieve
an output parameter and make it the lookup value?
"Gregor Anton" <nospamplease (AT) hotmail (DOT) com> wrote in message
news:eH1vG0tBEHA.3284 (AT) TK2MSFTNGP09 (DOT) phx.gbl...
I've tried to do that but I get an error: The Query Designer does not
support the EXEC SQL construct.

My Query for the lookup is exec sysdba.GetKey NULL, PICKLIST, XXXX

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in
message
news:JNcGq9FJm2TAFwDb (AT) sqldts (DOT) com...
In message <uQgcvMsBEHA.3284 (AT) TK2MSFTNGP09 (DOT) phx.gbl>, Gregor Anton
nospamplease (AT) hotmail (DOT) com> writes
Is it possible to execute a stored procedure for every record when
importing?

I have a table with a number of rows that need to imported into a
table
that
requires a unique key, this unique key is generated via a stored
procedure.
So what I'd like to do is call this stored procedure, grab the
return
value
and map it to the destination id field and map all other fields
from
the
import table to the destination table.

Any suggestions?

Use an ActiveX Script Transform, and call a DTS Lookup.

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Execute Stored Procedure for ever record when importing?

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Execute Stored Procedure for ever record when importing? - 04-01-2004 , 07:50 PM