Stored procedures/Tmp Table

[simma]

Hello,
I am trying to insert the resultsets from 4 sps into a temp table.The sp looks fine in query analyser but when Im trying to link to Crystal Report it gives blank error.Im giving the code.Is there anyotherway other than sub reports?
Thanks
Code
________________________________________-

create table #tmpstore
(loan_number varchar(15),
classification varchar(15),
sequence_num numeric(9),
acldescription varchar(40),
complete_date varchar(15),
activate_date varchar(15),
complete_days varchar(15),
active_flag varchar(15),
first_name varchar (15),
last_name varchar (25),
ladescription varchar(70),
bbkdescription varchar(40))
insert into #tmpstore
exec Rpt_ActivityReo
insert into #tmpstore
exec Rpt_ActivityFcl
insert into #tmpstore
exec Rpt_ActivityLmit
insert into #tmpstore
exec Rpt_ActivityBk
select * from #tmpstore
drop table #tmpstore

 

[lyanch]

What database are you using? Crystal can't handle having SP's call other SP's in certain db's
(oracle for one). Drop all the sp calls and see if crystal can see the data structure (even if it is empty). If it can.. that is probably where your problem lies.

Lisa

 

[simma]

Thanks Lisa
I am using SQL Server 2000.

 

[synapsevampire]

Here's a test I used, perhaps your Rpt_Activity* procs are bad or don't fit the format:

CREATE PROCEDURE [dbo].[tmpTableTest] AS
CREATE TABLE [#TmpTable] (
[dtmStartDate] [datetime] NOT NULL ,
[dtmEnddate] [datetime] NULL )

insert into #TmpTable ( [dtmStartDate] ,[dtmEnddate] )
values( getdate(), getdate()-1)
insert into #TmpTable ( [dtmStartDate] ,[dtmEnddate] )
values( getdate(), getdate()-1)
insert into #TmpTable ( [dtmStartDate] ,[dtmEnddate] )
exec twodates
select * from #TmpTable
drop TABLE [#TmpTable]
GO

The twodates proc is:

CREATE PROCEDURE [dbo].[twodates] AS
select getdate(), getdate()-2
GO

-k