Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 stored procedure fail

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-03-26 : 04:54:28
Hi

I have this stored procedure ...

@CartID nVarChar (20),
@DeliveryInformation nVarChar(500) = NULL
AS
SET NOCOUNT ON
--SET ARITHABORT ON
INSERT INTO OrderInfo (CartID, DeliveryInformation) VALUES (@CartID,@UserDeliveryInformation)

DECLARE @AddedID Int
SET @AddedID = SCOPE_IDENTITY();
INSERT INTO OrderStatusInfo (OrderID, OrderName) VALUES (@AddedID, @CartID)



If I run this through SSM it works as I want. But when I call the procedure from my webbpage the second insert part does not work. Does anyone have any clue to why this might occur?

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-03-26 : 06:24:51
I belive I found the cause. I have the database BROOKER_ENABLED and it seem to be a bugg when using that together with SCOPE_IDENTITY according to this link http://vadivel.blogspot.se/2012/02/avoid-using-scopeidentity-and-identity.html
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-26 : 22:53:52
MS Knowledgebase suggests this issue is fixed - make sure you have latest Service Pack installed?

SELECT @@version

and then check that against a list of version numbers (which Google will no doubt find for you )
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-03-27 : 04:06:35
According to the KB they only solved this in SQL Server 2008 SP1, I use SQL Server 2005....
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 06:19:27
Ah, OK. There was something else about parallel processing and using

OPTION (MAXDOP 1)
or
sp_configure 'max degree of parallelism', 1

(the second would potential reduce performance of all queries rather than specific ones)

Also using the OUTPUT clause of INSERT - I can;t remember when that came in, was that in 2005 even?
Go to Top of Page
   

- Advertisement -