homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Gold Sponsor 2015!
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

5+ Year Member

Msg#: 4173954 posted 11:57 am on Jul 21, 2010 (gmt 0)


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?




5+ Year Member

Msg#: 4173954 posted 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