SQL Stored Procedure no longer working in new Server and Database

Question

I am working on converting an asp.net project. I have it entirely working with our old database and server but since we switched over to our new DataBase and server it has all been going to hell. The old program used lots of @@Identity, which was no longer working in our new database. So we swtiched to SCOPE_IDENTITY() when we could and made changes so it mimics what @@Identity use to give us.

This particular stored procedure I can not figure out whre ItemID is being changed ( comes in as 0 but changes to a different ID[which we want]) And i get an error that says cannot insert @ItemID as NULL. Could someone point out where ItemID is being changerd? Also any extra info about why this database conversion has gone bad or why @@Identity needs to be changed is also welcomed.

GO
/****** Object:  StoredProcedure [dbo].[TVM_InsertDocumentToDB]    Script Date: 16/07/2014 10:44:32 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[TVM_InsertDocumentToDB]
(
    @VersionID              uniqueidentifier,
    @ItemID                 int,
    @ModuleID                   int,
    @UpdatedByUser      DT_UserID,
    @UpdatedDate        datetime,
    @FileFriendlyName_en    nvarchar(150),
    @Content_en             image,
    @ContentType_en         nvarchar(50),
    @ContentSize_en             int,
    @OriginalName_en        nvarchar(50),
    @DocType_en     char(3),
    @FileFriendlyName_fr    nvarchar(150),
    @Content_fr             image,
    @ContentType_fr             nvarchar(50),
    @ContentSize_fr             int,
    @OriginalName_fr        nvarchar(50),
    @DocType_fr         char(3),
    @UpdateableByOwnerOnly  bit,
    @NotifyOnDownload                nvarchar(75),
    @TopicID            int,
    @Keywords_en        varchar(200),
    @Keywords_fr        varchar(200),
    @ActiveDate         datetime,
    @ExpireDate         datetime,
    @PublishingGroup        DT_PublishingGroup,
    @Comments           nvarchar(500),
    @Live           bit,
    @NeedTranslation        bit,
    @ItemID_OUT         int output
)
AS
        INSERT INTO dbo.DocumentsV2
        (
            VersionID,
            ModuleID,
            CreatedByUser,
            CreatedDate,
            UpdatedByUser,
            UpdatedDate,
            FileFriendlyName_en,
            Content_en,
            ContentType_en,
            ContentSize_en,
            OriginalName_en,
            DocType_en,
            FileFriendlyName_fr,
            Content_fr,
            ContentType_fr,
            ContentSize_fr,
            OriginalName_fr,
            DocType_fr,
            UpdateableByOwnerOnly,
            NotifyOnDownload,
            TopicID,
            Keywords_en,
            Keywords_fr,
            ActiveDate,
            ExpireDate,
            PublishingGroup,
            Comments,
            Live,
            NeedTranslation
        )
        VALUES
        (
            @VersionID,
            @ModuleID,
            @UpdatedByUser,
            @UpdatedDate,
            @UpdatedByUser,
            @UpdatedDate,
            @FileFriendlyName_en,
            @Content_en,
            @ContentType_en,
            @ContentSize_en,
            @OriginalName_en,
            @DocType_en,
            @FileFriendlyName_fr,
            @Content_fr,
            @ContentType_fr,
            @ContentSize_fr,
            @OriginalName_fr,
            @DocType_fr,
            @UpdateableByOwnerOnly,
            @NotifyOnDownload,
            @TopicID,
            @Keywords_en,
            @Keywords_fr,
            @ActiveDate,
            @ExpireDate,
            @PublishingGroup,
            @Comments,
            @Live,
            @NeedTranslation
        )

    SET @ItemID_OUT = (SELECT MAX(ItemID) FROM DocumentsV2)

I don't think all of your proc made it into the post? For a breakdown on identity differences, check [here](http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide). — Kevin Dahl, Jul 16 '14 at 15:31
You are not using @itemid anywhere and you are not inserting a value to the column ItemID. Should the column ItemID be an identity column? — Mikael Eriksson, Jul 16 '14 at 15:33
@MikaelEriksson This was our problem. In our old database it was that but got defined differently when SQL-SERVER imported it for some reason — Hillboy, Jul 16 '14 at 15:49

score 1 · Accepted Answer · answered Jul 16 '14 at 15:33

Could someone point out where ItemID is being changers?

It's not being changed. It's not even being used.

And i get an error that says cannot insert @ItemID as NULL.

Is that error coming from the INSERT statement in the procedure? Because you're not inserting any value called ItemID. So if the table DocumentsV2 has a column called ItemID, and that column is NOT NULL, and it has no DEFAULT, then I would fully expect that exact error.

Perhaps the column is incorrectly defined? Perhaps you meant to update the INSERT statement to include a value for that column? We can't know. But if that's the error you're seeing on that statement then it means that a column named ItemID requires a value, and your INSERT statement isn't providing one.

We figured it out. Just have to implement it now. When we imported our tables for some reason SQL-Server defined our columns differently(no identity) and did not bring with it its Constraints, Triggers, etc — Hillboy, Jul 16 '14 at 15:48

SQL Stored Procedure no longer working in new Server and Database

1 Answers1