need help with openquery

Question

Hi Guys

I have an openquery which is used for fetching data from a stored procedure from a linked server.
This query works perfectly in sql server.
However i have to use the same query and create a SSRS report with different dates.
I am not sure how to do it.

Any help can be appreciated.

My SQL Query which works perfectly in sql server and fetches data from linked server

Select * From OPENQUERY (LIVEDB02, 'exec Cat.dbo.usp_DEF_Weekly_Statements @store =NULL,@StDate = ''2021-12-21'', @EdDate = ''2021-12-21'' ');

How do i create a ssrs datset which accepts openquery and creates parameters

Any question please let me know
REgards
Farhan Jamil

Answer 1

SSRS datasets can run any query you want so OPENQUERY shouldn't be an issue. Use the query type Text and paste in your query. The designer will attempt to run the query to get the columns. It is probably better to explicitly specify the column names instead of using a wildcard (in general a good DB recommendation). To allow parameters change the query from using a hard coded value to the parameter name like @startDate. To avoid conflicts with the sproc names either use different parameter names or leave off the parameter names in the sproc call altogether. Honestly I've never tried using OPENQUERY so not sure what all it requires. We generally set up a synonym on the local DB server that points to the same sproc on the remote server and then call the local server and let SQL handle the linked call itself.

There are posts online where folks are doing this. However the biggest concern seems to be the performance isn't great. Refer to this article on one possible solution to that but it might not be an issue in your case.

Answer 2

It seems from the screenshots that you are trying to run something like:

   Select * From OPENQUERY (LIVEDB02, 'exec Cat.dbo.usp_DEF_Weekly_Statements @Store=NULL,@StDate = ''@StDate', @EdDate = ''@EdDate'' ');  

That will not work out. What you pass to OPENQUERY is an exact query string; you cannot pass parameters. What you are doing here is to pass the string '@StDate', and that string does of course not convert to a date or a number.

To get the parameter values into the query string on the remote server, you would need to build the string with dynamic SQL.

Then again, you can just as well call the procedure directly with:

   EXEC LIVEDB02.Cat.dbo.usp_DEF_Weekly_Statements @Store=NULL,@StDate = @StDate, @EdDate = @EdDate  

Now if you want SSRS to automatically detect the shape of the result set, I'm afraid that I will have to pass on that one, since I don't know SSRS.

Then again, why not connect to LIVEDB02 directly?

Answer 3

Hi @Farhan Jamil

OPENQUERY does not accept variables for its arguments.
OPENQUERY cannot be used to execute extended stored procedures on a linked server.

For more infomation：https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

And I did a local test，not using the store procedure，just use ‘select’，and I can get result succeed,here is my QUERY STATEMENT，you can take a reference .

DECLARE @TSQL varchar(8000), @VAR char(3)  
SELECT  @VAR = (@color)  
SELECT  @TSQL = 'SELECT * FROM OPENQUERY(NODE1,''SELECT * FROM dailyTest.dbo.category WHERE catename = ''''' + @VAR + ''''''')'  
EXEC (@TSQL)  

Here is the THREAD I refer to :https://stackoverflow.com/questions/3378496/including-parameters-in-openquery

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Farhan Jamil

You can put the parameters out of the OPENQUERY ，for OPENQUERY does not accept variables for its arguments.
For more infomation，please refer to：
https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

I did a local test，and it can run successfully in Report Builder ，you can take a reference.

First ：CREATE DATASOURCE

Second：CREATE DATASET

EXEC LIVEDB02.Cat.dbo.usp_DEF_Weekly_Statements NULL,@StDate, @EdDate  

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hey Guys

Thanks for your help.
However I made it extremely simple.

What I have done is

1. I had a parametrised SP in linked server. I got rid of parameter dependancy as it wasnt required.
2. Next I created a new SP without parameter dependancy
3. Ran the sp both from sql using linked server and in ssrs. both worked fine for me.

Regards
Farhan Jamil