I've got a query like below

declare @str_CustomerID int
Insert into IMDECONP38.[Customer].dbo.CustomerMaster
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′
)

select @str_CustomerID= scope_identity()

After execution it returns null during my parameter.

I wish to get the need for identity. How do i do this?

The primary problem right here is "IMDECONP38" - the server title which i used. Basically remove i could possibly get the need for identity during my parameter.

See this old question for the same problem: You can't retrieve a scoped variable like SCOPE_IDENTITY() from another server. Rather, you need to use a saved procedure around the remote server to do this.

If you use "IMDECONP38" then you definitely break SCOPE_IDENTITY because

  • the Place scope has become around the IMDECONP38 linked server
  • SCOPE_IDENTITY works on the local server, not IMDECONP38

If on SQL Server 2005, try the OUTPUT clause but I am unsure how it operates for any linked server call

Insert into IMDECONP38.[Customer].dbo.CustomerMaster
OUTPUT INSERTED.ID   --change as needed
( CustomerName , CustomerAddress , CustomerEmail , CustomerPhone )
values ( ‘werw12e’ , ‘jkj12kj’ , ‘3212423sdf’ , ‘1212121′
)

Edit: Prutswonder stated it first: make use of a saved proc around the linked server

Make use of a saved procedure within the remote database.

CREATE PROCEDURE InsertCustomer (@name varchar(100), @address varchar(100), 
    @email varchar(100), @phone varchar(100), @id int OUT)
AS
    INSERT INTO dbo.CustomerMaster 
    (CustomerName , CustomerAddress , CustomerEmail , CustomerPhone ) 
    VALUES (@name, @address, @email, @phone)

    SET @id = SCOPE_IDENTITY()
GO

DECLARE @id int
EXEC IMDECONP38.Customer.dbo.InsertCustomer 'Fred','Bedrock','a@b','5',@id OUT
GO
select @@IDENTITY AS 'variablename'

Book if you will find any triggers looking for your table. This makes an issue if you use SCOPE_IDENTITY() to find the last placed identity area.

HTH