Problems with OPENQUERY and RDL (Reporting Services SSRS) SQL SERVER

Question

I currently have the following script

DECLARE @TSQL varchar(8000), 
        @VAR NVARCHAR(20)
SELECT @VAR = '500205728'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(BDTEST,''SELECT * FROM DEL d WHERE d.DCC = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

which only searches that table for the record number and in SQL it works perfectly (as a query and as a Stored Procedure), the problem is when I want to pass said query or SP to Reporting Services, I am using Report Builder, when I copy the query I get this window

and when I accept, it generates the DATASET without fields

I don't understand why this happens if in SQL it works perfectly, I don't know if I'm putting something wrong or missing, if someone could help me how to make Reporting Services read this, I already tried to create that query as a stored procedure and even so It gives the same error and I need to do it that way since it is the fastest

Answer 1

Hi,

Your code is wrong regarding the number of quotation marks, which you use.

You should always PRINT the query and check how it is looks like when using dynamic query.

if you will try to run the following code (printing your query), then you will see what query is actually pass to the server and you will notice that it is wrong

DECLARE @TSQL varchar(8000), 
        @VAR NVARCHAR(20)
SELECT @VAR = '500205728'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(BDTEST,''SELECT * FROM DEL d WHERE d.DCC = ''''' + @VAR + ''''''')'
PRINT (@TSQL)

The result is this test which is not well formatted SQL :

SELECT * FROM OPENQUERY(BDTEST,'SELECT * FROM DEL d WHERE d.DCC = ''500205728''')

Try to use this:

SELECT @TSQL = 'SELECT * FROM OPENQUERY(BDTEST,''SELECT * FROM DEL d WHERE d.DCC = ' + @VAR + ''')'

Fix your quotation marks and you should get the expected result set probably

Answer 2

Hi @Marco Chavez

In fact, I'm not particularly familiar with T-sql. According to the window you jumped out, it seems that the @Var value you set is not passed.

I did a simple test, created a Linked server, and used a statement similar to yours, but it works in the report builder. Of course, the stored procedure I created can also be used. What is your statement to create a stored procedure?

For more details, you can refer to this link: https://stackoverflow.com/questions/3378496/including-parameters-in-openquery.

I don't know why your value is not being passed. Can you tell me your relevant environment configuration?

Best regards,

Aniya

Answer 3

I believe that the problem is that Report Builder wants to find out the shape of the result set before running the query.

This might work

EXEC(@sql) WITH RESULT SETS ((col1 int,
                              col2 int,
                              col3 varchar(23)))

Replace the names and the types with the actual columns and data types from the query.

Although, I'm not fully sure that this works. This might only work if Report Builder is using sp_describe_first_result_set, and not the only SET FMTONLY ON. And I have a nagging feeling that it does...

The other option is to define a table variable (not a temp table) and insert the result into the temp table and then return the result from the temp table.