Passing a parameter to an SSRS DataSet using an EXEC ... AT query against a linked server

I created a report that is using an EXEC ... AT query to a linked server.  The code below works like a charm in the dataset:
 
DECLARE @myPatIEN AS VARCHAR(8) = '646040'
DECLARE @myStartDate AS VARCHAR(25) = CAST(CAST(GETDATE()-1 AS DATE) AS CHAR(10)) + ' 00:00:00'
DECLARE @myEndDate AS VARCHAR(25) = CAST(CAST(GETDATE() AS DATE) AS CHAR(10)) + ' 23:59:00'
 
 
EXECUTE('SELECT 
PATIENT->NAME AS PatientName,
ID_NUMBER AS OrderIdNumber,
ORDER_TYPE->NAME AS OrderType,
INACTIVE_STATUS AS Status,
ORDERING_HCP->NAME AS OrderingHCP,
PATIENT_LOCATION->NAME AS HospitalLocation
FROM CHCS.ORDER_101
WHERE (PATIENT = ?)
AND (ORDER_DATETIME BETWEEN ? AND ?)', @myPatIEN, @myStartDate, @myEndDate) AT LinkedServerName;
 
Now I'd like to be able to pass the report the patient IEN, but as soon as I remove the "DECLARE @myPatIEN AS VARCHAR(8) = '646040'"
line, the fields all disappear, presumably because the query wants an IEN to be able to retrieve a dataset so it knows what fields
will be returned.

I've tried setting up the @myPatIEN parameter in the Parameter pane of the Dataset Properties dialog box, but still have the same
problem with the fields disappearing.

I also tried setting up a Parameter at the report level and trying to bind that parameter to @myPatIEN, to no avail.

Has anyone else run into this situation and come up with a solution?

Thanks in advance for any assistance.

Christian Bahnsen

I created a report that is using an EXEC ... AT query to a linked server.  The code below works like a charm in the dataset:
 
DECLARE @myPatIEN AS VARCHAR(8) = '646040'
DECLARE @myStartDate AS VARCHAR(25) = CAST(CAST(GETDATE()-1 AS DATE) AS CHAR(10)) + ' 00:00:00'
DECLARE @myEndDate AS VARCHAR(25) = CAST(CAST(GETDATE() AS DATE) AS CHAR(10)) + ' 23:59:00'
 
 
EXECUTE('SELECT 
PATIENT->NAME AS PatientName,
ID_NUMBER AS OrderIdNumber,
ORDER_TYPE->NAME AS OrderType,
INACTIVE_STATUS AS Status,
ORDERING_HCP->NAME AS OrderingHCP,
PATIENT_LOCATION->NAME AS HospitalLocation
FROM CHCS.ORDER_101
WHERE (PATIENT = ?)
AND (ORDER_DATETIME BETWEEN ? AND ?)', @myPatIEN, @myStartDate, @myEndDate) AT LinkedServerName;
 
Now I'd like to be able to pass the report the patient IEN, but as soon as I remove the "DECLARE @myPatIEN AS VARCHAR(8) = '646040'"
line, the fields all disappear, presumably because the query wants an IEN to be able to retrieve a dataset so it knows what fields
will be returned.
I've tried setting up the @myPatIEN parameter in the Parameter pane of the Dataset Properties dialog box, but still have the same
problem with the fields disappearing.
I also tried setting up a Parameter at the report level and trying to bind that parameter to @myPatIEN, to no avail.

I guess my first question would be: why not just create a direct data source to the server rather than using a linked server?

If for some reason you can't create a direct data source then I would probably create a stored procedure to query your linked server on the server that you do have a direct data source and then use the stored procedure as your report data source.

Daniel,

Thanks for the suggestion.  This report is against a table in an instance of InterSystem's Cache running CHCS.  Per our DBA, a linked server is the best way to connect from SQL Server.

My solution was to

1) declare a Report Parameter named "PassedIEN" (text with a default value),

2) then declare a parameter named "@myParameter" in the Dataset Properties Parameters pane,

3) bind the Dataset parameter to the Report Paramater,

4) then tweak the query, using the Dataset parameter thusly: DECLARE @myPatIEN AS VARCHAR(8) = @myParameter

If anyone has a more elegant solution, please share.  This worked for me.