| Author |
 Topic  |
|
|
joe8079
Posting Yak Master
USA
113 Posts |
 Posted - 07/30/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/30/2012 : 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
USA
113 Posts |
Posted - 07/30/2012 : 10:27:15
|
| hi, i'm not sure I understand. |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 07/30/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/30/2012 : 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
USA
113 Posts |
Posted - 07/30/2012 : 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
USA
113 Posts |
Posted - 07/30/2012 : 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. |
|
|
| |
Topic |
|
openquery and dynamic sql
