CODEWITHSUNDEEP
sql-serveropenquery
Andrew
Andrew

Reputation: 2715

Why does OPENQUERY not accept variables?

I am well aware that MS SQL Server does not allow variables to be used in the OPENQUERY statement and I'm aware of the workarounds.

What I'd like to know is, does anyone know WHY variables or concatenated strings are not allowed?

The hoops this causes people to jump through is simply astounding and it just isn't in line with other functions such as EXEC.

Can anyone comment on this? Who do I have to bribe at MS to get his sorted out?

Upvotes: 4

Views: 4979

Answers (2)

kyle
kyle

Reputation: 19

declare @v1 varchar(max)

EXEC('SELECT * FROM OPENQUERY (<linkedserver>, ''SELECT * FROM <table> WHERE <column> = ''''' + @v1 + ''''''')')

Upvotes: 1

Dan S
Dan S

Reputation: 1206

The limitation is caused by the way linked servers are implemented in SQL Server, and the way its dependant libraries work. There is a whole chain of libraries used to pass your SQL Command, all of them have different variable sizes and cache sizes. Some of the limitations are in the OCBC drivers themselves, so 8k is a safe bet from Microsoft. Not allowing you to pass variables just enforces the 8k limit rule.

Upvotes: 4

Related Questions