homepage Welcome to WebmasterWorld Guest from 54.205.189.156
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Moving stored procedure from SQL Server 2000 to 2005, multiple table i
ProbablyMike




msg:4173956
 11:57 am on Jul 21, 2010 (gmt 0)

Hi,

I'm moving some tables & stored procedures from SQL Server 2000, to SQL Server 2005.

So far so good, but I've come across a problem with this stored procedure:

CREATE PROCEDURE [dbo].[user_insert]
@user_service_unit int,
@user_champion_1 varchar(50),
@user_champion_1_nt varchar(10),
@user_champion_2 varchar(50),
@user_champion_2_nt varchar(10),
@user_champion_3 varchar(50),
@user_champion_3_nt varchar(10),
@user_date_received datetime,
@user_requestor varchar(255),
@user_info_requested text,
@user_expiry_date datetime,
@user_10_days datetime,
@user_5_days datetime,
@user_2_days datetime
AS

INSERT INTO dbo.user_details
(user_service_unit, user_champion_1, user_champion_1_nt, user_champion_2, user_champion_2_nt, user_champion_3, user_champion_3_nt,
user_date_received, user_requestor, user_expiry_date, user_10_days, user_5_days, user_2_days)
VALUES
(@user_service_unit, @user_champion_1, @user_champion_1_nt, @user_champion_2, @user_champion_2_nt, @user_champion_3, @user_champion_3_nt,
@user_date_received, @user_requestor, @user_expiry_date, @user_10_days, @user_5_days, @user_2_days);

DECLARE @new_id INT
SET @new_id = SCOPE_IDENTITY()

INSERT INTO dbo.user_info_requested
(user_id, user_info_requested)
VALUES
(@new_id, @user_info_requested)

INSERT INTO dbo.user_details_supplied
(user_id, user_details_supplied)
VALUES
(@new_id, '')

INSERT INTO dbo.user_questions
(user_id, user_questions)
VALUES
(@new_id, '')

RETURN @new_id
GO


On SQL Server 2000 it inserts the main record into user_details, then the extra blank records into the other 3 tables using the @new_id from the SCOPE_IDENTITY.
And then returns @new_id back so I can use it in my ASP.net script.

On SQL Server 2005, @new_id is null, and nothing is ever inserted, not even into user_details.

All permissions are set correctly on the stored procudure, and tables.

Any ideas or hints what I have to change?

Cheers,
Mike

 

ProbablyMike




msg:4173972
 12:39 pm on Jul 21, 2010 (gmt 0)

Managed to get this resolved, nothing to do with the stored procedure in the end.

The identity property was not set on the id column, so set that, but it still wasn't working.

Once I managed to get the correct error from my ASP.NET, I found out that there were some bit fields in the table that didn't have the default value set any more, so error was cannot inset null.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved