SQL Server Performance

Global variable not setting

Discussion in 'SQL Server DTS-Related Questions' started by nilayinc, Oct 28, 2003.

  1. nilayinc New Member

    I have a DTS job which loads the data into the SQL Server. First step is to log "that job has started.." and on success or failure update the status in the log. And since primary key of table is Identity column, I need to store the key in global variable.

    In order to achieve this, I am using Execute SQL Task Properties and SQL as follows:

    insert into Interfacelog
    (InterfaceTime, InterfaceFile, Type, Description,
    NoOfRecords, ErrorRecords, Status, ModifiedBy, ModifiedDate)
    values(getdate(), 'MICSUBS','I','Item Substitution File',
    0, 0, 'F', 'Admin', getdate())

    select ? = @@identity

    And mapped global parameter in the parameters.
    But after executing this, global variable doesnt contain the updated value.

    Any thoughts?

    Nilay.
  2. Dave Wells New Member

    A shot in the dark, but it might be worth trying SCOPE_IDENTITY() rather than @@IDENTITY?
  3. ykchakri New Member

    @@IDENTITY and SCOPE_IDENTITY() are limited to session and scope respectively. IDENT_CURRENT is not limited to scope or session. You may want to try using that.
  4. nilayinc New Member

    I got it figured out.

    I made following changes and it worked..

    set nocount on
    .
    .
    .
    select @@identity as InterfaceID

    and InterfaceID became the output parameter which I then mapped to global variable.

Share This Page