what are some common techniques for ensuring an isolated temp table scope? For example, if 2 different sprocs happen to crud a temp table with the same name? I'm guessing that big SQL shops establish a standard for this early on to avoid conflicts between sprocs.
View 3 RepliesIn my sp I'm have an Insert statement. In the event that after the insert @@rowcount = 0 I'm throwing an error with RAISEERROR.
This is fine, however, in my CATCH block I'm referencing a local temp table but getting an error that it no longer exists.
My question is, does throwing an error drop any #temp tables? I thought they were in scope for the session?
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
Thanks,
Glen Smith
I can create a temp table like this, and immediately read the data:
declare @NAICSGroups table (NAICS2DigitCode int, NAICSShortTitle varchar(35));
insert into @NAICSGroups (NAICS2DigitCode, NAICSShortTitle)
values
(11,'Agriculture'),
(21,'Mining'),
(22,'Utilities'),
[Code] .....
I have a dynamic sql which uses Pivot and returns "technically" variable no. of columns.
Is there a way to store the dynamic sql's output in to a temp table? I don't want to create a temp table with the structure of the output and limit no. of columns hence changing the SP every time I get new Pivot column!!
The requirements are:
1. the user has read-only permissions to dbo tales.
2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables.
3. the user does not have any permission outside rpt schema, except permissions in #1.
The current solutions are:
1. grant the user select only on dbo tables.
2. make the user the owner of rpt schema.
3. Grant the user database permission on create table/create procedure/create view/create function.
My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,
"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.
Let's say that I have a stored proc that is assigned to a service broker queue and is constantly running while it waits for messages in said queue. When a message comes in on the queue, the stored proc creates a table variable based off of the contents of the message and performs various operations with the data. Since the stored proc is constantly running, do the contents of this table variable ever truly get emptied? Should I be deleting the contents of the table variable at the end of the operation to ensure that stale data doesn't persist?
View 5 Replies View RelatedI've recently started a new position and our production box. Contains a procedure that uses 30 + temp tables. I'm currently not in a position to change this as it's production and I would have to be granted a window to re-design.
However the tempDb is showing some strange activity.
If a table is created #CarrierService (CarrierServiceID,DeliveryZoneID,CollectionZoneID) for example
Once the procedure is called It will appear in the tempDB with the session info appended as expected
#CarrierService________________________________________________________2C78E45A
However once the session has ended the above table will get dropped and a new one created
#2C78E45A, I now have 7000 of these different Tables in the TempDB
When I Interrogate this using
SELECT o.name, o.create_date,o.modify_date , c.Name,C.Column_Id
FROM tempdb.sys.Objects o
Inner join tempdb.Sys.Columns c
ON o.object_id =c.Object_ID
WHERE o.type ='U'
I get the Following results
name create_date modify_date object_id name
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CarrierServiceId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282CollectionZoneId
#2C78E45A26/04/2015 18:0930/04/2015 14:55746120282DeliveryZoneId
Notice How It's getting Modified today.
For finding size values of temp database:
select * from sys.master_files -
size column value here is 1024 for .mdf,size here for .ldf is 64
select * from tempdb.sys.database_files -
size column value here is 3576 for .mdf,size here for .ldf is 224
Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?
1) "Deferred compile" recompile event occurs because of deferred name resolution. In other words, an object referred to in the statement does not exist at compile time. Later, when the object does exist, it requires a recompile of the statement so that it can create an optimal execution plan. One example of when a deferred compile will occur is if a temporary table is used in a batch and does not exist when the first statements in the batch are compiled.
View 2 Replies View Relatedhere's an example of what I am trying to do.
--Exec Database.Employees
--Use Database
--Go
--Create PROCEDURE AEM.TempTable
--AS
--BEGIN
--Select * into #emptemp From Database.Employees
--End
--Select * From #emptemp
Is something like this possible? I can get the EXEC to run the "Select * into #emptemp From Database.Employees" statement, but when I try to use the temp table it doesnt see it.
do you have a general rule of thumb for breaking a complex query into temp tables? For someone who is not a sql specialist, a query with more than a few table joins can be complex. So a query with 10+ table joins can be overwhelming for someone who is not a sql specialist.
One strategy is to break a problem into pieces so to speak by grouping together closely related tables into temp tables and then joining those temp tables together. This simplifies complex SQL and although not as performant as one big query it's much easier to understand. So do you have a general rule of thumb as far as a threshold for the number of joins you include in a query before you break the query into temp tables?
I am generating some dynamic sql which I would like to run and return the data from, however when I run EXEC(@TEMP_TABLE_STRING) it cannot return the table I think because it is out of scope.
Is there a way around this.
declare@Tablename VARCHAR(150) = 'TEMP_LOCATIONS'
declare@FilterClause VARCHAR(512)=NULL
declare@Classes VARCHAR(100)=NULL
declare@IsExcel BIT = 1
DECLARE @SQL Nvarchar(MAX) = ''
SET NOCOUNT ON;
[Code] ....
I have worked with many data mining softwares and found SQL Data mining part of Microsoft product needs to imrove a lot to whoo the customers.
Below are my temp tables
--DROP TABLE #Base_Resource, #Resource, #Resource_Trans;
SELECT data.*
INTO #Base_Resource
FROM (
SELECT '11A','Samsung' UNION ALL
[Code] ....
I want to loop through the data from #Base_Resource and do the follwing logic.
1. get the Resourcekey from #Base_Resource and insert into #Resource table
2. Get the SCOPE_IDENTITY(),value and insert into to
#Resource_Trans table's column(StringId,value)
I am able to do this using while loop. Is there any way to avoid the while loop to make this work?
I have a SQL Server 2008 instance that is running on "LiveServer" our production database (ProdDB) - and we need to upgrade to 2014. In order to do some upgrade testing, I spun up a VM with the same version of SQL server on the test VM (TestServer), did a backup of the production DB from the live server, and restored it to TestServer under a different name (ProdDBUA).
I then installed SQL2014 Upgrade advisor onto TestServer, and ran it, checking all the boxes (reporting services etc..) and it all came back clean - no issues whatsoever - not a single warning even. I'm under the impression that stored procs/functions etc... all reside within the DB, so a backup will include those. Is that correct?
The problem is, I know I have stored Procs, functions and views that use deprecated joins in that LiveServer.ProdDB. What do I need to do/configure/check in order to make sure that the Upgrade Advisor is actually checking through all that T-SQL that has deprecated code? I want to have a list to give to my report writers of procs/functions/views that need to be rewritten prior to the upgrade going live.
If there is a modification that needs to be run on the TestServer.ProdDBUA, a cursor to change the path etc. DB is running in Compatibility mode 90.
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
There r two types of temporary tables in sql servers
1) Local temporary table (#tablename)
2) Global temporary table(##tablename)
but when i m creating any temp table & using it in .NET it doesn't store all the data..
i am storing data one by one but only last inserted row is visible in that table...
any solution on that...
pls help
Thanks in advance...
Mukund Tambe
Hi,
A need a trigger to scope my data modifications but this code return EventData a NULL. Can someone help me with this?
Thanks
Code Snippet
CREATE TABLE [dbo].[_Log](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventData] [xml] NULL,
PRIMARY KEY CLUSTERED
(
[EventID] ASC
) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
---------
CREATE TRIGGER [dbo].[TRG_Clients]
ON [dbo].[Clients]
FOR DELETE,INSERT,UPDATE
AS
INSERT INTO [dbo].[_Log](EventData)
SELECT EVENTDATA()
Trying to do this all day and googling for answers but found none, hopesomeone can help. Thanks in advance.select * intoOPENROWSET('SQLOLEDB','SERVER';'uid';'pwd',##test) from LocalTableReason: I am joining local tables with linked server tables using theformat "LinkedServer.database.owner.object" to execute a query, ittakes forever to execute since the tables joined on the remote servershave more than 50Mil records. I read somewhere that sql server needs tocopy the tables locally to the temp db and does the join there, hence Iwas hoping to dump the data of the local database into a temp table onthe remote server and then do a join with OPENQUERY, which will executethe query on the linked server and return the results.
View 8 Replies View RelatedI'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
Hi I have the following Stored Proc which works in SQL Server 6.5 but not in SQL Server 7.0. All this Stored Proc does is Create a temp table, execute the DBCC ShowContig on a table and insert the results of the DBCC into a temp table. What am I missing. Thanks.
The code of the Stored Proc is:
/* This Stored Procedure Creates a temp table. (Step 1) */
/* Initializes a local variable @StirngToBeExecuted with */
/* a DBCC command. (Step 2) */
/* Step 3. The Command is Executed and the results of the */
/* DBCC command is inserted into Temp Table. */
/* Step 4. The results of the Temp table are shown on the */
/* Screen. */
/* This SQL Works Fine in SQL Server Version 6.5 */
/* In SQL Server 7.0 the results of the DBCC command is */
/* NOT getting inserted into the Temp table. WHY??? */
IF EXISTS (SELECT * from sysobjects where id = object_id('dbo.Test_sp') and sysstat & 0xf = 4)
drop procedure dbo.Test_sp
GO
CREATE PROCEDURE Test_sp
AS
DECLARE
@StirngToBeExecuted Varchar(100)
CREATE TABLE #temp( -- Step 1
OutputOfExecute Varchar(255)
)
-- Step 2
SELECT @StirngToBeExecuted = 'DBCC SHOWCONTIG (123456789)'
INSERT
INTO #temp exec (@StirngToBeExecuted) -- Step 3
SELECT * FROM #temp -- Step 4
DROP TABLE #temp --Drop the Temp Table
Hello
I have a local SQL2005 server with a linked SQL2000 server. I would like to know how to create a temporary table in the remote server in such a way that I can make an inner join as follows; my idea is to optimized a distributed query by doing so:
create table #myRemoteTempTable
insert into #myRemoteTempTable
select * from myLocalTable
update myRemoteTable
set
Value=#myRemoteTempTable.Value
from myRemoteTable
inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID
I am trying to insert a single row in Temp table #InventoryItems . The temp table is mentioned in a curosor childcur_inventory. While looping through the cursor I have mentioned nested if else condition . In the Else condition where I have mentioned PRINT "Hello World" I want to insert a single row in the temp #InventoryItems. I trying to use Select Top 1 but the cursor is looping indefinitely trying to insert multiple record
I want to insert only one record with null values.
DECLARE childcur_inventory CURSOR FOR SELECT Structure_Number, State, Neighbor_State, Border_Bridge_Structure_Number FROM #InventoryItems
OPEN childcur_inventory
FETCH childcur_inventory INTO @Structure_Number, @State, @Neighbour_State, @Border_Bridge_Structure_Number
[Code] .....
if a dataset doesnt use a stored proc to define a table variable, what is the scope of that table variable? Does the name need to be unique from such variables defined by other datasets?
View 4 Replies View RelatedI have a query which is part of bigger transaction:
;WITH CTE(
SELECT id, q,
totalQ=SUM(q) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING)
FROM dbo.myTable WITH (UPDLOCK, ROWLOCK)
), CTE1 AS(
SELECT id, q_take=CASE WHEN c.Total<@qRequired THEN c.q ELSE
@qRequired-c.Total+c.q END FROM CTE
WHERE (CASE WHEN c.Total<@qRequired THEN c.q ELSE
@qRequired-c.Total+c.q END)>0
)
UPDATE t set q-=c1.q_take FROM dbo.myTable t INNER JOIN CTE1 c1 ON t.id=c1.id
Because CTE query is executed separately of update query I must put UPDLOCK hint, otherwise myTable could be changed while my query executes by some other user.(I have snapshot row committed transaction).
The problem is that my hint will hold lock on rows of myTable until the end of outer transaction(the bigger one).
But I need lock only for the time of this small query execution. What would be the best way to achieve that?
Hi,
I would like to join two tables: one on a local server which I have admin access to and another server which I only have read access. The local table is very small, but the remote table is very large.
If I look at Query Analyzer's execution plan, it appears that the join will be done locally (i.e. the entire table is transferred from the remote server and then joined to my local table). Is there a way to create a temp table using linked servers, transfer my small local table to the remote server and then perform the join on the remote server? In the past, I have been able to use openquery to restrict the data to a small subset that is transferred but the local table is a little too large for that.
I appreciate any advice / guidance anyone can offer me!
Hello,
I apologize if this isn't the most appropriate place to ask but we have a SQL 2k server behind our website and we recently added a product review portion to our product pages. In the comments field in our db we are getting entries like:
1. Online Pharmacy, [url=http://w
2. iPb95U <a href="http://kmajvv (html tags are replaced in actual db)
My question is why the cut off? I just converted the code from escaped quotes and string concat to a stored proc using ADO params. The first entry was submitted with the sproc and the second while the string concat was still being used. So the visitor must be purposely truncating the entry, but I'm curious as to why. Is it just to try and break the code?
If anyone knows of better place to post questions like these, maybe a security forum, please let me know.
Thanks
I have a performance issue with one of the views when I join the view with a temp table
I have 2 Views - View1 and View2.
There is a third view - view_UNION where the
view_UNION =
SELECT * FROM View1
UNION ALL
SELECT * FROM View2
If I have a query like -
Select view_UNION.* FROM
view_UNION INNER JOIN #TMP ON #TMP.ID = view_UNION.ID
the execution is too slow.
But if I execute the views separately, I get good performance.
How to improve the performance of the view_Union
Hi, I am trying to pull e-mail addresses of certain users from a list of all the users in our company. I have two tables. One contains a single column of only the users I need. The other table contains every user and their e-mail address. Could someone tell me how to pull this data from these two tables. I am new to SQL Query and have been trying to figure out these JOIN statements, but nothing I am doing seems to work.
Thank you,
Lisa
Does anybody know of some good resources comparing transaction management techniques used in sql server. I am particularly interested in the areas of concurrency control, locks and recovery.
View 2 Replies View RelatedHi,
Still using 2000; I'm trying to figure out where to even start with optimization in SQL queries. BOL is very confusing to me on this point. I ran the following query with and without and index as seen below. Is Trace a good place to start?
DROP INDEX OrdDetails.OrderID_ind
GO
CREATE INDEX OrderID_ind
ON OrdDetails (OrderID)
GO
select count(*) from OrdDetails = 6467155 rows
GO
-- query; without index Duration 156; CPU 95; Reads 54
-- query; with index Duration 66; CPU 15; Reads 97
select OrderID, Count(OrderID) AS OrderIDCnt from OrdDetails
group by OrderID
having count(*) > 1
GO
Why are the reads more with the index? This is just a simple non-clustered index, which I believe is the default. Should I be starting elsewhere to find out this info?
thx.
Kat