cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Passing parameters to a linked server query - need help

Good morning, all!  Brand new to the Power BI world, and am trying to convert reports from our existing reporting system over to Power BI.  We have a lot of data stored on Sybase databases (which we're linked to via SQL linked servers), and I have a question on how to pass parameter values in the query.  I'm building this in Power BI Report Builder, so if this is the wrong forum, mods please feel free to move it.......

 

I found this article: https://support.microsoft.com/en-us/help/314520/how-to-pass-a-variable-to-a-linked-server-query which helped me with the basic syntax of the query process, but for the life of me, I can't seem to find any info on how to set variables equal to my parameters.  This is how I would imagine the query would look:

 

DECLARE @TSQL varchar(8000),

DECLARE @parStart int,

DECLARE @parEnd int

 

SELECT @parStart = heatStart (first parameter)

SELECT @parEnd = heatEnd (second parameter)

SELECT @TSQL = 'SELECT * FROM OPENQUERY([Linked Server], ''SELECT *
FROM database.dbo.tablename
WHERE heat_no >= @parStart

AND heat_no <= @parEnd'')'
EXEC (@TSQL)

 

I know the use of the single quotes within the query text has a lot to do with the format (need a bit of help with those as well), but the main issue, is how to set the variables equal to the parameters in the first place.

 

Thanks in advance for any help provided!

 

Scott

 

 

1 REPLY 1
Super User I
Super User I

Hi @SAS_BI ,

Let's see if @ImkeF is available.  She is a Power Query Wonder Woman!


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors