OPENQUERY query stored in string fails with EXEC

Question

I'm trying to pull back some data via a linked server that has 'Geography' fields present, consequntly I'm trying to use Open Query. I'm also trying to pass in a variable...

Can anyone explain this: This is my sql...

DECLARE @Sql VARCHAR(200)
DECLARE @tnum VARCHAR(20)= 'abc';
SET @Sql = 'SELECT * FROM NationalPolygon.dbo.Polygon WHERE TitleNumber = ''''' + @tnum + '''''';
SET @Sql = 'SELECT * FROM OPENQUERY(mylinkedserver01, ''' + REPLACE(@Sql, '?', '''') + ''')'

SELECT @Sql;
EXEC @Sql;

If I select @Sql and run it, it works. If I run the @Sql via EXEC it fails with:

Database 'SELECT * FROM OPENQUERY(mylinkedserver01, 'SELECT * FROM NationalPolygon' does not exist. Make sure that the name is entered correctly.

Thanks C

There are 2 different syntax forms for EXECUTE. One executes the query stored in a string - that requires parentheses around the string (or variable). — SMor
– SMor, Commented May 14, 2021 at 12:07

d0little · Accepted Answer · 2021-05-14 12:10:52Z

1

You need to surround @Sql with brackets

EXEC (@Sql);

answered May 14, 2021 at 12:10

d0little

4764 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Columbo Over a year ago

Ta, that's sorted it.

Collectives™ on Stack Overflow

OPENQUERY query stored in string fails with EXEC

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related