Exclusive Lock During A SELECT
Aug 9, 2000
Anybody know how a SELECT statement can generate an exclusive lock on a table ?
I always thought that SELECT's take out shared locks. Is this something to do with temporary tables generated by ORDER BY's and DISTINCT ?
Rogue SQL below (from Site Server).
SELECT A.i_Dsid, A.i_Aid, A.vc_Val, A.i_Val, A.dt_Val, A.img_Val FROM Object_Attributes A, ( SELECT DISTINCT L.i_Dsid FROM Object_Lookup L , Object_Attributes OA2 (index = IND_vc_Aid) WHERE ((( L.i_ObjectClass = 9999 )) AND ( OA2.vc_Val LIKE ( '999999999.9999999%' ) AND OA2.i_Aid = 99)) AND (L.i_Container_Dsid = 99) AND ( OA2.i_Dsid = L.i_Dsid )) AS B WHERE B.i_Dsid = A.i_Dsid AND A.i_Aid NOT IN( 1, 2, 3, 4, 5 ) ORDER BY A.i_Dsid, A.i_Aid
Can anybody suggest a workaround ? Thanks.
View 2 Replies
ADVERTISEMENT
May 12, 2008
Hi ,
I have some issues with deadlock.I am getting deadlock becuase of two
select on same table. The sql server log is like this ,
Select statements should have always shared lock.I am not getting why
its doing exclusive lock and creating deadlock.
5/6/2008 12:38 spid4s Unknown Deadlock encountered ¦. Printing
deadlock information
5/6/2008 12:38 spid4s Unknown Wait-for graph
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:1
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594050117632 (8d036f07c58f)
CleanCnt:3 Mode Flags: 0Γ0
5/6/2008 12:38 spid4s Unknown Grant List 3:
5/6/2008 12:38 spid4s Unknown Owner:0Γ12E9F160 Mode: S Flg:0Γ0 Ref:1
Life:00000001 SPID:68 ECID:0 XactLockInfo: 0Γ353D1C54
5/6/2008 12:38 spid4s Unknown SPID: 68 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:ORXdes:
0Γ2FBB67F0 Mode: X SPID:112 BatchID:0 ECID:0 TaskProxy0Γ0792E378)
Value:0Γ38baa20 Cost0/11888)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
5/6/2008 12:38 spid4s Unknown Node:2
5/6/2008 12:38 spid4s Unknown KEY: 9:72057594049986560 (6f02e1cd37c3)
CleanCnt:3 Mode:X Flags: 0Γ0
5/6/2008 12:38 spid4s Unknown Wait List:
5/6/2008 12:38 spid4s Unknown Owner:0Γ12396EE0 Mode: S Flg:0Γ2 Ref:1
Life:00000000 SPID:90 ECID:0 XactLockInfo: 0Γ0AA8178C
5/6/2008 12:38 spid4s Unknown SPID: 90 ECID: 0 Statement Type: SELECT
Line #: 4
5/6/2008 12:38 spid4s Unknown Input Buf: Language Event: (@actDefId
nvarchar(36)@stateList varchar(1)@stateList1 varchar(1)@procRelObjType
smallint@procRelObjIdList varchar(36))
5/6/2008 12:38 spid4s Unknown Requested By:
5/6/2008 12:38 spid4s Unknown ResType:LockOwner Stype:ORXdes:
0Γ353D1C30 Mode: S SPID:68 BatchID:0 ECID:0 TaskProxy0Γ13B3E378)
Value:0Γ12e9e780 Cost0/6164)
5/6/2008 12:38 spid4s Unknown Log Viewer could not read information
for this log entry. Cause: Data is Null. This method or property
cannot be called on Null values.. Content:
If you have any idea regarding this please let me know ASAP.
Thanks in advance .
View 4 Replies
View Related
Sep 10, 2007
Hello All!
I want to perform 4 or 5 statements as a transaction but I need to make sure that during this complete transaction no one else inserts or deletes records from a table named SomeTable.
So how can I lock MyTable at the beggining of the transaction so that during my transaction no one else can insert or delete anything in table SomeTable?
Thanks!
David
View 9 Replies
View Related
Feb 21, 2000
Hi,
Is it possible to place an exclusive row lock when running a SELECT query by using a lock hint (or otherwise).
Basically, when a select statement is run against a table I don't won't any other users to read that row until it has been updated - at some later stage.
Any suggestions on whether this is possible would be welcome.
Thanks,
Karl
View 2 Replies
View Related
Apr 1, 2004
I'm relatively new to SQL and I've come across something that doesn't seem quite right. When an insert becomes part of an transaction I notice an exclusive KEY lock in Enterprise Manager. The table in question was using a Clustered index but I changed that, dropped the table and brought it back in but I still get the lock which keeps all others out of the table. Is this the expected behavior or is there something I am missing? Could the size of the tabe affect things? This is a very small table currently. I'm using MSSQL 7 sp3.
Thanks,
John
View 10 Replies
View Related
Sep 4, 2006
Hello,
We currently use SQL Serv 2000 with an ERP application and VB applications.
Both system work with the same database & tables but the ERP application seems to put exclusive lock on all tables it needs during processes. So our VB applications are not able to read these data and we receive TIMEOUT error.
What can I do ?
thank you
Nico
View 4 Replies
View Related
May 29, 2008
hi
I want to lock the record on select query so that no other user can update this record, is it possible and i want to unlock record when use stop view this record.
View 1 Replies
View Related
Apr 17, 2004
Hi,
i would like to know how to lock a table from other to view when select statement..can anyone help me with that???
i tried
SELECT * FROM IMIS_FRAMEWORK_RUNNING_NUMBER with (HOLDLOCK,ROWLOCK)
WHERE running_type=@type;
but i m still able to see my row when i perform my SELECT STATEMENT from other query analyser..
Please help me with this..thank you
View 6 Replies
View Related
Mar 12, 2007
Here is the situation i am stuck with, see the example first and below explained the problem:
-- 'SESSION A
create table foo (
id integer,
pid integer,
data varchar(10)
);
begin transaction
insert into foo values ( 1, 1, 'foo' )
insert into foo values ( 2, 1, 'bar' )
insert into foo values ( 3, 1, 'bozo' )
insert into foo values ( 4, 2, 'snafu' )
insert into foo values ( 5, 2, 'rimrom' )
insert into foo values ( 6, 2, 'blark' )
insert into foo values ( 7, 3, 'smeg' )
commit transaction
create index foo_id_idx on foo ( id )
create index foo_pid_idx on foo ( pid )
begin transaction
insert into foo values ( 9, 3, 'blamo' )
-- 'SESSION B
begin transaction
select id, data from foo with ( updlock, rowlock ) where id = 5;
-- Problem:
-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.
-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?
Appreciate your help,
Rajesh.
View 5 Replies
View Related
Feb 2, 2007
I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"
From progress:
Error: The Script returned a failure result.
Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
View 11 Replies
View Related
Apr 7, 2004
Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Please advice. Thanks a bunch.
Pankaj
View 1 Replies
View Related
May 22, 2006
Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.
From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"
Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)
I "sometimes" get the following error:
OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
Help would be appreciated!
I have seen other posts on this but, not able to relate the solution to my scenario.
View 9 Replies
View Related
May 10, 2006
Hi All,
I have seen a few other people have this error.
Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)
The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")
A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.
I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.
The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.
Any ideas?
Cheers,
Crispin
Error log:
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.
OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".
View 1 Replies
View Related
May 21, 2003
Hi,
When I open an application, it prompts me for a message that SQL is locked in exclusive mode by other application.
How to solve this?
thanks in advance
christine
View 3 Replies
View Related
Jan 30, 2004
Is there a way to do a logical exclusive OR (XOR) in sql server?
I'm trying to do this in where clause, something like:
WHERE
(not exists (select 1 from table a where a.date > '01/30/03') XOR
exists (select 1 from table a where a.date < '01/30/03'))
Thanks!
View 14 Replies
View Related
Jun 11, 2008
Hi,
How do you lock a table in exclusie mode before running a query?
thanks,
View 5 Replies
View Related
Oct 17, 2007
A problem about many to many relationships from an SQL beginner. Here's a contrived abstract example, as I'd prefer not to give away specifics.
Imagine I have two tables: users, food
The relationship (to like) is many-to-many so I've got a link table, which might look like the below:
andrew, apples
bob, banana
bob, apples
chris, carrots
chris, apples
chris, banana
I want to select users who like bananas and apples exclusively.
The answer should be 'bob' ONLY. select * from users inner join food on <IDs> where food in ('bananas','apples') isn't suitable , because it'll also return 'chris' who should be disqualified (because he also likes carrots).
Apart from potentially being bad DB design (although this is an abstract example; I do have ID numbers), can anyone suggest how to get this in a scalable way?
View 8 Replies
View Related
Feb 5, 2003
Hi,
i need to run a restore of a database overnight onto a different server using the live data .bak file. however the job failed on the first run (last night) with the error:
"Exclusive access could not be obtained because the database is in use. ...."
how do i gain this Exclusive use via an SQL statement please?
View 8 Replies
View Related
Jul 12, 2004
Hi.
I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?
(SQLServer 2000)
thanks.
Francisco
View 2 Replies
View Related
Feb 11, 2004
I have created a SQL Agent job that is supposed to essentially duplicate a production database to another database. The code I am using is:
step1
__________________________________________________ ______
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'kill '+cast(spid as varchar)+char(13)+char(10)
from sysprocesses where dbid=12
--Print (@SQL)
exec(@sql)
step2
__________________________________________________ ________
RESTORE DATABASE HIWDYNARPT FROM PRDBACKUP
WITH REPLACE
__________________________________________________ ______
This works when I test it during the day, however when it runs at night I get the following error in the job log:
Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I'm not sure why this happens because I have killed all open threads in step 1, and then create my own new thread in step two. Maybe someone else is initiating a new thread to quickly between the steps???
Anyway, I am trying to use:
__________________________________________________ __
ALTER DATABASE HIWDYNARPT
RESTRICTED_USER
WITH ROLLBACK IMMEDIATE
__________________________________________________ ____
...as an alternative to the T-SQL killing PID's, but SQL 7.0 SP4 does not seem to support restricted user like 2000. It keeps giving me a syntax error. Does anyone have any suggestions?
If I bring step 1 and step 2 together, separated by "GO", could this fix the problem?
Thanks in advance!
Ryan Hunt
View 5 Replies
View Related
Dec 8, 2007
Could anybody give a lead as to what I can do get rid off this error please.
I alread tried following:
use master
go
Alter Database dbname set single_user with rollback immediate;
go
Still have the issue. SQL 2005 Server actually did lock the db.
So ran
Alter Database dbname set multi_user;
go
and refresh Query and it switch back to multi user.
But I can't restore db yet.
Thank you
View 1 Replies
View Related
Jan 4, 2008
Are Intent exclusive locks compatible with rowlock?
I am getting a deadlock since i have ix lock on a page and another process(select query) is trying to acquire a shared lock.How can i solve this?
View 3 Replies
View Related
May 25, 2004
How to close the existing connections to a particluar database in sql server. Please note that i donot want to start stop sql server. I just want to close the existing connections so that i can do a restore on that database programatically.
I am using sqldmo for this purpose. Does anyone knows how to do that with sqldmo or is there any other method??
Waiting for your earliest replies
View 1 Replies
View Related
Feb 25, 2001
HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....
SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5
FROM TPS_ZIPUSA
.5 is where the query bombs. any idea why is this happenning?
by the way, i'm using sql server 7.0.
francis,
View 2 Replies
View Related
May 31, 2008
Hi!
We're using a backup with sql server agent when doing a backup / restore
procedure. In some cases I get the following error when the restore job
fails:
Executed as user: DOMAINAdministrator. Exclusive access could not be
obtained because the database is in use. [SQLSTATE 42000] (Error 3101)
RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.
Is it related to unhandled lock? I've resolved this by restarting the sql
server. But is there a way to avoid such issues?
One more question. Is it OK to backup/restore database while there're users
connected? Or I can do only backup?
View 2 Replies
View Related
May 5, 2008
Purpose
I would like to know if it is possible and if so how to implement exclusive access to a given record within in a database table.
Example
For example, you can open a file system file with exclusive access so other processes cannot access the file until it is released. I would like to implement a solution that provides this same exclusive access at the database record level. Preferably a solution that does not require creating and managing table access state flags. (i.e. FieldName.State = (Closed, Opened, Exclusive)) I would like for the database server to mange this solution and return an error status when trying to access a record that has been opened exclusively. Furthermore, I would like to avoid using triggers.
Application Utilization
Im working on a system that has multiple threads that are each responsible managing data from the same normalized table. Each of these system threads work on the same unique record in parallel. Therefore it is possible that thread (0)s changes could be overwritten by thread (n)s. I would like to have each thread open the unique record exclusively and release it when finished. All of the other threads would loop until the record is released.
Questions
1. Is it possible to implement this at the server level?
2. What would be the easiest best practice for implementing this functional requirement?
Thanks,
Sean
View 5 Replies
View Related
Feb 9, 2008
Hi, I've got three tables that I'm trying to pull data from. The first is a family of rings, the second is the individual rings and the third relates one ring to another:
RingFamilies
------------------------------------
FamilyID pk | FamName
------------------------------------
Rings
-------------------------------------------
RingID | FamilyID fk | RingName
-------------------------------------------
RingAssociations
-----------------------------------------
RingID1 pk/fk | RingID2 pk/fk
-----------------------------------------
I'm trying to pull a list of RingIDs and names for a given FamilyID. To complicate it, I want to exclude rings that are already associated to a given RingID, i.e. I only want the unassociated rings in a given family. To complicate it even a little more then name needs to be the FamName+RingName...is this possible? Thanks!
Given @FamID and @RingID
Result Table
------------------------------------------------
RingID | FamName + RingName
------------------------------------------------
View 4 Replies
View Related
Sep 29, 2007
Hi All,
My question is regarding SQL Server database security. I want to create a login using SQL Server Authentication and assign it db_owner rights for my database. So far so good. But the critical part is that I want to give exclusive rights for my database to this user only i.e. no other users (dbo, guest etc) should be allowed to access my database.
It will be good to present here the scenario which I need to implement. I am running an ASP.NET application that uses SQL server 2005 db at back end. The database server might have other databases as well but I don't want the administrator (either SQL server admin or the server administrator) to be able to get access to my database or even view the schema. I don't want any other user except my own user to be able to detach the database or perform backup or restore operations.
I hope I was able to deliver the requirement.
View 6 Replies
View Related
Apr 11, 2007
Hi
I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:
Exclusive access could not be obtained because the database is in use
Anyone know how to solve this problem?
Thanks
Li
View 45 Replies
View Related
Jun 15, 2007
I have a test database that is automatically restored from the live database each day at 2:30AM. I recently started receiving this message:
Exclusive access could not be obtained because the database is in use.
and the restore is not able to occur.
Any ideas on how I can prevent this from happening?
View 1 Replies
View Related
Nov 30, 2004
I restored a full backup of db...then I use this script to backup log, using the stop at function:
Restore log db from disk='e:Program FilesMicrosoft SQL ServerMSSQLBACKUPdb_tlog_200411300800.TRN' with
RECOVERY,STOPAT = 'Nov 30, 2004 1:00 AM'
when I run this script in Query Analyzer, I get:
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
No other connections are present to the database except my connection through QA ...what can I do ?..please respond ASAP
thank you
View 4 Replies
View Related
Jul 23, 2005
Ive been playing with this for a few days and thought I might thow itout for seggestions.I have Several Queries that need counts returnedThe Queries are Mutually Exclusive meaning whatever Query they returnin first they cannot be included in the counts of any queries belowthem.This set of queries for exampleSelect ID From Customers where FIRST_NAME = 'Chris' (would return say150)Select ID From Customers where ST='OH' (This would retunr say 50, BUTRun alone it might return 70, however 20 of those were in the firstQuery so they arent to be retunred in this result set.The total for Bot Queries would be 200But If I reverse it like soSelect ID From Customers where ST='OH' (This now returns 70)Select ID From Customers where FIRST_NAME = 'Chris' (This now returns130)The total of course for BOT Queries is 200 but I dont need that total Ineed the total for EACH Query depending on its orderingWhat I need are the single counts depending on the order in which thequeries are runIt seems like a recursion problem, but It might go past 32 level so Icant use recursive SQL ( I dont think )I've thought of (or tried to think how to use Not In, Not Exist, etcbut still dosent come up with the results....)How Can I grab the counts for each Query ?Chris
View 5 Replies
View Related
May 23, 2001
Hi,
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
Thank you for any help
View 1 Replies
View Related
