Global Temp Tables
Aug 11, 1998
Hi everyone:
I am creating an sp, in which I check for the existence of a global temp table (using the exists)
statement. If the Exists returns a false, I move on to processing without the temp table. If it
returns a true, I utilize the temp table to do some inserts. I create the temp table when my
application first starts up. The problem that I am facing is that the check for the temp table`s
existence seems to be failing. Is there any other way to check for the existence of a global
temp table??
Any info really appreciated
Thanks
Nisha
View 1 Replies
ADVERTISEMENT
Nov 17, 2004
Hi all,
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"?
View 2 Replies
View Related
May 29, 2006
Hi group,
I want to create several global temp tables. I've created a script:
if not object_id('tempdb..##tbl_ProductTypes') is null
begin
drop table ##tbl_ProductTypes
end
select * into ##tbl_ProductTypes from dbo.tbl_ProductTypes
This script creates a global temp table. When I run it in QA the table is created. When I close QA the table is dropped. This is correct since I found the following in BoL:
[Global temporary tables are automatically dropped when the session that created
the table ends and all other tasks have stopped referencing them. The
association between a task and a table is maintained only for the life of a
single Transact-SQL statement. This means that a global temporary table is
dropped at the completion of the last Transact-SQL statement that was actively
referencing the table when the creating session ended.]
I created a job that executes the statement above. The job exists with success, however the table is not created!
Why wasn't the table created??
TIA
Regards,
SDerix
View 4 Replies
View Related
Oct 5, 2006
Hi!
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-Parul
View 14 Replies
View Related
Jul 20, 2005
Can anyone tell me or post a link that says how many global temptables can exist SQL Server 2000? Also, is there a limit to thenumber of local temp tables that can exist?Thanks,Billy
View 1 Replies
View Related
Jun 3, 2015
I have two global temp tables in my stored procedure.Is it possible for me to make the execution of a stored procedure dynamic. Based on a flag parameter value I should get the result set from first temp table and viceversa ex: If my flag is 0.I should be able to get the result set from ##temp1 (select * from ##temp1).If my flag is 1.I should be able to get the result set from ##temp2 (select * from ##temp2).
View 4 Replies
View Related
Oct 4, 2006
Hi,
I want to drop a Global temp table within a stored procedure.
But first, I want to check it's existence.
So, what I would like to do is something like this
if exists
(select * from sysobjects where name like '##globaltemptable')
drop table ##globaltemptable
But I don't think the global temp tables get stored in sysobjects.
Any suggestions?
Thanks in advance
View 2 Replies
View Related
Mar 2, 1999
I have an sql file that contains several queries that are generating numbers to populate a sql table. The sql file is too large for a single sp so I am nesting them. I have 4 nested stored procedures. Each of the queries in each stored procedure dumps into its own global temp table. The final stored procedure needs to insert into a sql table all the information gathered in the global temp tables. So the final stored proc. looks something like:
"Create procedure usp_myProc_4 AS EXEC usp_myProc_3
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myTempTable (which was created in usp_myProc_1)
INSERT INTO mySQLTable (a,b,c)
SELECT a, b, c FROM ##myOtherTempTable
INSERT INTO......etc;"
I have done this befor and it worked fine. The only difference is that when I did this before these insert statements were being called from within an sp_makewebtask procedure.
Now when I try to save this final stored procedure it tells me "Invalid Object Name: ##myTempTable"
How do I call on these global temp tables from my final nested stored procedure?
Thanks for any help.
View 1 Replies
View Related
Apr 10, 2007
Hi all,
I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?
Thanks,
Ning
View 5 Replies
View Related
Feb 24, 2005
Hi:
A regular permanent table is not an option:
Need to exec a procA which at the end also exec procB.
procA will insert to table A and also generate a intermidate result set to a ##A global table, it works fine at this point. However, when it exec the procB at the end of exec procA, the ##A global table is empty when entering procB.
The key is I want to pass a records set to the procB without using a tableTempB. Does a ## global table should be still alive until procB execution is done?
I also tried use table variable, but it does not look like to accept @tableA
as part of the procB's parameters.
Also tried function, but it could not support a #tempTable within it which will do a dynamic query insertion.
thanks
David
View 4 Replies
View Related
Jul 23, 2005
I have a pivot table implementation, part of which is posted below. Itreturns no errors in query analyzer, but when profiler is run, it showsthat "Error 208" is happening. I looked that up in BOL and it meansthat an object doesn't exist. This block of code below works fine on mylocal development machine, but not on our shared development serveruntil I go into the tempdb and make the user have the role db_owner.Even wierder is that when I do a select * from ##pivot there is noerror, but if I specify the single column name (pivot) i.e. selectpivot from ##pivot, it takes the error...Obviously this is a rights issue, but is there any way around thisother than making the user owner of tempdb??declare @select varchar(8000), @PackageId intset @PackageId = 10set @select = 'selectCompany = COALESCE(Users.Company, Contact.Company, ''''),SubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactIdFROMSubContractPackageVendorLEFT JOIN SubContractPackage ON SubContractPackageVendor.PackageId =SubContractPackage.IdLEFT JOIN Users ON UserOrContactType = ''User'' AND UserOrContactId =Users.UserIdLEFT JOIN UserRoles ON UserOrContactType = ''User'' ANDUserRoles.UserId = Users.UserId AND UserRoles.ProjectId =SubContractPackage.ProjectIdLEFT JOIN Role ON Role.RoleId = UserRoles.RoleIdLEFT JOIN Contact ON UserOrContactType = ''Contact'' ANDUserOrContactId = Contact.IdLEFT JOIN SubContractLineItem ONSubContractLineItem.RefType = ''Package'' ANDSubContractLineItem.RefId = SubContractPackageVendor.PackageIdLEFT JOIN SubContractLineItem as SubContractPackageVendorItem ONSubContractPackageVendorItem.RefType = ''PackageVendor'' ANDSubContractPackageVendorItem.RefId = SubContractPackageVendor.Id ANDSubContractPackageVendorItem.RefSubId = SubContractLineItem.IdWhereSubContractPackageVendor.PackageId = ' + CAST(@PackageId as varchar)+ 'GROUP BYSubContractPackageVendor.Id, SubContractPackageVendor.isActive,SubContractPackageVendor.isAwarded, Users.Company, Contact.Company,SubContractPackageVendor.UserOrContactType,SubContractPackageVendor.UserOrContactId'--print @sqldeclare @sumfunc varchar(100),@pivot varchar(100),@table varchar(100),@FieldPrefix varchar(5),@TotalFieldName varchar(50),@PivotFieldFilter varchar(1000)select@sumfunc ='Sum(isnull(SubContractPackageVendorItem.Total,0) )' ,@pivot ='SubContractLineItem.Category' ,@table ='SubContractLineItem' ,@FieldPrefix='~' ,@TotalFieldName = 'Total' ,@PivotFieldFilter = ' AND RefType=''Package'' AND RefId=' +CAST(@PackageId as varchar)set nocount onDECLARE @sql varchar(8000), @delim varchar(1), @TotalSql varchar(8000)SET NOCOUNT ONSET ANSI_WARNINGS OFFEXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + 'WHERE 1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' +@table + ' WHERE '+ @pivot + ' Is Not Null ' + @PivotFieldFilter)SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )SELECT @delim=CASE Sign( CharIndex('char',data_type)+CharIndex('date', data_type) )WHEN 0 THEN '' ELSE '''' ENDFROM tempdb.information_schema.columnsWHERE table_name='##pivot' AND column_name='pivot'select * from ##pivotDROP TABLE ##pivot
View 6 Replies
View Related
Oct 25, 2007
Greetings guru's,
Is there a benefit to using global temp tables? I've read a few articles this morning and there doesn't seem to be a real need for them.
Am I missing something?
Adam
View 4 Replies
View Related
Mar 1, 2006
Senerio:
I have to extract data from a read only table using a globel temp table then export it to another OLE DB connection or to a flat file. But in the new SSIS packages it does not allow you to do thisusing the global ## symbols in front of a table name. How do I get around this?
Thanks
View 6 Replies
View Related
Feb 13, 2015
I have created a global temp table in Step1 of SQL Job.
I have used that in remaining steps of same job...i ran the job
But i got error message like invalid object name ##xxxxxxxx later i have included as tempdb..##xxxxxxxx also. the i got invalid reference for...
From my SSMS:-
But i was able to do select query for the same from my SSMS...
i have incorporated all steps in single step and completed job...
My question is why ##temp table created in step1 is not able to use in other steps of same job ?
SQL Server 2012 Enterprise Edition
View 2 Replies
View Related
Sep 7, 2006
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
View 16 Replies
View Related
Oct 9, 2006
I have the following execution of a global temporary stored procedure on a remote SQL 2000 server:
insert into targetTable
exec remoteServer.master.dbo.sp_MSforeachdb ' ', @precommand = 'exec ##up_fetchQuery'
This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with
insert into query_log exec remoteServer.master.dbo.##up_fetchQuery
I get execution error
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'master' ignored, referencing object in tempdb.
When I try
insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'tempdb' ignored, referencing object in tempdb.
with
insert into query_log exec remoteServer..dbo.##up_fetchQuery
or
insert into query_log exec remoteServer...##up_fetchQuery
I get
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?
Dave
View 3 Replies
View Related
Jun 24, 1999
I think this is a very simple question, however, I don't know the
answer. What is the difference between a regular Temp table
and a Global Temp table? I need to create a temp table within
an sp that all users will use. I want the table recreated each
time someone accesses the sp, though, because some of the
same info may need to be inserted and I don't want any PK errors.
thanks!!
Toni Eibner
View 2 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Dec 17, 2004
I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server.
Those procedures scheduled to run every 3 hours.
Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?
Your suggestions?
View 1 Replies
View Related
Feb 22, 2008
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)
View 1 Replies
View Related
Sep 6, 2006
I m trying to create a global temporary table whose data will be deleted after the end of the session..
the DDL in oracle is
CREATE GLOBAL TEMPORARY TABLE STUDENT
(
name CHAR(20),
) ON COMMIT DELETE ROWS ;
I want to know the corresponding DDL in SQL Server..
when i try the following
CREATE TABLE ##STUDENT
(
name CHAR(20),
)
the table gets deleted at the end of the session..help needed
View 4 Replies
View Related
Dec 9, 2014
What is the maximum no.of global temporary tables can create in sql server
View 4 Replies
View Related
Nov 3, 2000
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?
Thanks.
View 2 Replies
View Related
Jun 12, 2002
Hi,
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?
Seoras
View 2 Replies
View Related
Jun 16, 2004
I have a stored proc that creates a temporary table, then calls several other stored procs to insert data.
CREATE PROCEDURE usp_CreateTakeoff
@iEstimate int,
AS
CREATE TABLE ##Temp_Takeoff
(
Field1 ......
Field2 ......
)
-- 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.
Any ideas?
Mike B
View 4 Replies
View Related
Dec 14, 2004
Hey,
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?
Thanks
View 1 Replies
View Related
Apr 7, 2006
Hi,
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.
Thanks,
Jeff
View 6 Replies
View Related
Sep 4, 2007
hi All,
I am using a temp table creating it as
create table #process
(
tons of coomuns in here
)
then
insert into #process(collumns)
select from peon
where etc....
Can i use the same temp table definition , but insert into another tempTable.Does alias help me accomplish this task.
Thanks for your input
View 3 Replies
View Related
Dec 11, 2007
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
--Drop Table #ttDesign
--Drop Table #ttTopSellers
View 2 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Nov 15, 2007
I am having problem with my temp table.
I cannot get the syntax correct.
I need to do the following, If @mybit & br > 0 then
insert childid 'condidtion case when br & @mybit > then 0'
into the temp table.
CREATE TABLE #tmp_table(
childid integer null
)
IF @mybit & br > 0 THEN
INSERT INTO #tmp_table
SELECT c.childid
VALUES (childid,
CASE WHEN br & @mybit > 0 THEN 1 ELSE 0
END)
FROM
child c
View 3 Replies
View Related
Jul 23, 2005
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?
View 1 Replies
View Related
Apr 12, 2006
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
View 6 Replies
View Related
