How to list fields from dynamic SQL query in SSRS dataset

Question

I'm running the following query in SSRS. If I add declarations for the two parameters, it runs fine in 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 plug this in to SSRS, it doesn't register that there are any fields available to report on. How do I convince SSRS that I do have fields to work with? Thanks.

Edit: I just declared the parameters in the query, and it recognized the field names.

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

Of course, that errored because I was declaring the parameters twice, once as query parameters and once within the query. But, as soon as I commented out the lines above, I lost the fields again.

Answer 1

Someone over at the MSDN forums suggested I select into a temp table, so I replaced the final exec statement with

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

and it seems to be working fine now.

Answer 2

How do you convince SSRS that you have fields to work with? Answer: Manually add the fields to the dataset from Dataset Properties...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 it worked for me using an OLE DB query to DB2 with dynamic query text.

Answer 3

there is probably something wrong with your query. SSRS datasets can pretty much evaluate anything you put into them. FOr example, I just ran a little test with these 2 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 datase set did recognize the field name2.

I advise you to review your query.

If it really doesnt work, you can try to add your code to a SQL Server procedure that returns a table with pre-defined fields names.

Answer 4

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