Checking for existance of a remote row

Question

Hi

I need to check if a row for SELECT 1 FROM Staff WHERE ID = @ID exists on a remote SQL Server. I am trying below code;

Declare @ID int = 10  
Declare @STAFFID int = -1  
Declare @sql nvarchar(max)  
  
Set @sql='SELECT @STAFFID = ID   
          FROM OPENROWSET(''SQLNCLI'',''...''; ''...''; ''...'',''SELECT * FROM Staff where ID = ' + convert(varchar(10), @ID) + ''')'  
  
Exec(@sql)  
  
IF @STAFFID = -1  
  PRINT 'Not Found'  
ELSE  
  PRINT 'Found!!!'  

However I am getting a 'Must declare the scalar variable "@STAFFID"' error. How can I check if the remote row exits?

Thanks

Regards

Answer 1

Hi

This works for me.

IF EXISTS (SELECT 1
FROM OPENDATASOURCE('SQLNCLI11', 'SERVER=...;DATABASE=MyDB;UID=...;PWD=...;').MyDB.dbo.Staff
WHERE ID = @ID)
  PRINT 'Found!'
ELSE
  PRINT 'Not Found!'

Regards