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.
ASKER CERTIFIED SOLUTION
simon_kirk

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 1 Comment.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 1 Comment.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004