why i am getting null ,when try to get last identity value from a table

I have a table (log table,which has been created as a part of ssis error handling and logging)e in the database.I am inserting a row in this table and trying to get the last identity value from the table using scope_identity() But I am getting NULL for the scope_identity?().Please see the dml statement and the scope_identity() statements being used as a part of one stored procedure

insert into logging.execution_log
            (ParentLogID, Description, PackageName, PackageGuid, MachineName, ExecutionGuid, LogicalDate, Operator, StartTime, EndTime, Status,FailureTask)
        select @ParentLogID, @Description, @PackageName, cast(@PackageGuid as uniqueidentifier), @MachineName, cast(@ExecutionGuid as uniqueidentifier),
                @logicalDate, @operator, getdate(), null, @status, null



**set @LogID = cast(scope_identity() as int)**

but when ever i try to get a value of @logid it returns null only

Please be noted that there is already a identity specification on the column LogId of the of logging.execution_log table

Regards

*Additional Details(i have just understood when I have done some testings) As a part of testing i have inserted some new rows manually like below insert into logging.execution_log (ParentLogID, Description, PackageName, PackageGuid, MachineName, ExecutionGuid, LogicalDate, Operator, StartTime, EndTime, Status,FailureTask)

    select 1, 2, 3, **NEWID()**,
     5, **NEWID()**,
            7, 8, getdate(), null, 0, null

select scope_identity()
select * from logging.execution_log

then it show the last value of identity.........it is fine but the problem is when i pass the PackageGuid as system::Packageid(ssis system variable) and ExecutionGuid as system::executioninstanceGUID then it does insert the row in the table correctly but i can not get the last identity value using scope_identi or @@identity or ident_current(all shows null)*

Answers


Use the output into clause:

insert into logging.execution_log
            (ParentLogID, Description, PackageName, PackageGuid, MachineName, ExecutionGuid, LogicalDate, Operator, StartTime, EndTime, Status,FailureTask)
        output LogID into @LogID
        select @ParentLogID, @Description, @PackageName, cast(@PackageGuid as uniqueidentifier), @MachineName, cast(@ExecutionGuid as uniqueidentifier),
                @logicalDate, @operator, getdate(), null, @status, null

begin try
    Set XACT_ABORT ON
    BEGIN TRAn
            INSERT into logging.execution_log
                (ParentLogID, Description, PackageName, PackageGuid, MachineName, 
                ExecutionGuid, LogicalDate, Operator, StartTime, EndTime, Status,FailureTask)
            Values(@ParentLogID, @Description, @PackageName, 
                cast(@PackageGuid as uniqueidentifier), @MachineName, 
                cast(@ExecutionGuid as uniqueidentifier),  @logicalDate, 
                @operator, getdate(), null, @status, null)

            Set @LogID = scope_identity()
    Commit Tran
End try

Begin Catch
    Select Error_Message();
    Rollback Tran
End Catch

Need Your Help

How to load image from a redirected url in android

android image url redirect imageview

I am trying to load image in ImageView. The URL for image,when I hit it on web browser, it redirects me to another link for the image.

How I can access to WSDL 1.1 file from ASP.NET 3.5

asp.net service wsdl

I have a WSDL 1.1 file which describes my Web Service. Now I need get access to its methods using ASP.NET 3.5