Return Key (identity) on INSERT operation (SQL/ASP)

[CJM]

I have the following SP:

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
If @@RowCount > 0
Select Scope_Identity() as OrderID
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The theory is that if the Insert operation is successful, the key
(Scope_Identity) is returned.

If I try the following in QA, it works: Exec Orders_CreateOrder 1, 1,
'123d', '20040323'

However, in my ASP code, I get an error:
"Operation is not allowed when the object is closed."
ASP Snippet:
sSQL = "Exec Orders_CreateOrder " & _
Request.Form("CustomerID") & ", " & _
Request.Form("Depot") & ", '" & _
Request.Form("CustOrderNo") & "', '" & _
ISODate(Request.Form("OrderDate")) & "'"

Set rsOrders = oConn.Execute(sSQL)
Any ideas?

Cheers

Chris

[Tibor Karaszi]

Add SET NOCOUNT ON in the beginning of your proc code.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CJM" <cj*****@yahoo.co.uk> wrote in message
news:Of**************@tk2msftngp13.phx.gbl...

I have the following SP:

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
If @@RowCount > 0
Select Scope_Identity() as OrderID
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The theory is that if the Insert operation is successful, the key
(Scope_Identity) is returned.

If I try the following in QA, it works: Exec Orders_CreateOrder 1, 1,
'123d', '20040323'

However, in my ASP code, I get an error:
"Operation is not allowed when the object is closed."
ASP Snippet:
sSQL = "Exec Orders_CreateOrder " & _
Request.Form("CustomerID") & ", " & _
Request.Form("Depot") & ", '" & _
Request.Form("CustOrderNo") & "', '" & _
ISODate(Request.Form("OrderDate")) & "'"

Set rsOrders = oConn.Execute(sSQL)
Any ideas?

Cheers

Chris

[Aaron Bertrand [MVP]]

Tibor is correct, you need to add SET NOCOUNT ON at the beginning of your
stored procedure (in this case, on the line after AS). FWIW, I add this
line to EVERY stored procedure I create.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"CJM" <cj*****@yahoo.co.uk> wrote in message
news:Of**************@tk2msftngp13.phx.gbl...

I have the following SP:

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
If @@RowCount > 0
Select Scope_Identity() as OrderID
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The theory is that if the Insert operation is successful, the key
(Scope_Identity) is returned.

If I try the following in QA, it works: Exec Orders_CreateOrder 1, 1,
'123d', '20040323'

However, in my ASP code, I get an error:
"Operation is not allowed when the object is closed."
ASP Snippet:
sSQL = "Exec Orders_CreateOrder " & _
Request.Form("CustomerID") & ", " & _
Request.Form("Depot") & ", '" & _
Request.Form("CustOrderNo") & "', '" & _
ISODate(Request.Form("OrderDate")) & "'"

Set rsOrders = oConn.Execute(sSQL)
Any ideas?

Cheers

Chris

[Tom Moreau]

My $0.02. In addition to Tibor and Aaron's comments, I'd avoid using a row
set and go with using an output parameter with your proc. It's more
efficient.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:#d**************@TK2MSFTNGP12.phx.gbl...
Tibor is correct, you need to add SET NOCOUNT ON at the beginning of your
stored procedure (in this case, on the line after AS). FWIW, I add this
line to EVERY stored procedure I create.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"CJM" <cj*****@yahoo.co.uk> wrote in message
news:Of**************@tk2msftngp13.phx.gbl...

I have the following SP:

CREATE PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)
If @@RowCount > 0
Select Scope_Identity() as OrderID
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

The theory is that if the Insert operation is successful, the key
(Scope_Identity) is returned.

If I try the following in QA, it works: Exec Orders_CreateOrder 1, 1,
'123d', '20040323'

However, in my ASP code, I get an error:
"Operation is not allowed when the object is closed."
ASP Snippet:
sSQL = "Exec Orders_CreateOrder " & _
Request.Form("CustomerID") & ", " & _
Request.Form("Depot") & ", '" & _
Request.Form("CustOrderNo") & "', '" & _
ISODate(Request.Form("OrderDate")) & "'"

Set rsOrders = oConn.Execute(sSQL)
Any ideas?

Cheers

Chris

[Aaron Bertrand [MVP]]

>> My $0.02. In addition to Tibor and Aaron's comments, I'd avoid using a

row set and go with using an output parameter with your proc. It's more
efficient.

Yes, but the difference is negligible -- unless you're up into the thousands
of concurrent users. The verbose and troublesome command object syntax that
would be required in ASP to handle the alternative approach is hardly worth
the trouble, if the only reason is to use the "more efficient" output
variable. That's my $0.02, and though I'm in the states, it's on par with
your CDN$0.02. :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[Tom Moreau]

Actually, when you have a high number of inserts, it's not so negligible.
Craig Utley did a cool article on output vars vs. rowsets in SQL Server
Professional:

http://www.pinpub.com/html/main.isx?sub=64&story=729
He used ASP pages as a test harness. Craig seems to think that the payback
is in ADO - less overhead in handling the rowset than the output var.

I think I've seen similar writings elsewhere, perhaps in Bill Vaughn's
ADO/ADO.NET book.
--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:#n**************@TK2MSFTNGP12.phx.gbl...

My $0.02. In addition to Tibor and Aaron's comments, I'd avoid using a
row set and go with using an output parameter with your proc. It's more
efficient.

Yes, but the difference is negligible -- unless you're up into the thousands
of concurrent users. The verbose and troublesome command object syntax that
would be required in ASP to handle the alternative approach is hardly worth
the trouble, if the only reason is to use the "more efficient" output
variable. That's my $0.02, and though I'm in the states, it's on par with
your CDN$0.02. :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[Tibor Karaszi]

> I think I've seen similar writings elsewhere, perhaps in Bill Vaughn's

ADO/ADO.NET book.
I recall an article in SQLMag a few years back by Bill Vaughn where he
measured I believe a few thousand times improvements using out params
compared to a result set. This was, as I recall it, ADO and I believe that
Bill's assumption here as well was that the major part was in the ADO
handling.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message
news:uR**************@TK2MSFTNGP12.phx.gbl... Actually, when you have a high number of inserts, it's not so negligible.
Craig Utley did a cool article on output vars vs. rowsets in SQL Server
Professional:

http://www.pinpub.com/html/main.isx?sub=64&story=729
He used ASP pages as a test harness. Craig seems to think that the payback is in ADO - less overhead in handling the rowset than the output var.

I think I've seen similar writings elsewhere, perhaps in Bill Vaughn's
ADO/ADO.NET book.
--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:#n**************@TK2MSFTNGP12.phx.gbl...

My $0.02. In addition to Tibor and Aaron's comments, I'd avoid using a
row set and go with using an output parameter with your proc. It's more efficient.

Yes, but the difference is negligible -- unless you're up into the

thousands of concurrent users. The verbose and troublesome command object syntax that would be required in ASP to handle the alternative approach is hardly worth the trouble, if the only reason is to use the "more efficient" output
variable. That's my $0.02, and though I'm in the states, it's on par with
your CDN$0.02. :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

[Aaron Bertrand [MVP]]

Again, this kind of performance gain is unlikely to be a factor when you're
dealing with one insert at a time. I understand, and stated earlier, that
is likely to be worth pursuing when you're at high volume.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/


"Tibor Karaszi" <ti***************************@hotmail.nomail.co m> wrote in
message news:%2****************@TK2MSFTNGP09.phx.gbl...

I think I've seen similar writings elsewhere, perhaps in Bill Vaughn's
ADO/ADO.NET book.

I recall an article in SQLMag a few years back by Bill Vaughn where he
measured I believe a few thousand times improvements using out params
compared to a result set. This was, as I recall it, ADO and I believe that
Bill's assumption here as well was that the major part was in the ADO
handling.

[Bob Barrows [MVP]]

Tom, The SQL Pro link seems to be dead. I get a blank page when I try it.

From what I've read in both Bill Vaughn's and David Sceppa's books, you are
both correct that the major impact was in the ADO handling. Yes, there is
some overhead in SQL Server itself to create the cursor, but passing the
cursor across processes, creating the recordset object to receive the
cursor, and initializing the ADO cursor library was where the main impact
would be seen.

In fact, Bill goes on to maintain that using a recordset to retrieve a
single record is never warranted. While I agree in principle with him, I
usually take the easy way and use a recordset, especially when a large
number of columns is involved. Bill made the point that, in an interpreted
language such as vbscript, using a large number of CreateParameter
statements might negate the performance benefits gained from using output
parameters instead of a recordset, due to the need to compile all those
statements every time.

And yes, Aaron is correct: creating all those CreateParameter statements can
be error-prone and hard to debug. That is why I use a code generator.
There's a VB addin for this available on Bill's site www.betav.com. I
adapted it (and fixed a couple of problems in the process) for vbscript.
It's available here:
http://www.thrasherwebdesign.com/ind...s&hp=links.asp

Bob Barrows

Tibor Karaszi wrote:

I think I've seen similar writings elsewhere, perhaps in Bill
Vaughn's ADO/ADO.NET book.

I recall an article in SQLMag a few years back by Bill Vaughn where he
measured I believe a few thousand times improvements using out params
compared to a result set. This was, as I recall it, ADO and I believe
that Bill's assumption here as well was that the major part was in
the ADO handling.
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message
news:uR**************@TK2MSFTNGP12.phx.gbl...

Actually, when you have a high number of inserts, it's not so
negligible. Craig Utley did a cool article on output vars vs.
rowsets in SQL Server Professional:

http://www.pinpub.com/html/main.isx?sub=64&story=729
He used ASP pages as a test harness. Craig seems to think that the
payback is in ADO - less overhead in handling the rowset than the
output var.

I think I've seen similar writings elsewhere, perhaps in Bill
Vaughn's ADO/ADO.NET book.
--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:#n**************@TK2MSFTNGP12.phx.gbl...

My $0.02. In addition to Tibor and Aaron's comments, I'd avoid
using a row set and go with using an output parameter with your
proc. It's more efficient.

Yes, but the difference is negligible -- unless you're up into the
thousands of concurrent users. The verbose and troublesome command
object syntax that would be required in ASP to handle the
alternative approach is hardly worth the trouble, if the only reason
is to use the "more efficient" output variable. That's my $0.02,
and though I'm in the states, it's on par with your CDN$0.02. :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Tom Moreau]

Bob,

Thanx for this. I just tried the SQL Pro website and it's up.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:OY**************@tk2msftngp13.phx.gbl...
Tom, The SQL Pro link seems to be dead. I get a blank page when I try it.

From what I've read in both Bill Vaughn's and David Sceppa's books, you are
both correct that the major impact was in the ADO handling. Yes, there is
some overhead in SQL Server itself to create the cursor, but passing the
cursor across processes, creating the recordset object to receive the
cursor, and initializing the ADO cursor library was where the main impact
would be seen.

In fact, Bill goes on to maintain that using a recordset to retrieve a
single record is never warranted. While I agree in principle with him, I
usually take the easy way and use a recordset, especially when a large
number of columns is involved. Bill made the point that, in an interpreted
language such as vbscript, using a large number of CreateParameter
statements might negate the performance benefits gained from using output
parameters instead of a recordset, due to the need to compile all those
statements every time.

And yes, Aaron is correct: creating all those CreateParameter statements can
be error-prone and hard to debug. That is why I use a code generator.
There's a VB addin for this available on Bill's site www.betav.com. I
adapted it (and fixed a couple of problems in the process) for vbscript.
It's available here:
http://www.thrasherwebdesign.com/ind...s&hp=links.asp

Bob Barrows

Tibor Karaszi wrote:

I think I've seen similar writings elsewhere, perhaps in Bill
Vaughn's ADO/ADO.NET book.

I recall an article in SQLMag a few years back by Bill Vaughn where he
measured I believe a few thousand times improvements using out params
compared to a result set. This was, as I recall it, ADO and I believe
that Bill's assumption here as well was that the major part was in
the ADO handling.
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message
news:uR**************@TK2MSFTNGP12.phx.gbl...

Actually, when you have a high number of inserts, it's not so
negligible. Craig Utley did a cool article on output vars vs.
rowsets in SQL Server Professional:

http://www.pinpub.com/html/main.isx?sub=64&story=729
He used ASP pages as a test harness. Craig seems to think that the
payback is in ADO - less overhead in handling the rowset than the
output var.

I think I've seen similar writings elsewhere, perhaps in Bill
Vaughn's ADO/ADO.NET book.
--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:#n**************@TK2MSFTNGP12.phx.gbl...

My $0.02. In addition to Tibor and Aaron's comments, I'd avoid
using a row set and go with using an output parameter with your
proc. It's more efficient.

Yes, but the difference is negligible -- unless you're up into the
thousands of concurrent users. The verbose and troublesome command
object syntax that would be required in ASP to handle the
alternative approach is hardly worth the trouble, if the only reason
is to use the "more efficient" output variable. That's my $0.02,
and though I'm in the states, it's on par with your CDN$0.02. :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows [MVP]]

I don't get it. I'm registered, but when I follow your link (or do a site search and follow that link) all I get is a blank frame - their outer frame shows up all right, it's the article itself that is missing .... I've sent them an email.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message news:eg**************@TK2MSFTNGP10.phx.gbl...
Bob,

Thanx for this. I just tried the SQL Pro website and it's up.

--

[Tom Moreau]

Bob,

I think I see your problem, though I don't know what caused it. Try getting
at it this way:

http://www.pinpub.com/html/main.isx?sub=38&auth=212

This gets you to Craig's author page. Then click on the article and it
brings it up.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
..
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uy**************@TK2MSFTNGP10.phx.gbl...
I don't get it. I'm registered, but when I follow your link (or do a site
search and follow that link) all I get is a blank frame - their outer frame
shows up all right, it's the article itself that is missing ... I've sent
them an email.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From header is
my spam trap, so I don't check it very often. You will get a quicker
response by posting to the newsgroup.
"Tom Moreau" <to*@dont.spam.me.cips.ca> wrote in message
news:eg**************@TK2MSFTNGP10.phx.gbl...
Bob,

Thanx for this. I just tried the SQL Pro website and it's up.

--

[CJM]

Thanks for the responses guys.

I still have a little problem.... I'm updating the SP to include SET NOCOUNT
ON, and when I run the ALTER statement in QA it works... but when I look at
the SP again, the NOCOUNT lines are missing...

And needless to say the ASP page still doesnt work..

Any ideas?

Thanks

[Tibor Karaszi]

Can you show us the procedure code (or the beginning of the code)?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CJM" <cj*****@yahoo.co.uk> wrote in message
news:eC**************@TK2MSFTNGP11.phx.gbl...

Thanks for the responses guys.

I still have a little problem.... I'm updating the SP to include SET NOCOUNT ON, and when I run the ALTER statement in QA it works... but when I look at the SP again, the NOCOUNT lines are missing...

And needless to say the ASP page still doesnt work..

Any ideas?

Thanks

[CJM]

Tibor,

Here's the full SP code:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.Orders_CreateOrder Script Date:
24/03/2004 09:41:39 ******/

ALTER PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)

Select Scope_Identity() as OrderID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
No matter what I do, the NOCOUNT lines disappear, and the
QUOTED_IDENTIFIER/ANSI_NULLS lines appear. I went so far as deleting the SP,
and recreating it with SET NOCOUNT ON, but not including the other settings,
but when I look back they, everything is back to as it was...

Help!

Chris

[Tibor Karaszi]

> No matter what I do, the NOCOUNT lines disappear, and the

QUOTED_IDENTIFIER/ANSI_NULLS lines appear.
It is no surprise that the QUOTED_IDENTIFIER and ANSI_NULLS commands
disappears as you set them *outside* the procedure code. These two are
"sticky parameters" meaning that the proc will have the setting you had in
the connection from where you *created* the proc.

All other options you need to set *inside* the procedure code.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"CJM" <cj*****@yahoo.co.uk> wrote in message
news:OV**************@TK2MSFTNGP12.phx.gbl... Tibor,

Here's the full SP code:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.Orders_CreateOrder Script Date:
24/03/2004 09:41:39 ******/

ALTER PROCEDURE Orders_CreateOrder
@CustomerID int,
@LocationID int,
@CustOrderNo varchar(20),
@OrderDate smalldatetime
AS

Insert into Orders (CustomerID, LocationID, CustOrderNo, OrderDate)
Values (@CustomerID, @LocationID, @CustOrderNo, @OrderDate)

Select Scope_Identity() as OrderID

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
No matter what I do, the NOCOUNT lines disappear, and the
QUOTED_IDENTIFIER/ANSI_NULLS lines appear. I went so far as deleting the SP, and recreating it with SET NOCOUNT ON, but not including the other settings, but when I look back they, everything is back to as it was...

Help!

Chris

[CJM]

Tibor,

Thanks...

After a few guesses, I've finally figured the correct syntax...!

All appears to be working now, though it is no thanks to Books Online, which
was as much use as a chocolate teapot!

Thanks again for you efforts and you prompt response...

Chris

[Bob Barrows [MVP]]

[Tom Moreau]

[Bob Barrows [MVP]]

[Tom Moreau]

[Bob Barrows [MVP]]

[Tom Moreau]

[Bob Barrows [MVP]]

[Tom Moreau]