I had a SP to generate a recordset for a MIS report. It had 11 temp tables , basically taking data from real tables , performing some aggregation and counts and passin on the records to a temp table which gave the result as desired ..ofcourse with some group by clauses...
Now i replaced these temp tables (most of them..left with just 2), and used derieved tables instead in the final query..and joins which will execute with each query iteration..something of the sort
select col1,co2 ,
(select count(id) from sometable x group by sayaccount where x.id = temp.id) ,
(select sum(id) from (select count(id) from sometable group by sayaccount) DERIVED_Tab),
......
from #finaltemp temp
group by col1.....
(earlier the count was stored in 1 temp table, then sum one n stored in other).
the idea was to reduce the execution time...but i din achieve it...not with just a single user running the report i.e , rather it marginally increased. my thinking was that i'll be avoiding the locks on tempdb by reducing the number of temp tables....pls tell me if im goin wrong...i still have the option of using table datatype if thats feasible..
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000)) INSERT ##MyTempTbl values ('Put your long message here.') INSERT ##MyTempTbl values ('Put your second long message here.') INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!') DECLARE @cmd varchar(256) DECLARE @LargestEventSize int DECLARE @Width int, @Msg varchar(128) SELECT @LargestEventSize = Max(Len(MyWords)) FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' + CONVERT(varchar(5), @LargestEventSize) + ')) FROM ##MyTempTbl order by SeqNo' SET @Width = @LargestEventSize + 1 SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------' EXECUTE Master.dbo.xp_sendmail 'YoMama@WhoKnows.com', @query = @cmd, @no_header= 'TRUE', @width = @Width, @dbuse = 'MyDB', @subject='none of your darn business', @message= @Msg DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet
I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?
[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)
I require to perfom a join on 3 tables within the same query . To explain myself better i have 3 tables
Main table
Label table
textbox table The Main table contains the common fields in both the label and textbox table. While the label and textox table contain the fields that are sepcfic to them .
MAIN Table
pk Moduleid ItemName itemtype
36 372 test1 4
37 372 test2 4
38 372 test3 4
39 372 test4 6
40 372 test5 4
label
pk Main_fk labeltext
4 36 labeltext1
5 37 labeltext2
6 38 labeltext3
7 40 labeltext4
Textbox
pk Main_fk textboxtext
1 39 textbox1
I did infact manage to perform a join on these these tables.
Select * From tb_Main
inner join tb_Label
on tb_Main.pk = tb_Label.main_fk
where moduleID = @moduleID
Select * From tb_Main
inner join tb_textbox
on tb_Main.pk = tb_textbox.main_fk
where moduleID = @moduleID
The problem is that it returns two separate results . I require a join on the label and textbox table within the same query to return one result.
Is what im asking possible? I would appreciate if some exmaples are posted
I have no control on the design of the tables as i didnt create them but still if anyone has a suggestion on improving them please do ,so i can tell my colleague that they aren't designed well !!!!
Is it possible to utilize more than two tables in a single outer join? I have one table that I want every row and 18 others where I only want an entry if one is present meeting the conditions of "1.customerid = 2.Customerid" etc. I haven't run across this before and would appreciate any help.
I have a general question concerning joins. Below is a table scenario:
SELECT * FROM TABLE_A T0 INNER JOIN TABLE_B T1 ON T1.[Some_Column] = T0.[Some Column] LEFT JOIN TABLE_C T2 ON T2.[Some_Column] = T0.[Some Column]
Does the above indicate that all records in common between TABLE_A & TABLE_B will be returned, then the records from TABLE_C will be joined to the initial 'result set' (that is the result of joining TABLE_A & TABLE_B), or will TABLE_C simply be joined to TABLE_A regardless of the inner join between TABLE_A & TABLE_B?
I am writing a download process in which i have a condition where ineed to join four tables. Each table have lot of data say around300000 recs.my question is when i am doing the joins on the columns is there anyspecific order i need to follow.for exampleMy original query looks like thisselect a.col1,a.col2from ainner join bon a.id1=b.id1and a.id2=b.id2inner join con c.id1=b.id1and c.id2=b.id2inner join don d.id1=c.id1and d.id2=c.id2If i change the query like below... does it make any differenceselect a.col1,a.col2from ainner join bon b.id1=a.id1and b.id2=a.id2inner join con c.id1=a.id1and c.id2=a.id2inner join don d.id1=a.id1and d.id2=a.id2Any help is appreciated.ThanksSri
Hi, all:This is probably a simple problem, but, as an SQL newbie, I'm having alittle trouble understanding multi-joins and subqueries.I have the following tables and columns:MemberTable-----MemberID (primary key)MemberNameAddressCountryFoodsTable------FoodID (primary key)FoodNameMusicTable-----MusicID (primary key)MusicNameMoviesTable-----MoviesID (primary key)MoviesName....and their linking tables...Members2FoodsTable-----MemberID (foreign key)FoodsID (foreign key)Members2MoviesTable-----MemberID (foreign key)MoviesID (foreign key)....and so forth.Now what I'm trying to do is retrieve a specific MemberID, his address info(from the Members table), and get a listing of his favorite Movies, Foods,Music, etc. I know I probably need to JOIN tables, but where do I JOIN thetables? Do I have to JOIN the various Music/Foods/Movies tables or is itthe Music/Members2Music and Foods/Members2Foods, etc. tables? And I assumeI would probably need to perform a subquery somewhere?I realize I'll need to first filter the Members, Members2Music,Members2Foods, etc. tables by the MemberID, and afterwards, retrieve alisting of the actual Music/Foods/Movies names. I'm just confused how to dothat. (By the way, I have a total of 10 other tables or in addition toMusic, Foods, etc. so it's a lot of table JOINing.)If someone could please help me out with the actual SQL coding, I'd reallyappreciate it!Thanks for the help!J
I have to write a report using SSRS using a cube somebody (no longer here) created. I know SSRS and SQL very well, but have never worked with cubes.
I want to make sure the cube was properly constructed (in terms of table/join relationships). I cannot find the tables/joins. I looked in SSMS under Data Source Views and everything else I could think of (or google).
I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get 'Error Source: Microsoft OLE DB Provider for SQL Server Error Description: Invalid Object name "#tempT1"'
Is there a way to make a DTS package call a stored procedure that uses temp tables?
I want to check to see if a temporary table exists before I try creating one but I can't seem to find which sys table or schema collection I check. Any ideas?
-- Add Structural data usp_AddStructural @iEstimateID, 1, 'Structural' usp_AddForming @iEstimateID, 2, 'Forming' ... ... ... GO
Now, a couple of problems, after the table is created and populated, I cannot find it in my list of tables, even after "refreshing".
I checked to ensure that it exists using the query analyzer and it does so I know the table is being created.
Also, I cannot see the table using crystal reports, connecting etc...... Can I not access a temporary table from 3rd party applications? I have crystal reports 7.0 professional.
I am in the process of modifying some stored procedures that currently do not use temp tables. For this modification I am required to make the stored procedures use temp tables. There are several UDF's within this stored procedure that will need to use the temp tables, and this is where in lies the problem. Does anyone know of a work around that would allow UDF's to use temp tables, or does anyone know of alternate methods instead of temp tables that wouldn't involve too much change?
I have a called stored procedure which creates a bunch of temporary tables, inserts data into them, indexes the tables and then returns to the main calling SP. In the main stored procedure I then do SELECTs from the temporary tables. My problem is I keep getting invalid object errors on the temporary tables: Invalid object name '#temp_table1'
The stored procedure is in a test environment. In the SELECT I tried a prefix of database owner (my logon) as well as "dbo." but still get the error. Any suggestions as to what I am doing wrong would be much appreciated.
In these two tables im just to bring the data back where the two DesignID's dont match. Im gettin an error
Server: Msg 107, Level 16, State 3, Line 1 The column prefix '#ttTopSellers' does not match with a table name or alias name used in the query.
Declare @CustomerID as VARCHAR(25) Set @CustomerID = 'DELCOZ01-10'
/*Figure the designs that stores carry*/ Select Design.Description, Item.DesignID, CustomerClassificationID, CustomerID, Region.[ID] as RegionID, Region.Name Into #ttDesign From Mas.dbo.Item Item Inner Join MAS.dbo.Style Style on Item.StyleID = Style.[ID] Inner Join MAS.dbo.Line Line on Style.LineID = Line.[ID] Inner Join MAS.dbo.Design Design on Item.DesignID = Design.[ID] Inner Join Mas.dbo.DesignRegionIndex DRI on Design.[ID] = DRI.DesignID Inner Join MAS.dbo.Region Region on DRI.RegionID = Region.[ID] Inner Join MAS.dbo.CustomerClassificationRegionIndex CRI on Region.[ID] = CRI.RegionID Inner Join MAS.dbo.CustomerClassification CC on CRI.CustomerClassificationID = CC.[ID]
Where @CustomerID = CustomerID Group By Design.Description, Item.DesignID, CustomerClassificationID, CustomerID, Region.[ID], Region.Name
/*This finds the top retail sales globally*/ Select Top 10 Sum(Sales) as Sales, DesignID, Design.[Description] Into #ttTopSellers From Reporting.dbo.RetailSales_ByStore_ByCustomer_ByDay_ByItem DI Inner Join Mas.dbo.Item Item on DI.ItemNumber = Item.ItemNumber Inner Join MAS.dbo.Style Style on Item.StyleID = Style.[ID] Inner Join MAS.dbo.Line Line on Style.LineID = Line.[ID] Inner Join MAS.dbo.Design Design on Item.DesignID = Design.[ID] Where [Date] >= Month(getdate())-12 and DesignID <> 0 Group By DesignID, Design.[Description] Order by Sum(Sales) Desc
Select * From #ttDesign Where #ttDesign.DesignID <> #ttTopSellers.DesignID
Why cant I use the same temptable name i a stored procedure after i have droped it?I use the Pubs database for the test case.CREATE PROCEDURE spFulltUttrekk ASSELECT *INTO #tempFROM JobsSELECT *FROM #tempDROP TABLE #tempSELECT *INTO #tempFROM EmployeeSELECT *FROM #temp
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN workingon a three table query.Assumptions:-- I have events in the Event table.-- Each event CAN have one Transaction, but it's not guaranteed-- Each transaction, ir present, will have one or more Amount recordsThis would be the pseudo-query without any special joins:-----------------------------------------SELECTa.Name,SUM( c.amount ) as TotalFROMEvent a,Transaction b,Amounts cWHEREa.EventID = b.EventIDANDb.TransID = c.TransID-----------------------------------------This is fine if there is a Transaction for the Event. But, if there'sno transaction for an event, no record is pulled of course.What I need is for a record to come back for each event regardless ofthe presence of a Transaction. If there's no transaction, then the"Total" column should be 0.How would I get an OUTER JOIN to work on this so that each Event gets arecord?TIA-BEP
Table 3: ID RoleID Time 1 1 09:14 2 1 09:15 1 2 09:16
Now I want all the records which belongs to RoleID 1 but if same ID is belongs to RoleID 2 than i don't want that ID.From above tables ID 1 belongs to RoleID 1 and 2 so i don't want ID 1.
I have a stored procedure which contains a temp table called #NamesThis has n rows of values which are peoples names (they are varchars)i.e#NamesRickRobFrankI have a table called tblPeople.tblPeopleid Name Telephone1 Ric 012334213452 Robert 0321120931233 Paul 123 123 123 123 I want to find all the people in tblPeople whose names are like those in the temp table #NamesHow do I do this?
Please tell me there's something I haven't set. I've done several tests now. If your final return in a stored proc is from a temp table (ala #mytable ) the system cannot read the schema - for that matter, it won't run at all, complaining that the object #mytable doesn't exists. It can't possible be that temp tables aren't allowed in procs used by SQLDatasource - please tell me what I am doing wrong. This proc, when fed to a sqldatasource, fails in the designer with #temp does not exists. CREATE PROCEDURE dbo.repTest_TempASBEGIN CREATE TABLE #Temp( [iTestID] uniqueidentifier, [bTest] [bit], [cTest] [varchar] ) INSERT INTO #TempSELECT *FROM tTest SELECT *FROM #Temp END
I have been researching some performance problems in a very large application and I have a couple of questions about temp tables. (SQL 7.0 SP2)
I have one large procedure that I have been using as a test case. Originally this procedure was a cursor with lots of processing steps involving writing to, reading from and deleting in temp tables inside the cursor. I remember reading that temp tables inside a cursor were a potential performance problem, so I rewrote the procedure, replacing the cursor with a While Loop.
Doing this showed no increase in performance. Since Profiler was showing .5 second duration times on statements in the procedure accessing the temp tables I tested some more. I moved all the create statements to the top of the procedure, as I know these statements after processing steps can cause recompiles to happen. Still no performance increase.
Finally I replaced all the temp tables with actual tables, just to see what would happen. With no other changes the performance increased by more than 500%.
Can someone give me some clues as to what is happening here, because if this is a symptom of something I don't understand, the potential performance problems from other places where temp tables are similarly used in the application are enormous.
The problem is we need local tables in Access for SubReports (can't use store procedures as record source for this), therefore trying to create temp table and link to Access for each report instance.
When we create the ## type table in tempdb this cannot be linked from Access (cannot be seen via DSN). The only other option we can find is doing CREATE TABLE tempdb.dbo.[tablename] but this requires the user to have admin permissions and therefore be 'dbo' on tempdb and not 'guest'.
Is there any way to test if a table exists in the temp dB. In a procedure I create table called ##Test but I want to test if it exists before it's created and if it does I want to delete it. (It has to be a ## table because it's in a procedure being called in another procedure depending on an IF statement). The reason I want to do this is that I have multiple users and Occasionally they get the ... There is already an object named '##Test' in the database. error
