empty data explorer when use store procedure

[Hans]

Hi,

In Visual Studio 2010 I added a data source to sql server 2008 r2 . In Configure DataSource Command screen I added a store procedure, I gave the parameters, I press the button Execute query, it shows nice the results but when I click Finish, no fields are shown in Data Explorer.
I did some tests and I discover when I have a stored procedure that internal use temporary tables, and the last select is from temporary table, it not shows the fields in data explorer.
Example
-------------------this doesn't show the field department in data explorer -------------
create procedure TEst_Department
as
BEGIN
SET NOCOUNT ON


select 'D1' as Department into #temp1

select * from #temp1

 END

 --------------this shows the data in data explorer

create procedure TEst_Department
as
BEGIN
SET NOCOUNT ON

select 'D1' as Department into #temp1

DECLARE @dept TABLE
(
    department NVARCHAR(10)
)


insert into @dept (department)
Select Department from #temp1

select * from @dept

 END

----------------------------------------------------------------


I have a pretty complicated stored procedure with a lot of temporary tables and at the end I have to add a Table variable and I have to insert the results in this table variable in order to work with your reports

Any thoughts ?

Regards,

Hans

[Stef]

Hi Hans,

This is a know limitation related to Microsoft as stated by my colleague here and the solution is to add:

IF 1=0 BEGIN
    SET FMTONLY OFF
END

at the beginning of your store procedure which uses temporary tables.

i hope this helps.

Greetings,
Stef
the Telerik team