I had a hard time remembering how to do this and was not using the right keywords in Google.

There are a few ways to do temp tables in SQL. In SQL7 we could use a #tablename, but there are issues with that. It becomes part of the returned resultset and you have to remember to delete them.

In SQL2000, you can still use that syntax but in many cases a table variable is a much better route. It is a variable declared in your stored procedure, it is local so you don’t have to worry about deleting it and it does not affect the result set.

NOTE: You must include “SET NOCOUNT ON“ at the beginning of your stored procedure to prevent that extra gunk in the result set that ADO retrieves. I found that this works with table variables, but it does not fix the problem with #temptables.

Here’s a useful article on it: http://www.sqlteam.com/item.asp?ItemID=9454 with syntax and caveats.

I had a sproc using the #temptable method that worked just fine here on my test server which is SQL2000. When I deployed it to my client’s production server, also SQL2000 with latest service packs, ADO (in VB6 , not ado.net) was having an issue against the production database with recordset.next that did not show up when I ran against the local database. So I went with the table variable instead.

One of the advances of Crystal in version 8 was that we had the ability to create TTX files from our recordsets and then use the TTX files to design reports from. This alleviated a huge headache of being forced to either use that mistake of a database environment or hooking directly to databases to create reports. Building these dependencies between the report and one of those resources meant confusing memory problems when you ran the report. The TTX file was just a little text file that contained the schema of your recordset. Once the report was designed, you didn’t even need to tote the ttx file around anymore.

In .NET, this evolved to the ability for the report to be designed from an ado.net dataset. You do this by creating a dataset object in your project and linking to that when you design the report. I generally create the object by doing a dataset.writexmlschema in my code initially. Once that file is created, I comment out the line and bring the schema into my project and generate a dataset object from it. (Did you know you can do that in vs2003 by right clicking on the xsd and selecting the mysterious “run custom tool”?)

Many people are still unaware of this feature and do what comes naturally, which is to point directly to the database for desiging a report. This is one of the causes of the dreadful “please logon” message we get at runtime when trying to view a report. (Another one, I discovered was passing a dataview to the report’s datasource instead of a datatable and apparently passing a dataset can do this too.)

Here is a PDF from the Crystal Reports .NET DevCenter on the Business Objects site that explains how to build reports from ADO.NET.

Also, here is an article by Susan Harkins that I found for someone that explains how to create an XSD file from Access.