troubleshooting Question

Microsoft Reporting Services - SQL Query

Avatar of Mohlerp1942
Mohlerp1942 asked on
DB Reporting Tools
1 Comment1 Solution765 ViewsLast Modified:
I have three datasets two that populate a temporary table called  ##GlobalTable1 the first dataset creates the global table and inserts records. Query shown below is Dataset "GlobalLocalTable"
SELECT ASSMNT_TOPIC_1.ASSMNT_TOPIC_NAME AS Column1, COND_TOPIC_1.COND_TOPIC_NAME AS Column2Name, dbo.COND_TOPIC.COND_TOPIC_NAME AS Column3,
            COUNT(COND_TOPIC_1.COND_TOPIC_NAME) AS Column2Count
INTO    [##GlobalTable1]
FROM   dbo.COND INNER JOIN
            dbo.COND_TOPIC ON dbo.COND.COND_TOPIC_ID = dbo.COND_TOPIC.COND_TOPIC_ID INNER JOIN
            dbo.COND_TOPIC COND_TOPIC_1 ON dbo.COND_TOPIC.PARENT_COND_TOPIC_ID = COND_TOPIC_1.COND_TOPIC_ID INNER JOIN
            dbo.ASSMNT_TOPIC ON dbo.COND_TOPIC.PARENT_COND_TOPIC_ID = dbo.ASSMNT_TOPIC.ASSMNT_TOPIC_ID INNER JOIN
            dbo.ASSMNT_TOPIC ASSMNT_TOPIC_1 ON dbo.ASSMNT_TOPIC.PARENT_ASSMNT_TOPIC_ID = ASSMNT_TOPIC_1.ASSMNT_TOPIC_ID
WHERE (dbo.COND.INSERT_DATE >= @BeginPeriod) AND (dbo.COND.INSERT_DATE <= @EndPeriod)
GROUP BY ASSMNT_TOPIC_1.ASSMNT_TOPIC_NAME, dbo.COND_TOPIC.COND_TOPIC_NAME, COND_TOPIC_1.COND_TOPIC_NAME
ORDER BY ASSMNT_TOPIC_1.ASSMNT_TOPIC_NAME, COND_TOPIC_1.COND_TOPIC_NAME, dbo.COND_TOPIC.COND_TOPIC_NAME

The second query appends records to same table. See below! This Dataset is "InsertIntoGlobalTable"
INSERT INTO [##GlobalTable1]
SELECT COND_TOPIC_1.COND_TOPIC_NAME AS Column1, dbo.COND_TOPIC.COND_TOPIC_NAME AS Column2Name, 'N/A' AS Column3, COUNT(dbo.COND_TOPIC.COND_TOPIC_NAME)
            AS ColumnCount
FROM   dbo.COND INNER JOIN
            dbo.COND_TOPIC ON dbo.COND.COND_TOPIC_ID = dbo.COND_TOPIC.COND_TOPIC_ID INNER JOIN
            dbo.COND_TOPIC COND_TOPIC_1 ON dbo.COND_TOPIC.PARENT_COND_TOPIC_ID = COND_TOPIC_1.COND_TOPIC_ID
WHERE (dbo.COND.INSERT_DATE >= @BeginPeriod) AND (dbo.COND.INSERT_DATE <= @EndPeriod)
GROUP BY COND_TOPIC_1.COND_TOPIC_NAME, dbo.COND_TOPIC.COND_TOPIC_NAME, COND_TOPIC_1.PARENT_COND_TOPIC_ID
HAVING (COND_TOPIC_1.PARENT_COND_TOPIC_ID IS NULL)

The third dataset queries the temporary table. See query below! Dataset name is "GetLocalGlobal"
SELECT Column1, Column2Name, Column3, Column2Count
FROM   [##GlobalTable1]
I want to use the third Dataset to populate my report.

When I test these queries from the Data tab they work, but when I try to run the report I get the error message
 "An error has occurred during processing. Query execution failed for dataset "GetLocalGlobal" invalid object name '##GlobalTable1'
I don't understand the error message.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros