|Moving stored procedure from SQL Server 2000 to 2005, multiple table i|
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]
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);
DECLARE @new_id INT
SET @new_id = SCOPE_IDENTITY()
INSERT INTO dbo.user_info_requested
INSERT INTO dbo.user_details_supplied
INSERT INTO dbo.user_questions
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?
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.