Hi all
I have a procedure where I am inserting some elements into #Table and then finally get the datset I need.
Now when I am using this procedure as dataset to my report, it throws up the following error:
Invalid object Name "#TEMP2".
The data that I retrieve is similar to the data that I get from this query in the post by Manivannan.D.Sekaran
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1
Is it because my columns are generated on the fly that I am not able to retrieve the column headers appropriately. If so can someone suggest a way over to this?
I am not sure should I posting it here or in T-SQL Forum.
Hi,
#Table is not #TEMP2.
Cannot help. Please be more specific. Post your code snippets.
Your data set should at least contains a first set of columns were the name is constant. Then if you need another suite of variable columns, you use another technique. Try to work this out one piece at the time.
Philippe
|||Generally due to limited scope Reporting Services rejects local temp table try changing to global temp table, if you are still getting the error you need to rewrite your stored proc. Hope this helps.|||
Ok here is my code snippet..
The first select statement gives me Customer names across the rows and Circles as columns and the number of associates in it mapped as a pivot table.
(there are actually some 17 odd circles...i am putting Circle 1 , Circle 2 for security reasons...)
Then after obtaining the results I am unpivoting it to meet my requriement i.e. to get only those columns that have the top two highest total and everything else clubbed as others.
I am using this procedure as my datasource for the report.
That is when it is throwing the error Invalid object name "#TEMP".
Caddre, could you tell me how i can change the local temp table to global temp table now that you know what my procedure is looking like..
I know it is a roundabout procedure of pivoting and then unpivoting it which i will try to post shortly on how exactly my db is structed but with the present one could you suggest me the solution..
Code Snippet
ALTER PROCEDURE [dbo].[MnE_usp_ViewSummary_Main]
AS
Select CustomerName,ISNULL([Circle 1],0) AS [Circle 1],ISNULL([Circle 2],0) AS [Circle 2]
INTO #TEMP
FROM
(
SELECT 'CustomerName'= CASE
WHEN Lkp_CustomerGroup.CustomerGroup ='NA' THEN 'Pool'
ELSE ISNULL(Lkp_CustomerGroup.CustomerGroup,Lkp_CustomerGroup.CustomerGroup) END,Lkp_Circle.Abbreviation AS[Circle],ISNULL(COUNT(Tbl_User.ID),0) AS[Total]
FROM Tbl_Customer LEFT OUTER JOIN
Tbl_Project ON Tbl_Project.CustomerID=Tbl_Customer.ID LEFT OUTER JOIN
Tbl_Associate_Project ON Tbl_Project.ID =Tbl_Associate_Project.ProjectID LEFT OUTER JOIN
Tbl_User ON Tbl_User.ID=Tbl_Associate_Project.AssociateID LEFT OUTER JOIN
Tbl_UserDetails ON Tbl_User.ID=Tbl_UserDetails.AssociateID LEFT OUTER JOIN
Lkp_Circle ON Lkp_Circle.ID=Tbl_UserDetails.CircleID LEFT OUTER JOIN
Lkp_CustomerGroup ON Tbl_Customer.CustomerGroupID=Lkp_CustomerGroup.ID
WHERE Tbl_User.ID<>0 AND Tbl_UserDetails.AssociateID<>0 AND Tbl_User.IsActive='True'
GROUP BY Lkp_CustomerGroup.CustomerGroup,Lkp_Circle.Abbreviation,Tbl_Customer.Name
)SUB
PIVOT
(
SUM(Total) FOR
Circle IN ([Circle 1],[Circle 2])
)PivotTable
;
With UnPivoted
as
(
Select
*,
Sum(Data) Over (Partition By Circle) Sumed
from
#TEMP
Unpivot
(
Data for Circle In ([Circle 1],[Circle 2])
) Pvt
)
, Ranked
as
(
Select *, DENSE_RANK() Over(Order By Sumed Desc,Circle) Rank From UnPivoted
)
select * INTO #TEMP2 from Ranked;
Declare @.Col1 as Varchar(100);
Declare @.Col2 as Varchar(100);
Select @.Col1 = Circle from #TEMP2 Where Rank=1;
Select @.Col2 = Circle from #TEMP2 Where Rank=2;
;
EXEC('Select
Isnull(CustomerName,''Total'') CustomerName,
''sortorder''=
CASE
WHEN CustomerName =''Pool'' THEN ''YYYYYYY''
WHEN ISNULL(CustomerName,''Total'')=''Total'' THEN ''ZZZZZZZ''
ELSE CustomerName
END,
Sum(Case When Rank=1 Then Data End) as ['+ @.Col1+'] ,
Sum(Case When Rank=2 Then Data End) as ['+ @.Col2+'] ,
Sum(Case When Rank>2 Then Data End) Others,
''Total''=Sum(Data)
From
#TEMP2
Group By
CustomerName
With Cube
Order By
sortorder')
;DROP TABLE #TEMP;
Drop table #TEMP2;
|||A local temp table is # while global temp table is ## but you have two temp tables that is not valid you can only use one temp table in a stored procedure however you can ALTER the temp table many times. So change to global temp table and add one or two indexes and remember to ALTER your existing temp table instead of creating a new one.|||
Cddre, thanks for the suggestion but I haven't worked with indexes before. My procedure does need two temp tables as you have seen. Could you please alter the code and let me know. I tried dropping the #TEMP Table before the statement "select * INTO #TEMP2 from Ranked;" and renaming all the #TEMP2 instances to #TEMP so that there can only be one # table but it is throwing me the error " Incorrect syntax near the keyword "DROP" '
here is what i did with other remaining code above remaining the same..
Code Snippet
DROP TABLE #TEMP;
select * INTO #TEMP from Ranked;
Declare @.Col1 as Varchar(100);
Declare @.Col2 as Varchar(100);
Select @.Col1 = Circle from #TEMP Where Rank=1;
Select @.Col2 = Circle from #TEMP Where Rank=2;
;
EXEC('Select
Isnull(CustomerName,''Total'') CustomerName,
''sortorder''=
CASE
WHEN CustomerName =''Pool'' THEN ''YYYYYYY''
WHEN ISNULL(CustomerName,''Total'')=''Total'' THEN ''ZZZZZZZ''
ELSE CustomerName
END,
Sum(Case When Rank=1 Then Data End) as ['+ @.Col1+'] ,
Sum(Case When Rank=2 Then Data End) as ['+ @.Col2+'] ,
Sum(Case When Rank>3 Then Data End) Others,
''Total''=Sum(Data)
From
#TEMP
Group By
CustomerName
With Cube
Order By
sortorder')
;
DROP TABLE #TEMP;
|||I did not tell you to drop the table just ALTER it, so print out the article from the link below and clean up your code. If that did not work then you have to use SELECT INTO to create a permanent table first then pass the results to a global ## temp table. Hope this helps.
http://www.informit.com/articles/printerfriendly.aspx?p=25288
|||
The point that has already been made by Caddre, and which I also believe is the source of your problem, is that the EXEC statement can't see your temp table because it's in the wrong scope. It would need to have two #'s (##TEMP or ##TEMP2) for it to be "global". When you do the EXEC you are outside the scope of the procedure that is creating your temporary table.
Look at it this way:
Suppose your EXEC statement read something like this :
Code Snippet
EXEC('Select ISNULL(Something, @.MyVal)')
... instead of :
Code Snippet
EXEC('Select ISNULL(Something,'+ @.MyVal+') ')
... now your procedure would complain that it could not find @.MyVal, and for exactly the same reasons. Do you understand this, and does it help you understand why your temp table is not found?
There are many ways to fix this -- and using a global temp table is only one, probably not the best -- but I want to make sure you see the scoping problem first.
OK, if you're "there", now let's think about how to fix it.
You might be able to get rid of the EXEC very easily, by adding a couple of columns indicating the column names,
like this (this is a hack and I'm not being very careful, I just want you to see what I mean:
Code Snippet
Select
Isnull(CustomerName,''Total'') CustomerName,
''sortorder''=
CASE
WHEN CustomerName =''Pool'' THEN ''YYYYYYY''
WHEN ISNULL(CustomerName,''Total'')=''Total'' THEN ''ZZZZZZZ''
ELSE CustomerName
END,
Sum(Case When Rank=1 Then Data End) as Col1,
MAX(@.Col1) AS Col1Name,
Sum(Case When Rank=2 Then Data End) as Col2,
MAX(@.Col2) AS Col2Name,
Sum(Case When Rank>2 Then Data End) Others,
'Total'=Sum(Data)
From
#TEMP2
Group By
CustomerName
With Cube
Order By
sortorder
... now your report or your additional code looks at Col1Name and Col2Name to get whatever significant stuff you need to glean from the column names...
>L<
|||
Thanks Lisa for the reply but getting rid of the EXEC and using your method still is issuing the same error.. "Invalid Object #Temp" but my procedure as stand alone works fine when executed or when called from .Net applications.
Caddre,I have gone through the article and I understand that two # tables can't be used. The problem still remains when I use permanent table. Apart from that I don't know my column names before hand for altering my temp table that too in my case there are three fixed columns i.e. CustomerName,Total, Others and the rest only comes after executing the query. I am still running into the error. I don't know how else to clean up my code without knowing how it is that I am going to alter the temporary table #TEMP. Please help..
|||
Clearly, we can't properly see what your problem is <s>.
I do get that you don't know the column names in advance. I also get that you took this code more or less verbatim from another thread, where they are solving something similar to what you need to do. What I don't really know (sorry!) is:
* -- what parts of that solution are really significant to the original problem you were trying to solve and what parts may be artifacts of the original solution that don't really pertain to your problem-to-solve
* -- what is the significance of needing to know the column names for that original problem.
The need to get the top-ranked 2 columns and then a group of all other columns -- if this was your original goal -- should probably *not* require a pivot followed by an unpivot. If that is your actual problem to solve, can we start from the top please?
I have a real feeling that if, instead of having us come in the the middle (and I know you are invested, at this point, in something you've worked hard on and is "almost working" <s>), it would be better if you said something like this:
I have this schema with these tables (... insert CREATE TABLE statements or whatever description here...) and I need to pull out this information (... insert description of the table structure you're looking for in the report here...). What's the best way to do that?
Again, I'm sorry that we're not immediately coming up with a solution that will work for you but we are willing to try to think it out with you.
>L<
|||
I am sorry I pushed you all into middle of a mess and as you rightly said "something I have worked hard on and is 'almost working' " I didn't want to mess up. But for that I thought I would have to open seperate thread again to get them working. I guess I will define now what my requirement really is :
The tables that are involved with the necessary details
Tbl_Customer
-
ID Name
-
CUS1 ABC
CUS2 DEF
Tbl_Project
CustomerID ID NAME
CUS1 PRJ1 Project A
CUS2 PRJ2 Project B
CUS2 PRJ3 Project C
Tbl_UserDetails
-
PROJECTID ID NAME CircleID
--
PRJ1 USR1 User 1 0
PRJ1 USR2 User 2 1
PRJ2 USR3 User 3 2
Lkp_Circle ( there are 17 circles in total and the list is pretty dynamic)
--
ID Circle
0 Circle1
1 Circle2
2 Circle3
Now the output I want is as follows : (the numbers indicate the number of users for each customer account)
[Main Table]
-
CustomerName Circle1 Circle2 Circle3 .... Others Total
--
ABC 1 1 0 0 2
DEF 0 0 1 0 1
The link as you can see is like this Customer - Project - User - Circle.
The thing is since there are some 17 odd circles I want the list to be pretty dynamic and display only top 5 circles with their totals and the rest to be clubbed as others and a column called total to see the list as a whole. Well for that I initially pivoted to get the [Main Table] with all the circles corresponding to each customer. From there using unpivot I was trying to achieve the max condition. For clearer understanding you can just see this link where I gave a similar anolagy replacing Class in place of Circle.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1871478&SiteID=1
Please let me know for any further information. Also could it be possible to extract the Circle names dynamically rather than hard-coding it.
|||
Hi again,
Thanks for your patience.
I don't have anything at hand to mock this up properly (and have to get to bed!!) so I will just try to describe what I would do to do this briefly and sketch it out with the first table at hand:
1. get your top ranked items, possibly using partition, although I won't do that here
2. union the above with another one that basically does WHERE NOT IN against the partition and aggregates all those rows
3. save the totalling for the matrix.
It should not matter how dynamic the circle list is, this should work...
I'm pretty tired and may screw this up, but you've made an effort, and you can see the idea with pretty much any data, so I'll give it a quick shot.
So, example. I have a notice queue table, I can find out the 5 dates that had the highest number of messages go through like this:
Code Snippet
SELECT TOP 5 COUNT(*) AS NumberOfNotices, Notice_Requested FROM S2S_Notices
GROUP BY Notice_Requested ORDER BY COUNT(*) DESC
... therefore I can show these five dates unioned with an an aggregate (clubbed) column showing all the other ones in one set like this:
Code Snippet
SELECT NumberOfNotices, NoticeRequested FROM
(SELECT TOP 5 COUNT(*) AS NumberOfNotices,
CAST(Notice_Requested AS VARCHAR) AS NoticeRequested
FROM S2S_Notices
GROUP BY Notice_Requested ORDER BY COUNT(*) DESC) ZZ
UNION ALL
SELECT NumberOfNotices,'All Others' As NoticeRequested FROM
(SELECT COUNT(*) AS NumberOfNotices FROM S2S_Notices WHERE Notice_Requested
NOT IN
(SELECT TOP 5 Notice_Requested FROM
(SELECT COUNT(*) AS NumberOfNotices, Notice_Requested FROM S2S_Notices
GROUP BY Notice_Requested) XX
ORDER BY NumberOfNotices Desc)) YY
My result for this sample looks like this:
Code Snippet
| NumberOfNotices | NoticeRequested |
| 35386 | Jan 10 20078:17AM |
| 30666 | Jan 30 20074:19AM |
| 29768 | Jan2 2007 11:13AM |
| 20402 | May 30 20065:18PM |
| 17125 | Aug 10 20061:31PM |
| 362122 | All Others |
... do you see what I'm driving at? I realize that your case is more complex but basically what's going to happen is the matrix is going to take care of those column names the way it should, if we get the selects done right. And the matrix should do the total in its normal way, too.
Does this make any sense at all?
>L<
|||
Hi Lisa,
I get the drift, thank you. My problem is my end result is not linear as was in the case you described. I will just illustrate this. For getting the top 5 in the following format
Customer Name Circle Total
would be for given customer and given circle i would be getting the total say if there are 10 customers then 170 rows. You see in my final grouping that I get cumulatively I see highest total of all associates in circles across all the the customers then get my result ..something like this
--
CustomerName Circle1 Circle2 Circle3
--
abc 5 4 6
def 0 3 10
--
total 5 7 16
--
From the above supposing I need only the top 2 then I filter that out and query to get the appropriate result. Hope I am clear on this. It's okay , please take your time and don't spoil your sleep for replying 
.
Many thanks again
|||
Hi again,
At this point, it isn't so much sleep (I've given up on that <g>) as time -- don't have much time to think about this stuff during my work week <s>.
From my point of view, it shouldn't matter that my data or requirements are different/less complex. The point was to figure out something atomistic -- basically a placeholder for whatever you were filling your temp tables with.
From there, I wanted to illustrate how you would start from that inner most/determining SELECT, whatever it was, and build stuff "outward" to accomplish the whole thing, without a temp table, even if some operations had to be repeated in the nested process.
Right now, although I hear you saying that your data is "not linear", I'm not seeing the issue exactly with doing this with your data set. It can't just be the bottom line totals, because that shouldn't be a problem at all. So I have to CREATE TABLE, add rows, and figure out what wouldn't work.
I am not, unfortunately, the kind of person who easily "sees data" without working it out physically. My spouse/better half/work partner might be able to just see it from the data set, but I have to work it out <s>.
Maybe the problem is that I think you are talking about "top 2 circles" but you mean "top two customers"? That can't be it...
Maybe somebody else here will see what the critical point here and speak up. I will try this out later in the week or in the weekend if you haven't already grokked the answer.
>L<
|||Hi Lisa,
Have you figured out a solution for this or should I be posting it in the T-SQL Forums? Please let me know as there are some pending things that are left to do once I am ready with the dataset.
