1

I'm trying to declare connection as a variable and use it in a query:

DECLARE @connStr VARCHAR(MAX) = 'server=xxxxx'
DECLARE @table VARCHAR(MAX) = 'xxxxx'

BEGIN
DELETE FROM @table;
INSERT @table
SELECT * FROM OPENROWSET(
    'SQLNCLI', 
    @connStr, 
    'SELECT TOP 10 * from xxxx ORDER BY xxxx DESC'
);
END;

And it results in syntax error.

What's wrong with @connStr? I'm using SQLServer 2012

daisy
  • 22,498
  • 29
  • 129
  • 265
  • 3
    `OPENROWSET` does not support variables, period. Only string literals. Doing anything dynamic with `OPENROWSET` requires dynamic SQL. – Jeroen Mostert Dec 13 '19 at 12:00
  • @JeroenMostert So I need to use `exec`? – daisy Dec 13 '19 at 12:00
  • If you really need the connection string to be variable, yes. And certainly if `@Table` is also intended to be a variable holding a table name (which will not work either). In that case, you are much, much better off doing this client-side, as T-SQL does *not* like dynamic result sets, and having multiple levels of escaping is no picnic either. – Jeroen Mostert Dec 13 '19 at 12:02
  • If `@table` *is* supposed to represent a dynamic table's name, why is is a `varchar(MAX)`? An object name can be at most 128 characters long, not 2Billion... – Thom A Dec 13 '19 at 12:05
  • 2
    Does this answer your question? [Using a Variable in OPENROWSET Query](https://stackoverflow.com/questions/13831472/using-a-variable-in-openrowset-query) – SMor Dec 13 '19 at 12:13

0 Answers0