openquery and dynamic sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

openquery and dynamic sql

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-30 : 09:05:34

I have an openquery with dynamic sql that works perfectly fine in Management studio; however, when I try to add the code into visual studio, i'm getting an error on the report designer that says "An Error occurred while the query design method was being saved. Conversion failed when converting the varchar value 'Select Statement' to datatype int. I have no idea how it could run in management studio, but get stopped here in report designer.

--- sample query

DECLARE @abc VARCHAR(MAX)
DECLARE @sql_str NVARCHAR(4000)
DECLARE @efg VARCHAR(MAX)
SET @abc = '856969'
SET @efg = '55536'

SET @sql_str = 'SELECT column1
,column2

,column3

,column4

FROM tb1 where abc = ''' + @abc + '''
and efg = ''' + @efg + ''''

SET @sql_str = 'select * from OPENQUERY(linkedServer, ''' + REPLACE(@sql_str,
'''', '''''')
+ ''')'

--PRINT @sql_str

EXEC (@sql_str)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-30 : 09:47:03

Don't knkow if it will help here but it's often easier to run it as
exec (@sql) at linkedserver

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-30 : 10:27:15

hi, i'm not sure I understand.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-30 : 11:22:29

If your query is really that simple you don't need dynamic SQL at all, just use 4-part names:

DECLARE @abc int;
DECLARE @efg int;
SET @abc = 856969;
SET @efg = 55536;

SELECT column1,column2,column3,column4
FROM linkedServer.dbname.dbo.tb1 
WHERE abc = @abc AND efg = @efg;

Also, don't declare a variable varchar(max) if you're going to concatenate it with an nvarchar(4000) value. Never use max unless you are certain you will exceed the 4000/8000 character limit, and don't mix them with non-max variables.

Lastly, if you have to use dynamic SQL you can most likely use sp_executesql: http://msdn.microsoft.com/en-us/library/ms188001.aspx

The only reason you'd truly need dynamic SQL is if you had to change syntactical structures, like adding a dynamic WHERE clause or GROUP BY. For variable substitutions you don't need it, use sp_executesql instead.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-30 : 11:24:06

Why not put it in a stored procedure and use that in the report?
Saves trying to embed code in the client.

Instead of
SET @sql_str = 'select * from OPENQUERY(linkedServer, ''' + REPLACE(@sql_str,
'''', '''''')
+ ''')'
EXEC (@sql_str)

you could have
exec (@sql_str) at linkedServer

The report will try to get the format of the resultset without executing the statement fully - probably not possible with what you have.
Usual way around this is to put at the begining of the SP

set nocount on
if 1=0
select column1 = space(20), column2 = 1, column3 = getdate() where 1=0

It never gets executed but the client will use it for the format of the resultset and as long as it matches what is returned when executed then everything is happy.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-30 : 14:09:52

Thanks for your help, i'll give this a shot and see if it works. I have to get my data from a linked server and the only way to pass parameters into a linked server is by using dynamic query - at least that is the only thing that has worked for me thus far.

joe8079
Posting Yak Master

127 Posts

Posted - 2012-07-30 : 14:21:44

when I try exec (@sql_str) at linkedServer
, I get the following

Msg 7411, Level 16, State 1, Line 163
Server 'LInkedServer' is not configured for RPC.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources