Stored Procedure Issue - Problem With Temporary Tables
Jun 14, 2006
I would like to create a stored procedure which creates a temp table to
store some XML. The # of fields of XML is dependent upon the contents
of another table in the application, so the first part of my procedure
identifies the # of fields necessary. That is working correctly. The
second part of the procedure actually attempts to create the table.
This is where my issue is.
If I attempt to create the table as a non-temporary table, the
procedure executes correctly. As soon as I add the hash marks in front
of the table name to indicate that it is a temporary table, it is
failing. Is this a known bug? Or is my code just uncharacteristically
bad? ;)
I'm getting an error that says "Invalid object name '#temp'."
The section of code that has an issue is (the value of @max is 25 in my
test):
SET @xq = 'CREATE TABLE #temp ( respid int, '
SET @i = 0
WHILE( @i <= @max ) BEGIN
SET @xq = @xq + 'response' + CAST( @i AS VARCHAR( 4 ) ) + '
xml'
IF ( @i < @max ) BEGIN
SET @xq = @xq + ', '
END
SET @i = @i + 1
END
SET @xq = @xq + ' )'
SELECT @nxq = CAST( @xq AS NVARCHAR( 40000 ) )
EXECUTE sp_executesql @nxq
Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu
What I am trying to do now is combine multiple complex queries into one table and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are pretty complex so I created each query as a Stored Procedure. These queries are dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the current user's username as a parameter. Within the master SP (Stored Procedure) it creates a temporary table inserts and executes multiple stored procedures and inserts into the temporary directory. Each of the sub stored procedures all have the same columns. After the insert to the temp tables, I then query the temp table and return it to the function it was executed in code and fill it as a System.Data.DataTable. I then bind the DataTable to a Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the data is a disconnected datasource? Perhaps the session in the SQL Server when the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT] varchar(200), [LINK] varchar(200) ) INSERT INTO #MyAlertsEXEC TM_getAlertsNewTasks @Username INSERT INTO #MyAlertsEXEC TM_getAlertsLateTasks @Username SELECT [DATE] AS 'DATE', [TEXT] AS 'TEXT', [LINK] AS LINK FROM #MyAlerts GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the the DataTable is filled. I tried just executing one of the sub SP without creating temporary tables and it works flawlessly. But the query in one of the sub SP is a normal but complex select.
This post is related to SQL server 2000 and SQL Server 2005 alleditions.Many of my stored procedures create temporary tables in the code. Iwant to find a way to find the query plan for these procsRepro--***********************************use pubsgoCREATE PROCEDURE Test @percentage intASSET Nocount on--Create and load a temporary tableselect * into #Temp1 from titleauthor--Create second temporary tablecreate table #Temp2 ( au_id varchar(20), title_id varchar (20), au_ordint, rolaylityper int)--load the second temporary table from the first oneinsert into #Temp2 select * from #Temp1goset showplan_Text ONgoEXEC Test @percentage = 100GOset showplan_Text OFFgo**************************************I get the following errorServer: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp2'.Server: Msg 208, Level 16, State 1, Procedure Test, Line 10Invalid object name '#Temp1'.I do understand what the error message means. I just want to know abetter way of finding the query plan when using temp objects.My real production procs are hundreds of lines with many temp tablesused in join with other temp tables and/or real tables.Regards
Can a SqlDataSource or a TableAdapter be attached to a stored procedure that returns a temporary table? I am using Sql Server 2000. The SqlDataSource is created with the wizard and tests okay but 2.0 controls will not bind to it. The TableAdapter wizard says 'Invalid object name' and displayes the name of the temporary table.ALTER PROCEDURE dbo.QualityControl_Audit_Item_InfractionPercentageReport @AuditTypeName varchar(50), @PlantId int = NULL, @BuildingId int = NULL, @AreaId int = NULL, @WorkCellId int = NULL, @WorkShift int = NULL, @StartDate datetime = NULL, @EndDate datetime = NULL, @debug bit = 0 AS CREATE TABLE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader varchar(100), Item varchar(250), HeaderSequence int, ItemSequence int, MajorInfractionPercent money, MinorInfractionPercent money) DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = ' INSERT INTO #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader, Item, HeaderSequence, ItemSequence, MajorInfractionPercent, MinorInfractionPercent) SELECT DISTINCT QualityControl_Audit_Item.Header, QualityControl_Audit_Item.AuditItem, QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence, NULL, NULL FROM QualityControl_Audit INNER JOIN QualityControl_Audit_Item ON QualityControl_Audit.QualityControl_Audit_Id = QualityControl_Audit_Item.QualityControl_Audit_Id WHERE (QualityControl_Audit.AuditType = @xAuditTypeName)' IF @PlantId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.PlantId = @xPlantId' IF @BuildingId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.BuildingId = @xBuildingId' IF @AreaId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.AreaId = @xAreaId' IF @WorkCellId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkCellId = @xWorkCellId' IF @WorkShift IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkShift = @xWorkShift' IF @StartDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed >= @xStartDate' IF @EndDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed <= @xEndDate'SELECT @sql = @sql + ' ORDER BY QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence' IF @debug = 1 PRINT @sqlSELECT @paramlist = '@xAuditTypeName varchar(50), @xPlantId int, @xBuildingId int, @xAreaId int, @xWorkCellId int, @xWorkShift int, @xStartDate datetime, @xEndDate datetime' EXEC sp_executesql @sql, @paramlist, @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDateDECLARE my_cursor CURSOR FOR SELECT ItemHeader, Item, MajorInfractionPercent, MinorInfractionPercent FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable OPEN my_cursor --Perform the first fetchDECLARE @ItemHeader varchar(100), @Item varchar(250), @MajorInfractionPercent money, @MinorInfractionPercent money FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent --Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN --Major EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Major', @debug, @InfractionPercent = @MajorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable SET MajorInfractionPercent = @MajorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item)) --Minor EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Minor', @debug, @InfractionPercent = @MinorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable SET MinorInfractionPercent = @MinorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item)) FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent END CLOSE my_cursor DEALLOCATE my_cursor SELECT * FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
hi what are temporary store procedure and where they are used,can u give me an example where the temporary stored procedures are used. can we create a procedur with in a procedure?
I'm so desperate, I'll pay $100 to the proper solution to this problem. I'm sure it's an easy fix, but I'm wasting more money every day trying to figure it out...
I have a table with hierarchial data in it (see the bottom tabledef) and I need to query an "indented outline" of the records in it for a tree control on a website. To do that I have to perform some sort of recursive or nested query or I can do all that manipulation in a temporary table/cursor... However, even though the resultset will display when I check the query, when I try to open it using ADO, I get a recordcount of -1.... it's very frustrating and extremely important.
I'd rather pay an expert here than try to navigate a tech help line.
ConnIS is defined in an earlier include file...
Set oCmd = Server.CreateObject("ADODB.Command") Set oCmd.ActiveConnection = ConnIS oCmd.CommandText = "dbo.Expandset" 'Name of SP oCmd.CommandType = adCmdStoredProc 'ADO constant for 4 Set oTmp = oCmd.CreateParameter("@current", adInteger, adParamInput,, 892) oCmd.Parameters.Append oTmp Set oRs = Server.CreateObject("ADODB.Recordset") oRs.Open oCmd Response.Write oRs.RecordCount & "<hr>" oRs.Close Set oRs=Nothing
This code generates the following result when run from an active server page:
-1<hr>
When I execute the raw SQL code ("exec Expandset 892") against the stored proc in the query analyzer, I get:
When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed. These stored procedures are deleted only when the connection is closed. My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.
My questions: Why it occur ? Wich have created this stored procedure ? How to avoid it occur ?
The versions are: SQL Server 6.5 Visual Basic 5.0 SQL Server ODBC Driver 2.65.0240
I'm trying to insert the results of a stored procedure call into a temporary table, which is not working. It does work if I use a non-temporary table. Can anyone tell me if this is supported or what I am doing wrong.
Here is an example:
-- DROP PROCEDURE testProc CREATE PROCEDURE testProc AS BEGIN SELECT '1111' as col1, '2222' as col2 END
-- this call will fail with message Invalid object name '#tmpTable'. INSERT INTO #tmpTable EXEC testProc
SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...
This stored procedure works fine in Management Studio.
I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.
What does the error message mean and what have I done wrong?
Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
Can you create a temporary table using an ad-hoc query?
What I am trying to do is a type of filter search (the user has this and this or this) were i will not know how may items the user is going to select until they submit....that is why i can't use a stored procedure(i think)....any help on how to do this?
I'm having trouble with a query and ASP. The query itself is easy. I need a temporary table to be filled with the contents of a select and then i need to select out of the temporary table and return the results to the ASP.
INSERT into @results (ItemID, ItemDescription, ItemType, ItemRequestedBy, ItemStatus, ItemQuantity, ItemCostPer, ItemOrderNumber, DateAdded, DateLastEdited) SELECT * from cr_EquipmentData
SELECT * from @results
When I run this in Query Analyser, I get exactly the results I need... But when I try and run the ASP script, I get an error about the recordset not being open. (It's not the ASP checking 'cos when I run a simple select statement it works)
I appreciate there is no use for the query as it stands, but eventually I want to be able to perform not destructive statements on the @results table before returning the data to ASP. This is more 'testing' than anything at the mo'
I am writing a stored procedure that outputs it's information to a temporary table while it assembles the information. Afterwards, it returns the contents of the temporary table as the results of the stored procedure.
I found that if you create the table, inside the SP, as an ordinary table, the information builds to that table considerably faster than if you use a true temporary table.
I found that if I create a user function that returns a table as it's return value, it is also as slow as if I used a true temporary table.
The database can amass over 2 million records in one table in just a few days. If I have the procedure query against this table, and output to an ordinary table it creates, and summarize the information it is adding to the table, then it takes an average of around 4 minutes to return the results from the query. If I change the output table to a temporary table (#temp), it between 12 and 15 minutes. Nothing else in the procedure changed. Just the kind of table. If I take the logic and move it to a function which returns those results in a RETURN table, it also takes over 14 minutes.
Why would it take so much longer outputing to a temporary table rather than a normal table? Is it because temporary tables are stored in a different database (tempdb)? Why would returning query results from a function be just as slow?
Hi, I am trying to join two tables usig two temporary tables.I want the Output as table2/table1 = Output
Select temp2.Value/temp1.Value as val from ( ( select count(*)as Value from [Master] m inner join [Spares]s on s.SId=m.SID where m.Valid_From between '2007-06-01' and '2007-06-30' )temp1 Union ( select isnull(sum(convert(numeric(10,0),s.Unit_Price)),'0') as Value from [Order] h inner join [Spares] s on s.Number = s.Number where h.Valid_Date between '2007-06-01' and '2007-06-30' ))temp2 as t
Our java application is running on oracle and now we would like to port it to sql server 2000. In oracle we have a global temporary tables that has an option on commit to delete rows. Now I am looking for the same option in sql server but as far as I can see sql server's local temporary tables are visible per connection. Since the connection are shared in the pool it seems I can not rely on local temporary tables since the connection does not get closed at the end of the user's session. I need something that is visible per transaction not per connection.
Is it a better approach just to create a regular table and basically have a trigger to delete all data on commit?
If a stored procedure invokes another stored procedure that creates atemporary table why can't the calling procedure see the temporary table?CREATE PROCEDURE dbo.GetTempASCREATE TABLE #Test([id] int not null identity,[name] as char(4))INSERT INTO #Test ([name]) VALUES ('Test')CREATE PROCEDURE dbo.TestASEXEC dbo.GetTempSELECT * FROM #Test -- Invalid object name '#Test'.Thanks,TP
Hi, just a quick question regarding performance and speed.Q. Run a complicated query three times or create a TEMPORARY table andaccess it as needed?I have a page where it will be accessed 10,000+ a day.In that page I have an SQL query where it involves 3 different tables(approximate table sizes T1) 200,000 T2) 900,000 T3) 20 records)I'll be running that query 3 times in that page...One to retrieve the content and display it on the page.Second to count the number of records (using COUNT(*) function)And third to retrieve the content regions. (using DISTINCT function)What would be the best way of doing...Running the SQL query 3 timesOrCreate a temporary table and access it as many time as I needRegards,
I would like to know from other members whether they are successful in using temporary tables within a stored procedure and use that stored procedure in a report.
My scenario is like this:
I have a stored procedure A which fetches the data from different tables based on the orderno passed as input parameter.
I have built another stored procedure B with a temporary table created and inserting the rows in to this temporary table based on vendor related specifc orders by calling the above procedure A.
I have used this stored procedure in the dataset created and getting this error:
Could not generate a list of fields for the query. Check the query syntax, or click the Refresh Fields on the query toolbar.
Does anybody encountered this and have a resolution.
The table definition for @temp is the same as TBL_EVENT_DEFINiTION. The problem is I need to add a field to the @temp table and define the values using an update statement. If I include the additional field in the @temp table declaration then I get an error saying something to the effect of the number of fields is different. Is there a way of altering the temporary table to add this field?
I'm creating a temporary table in a procedure to help me with some calculations. I would like the name of the temporary table to be 'dynamic', I mean, to have the hour it was created, something like this: create table #myTempTable15May11:10:00, so if someone access the procedure while it's running, he won't have problems in creating his 'own' temporary table (create table #myTempTable15May11:10:02). Is there anyway I can do this?
I tried to make a DTS to transform data in a text file, I used a Store Procedure that use a temp table (#Resultados) but the DTS give me an error.
I read that in this case I canīt use local temp tables but I can use global temp tables, then I changed in my Store, #Resultados by ##Resultados, bu the result was the same.
SELECT LEFT(RP.Grupo,3) + LEFT(RP.Equipo,12) + LEFT(RP.SubGrupo,2) + LEFT(D.Fecha,8) + D.Valor as Dato FROM ReportesPlantilla RP LEFT JOIN ##Resultados D ON RP.Planta = D.Planta AND RP.Etapa = D.Etapa AND RP.GrupoEquipo = D.GrupoEquipo AND RP.Equipo = D.Equipo AND RP.Concepto = D.Concepto AND D.Fecha BETWEEN @FechaIni AND @FechaFin
Can some one tell me how to display the fields of temporary table in the report? In the query a global temp table is created and in the end i am displaying the fields of that table. How do i do that?
I need to use a temporary table within a stored procedure (which is a transaction), should I use local temporary table or global temporary table?
If I use a temporary table in my transaction, do I destroy the temporary table at the end of the transaction, or just leave it, and let the sytem clean it up? If I destroy the temporary table at the of the transaction, what happens if another user session is accessing the temporary at the very moment?
I am searching for information on paging large datasets, and have found some that involve creating temporary tables in the database. Before I head off and implement something, I have a number of issues I'd like to bounce around here.
1. An example I found on MSDN involves creating a temporary table, copying relevant columns to the row in the temp table. Why do this, rather add the source tables primary keys into the temp table, and do a join? Example; browsing Products Catalog which is categorised into hierarchies. The MSDN version would have a temp table created with a incrementing field which is used for the paging, and then a number of fields are also copied from the products table to the temp table - my question is why not simply copy the product primary key into the temp table, and then join?
2. In real life, do people allow each user to create their own temporary tables? If I have 1000 concurrent users, all wishing to perform a page-based browse, I would be creating 1000 new temporary tables. Do people consider default temp tables, that is, creating a default temporary table for browsing each category in the products table, for example?
3. Do you have any advice/tips for this type of problem?
In Access 97/2000, a local table can be created in the program database, while the data is stored in a linked database. This is useful for me to do a sort-of iterative drill-down on linked tables, e.g. filter table1 into table2, then further filter table2 into table3 and so on.....
If I do this I leave a trail that I can navigate in reverse (back up a step). This is very important in my app.
So now my problem is....I'm trying to duplicate this in an ADP file using only ADO 2.5. I have no local tables, only server tables. If I create a temporary table, it only exists until the procedure ends.
Is there any way to create temporary tables that are specific to a user session and do not automatically delete themselves, or is there a better way to do this in SQL7?
on sql-server-performance.com i read : Do not create temporary tables from within a stored procedure that is invoked by the INSERT INTO EXECUTE statement. If you do, locks on the syscolumns, sysobjects, and sysindexes tables in the TEMPDB database will be created, blocking others from using the TEMPDB database, which can significantly affect performance. [6.5, 7.0, 2000] Added 9-1-2000
I have a question does this negative effect also include simple SQL commands apart from stored procedures. For example if from vb i execute a "Select into" temporary table. Will this have the same negative impact as with executing this from a stored procedure ?
