I'm running SQL Server 7.0 and in the morning received the following message
and immidiately after the server went for a reboot.
ERROR :
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth..
However,I fail to understand why I got the above error even though the temp database is configured to grow automatically by 10% and also why did the SQL
Server went for a reboot? Can anyone help me on this
Thanks!
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"?
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
I am using SQL 2008 r2. I have two SQL Queries from 2 different database but they share one server. I need to linked these two SQL Queries as they share the same Primary key which CustomerID see example below
Query 1
Database::Student Select StudentID , FName, LName From Student
Query 2
Database ::Finance Select StudentID,Tution FROM Payment
I need to be able combine two query which come from two database but they share one server.I would like to use two temp tables so that I can perform a left / right join to retrieve the data by linking two queries using primary id which they both share ( StudentID)
Summary : I have two DB's on the same server. I have two simple select queries for each DB which work correctly.
I have seen a bunch of ways to get the size of all the tables within a database posted on this board. I decided to modify an older one I found here (http://www.sqlteam.com/item.asp?ItemID=282). I set it up so there is no cursors or temp tables. Pretty much just one select statement to return all the info you would need. It seems to be faster than anything I have seen so far. Take it for whats its worth. Thanks to the original creator.
/* Original by: Bill Graziano (SQLTeam.com) Modified by: Eric Stephani (www.mio.uwosh.edu/stephe40) */
declare @low int
select @low = low from master.dbo.spt_values where number = 1 and type = 'E'
select o.id, o.name, ro.rowcnt, (r.reserved * @low)/1024 as reserved, (d.data * @low)/1024 as data, ((i.used-d.data) * @low)/1024 as indexp, ((r.reserved-d.data-(i.used-d.data)) * @low)/1024 as unused from sysobjects o
inner join (select distinct id, rowcnt from sysindexes where keys is not null and first != 0) ro on o.id = ro.id
inner join (select id, sum(reserved) reserved from sysindexes where indid in (0, 1, 255) group by id) r on o.id = r.id
inner join (select c.id, dpages+isnull(used, 0) data from (select id, sum(dpages) dpages from sysindexes where indid < 2 group by id) c full outer join (select id, isnull(sum(used), 0) used from sysindexes where indid = 255 group by id) t on c.id = t.id) d on r.id = d.id
inner join (select id, sum(used) used from sysindexes where indid in (0, 1, 255) group by id) i on d.id = i.id
I am a starter of vb.net and trying to build a web application. Do anyone know how to create a temp table to store data from database? I need to extract data from 3 different tables (Profile,Family,Quali). Therefore, i need to use 3 different queries to extract from the 3 tables and then store it in the temp table. Then, i need to output the data from temp table to the screen. Do anyone can help me?
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
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 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.
what is the best practice for sizing the temp db in sql 2000?what the best way to free up temp db space without shutdown the sql?how to monitor temp db parameters?
HI ALL,iam creating a temporary table using following Stored procedure but when i complile the Stored procedure iam getting the following errorServer: Msg 2714, Level 16, State 1, Procedure SP!, Line 15There is already an object named '#TEMP2' in the database.the stored procedure is ALTER PROC SP1@SELECT VARCHAR(15)=NULL AS BEGIN IF @SELECT ='FOLDER'BEGIN SELECT DISTINCT(HIERARCHY_ID),HIERARCHY_NAME,HIERARCHY_DESCRIPTION,HIERARCHY_PARENT_ID INTO #TEMP2 FROM BM_HIERARCHY_MASTER ENDELSE IF @SELECT='PAGE' BEGIN SELECT DISTINCT(HIERARCHY_ID),HIERARCHY_NAME,HIERARCHY_DESCRIPTION,HIERARCHY_PARENT_ID INTO #TEMP2 FROM BM_HIERARCHY_MASTEREND
Which one is better to use User Defined function or Temp Table?
I am working on SQL Server 2005, and I have a user defined function that returns a table. I need to run queries on this table.
I am not sure if I should run the function once and store the results in a temp table and run queries on the temp table or call the UDF multiple times. I am concerned about performance.
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?
Using SQL Server 7 I have the following files hanging out and I'm not sure what they are for or if I can delete them.
My data and logs are on the same RAID 5 drives. They are located in the E:DATA and E:LOGS directories. I have the following files on the root of my E: drive
tempdbData.ndf tempdbLog.ndf
tempdbData1.ndf tempdbLog1.ndf
this goes on up to tempdbData4.ndf. The data files are large up to 2 GBytes. The dates are not current except for the last 2 sequential files.
Anybody know what they are there for and my I delete any of these files?
INSERT INTO ... SELECT ... FROM ... WHERE ... . . .
When I create procedure as above, I got error message which I list below: ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++ Server: Msg 207, Level 16, State 3, Procedure pro_order, Line 40 Invalid column name 'orid'. ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++
Is this mean that I can not use identity in the #table?
The tempdb in a lot of OLTP MS SQL 6.5 servers of our customer site are experiencing continuous descrease in the available space. The more transactions the server has done, then more space is ocupied.
We have to restart SQL server service once a week. This has caused some inconvenience.
By the way, we are not tunning DBCC stuff to maintain the database regularly.
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.
Need a little help ...i am trying to create a temp table with a result set from a record query that yields, a two column multiple row resultset.
I would like to obtain the second column from the first result set and make that the first row in the first column of my temp table, then the first column from the second result set and make that the first row in the second column of my temp table, etc.
I.E
resultset:
color l number green l 0 blue l 1 red l 2 black l 3
TO #temptable
A l B 0 l blue 1 l red 2 l black
I have tried to do this
but yielded :
A l B 0 l NULL 1 l NULL 2 l NULL NULL l blue NULL l red NULL l black
Not exactly the results I need :( I would GREATLY appreciate any help!!!
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 2 sql tables. One containing sales agents and sub-agents (Up to 5 or 6 levels deep), the other containing customers. Each customer has an agent associated with them. Agents Table - AgentId - ParentId Customers Table - CustomerId - AgentId What I need to do is when you select an agent, I need to return all of that agents customers AND all of that agents sub-agents customers, and down the tree like that. I think the way to do this is to use a Stored Procedure to create a TEMP table and then select theh top level agent, then continue a loop to get their subagents, then each of their subagents, etc.. and put the results all in this temp table. I then can call the temp table and retrieve the customers where the agent id matches any of the agents in the temp table. Am I thinking the correct way on this? I have never even built a stored procedure before, so can anyone help me with any tutorials or syntax for doing this? I still haven't been able to google anything yet. Thanks!
I have a very long stored procedure running with a temp table in it. While the stored procedure is running (and the temp table hasn't been dropped) How can I query the temp table.
I've tried something like the following with no success:
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.
select sum(total) as app from f where f.file_id = g.file_id and f.wire = 1 and f.desc = 'app'
like above query i have to select sum(total) for different descriptions.I want to keep all these in temp table and use those fields in main table.How do i do that?Iam new to SQL can anybody help me.
I could not figure out, if this is a feature. I set TempDB to autogrow, it accepts it but when I go back to it it is showing as fixed growth or max. size as 2GB. Has anybody encountered that ?
I have a developer that used temp table to store data using the select * into #tbl. Apparently the data got delete, I was just wondering how hong does the temp #tbl hold the data, and when does it delete the data?
