How to list fields from a dynamic SQL query in an SSRS dataset

Question

I run the following query in SSRS. If I add declarations for two parameters, it works fine in the SQL Management Console.

declare @EMRQuery varchar(max) declare @CPSQuery varchar(max) set @EMRQuery = 'select Person.ExternalId from ml.Person join ml.Obs on Person.pId = Obs.pId join ml.ObsHead on Obs.hdId = ObsHead.hdId where ObsHead.name = ''SCHOOLREGDTE'' and Obs.xId = 1.e+035 and Obs.change = 2 and Obs.obsDate >= to_date(''' + convert(varchar(30), @DateYearStart, 120) + ''', ''YYYY-MM-DD HH24:MI:SS'') and Obs.obsDate < to_date(''' + convert(varchar(30), @DateQuarterEnd, 120) + ''', ''YYYY-MM-DD HH24:MI:SS'')' set @CPSQuery = 'select ic.ListName, count(distinct pp.patientprofileid) as PatCount from PatientProfile pp left join PatientInsurance pi on pp.PatientProfileId = pi.PatientProfileId and pi.OrderForClaims = 1 and pi.Inactive <> 1 left join InsuranceCarriers ic on pi.InsuranceCarriersId = ic.InsuranceCarriersId join OpenQuery(EMR_LIVE , ''' + replace(@EMRQuery, '''', '''''') + ''' ) Students on pp.PatientId = Students.ExternalId group by ic.ListName ' exec(@CPSQuery) 

However, when I connect it to SSRS, it does not register that any fields are available for the report. How can I convince SSRS that I have fields for work? Thanks.

Edit: I just declared the parameters in the request and recognized the field names.

 declare @DateYearStart datetime declare @DateQuarterEnd datetime set @DateYearStart = '2011-07-01' set @DateQuarterEnd = '2012-03-31' 

Of course, this is a mistake because I declared the parameters twice, once as request parameters and once in the request. But, as soon as I commented on the above lines, I again lost the fields.

Answer 1

Someone on the MSDN forums suggested I select a temporary table, so I replaced the final exec statement

 declare @CarrierList table (Listname varchar(200), PatCount int); insert @CarrierList exec(@CPSQuery) select * from @CarrierList 

and now it works fine.

Answer 2

maybe something is wrong with your request. SSRS datasets can pretty much value everything you invest in them. For example, I just did a little test with these two queries:

 declare @a varchar(500) declare @b varchar(500) set @a = '(select name from sys.tables) B' set @b = 'select B.name as name2 from ' +@a exec(@b) 

and my dataset recognized the name of field2.

I advise you to review your request.

If this really does not work, you can try adding your code to the SQL Server procedure, which returns a table with the specified field names.

Answer 3

Since SSRS executes the stored procedure with SET FMTONLY ON; when you click update button. Then put SET FMTONLY OFF; as the first line in your stored procedure, and it will return your fields.

Answer 4

How do you convince SSRS that you have fields for work? Answer. Manually add fields to the dataset from the properties of the dataset ... Fields. This will make the designer happy and as long as the fields exist when the query is executed, the report will work. At least this worked for me using an OLE DB query for DB2 with dynamic query text.

Answer 5

I suspect this is due to the fact that there are two requests in your code, although only one needs to be executed. Therefore, I suggest combining two queries into one query.