Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

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

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

5+ Year Member


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

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)
(@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);


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

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

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

RETURN @new_id

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?

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

5+ Year Member

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.

Featured Threads

Hot Threads This Week

Hot Threads This Month