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.
Thanx in advance.
Regards,
Sushma
View 1 Replies
ADVERTISEMENT
Jul 12, 2006
Hi,
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.
Thanking you.
tats
View 8 Replies
View Related
May 29, 2006
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
View 1 Replies
View Related
Jul 16, 2015
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:
insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15
[code]...
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.
View 9 Replies
View Related
Aug 5, 2015
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.
View 7 Replies
View Related
Jul 20, 2005
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.
View 2 Replies
View Related
Jul 23, 2005
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
View 11 Replies
View Related
Sep 18, 2006
What could be the query or the stored procedure to list locks or deadlocks ?
Thanks
View 2 Replies
View Related
Jul 9, 2002
How do I temporarily lock a database (read-only mode) programatically?
For example:
BeginTrans
//LOCK OUT ALL OTHER USERS
//Only the Server has Write access
CommitTrans
THANKS!
View 1 Replies
View Related
Nov 22, 2007
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."
Rachel
View 5 Replies
View Related
Oct 30, 2007
Greetings:
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?
View 7 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Jan 24, 2008
Hi,
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.
Thanks
Dinesh
View 5 Replies
View Related
Dec 2, 2014
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
View 9 Replies
View Related
Jan 3, 2008
Hello all,
When I create a new database and replicate to it using BeginMonitoredBackgroundSync :
Code Block
public void BeginMonitoredBackgroundSync(string User)
{
CreateReplicationInstance(User);
repl.BeginSynchronize(
OnSimplifiedSynchronizeComplete,
SqlCeReplication_OnStartTableUpload,
SqlCeReplication_OnStartTableDownload,
SqlCeReplication_OnSynchronization,
repl);
}
private void CreateReplicationInstance(string User)
{
repl = new SqlCeReplication();
string host = repl.HostName;
repl.HostName = User;
string dbFilePath = "";
dbFilePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) +
"\" + repl.HostName + ".sdf";
string myConnectionString = string.Format("Data Source = {0};PWD = {1}", dbFilePath, sqlSettings.Items["SqlPassword"]);
repl.InternetUrl = dynamicsReplicationSettings.ReplicationSettingsItems["InternetUrl"];
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.Publisher = dynamicsReplicationSettings.ReplicationSettingsItems["Publisher"];
repl.PublisherDatabase = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherDatabase"];
repl.PublisherLogin = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherLogin"];
repl.PublisherPassword = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherPassword"];
repl.Publication = dynamicsReplicationSettings.ReplicationSettingsItems["Publication"];
repl.Subscriber = "RemoteSubscription" + repl.HostName;
repl.SubscriberConnectionString = myConnectionString;
repl.ConnectionRetryTimeout = 120;
repl.LoginTimeout = 120;
repl.CompressionLevel = 6;
if (File.Exists(dbFilePath))
{
FileInfo info = new FileInfo(dbFilePath);
if (info.Length <= 20480)
{
File.Delete(dbFilePath);
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
}
else
{
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
primeConnection();
}
After the replication finishes, I dispose the replication object like so:
Code Block
void OnAsyncSynchronizeComplete(IAsyncResult asyncResult)
{
try
{
repl.EndSynchronize(asyncResult);
if (repl != null)
{
repl.Dispose();
repl = null;
}
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?
View 1 Replies
View Related
Feb 11, 2015
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.
The CROSS JOIN and CROSS APPLY seem suspect.
(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,
[code]....
View 9 Replies
View Related
Oct 1, 2015
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,
SELECT sqltext.TEXT
, sqlplan.query_plan
, req.session_id
, req.status
, req.command
[Code] ....
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?
View 4 Replies
View Related
Mar 9, 2006
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?
View 10 Replies
View Related
May 19, 2008
Greetings,
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?
Thanks in advance for your replies.
View 3 Replies
View Related
Jan 31, 2008
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
View 1 Replies
View Related
Jun 28, 2005
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,
View 1 Replies
View Related
Apr 25, 2005
Hi all!
I have a table that looks like this:
m_id | s_id1 | s_id2 | dt
m1 | 1 | 2 | 2001-01-01
m2 | 3 | 2 | 2001-01-02
m3 | 4 | 1 | 2001-01-03
m4 | 2 | 3 | 2001-01-04
m5 | 1 | 2 | 2001-01-05
m6 | 5 | 3 | 2001-01-06
...
I need to create a view that displays ONE m_id for every s_id, and that m_id should be the latest one...
ie:
s_id | m_id
1 | m5
2 | m5
3 | m6
4 | m3
5 | m6
I've been trying hard for a while, but I simply can't get it do display only one row per s_id...
any helponthis one would be greatly apreciated...
cheers.
View 3 Replies
View Related
Oct 5, 2006
Hi all, I ran a query, pasting in my sql text. I received this error.
Msg 8163, Level 16, State 3, Procedure All_Participants, Line 7
The text, ntext, or image data type cannot be selected as DISTINCT.
Msg 208, Level 16, State 1, Procedure Distinct_Proposals, Line 7
Invalid object name 'Proposal_Details'.
But, I cannot see the msg numbers in my script. How do I find Msg 8163? Excuse the ignorance....only my second day using this :P
Thanks,
Steve
View 3 Replies
View Related
Apr 7, 2004
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'.
View 4 Replies
View Related
Feb 17, 2006
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
View 3 Replies
View Related
Nov 15, 2004
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:
id child1 child2 child3
1 sam bob chris
and i would like it like this......
id child
1 sam
1 bob
1 chris
Can anybody help me? Thanks in advance,
Bob
View 3 Replies
View Related
Apr 9, 2008
Hi,
I have an accounting table with 5 year of data for an account, the table headers look as following
Acctno___Year___db_1,db_2,db,db_3,db4,….db_12,Cr_1,cr_2,cr_3…,cr-12
I want to with select statement to convert the data to query the header must be the following:-
Years1___Year2___year3___year4____year5
Db_1
Db_2
Db_3
.
.
.
Thanks for any help
View 3 Replies
View Related
Jan 23, 2015
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.
View 1 Replies
View Related
May 29, 2007
Dear Friends
I have 3 form with these names :
Clients <----- Projects <------- Files
I am using this query in my [ web ] page :
select clients.id ,
(select count(*) from projects where projects.clients_id = clients.id) as TotalProjects
from clients
I want to compute SUM for all relevant files for each client's projects.
how i can add it to select select statement ??
thank you.
m.o
View 5 Replies
View Related
Oct 8, 2007
Table 1
-------
NAME GROUPID
AAA 1
BBB 2
Table 2
-------
NAME GROUPID
CCC 1
DDD 1
EEE 2
Name & GroupID are column names.
I want to query these tables and get a result like that:
AAA CCC
DDD
BBB EEE
-----------
NOT
-----------
AAA CCC
AAA DDD
BBB EEE
Is it possible? If so, how?
Thanks in advance.
View 1 Replies
View Related
Jul 23, 2005
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.
View 3 Replies
View Related
Mar 28, 2006
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.
View 4 Replies
View Related
Jul 31, 2007
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).
Thanks for the help.
View 3 Replies
View Related
