Query With View Locks Database (was Problem With Viiews)
Jan 20, 2005
Hi,
I am joining a table with a view in my query to get the desired data. But when I run this query it does not produce any result, instead the execution goes on never ending finally locking the database.
Surprisingly if the selected data from this view is put in a temporary table and that table is joined with the table to get the result, it works fine.
Could anybody please help me with this as creating a table every time would be slow procedure. Is there any restrictions related to views which may be I have ignored.
I want to make SQLTransaction as global and use it checking the State. But then where there are Only Select queries are going to fire, it will open transaction.
So, Does it locks database/tables when Only Select query comes in SQLTransaction.
If you have another successful way of doing this, Please suggest.
Hi,We have SQL 7 running on Windows 2000 Server. For some reason we areunable to view Locks / Process ID from workstations running Windows XPSP2 with Enterprise Manager. Nothing shows up in the window on theright. All it's says at the top of the window is "There are no items toshow in this view" If we use Enterprise Manager on the server, we canview the Locks / Process ID. We can view both, Process Info and Locks /Object from the workstations and server, just not the Locks / ProcessID. We used to, but I don't know what changed. I'm thinking it mighthave to do with upgrading to XP SP2, or a Windows update, but I'm notsure.Has anyone out there experienced this? Or does anyone know thesolution?Thanks,Mark
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks -- select * FROM sys.dm_tran_locks --where request_session_id = 77 GROUP BY request_session_id ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
Hi,In our environment a database view is based on columns in a table from anoracle database.This is a linked server I believe.I am told that since we use this construction no where clause is possible.So we get to many rows in our database which we have to filter later on.How can we do this differently ?? Since it takes a lot of time to go throughthis process.Bye,Arno de Jong, The Netherlands.
Hey all,I'm having this weird problem on my Windows 2003 Server machine..Whenever I open two connection pages in Query Analyzer, then close oneof them, it locks up and I have to kill the task.I read a posting somewhere that said this could be due to an MDACinstallation problem, but I've run MDAC Component Checker and all filesare ok, version 2.8. Also, since I'm running Windows 2003 and MDAC 2.8is the latest, I can't reinstall it.There has to be some other cause for this. Why would QA lock up everytime I close a page?-John
Our enterprise consists of a mix of mission-critical transaction-oriented applications. The ERP applications are accessed by 70 branch offices around the country. The network also carries file transfers, e-mail, and Web traffic. Each branch is linked to headquarters via a Frame Relay network with port speeds of 64Kbps.
We are facing problems when ERP database locks up and cause time-outs and frustrating messages such as: "User disconnected - unknown error."
I've a stored procedure that grabs from a table all of the records that have a status = 2. Once these records are grabbed the same stored procedure needs to update the status of the records to 3. Something like this:
SELECT * FROM [MyTable] WHERE Status = 2
UPDATE [MyTable] SET Status = 3 WHERE Status = 2
What I'm afraid will happen is that between the running of the select statement and the running of the update statement that a record that originally had a status of 1 will be set to 2 - which means the record was never selected by the first half of the stored procedure but had its status updated to 3 in the second half of the stored procedure.
I thought at first wrapping all of this in a transaction would fix the problem but I've done some more reading and that doesn't seem to be the case. I need the Update to run on only those records the Select query grabbed no matter what else happens in the table.
Is there a lock to address this in a way that will allow other records (those not selected) to be updated to a value of 2 without them being updated to 3 when the Update query runs?
The database is configured for single publisher, many subscribers,merge replication. The maintenance plan started to fail a couple ofmonths ago and the database would not get backed up. After clearingall the locks, I am able to backup the database manually. The locksreturn again and I'm not able to backup the database with themaintenance plan. How can I get around the lock issue or solve it sothat I can backup the database again?Thanks,Chris
I had application that access the database 'test'. Now i created a ETL package, in this Souce database is 'test' and destination database is 'mytest'. I have scheduled the job for every hour.
Now, i want to know that whether any locks will create on source database 'test' while ETL package is running.
} if (ReplicationComplete != null) ReplicationComplete(this, true); } catch(Exception ex) { if (ReplicationComplete != null) ReplicationComplete(ex, false); }
}
Then later, if I try to update, insert or delete to the database, the application will hang. I can read from it, but I cannot write. If I close the application down and open it back up without replicating, I will not get any lockups. It also will not hang up after any replications prior to the create replication. I think I am doing something wrong in the initial replication that is holding on to some connection to the DB causing it to lock up. Has anyone seen anything like this before?
I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.
I just ran into an issue with cascading locks due to a SPID on one of my production servers. When researching the lock, I noticed that there was no sql text. SP_Who 2, nor the following query captured anything,
I spoke to the user causing the lock and he ran into a visual basic error when this occurred and didn't close out that window. So my guess is that it's due to an uncommitted transaction. However, shouldn't I still see something if that was the case?
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
I recently started working with a database that uses several views, none of which are indexed. I've compared the execution plans of querying against the view versus querying against the tables and as best I can tell from my limited knowledge the two seem to perform equally. It seems to me that having the view is just one more thing I need to keep track of.
I've done some google searches but haven't found anything that really tells me which performs better, querying the view or the tables directly. Generally speaking which is better?
Hi I have an application that I have started to develop. I have successfully set the connection to open the SQL Server database that I created. When I first started on the program, I was able to create Table Adapters by dragging the tables or stored procedures onto the DataSet work surface and going through the configuration process. At one point, however, I stopped being able to see any of my stored procedures in the database view although they are there because when I go to create a new table adapter, I am able to right click and add the new adapter and find the stored procedure in the wizard.Is there any way I can reset this so that my stored procedures are visible again. I have tried refreshing to no avail - and I think this is creating problems in other parts of the application.Any help appreciate.Roger
Hello,I have a View called View1 with the field ID, F1, F2, F3. Now I need to check if (Total F1 < Total F2 + Total F3) per ID, if yes fetch all records (so if condition matches, I need to bring rows, not only totals) how can I write my view query to handle this?Thanks,
Is there a way to access your views in x database from the master database?
I'm getting this error
Invalid object name 'v_StatisticsScalars'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.
I have a View which does what I need it too-select distinct d.deptid, d.[description], d.supervisor, d.approvedby, case when c1.cnt = c2.cnt then 'reviews are complete' else 'reviews still in progress' end as deptstatusFrom department dLeft join (Select Count(*) cnt, deptid from employee group by deptid) c1 on (c1.deptid = d.deptid)Left join (Select Count(*) cnt, deptid from employeeeval join employee on (employeeeval.employeeid = employee.employeeid) group by deptid) c2 on (c2.deptid = d.deptid)Brings back data as follows:Cast - Cast - 00999 - 09246 - reviews still in progressCMP- Copper Metal Products - 03315 - 09246 - reviews are completeCNS- Copper Narrow Strip - 07530 - 09246 - reviews still in progressCRW- Copper Rod and Wire - 01950 - 09246 - reviews still in progressSo I did the following: select e.DeptID, e.LastName + ', ' + e.FirstName AS EmpName, e.EmployeeID, u.UserName, CASE WHEN ev.approved is null THEN 'Not Started' ELSE 'In Progress' END AS Status, d.deptstatusfrom vw_DeptStatus d Left OUTER JOIN Employee e ON e.deptid = d.deptid LEFT OUTER JOIN EmployeeEval ev ON e.EmployeeID = ev.EmployeeID LEFT OUTER JOIN [User] u ON u.Department = e.DeptID WHERE (u.RoleID = 'supervisor') AND (e.CompanyID = '21') AND (e.FacilityID = '01') -- and (ev.PersonalScore is null)ORDER BY e.DeptID, e.LastNameNow this isn't bringing back what I want--If run the query w/out joining it up w/the view it brings back the following: Cast - Atkins, Carl - 09935 - Chris Burke - Not StartedCast - Bridges, James - 09929 - Chris Burke - In Progress CNS - Cunningham, Kenton - 02100 - Kahle Rummer - Not StartedCNS - Mitchell, Bill - 06200 - Kahle Rummer - In ProgressNow what I really need it to do is (below are the results from my View joined in with my query) -- return all the dept's--regardless if they are finished. If they are complete I only need ONE row, not all the rows for that dept. (highlighted in RED) I added these rows in-- Doesn't have to be NULL but maybe a blank string.Cast - Charlton, Maurice - 01313 - Chris Burke - In Progress - reviews still in progressCast - Dorsey, Steve - 02455 - Chris Burke - In Progress - reviews still in progressCMP - NULL - NULL - Fred Grove - NULL - reviews are completeCNS - Bennett, Mark - 09911 - Kahle Rummer - In Progress - reviews still in progressCNS - Buckingham, Mark - 00964 - Kahle Rummer - In Progress - reviews still in progressCRW - Eubanks, Kellie - 07599 - Rick Cramer - In Progress - reviews still in progressCRW - Luikart, Tyler- 09908 - Rick Cramer - In Progress - reviews still in progressMicroMll - NULL - NULL - Tim Cross - NULL - reviews are completeI hope this makes sense to someone -- if you have any questions just ask me. Another note I need to bring this back in a DS --for Crystal Reports. So the outline looks like this: Dept ID- Supervisor - Reviews still in Progress Any unfinished Reviews for that dept Dept ID- Supervisor- Reviews Complete No data shown since COMPELTEDept ID- Supervisor - Reviews still in Progress Any unfinished Reviews for that dept
I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields : id and child. So, my table currently looks like this:
I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS Select * from don0001 left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
Hi,I have a view(A) and I am trying to do a join on another table (B) toinclude only rows where date values in view A is greater than in tableB. I also want the view to pick up rows in viewA based on date values.Here is what I have so far:SELECT *FROM viewA vwleft JOIN tableB tb ONvw.id = tb.id and(vw.date1 > tb.date1 orvw.date2 > tb.date2 orvw.date3 > tb.date3)WHERE vw.date4 > getdate()-1Not matter what kind of join I use I can get both the rows from theview where dateA > getdate()-1 AND where date1-3 are greate than intableB. Dates 1 - 4 seperate date fields. Could someone please tellme what I am doing wrong.Thanks.
I have a SQL 2000 table containing 2 million rows of Trade data. Hereare some of the columns:[TradeId] INT IDENTITY(1,1) -- PK, non-clustered[LoadDate] DATETIME -- clustered index[TradeDate] DATETIME -- non-clustered index[Symbol] VARCHAR(10)[Account] VARCHAR(10)[Position] INTetc..I have a view which performs a join against a security master table (togather more security data). The purpose of the view is to return allthe rows where [TradeDate] is within the last trading days.The query against the view takes over around 30 minutes. When I viewthe query plan, it is not using the index on the [TradeDate] column butis instead using the clustered index on the [LoadDate] column... Theodd thing is, the [LoadDate] column is not used anywhere in the view!For testing purposes, I decided to do a straight SELECT against thetable (minus the joins) and that one ALSO uses the clustered index scanagainst a column not referenced anywhere in the query.There is a reason why I have not posted my WHERE clause until now. Thereason is that I am doing what I think is a very inefficient clause:WHERE [TradeDate] >= fGetTradeDateFromThreeDaysAgo(GetDate())The function calculates the proper trade date based on the specifieddate (in this case, the current date). It is my understanding that thefunction will be called for all rows. (Which COULD explain theperformance issue...)However, this view has been around for ages and never before caused anysort of problems. The issue actually started the day after I had torecreate the table. (I had to recreate the table because some columnswhere added and others where renamed.)On a side note, if I replace the WHERE clause with a hard-coded date(as in 'WHERE [TradeDate] >= '20060324'), the query performs fine butSTILL uses the clustered index on the [LoadDate] column.
Hi All, I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?
Does anyone have idea how to join table that can reflect both left and right join? Table A Table B Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5 A Jan 5 xxx A Jan 1 yyy B Feb 3 C Mar 4 B Mar 4 C Apr 3 C Apr 2 D May 2 E Mar 1
Result Table Column1 Column2 Column3 Column4 Column 5 A Jan 6 (= 5+1) xxx yyyy B Feb 3 B Mar 4 C Mar 4 C Apr 5 (= 2+3) D May 2 E Mar 1
So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).
