Re: ADO.NET vs OLE DB for Stored Procedure Execution

#1

You can use EITHER ADO.NET or OLEDB connections from your .NET application.
However, I suggest that you will have better performance using ADO.NET.

Also in your stored procedure, you would be better served by using an output
parameter rather than having the IDENTITY value returned as a scalar value.

Also, instead of using the @@IDENTITY to obtain the value from the last
insert row, it would be more secure to use SCOPE_IDENTITY(). There are
circumstance where @@IDENTITY could return a value different that the one
that you are expecting.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

"Todd C" <ToddC (AT) discussions (DOT) microsoft.com> wrote

Quote:

I am trying to execute a stored procedure that inserts a record with the
values I pass to it in parameters, and have it return a single value: that
of
@@Identity

***** Sample SP Code: ******
create proc MyProc
( << my list of poarameters here >> ) AS
INSERT INTO MyTable ( << field list >> )
VALUES ( << parameter values >> )
SELECT @@ IDENTITY
*************** end *******

Setting up my Execute SQL Task involves setting up the Parameter Mapping
and
Result Set pages, with appropriat varialbles.

Here's the thing: The task only executes with an ADO.NET type connection,
not an OLE DB connection. Why?
Since my package also manages records from that same database, I also need
an OLE DB type connection for the data flow.

Why must I manage TWO connections Types to the SAME database?

Other Ececute SQL Tasks seem to execute OK with OLE DB type connections
(commands such as TRUNCATE TABLE).

--
Todd Chittenden

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

Re: ADO.NET vs OLE DB for Stored Procedure Execution

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

Re: ADO.NET vs OLE DB for Stored Procedure Execution - 12-01-2006 , 10:32 AM