stored procedure fail - SQL Server Forums

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

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

)

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....

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources