RS Does Not Recognize Temp Table Fields

I am using the generic query designer and have defined 2 temp tables as
follows:
DECLARE @.MainTable
DECLARE @.JoinTable
INSERT INTO @.MainTable
SELECT...
INSERT INTO @.JoinTable
SELECT...
SELECT FROM @.MainTable JOIN ON @.JoinTable...
My problem is the field list does not recognize all the fields; all the
original fields are in the @.MainTable but only the first 3 from the
@.JoinTable. I tried to add additional fields to the @.MainTable and they are
not recognized. I have tried the Field Refresh Button, manually adding
fields to the list and hacking the RDL field list. Each time I manually add,
I get an index out of bounds error when trying to run the report. If I then
hit the refresh button the fields I have manually added are removed'
Also, must the field names be unique among the temp tables? I assumed not
since they are in separate queries.
Many thanks for any helpFirst off, even if you get this to work it won't work for you. Let me
explain. What happens with using temp tables in the generic query designer
is the same thing that happens if you post the below code in query analyzer
and hit execute twice. The first time it will work, the second time it will
error out because the tables already exist. So even if this worked for you
in development when you deploy it the report would work the first time. If
the user click on refresh report it will die (I haven't done this in 2005
but I did try this out in 2000 and I doubt if this would have changed). The
reason is what is going on with connection pooling. Dropping the tables at
the end would probably not work either because it would probably do this
before RS is done with them.
I suggest creating a stored procedure. Do not drop the temp tables, just let
them fall out of scope. Have your select statement be the last statement.
I do this all the time (multiple temp tables, lots of inserts, updates, join
etc between them).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:uloHfFM7FHA.2692@.tk2msftngp13.phx.gbl...
>I am using the generic query designer and have defined 2 temp tables as
>follows:
> DECLARE @.MainTable
> DECLARE @.JoinTable
> INSERT INTO @.MainTable
> SELECT...
> INSERT INTO @.JoinTable
> SELECT...
> SELECT FROM @.MainTable JOIN ON @.JoinTable...
> My problem is the field list does not recognize all the fields; all the
> original fields are in the @.MainTable but only the first 3 from the
> @.JoinTable. I tried to add additional fields to the @.MainTable and they
> are not recognized. I have tried the Field Refresh Button, manually adding
> fields to the list and hacking the RDL field list. Each time I manually
> add, I get an index out of bounds error when trying to run the report. If
> I then hit the refresh button the fields I have manually added are
> removed'
> Also, must the field names be unique among the temp tables? I assumed not
> since they are in separate queries.
> Many thanks for any help
>|||Bruce
Many thanks for your prompt reply. I found my problem was a simple one where
I had not included all the new fields in all the select statements. Your
point is well taken though and the use of the temp tables was a last resort
because I could not get the original more straight-forward query to work. I
was getting redundant hits as a result of a subquery so had to use this
approach to get it working for a deadline.
Surprisingly this approach does work in the preview panel as well as
deployed to the web. However the query does not execute inside of the data
panel; it just dies without any errors. I assume this is what you are
referring to.
Many thanks for all your help up here!
Mike