Hi guys

Can anyone please help me. I need to know how to pass an integer value to an openquery (linked server) statement. I know how to pass a string value. But when i pass try to pass integer values i get all sorts of errors.

Thanx again

avi

OpenQuery requires the statement to be a literal and does not permit variables

Nonetheless, you could wrap the OpenQuery statement inside of a sp_ExecuteSQL call, or an Exec('') call.

-- Here is how OpenQuery normally works -- no parameters nothing generating variably
SELECT * FROM OPENQUERY(LocalServer,'SELECT * FROM pubs.dbo.Employee WHERE [job_id]> 1')

-- Here is how to wrap OpenQuery inside a sp_ExecuteSQL or Exec statement
-- We can't pass in parameters to the sp_ExecuteSQL because it will give us an error about not being
-- able to prepare the deferred query. So we must build our parameters into the SQLString
DECLARE @StrSQL nvarchar(4000)
DECLARE @jobid smallint
DECLARE @ParmDef NVARCHAR(500)

SET @jobid = 1 -- This is the parameter you are getting
SELECT @StrSQL = N'SELECT * FROM OPENQUERY(LocalServer,''SELECT * FROM pubs.dbo.Employee WHERE [job_id]> ' + CAST(@JobID as nvarchar(30)) + ''')'
EXEC sp_ExecuteSQL @StrSQL


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

Thanx alot mr David Lundell

It works brilliantly.

Thanx Again
aven