|
Subject:
|
Openquery statements
|
|
Posted By:
|
aven
|
Post Date:
|
1/10/2006 6:28:17 AM
|
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
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/10/2006 11:27:42 AM
|
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
|
|
Reply By:
|
aven
|
Reply Date:
|
1/11/2006 12:33:35 AM
|
Thanx alot mr David Lundell
It works brilliantly.
Thanx Again
aven
|
