Using SSRS 2008 with Linked Server and Parameters

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

Analysis Server and Reporting Services (2008)

Using SSRS 2008 with Linked Server and Parameters

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2012-08-27 : 14:18:26

I have a query that pulls data from an Oracle Linked Server and it uses two parameters. The query runs perfectly fine in management studio, but when I try to use it in SSRS I get the following error:

The error message is directly below and the code from the linked server with parameters is directly below that. Again, the code works perfectly fine in management studio, but fails as soon as I try to enter it through SSRS. If anyone can help out that would be greatly appreciated.

-- ERROR

MICROSOFT SQL SERVER REPORT DESIGNER

X AN ERROR OCCURRED WHILE THE QUERY DESIGN METHOD WAS BEING SAVED. CONVERSION FAILED WHEN CONVERTING THE VARCHAR
VALUE 'SELECT columnX

,Code
,ID
columnY

FROM table1 where ID = " to data type int.

-- ERROR

--CODE TO GET DATA FROM LINKED SERVER - Works fine in management studio but fails in SSRS

-- Declare two parameters ID and Code. Both are Varchar
DECLARE @ID VARCHAR(MAX)
DECLARE @sql_str NVARCHAR(4000)
DECLARE @Code VARCHAR(MAX)
SET @ID = '111222' -- ID Number. 6 Characters long
SET @Code = '08865' -- Code. 5 Characters Long

SET @sql_str = 'select columnX

,Code
,ID
columnY

FROM table1 where ID = ''' + @id + '''
and code = ''' + @Code + ''''

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

--PRINT @sql_str

EXEC (@sql_str)

--CODE TO GET DATA FROM LINKED SERVER - Works fine in management studio but fails in SSRS

joe8079
Posting Yak Master

127 Posts

Posted - 2012-08-27 : 14:46:24

I actually just resolved this:
Basically, when putting the parameter values into SSRS, I had to enclose them in double quotes and that seems to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources