SQL Server Performance Forum – Threads Archive

Global variable not setting

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.

---

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

---

@@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.

---

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.

---

]]>