SQL Server Data Source Issue

[mws]

I am trying to use a stored procedure with temp tables as a data source. The 'View Query' function works correctly, returning records. If I click on 'Retrieve Columns', I get an error message 'Invalid Object Name '#tSample.

Here is a very simple version of the stored procedure...

Create Procedure xTest AS
SET NOCOUNT ON
create table #tSample ( setting_value varchar(5) )
insert into #tSample values('test')
select * from #tSample

GO

[Vital]

At this moment StimulReport.Net does not support function "retrieve columns" for stored proc with temp tables. We hope fix this problem soon.

Thank you.

[rknunna]

Create Procedure xTest AS
SET NOCOUNT ON
create table #tSample ( setting_value varchar(5) )
insert into #tSample values('test')
select * from #tSample

GO

You can use to solve the problem.

Create Procedure xTest AS
SET NOCOUNT ON
declare @tSample table ( setting_value varchar(5) )
insert into @tSample values('test')
select * from @tSample

GO

[Vital]

In latest prerelease builds this problem already solved.

Thank you.

[Vital]

In latest prerelease builds function "Retrieve columns" works fine for stored proc.

Thank you.

[adinac]

Hello,

I am experiencing the same problem in the MVC designer. Is this not fixed or implemented for mvc? I am using Version=2013.1.1600.0.

Thanks.

[Alex K.]

Hello,

Unfortunately, but retrieve columns does not work directly in Web designer.
As a way you can create datasource with stored procedure with parameters in standalone designer and then uses it in web version.
We have added this issue in our to-do list.

Thank you.

[adinac]

Hello,

I have also tried it from Demo.exe and still get the same error:(
If I make 'view data', data is displayed properly.

Thanks

[Alex K.]

Hello,

Can you please send us a sample procedure with test data which reproduce the issue for analysis.

Thank you.

[adinac]

Hello,

Back from vacation with additional information.
This would be the script to create the db and everything necessary for this problem:

Code: Select all

create database TestTemporaryTable;
go
USE [TestTemporaryTable]
GO
CREATE TABLE [dbo].[myTable](
	[serial] [int] NULL,
	[locationname] [nvarchar](50) NULL,
	[statusid] [int] NULL,
	[locationid] [int] NULL
) ON [PRIMARY]
GO


insert into myTable values (1, null, 3, null);
insert into myTable values  (2, 'asa', 3, 5);
go 
CREATE PROCEDURE [dbo].[TestProcedure]
	(@locationname varchar(50) = 'All Locations')
AS
begin
	declare @locationid int;
	begin try
	drop table #tempDuedates
	end try
	begin catch
	end catch
	select * into #tempDuedates from myTable
	
	select @locationid = locationid from
	(
	select distinct locationid, locationname,2 as ordernb  from #tempDuedates
	where statusid = 3 
	) t
	where t.locationname like @locationname
END
GO

Thanks,

Adina