Dataset doesnt show fields with pocedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Dataset doesnt show fields with pocedure

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-08-26 : 07:54:12

Hi All,

I have a procedure which calculates percentage. so i have two temp tables.
One to fetch the data from DB and the other to store the calculation.
finally my result set would be

select * from #temp

The same procedure when run with a table variable (@temp) , works fine and dataset also shows the fields.

I found this strange. will usage of temporary tables cause this problem?

Please suggest me.

here is a sample:

1st code does not work. the data set will be blank. The 2nd one works fine.
***************************************************************************************
create proc temptabletest
as
begin
create table #temp
(id int,
name varchar(20))

insert into #temp
select 1,'AAA' union all
select 2,'BBB' union all
select 3,'CCC' union all
select 4,'DDD'

select * from #temp
end

*****************************************************************************
create proc tablevariabletest
as
begin
declare @temp table
(id int,
name varchar(20))

insert into @temp
select 1,'AAA' union all
select 2,'BBB' union all
select 3,'CCC' union all
select 4,'DDD'

select * from @temp
end

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-26 : 08:16:26

Huh -- works fine for me



create proc temptabletest
as
begin
create table #temp
(id int,
name varchar(20))

insert into #temp
select 1,'AAA' union all
select 2,'BBB' union all
select 3,'CCC' union all
select 4,'DDD'

select * from #temp
end
GO

EXEC dbo.tempTableTest

Results


id	name
1	AAA
2	BBB
3	CCC
4	DDD

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-26 : 08:17:30

Maybe you have an error on your proc to 'which calculates percentage'

Feel free to post the code.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mrm23
Posting Yak Master

198 Posts

Posted - 2010-08-26 : 08:38:30

Hi,

Thanks for the reply. I tried to create a Dataset for this but i did not get any fields.
The procedure alone when run works fine.
I think the prob is using the temp table with proc and trying to push that into a dataset.

advice me if i am wrong.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources