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

Improve this question
5
  • 3
    OPENROWSET does not support variables, period. Only string literals. Doing anything dynamic with OPENROWSET requires dynamic SQL. Commented Dec 13, 2019 at 12:00
  • @JeroenMostert So I need to use exec? Commented Dec 13, 2019 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. Commented Dec 13, 2019 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... Commented Dec 13, 2019 at 12:05
  • 2
    Does this answer your question? Using a Variable in OPENROWSET Query Commented Dec 13, 2019 at 12:13
Related questions
1
SQL Server syntax error when using OPENROWSET?
37
Using a Variable in OPENROWSET Query
4
SQL SELECT * FROM OPENROWSET with Variable
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.