Forum Discussion

SAS_BI's avatar
SAS_BI
Advocate I
5 years ago

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

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion

    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