How do I bind a SQL Server temp table to a report ?

[defektorx]

I have designed a report by creating a table in SQL
server. I have then placed the report into VB. I am running a stored
procedure which creates a temp table with the exact structure needed by
the report. I am then using AddADOCommand to change the data data source
of the report and I want to supply the stored procedure to this so the
report is gettting the data supplied by it. I have tried many variations
to try and get the report to recognise the datasource. I can add a
different data source and a table using AddOLEDBSource, but every time I
try to use a procedure linking to a temp table I get a Error in database
DLL. What can i do ?

 

[balves]

Perhaps I'm not fully understanding the problem, but what I have done in the past is...

1. Create a stored procedure that begins by defining a temp table #T1 with a certain set of fields.
2. Write the code of the stored procedure that eventually fills #T1 with records
3. As the last statement in the stored procedure, do a "SELECT * FROM #T1"
4. Create a new Crystal Report and as the data source, point it at the stored procedure. Crystal will then execute the stored proc behind the scenes and figure out that the database fields it can use come from #T1 in the stored proc. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[defektorx]

Very good. Nice one mate. Can I bind it to the stored procedure in VB code ? I have 3 different stored procedures that return the same data for the report. I want to bind them at runtime to a report (the same one)

 

[balves]

I haven't tried binding an stored proc at run-time. If you're using the OCX, you are supposed to be able to do it with the command:

CrystalReport1.Datafiles(0) = " Proc(myprocnamehere;1)"

where CrystalReport1 is the name of your OCX on the form.

There must be a way with the new RDC object model but I haven't found it yet.

Another method is to have one stored proc that takes different code paths depending on the parameters passed to it. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[MalcolmW]

For the sake of simplicity at this point, you may just wish to create another stored proc, which then calls one of your three other stored procs depending on the value of a parameter you pass to it.
This is logically equivalent to Brian's suggestion of having different code paths in a single stored proc - it boils down to a style preference. Malcolm

 

[MalcolmW]

P.S.
Another alternative is to use a global temp table (see BOL) but local temp tables inside stored procs are much neater to handle. Malcolm