Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Lock A Table Or Row In Sql?


hi, i have an application that updates some records in sql tables, and i want to do a  web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table  so i dont have concurrency problems.tnx in advance.


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
How To STOP Row Lock ==> To TABLE Lock- SQLSERVER AUTOMAGICALLY
Hi ALL

Environment
H/W-Dual PIII, 1GB RAM, 25GB Hard drives
S/W-NT 4.0 SP5, sqlserver 7.0 sp1, MTS, VB COM objs, ADO 2.1

This one is giving me some extra gray......
We have an order entry system, where we want to "create" invoices from about
20000- 25000 orders
at a time we WANT this to be a single transaction speed is not the issue
concurrency IS we DO not want
SQL to escalate the lock to table, but as we watch with sp_lock, row lock
occur for the first 2-3000 rows then it becomes a table lock.
I have read that if a query involves a table scan then it will escalate to a
table lock , that is why we have the hint to use the PK_OrderMain index, I thought sp_indexoption was going to help but all you can do is stop row/page locks
Does any one know how to stop sqlserver from automagically putting table
locks on ??????

our VB code looks some thing like this

sql = SELECT blah FROM OrderMain WITH
(ROWLOCK,UPDLOCK,INDEX=PK_OrderMain) WHERE OrderMain.OrderID in
(SELECT OrderMain.OrderID FROM OrderMain WITH (READCOMMITTED) WHERE
OrderMain.OrderStatus='A'

rs.open sql .....
while not rs.eof
...process orders
loop

if not errorflag
rs.updatebatch
end if

View Replies !   View Related
How To Lock A Table So Others Cannot Lock It
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 Replies !   View Related
Lock SQL Table Using Asp.net
Hi,all:
This problem almost drives me crazy, hope I can get some hints from you guyz!!!
Ok, here is the situation:
I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating.
It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!!
Thanx in advance!

View Replies !   View Related
Table Lock
Hi,If I run an insert statement from the query analyzer and then try toopen the table from enterprise manager then it takes long time to openthe table. But this problem dissapears when i put the statement insideBegin/End Transaction statement.Any idea why this is happening?Thank in advance.Taw.

View Replies !   View Related
Table Lock
Which lock type or isolation level should I use to be sure that no onewill read or write or do anything with the table I'm using?Code block should look something like this:lock tableread value from tablechange value to new_valueupdate table set value = new_valuerelease lockWhile I'm changing the value absolutly no one should be able to readfrom the table.

View Replies !   View Related
Table Lock...
Hello Friends,
I am having a VB application running for the SQL SERVER DB. The VB application is installed on the multiple of PCs in the network. Now when I am trying to fetch the same from all the different PCs simultaneously, its amazingly fast. But the issue comes when I am trying to update the same table (but different rows) from the different PCs simultaneously. The time taken is directly proportaional to the number of users. I am not getting what could be the problem? Can any one suggest me the approch? Is it some related to table / row / page locking? As all the connections are trying to update on the same table. I checked the isolation level. Its default, "READ COMMITTED". Kindly suggest...




Thanks in advance........
Rahul Jha

View Replies !   View Related
How To Lock A Table
Hai to all,

I want to lock a patricular table during my transaction is this possible, for Example

Create sp() as
BEGIN
Lock Table
Do Some work
Relaese the Lock
END

like this.

View Replies !   View Related
Table Lock
Hi Gurus,

How can we know that particular table is locked.



Thanks,
ServerTeam

View Replies !   View Related
Table Lock
Is there any way to lock tables in SQL Server?

View Replies !   View Related
Table Lock
How do I do a simple table lock?

Thanks,

MeTitus

View Replies !   View Related
Table Lock
Hi Friends
 
In my server i am getting this error how can i slove this error
could u people any body please help me
 
error:
Table Lock Wait Time Per Second is   867.3330
milliseconds per second, which exceeds threshold of 10                
milliseconds per second."              

 
 
Thanks
subu

View Replies !   View Related
How Do You Lock An Entire Table?
Hi,I need to lock a table so that Inserts are prevented as well as deleted andupdates. At present I'm thinking this might do it:SELECT * FROM myTable WITH(UPLOCK)but then again I'm not sure whether this will cover the insert case.Thanks,Robin

View Replies !   View Related
Lock Table Problem
HiI want to lock a table using JDBC as I want to perform some query's (readand write) in exclusive mode. Different threads will be executing the samecode simultaneously. I am using the following statementsStatement stmt = connection.createStatement();stmt.executeUpdate("lock table STATUS_TBL in EXCLUSIVE mode");but am getting the following error[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrectsyntax near 'STATUS_TBL'.can somebody tell the correct syntax for the lock table statement.Thanks

View Replies !   View Related
To Give A Table Lock
How can i give a table wise exclusive lock in MSSQL Server ?

I got the description but, How can I apply this ?
The sql :
LOCK TABLE <tablename> IN EXCLUSIVE MODE
is not working.

Is there any query/method to do this ?
Please help ...

thanks


About Exclusive locks
--------------------
Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.

Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.
--------------------

View Replies !   View Related
Keeping A Lock On Table Or Row
I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?

View Replies !   View Related
Help: Table Lock Confirmation
I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!

This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.

BEGIN TRANSACTION

UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @forum_id

SELECT @new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @forum_id

COMMIT TRANSACTION

View Replies !   View Related
How To Lock The Row Or Table When Update?
Hi,
I am working on a project which need to produce a sequential certificate number, Everytime I need a new certificate number, I need to find out what is the max number in the database and then the new certificate number just max+1.
But how can I block another
transaction to check what is max certi. number while this transaction is in the middle of writing the new certificate number(max+1) into database . Does ADLockOptimistic work in this case? Here is the code:
My database sql 2000.

cmdTemp.CommandText="Select max(certificateNumber) from product_table where certificateNumber<> 8888888"
set cert_info=Server.CreateObject("ADODB.RecordSet")
cert_info.Open cmdTemp, , AdOpenKeySet, adLockOptimistic
If Not cert_info.EOF then

certnumber=cert_info(0)+1

End if
cert_info.close
set cert_info=nothing

View Replies !   View Related
Table Lock-urgent
Hi,

How can I see which table is locked up by some particular process?
I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem?
I really appreciate your help..

View Replies !   View Related
Table Lock Issue?
Hi All

We are facing atable lock issue , while running a SSIS package
We have two flows . one for insert and other for update into the same target table.
The update is done using a procedure.

We have disabled the lock table option in the target( in the insert flow)
The first set of records which come for updates flow fine.
But when the records start coming into the insert flow, the data flow stops.

Is this a table lock issue?
How do i set the commit interval at the update flow?
can someone lpease help me out of this situation

Thanka and Regards
Preethe

View Replies !   View Related
Explicitly Lock A Table?
I have multiple applications which can potentially update the same trigger simultaneously. Each application:

(1) Reads the contents of the current trigger
(2) Creates a new trigger based on the current contents
(3) Drops the trigger
(4) Creates the new trigger

I need to insure that once one application begins step (1), then no other application can start step (1) until step (4) has completed.

Any ideas on how this can be done? Some databases have a concept of locking tables explicitly, so for them, I can do:

(0) lock table foo;
(1) - (4)
(5) unlock table foo;

Is there such a concept in mysql or is there another way of accomplishing this?

Thanks,
Eric

View Replies !   View Related
Table Lock - Issue
Folks,

I have an update statement and it looks like it is holding exclusive lock on the table and does not release it until it completes. a PAGLOCK hint has been specified on the update statement and i think it is being ignored. It is a transaction database.so, other queries accessing that table has to wait for it to complete and thus causing timeouts.The Update statement is also causing high IO and CPU utilization. How do I

1) reduce the granularity level to prevent the locking
2)any ideas on optimizing the query?

Here is the query:

UPDATE Customers
SET IndexStart = TMP.IndexStart,
IndexPosition = TMP.IndexStart,
IndexStop = TMP.IndexStop,
IndexLevel = TMP.IndexLevel
FROM Customers AS C WITH (PAGLOCK)
INNER JOIN #tmp_IndexBCs AS TMP WITH (TABLOCKX) ON C.ID= TMP.ID

Thx
Sri

View Replies !   View Related
Alter Table Lock?
Hi!
I use proc handling special business logic (I also use constraints, indexes for that ;-)
Now I have a situation where I should check multiple rows with an proc.
Preventing multi-user issues I want to lock the table (yes, yes potential performance issue, but in this case there are few simultaneous jobs) - in Oracle I could lock the table, but what to do in SQL Server?
 
Maybe you have an better alternative, then let me hear ;-)
 
Or should I use "begin transaction"...
 
Thanks for help

View Replies !   View Related
Lock 'Childreen' Table
Hi!

I'm new in SQL and I´ve been the problem bellow:

I lock a record in one foreign table and automaticaly SQL lock the record that matches on primary table.

But this occours just in some primaries tables and not in all. I need that just the table that are of SELECT are lock. How can I do this?

Example:

** At open of the invoice:

SET ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

SELECT * FROM INVOICE WITH (ROWLOCK UPLOCK) WHERE ( ID = 15 )

******* at the end of invoice:

INSERT INTO INVOICE ........

COMMIT TRANSACTION

END

 

******* The relationship are:

INVOICE <>> ITENS_INVOICE 

ITENS_INVOICE <>> PRODUCTS

CUSTOMER <>> INVOICE

VENDORS <>> INVOICE

Just the INVOICE and PRODUCTS record´s involved in Transaction are lock. ( The CUSTOMER and VENDORS are not locked for example )

But I need that just INVOICE record be locked.

Thank´s for all and sorry my English.

 

Igor Sane

São Paulo - Brazil

View Replies !   View Related
Exclusive Insert Lock On A Table
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 Replies !   View Related
Placing A LOCk On The Table In SqlServer2005
Hi,
 I am using Sql Server 2005.
I want to place a LOCK, insert something in the table then return back to the code (with the ID of the inserted item) and then call FUNCTION_A and then based on the outcome of the function I either want to

I want to keep the inserted item and release the lock or
I want to delete the inserted item and release the lock
Any suggestion is greatly appreciated.
Regards
naimulah

View Replies !   View Related
How Can I Avoid Table Lock In SQL2000?
To any and all;I have a very large table (16Mil+ records), for which I want to deleteabout 8 Million records. There are a few indexes on this table.Is there a way that I can run either a query or a series of queriesthat will run against each record and delete based on criteria (date)?If I do a single DELETE query, it will take forever, lock the table,and my app that runs against it will stop INSERTING, which is bad.If I do a cursor, I think it locks the table also, so that won't do,right?Any help would be appreciated.Glenn DekhayserContentcatcher.com

View Replies !   View Related
LOCK Table Necessary In Stored Procedure?
Hi,

I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.

I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.

The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.


The table looks like this:
Logid PK
LogTypeID -- what category
LogValue --
LogTime -- when it occurred

My imaginary stored procedure looks something like this:

CREATE PROCEDURE ShrinkDB AS
-- 1)
"lock table log" -- do I have to do something like this?

-- 2)
select * into log_keep FROM Log where
(
logtypeid <> 2020 AND -- activity played
logtypeid <> 5020 AND -- database connected
-- ....etc et..... about 10 different things I don't need to keep
or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours
)
-- 3)
drop table log

-- 4)
EXEC sp_rename 'log_keep', 'log'
GO

I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??


Hopefully it works that way, but if not I assume I'll run into these two problems:

- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?

- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?


Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject.... :-)

View Replies !   View Related
Obtaining A Table Lock In SQL Server
I need to lock a table in SQL Server. The reason is that I need to insert a value into an identity column (rather than just taking the next sequence number). I have a program that:

1. Sets identity insert on
2. Inserts into the table
3. Sets identity insert off

The problem is that I believe I should lock the table before I turn identity insert on (otherwise, other transactions will fail). I need to ensure that no other operations will take place on the table while I am doing all three of these steps.

I believe I can set the isolation level, but I cannot find any way to establish the lock without executing the update statement itself - which is a nanosecond too late to prevent another user from getting an error.

View Replies !   View Related
Will A Cursor Fetch Lock The Table
Open a cursor , Fetch the record ,
during this kind of operation , will the specific table be locked and fail
to be updated or select by another session ?

View Replies !   View Related
DTS Task Failing When Using Table Lock
I currently have a DTS task that is failing on one step due to more locks being allocated than are available.

I therefore changed the step to ensure that a table lock is placed on the table. However, now the step doesn't seem to run at all. The step starts but after waiting for 20 minutes no rows were copied across - whereas before I was experiencing the locking problem that step would take about 2 minutes.

I have since managed to work around this problem by creating a SQL Task that does an insert. This works fine.

I am curious to know why the step was not working with a table lock specified, and was wondering if anyone has come across this before.

Regards,

Karl

View Replies !   View Related
Table Lock Option Clarification
Does the Table Lock option in the OLE DB Destination task in a data flow refer to a lock on the destination table or on the source table from which the table is loading from?
 
The reason I ask is because I have a package run twice simultaneously pulling from the same server and table onto two different tables on two different servers. When I kick off my job to run the two data pulls, one of the jobs terminates with a

"TCP Provider: An existing connection was forcibly closed by the remote host." message. I believe this is due to a table lock on the server, but can't figure this out.
 
Thanks in advance for your help.

View Replies !   View Related
How To Lock A Table In Sql2005 Express.?
Hi,
 
I have an aplication (VS2005 and Sql2005 Express), where I need to lock a table when opening one form, and then close it when completing another form.
 
Is there a way to do this, maybe using a Stored Procedure.? I'd prefer to be still able to access the table in read only mode if possible.
 
I've looked at pessimistic concurrency, but the particular part of my application is not using a dataset, being based upon SqlCommand.ExecuteScalar(), so I'm fairly sure this is not an option.
 
Thanks

View Replies !   View Related
Row Lock Versus Page Lock In SQL 2000.
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 Replies !   View Related
How Do You Lock Table (or View) Fields From Editing???
Hello:I have an Access 2K form I built from a SQL Server 7.0 view. I want tolock certain fields in the database from users so they can see them onthe views and forms, but NOT be able to edit them.I've looked in BOL, MS SQL Server web page and SQL Server 7.0 books andcould not find how to do this.Any advise will be greatly appreciated.Thanks,Richard

View Replies !   View Related
Lock Table For Queries With Query WAITFOR
Hi all.

How can I lock table with longtime query?

View Replies !   View Related
Dead Lock In Table Contains Text Column
Hi Frineds

I have a table with contains Text column. and which is accessed by each page of the site. It is getting frequent Dead Lock Any body can suggest me a good solution pl.

Thanks.

MuraliB

View Replies !   View Related
Is A Join Table Always A Potential Dead Lock?
Hi,

Lets say I have table A and table B and another table AB where each row in AB refereces a row in A and a row in B. Furthermore, I set both relationships to cascade upon delete.

Then one user deletes a row from A which cascades to two rows in AB. But another user has deleted a row in B which is also trying to delete the same two rows in AB. The first transaction deletes one of the AB rows, the second deletes the other and then both transactions cannot get the other row in AB to delete because its locked. So this is a deadlock! Is it really that easy to get a dead lock?

Cheers, XF.

View Replies !   View Related
Lock A Table Until Package Execution Finishes
Hello,
I need to lock a table in startup of my package so that access calls from other applications are put on "wait" by sql server until I unlock.


Any idea how would I do it ?
Or is it possible or not ?


Thanks,
Fahad

View Replies !   View Related
Is It Possible To Read Data From A Table With Exclusive Lock ?
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 Replies !   View Related
Online Rebuild Index Table Lock
I have a very large table with approximately 400 million records in it.  Every 10 seconds approximately 150 insert are done on the table.  I am attempting to rebuild one of the indexes (non-unique, non-clustered).  But when I run a script to rebuild the index online (i have enterprise edition) the VB.NET service that is attempting to insert generates SQL timeout errors (timeout set to 30 seconds).  From an article on msdn they state that long term table locks are not held for the duration of the index operation.  So what am I missing because I am not close to being a DBA. I know SQL Server is not a SCADA but it is not my choice. 
 
Here is the script for one of the rebuilds
USE [DATABASENAME]
GO
ALTER INDEX [IX_REALLY_BIG_TABLE_DT]
ON [dbo].[REALLY_BIG_TABLE] REBUILD
WITH ( PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    ALLOW_ROW_LOCKS  = OFF,
    ALLOW_PAGE_LOCKS  = ON,
    SORT_IN_TEMPDB = OFF,
    ONLINE = ON )
GO

 
 
Here is the article
http://msdn.microsoft.com/en-us/library/ms188388.aspx

View Replies !   View Related
Adding Table To Dataset For SQL Server Mobile Causes VS 2005 To Lock Up.
I am running VS 2005 Professional Edition

Windows XP profession with Service Pack 2

SQL Server 2005 Developer Edition.

WHAT I HAVE DONE:

I have a database running in an instance of SQL Server.

I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication.  After a few oversights I got everything working.  The Mobile database replicated just fine.  I went back verified all data was there.  Can make queries to it.

 

PROBLEM: 

I set up a new dataset to use tables from the SQL Server Mobile database.  If I drag one of the tables to the dataset, VS 2005 simply stops responding.  It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:

Microsoft Visual Studio is Busy.

Microsoft Visual Studio is waiting for an internal operation to complete.  If you regularly encounter this delay during normal usage, please report this problem to Microsoft.

Well...  It is more than just a delay.  The environment is not using any processor its just sitting here.  And I left it running for 2.5 hours...  so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic.  The only option I have is to Kill the process.

Hopefully someone out there can help.

Additional Services running:

IIS (Whatever version comes with Windows XP Pro.  I think 5.1)

SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch

UPDATE:  I am editing this post with an update.

I noticed that my other tables get added to the dataset just fine.  It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification.  I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them.  I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.

 

View Replies !   View Related
Error: A Deadlock Was Detected While Trying To Lock Variable &&"x&&" For Read Access. A Lock Could Not Be Acquired After 16 Attempts
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 Replies !   View Related
Update Statement Performing Table Lock Even Though Where Condition On Clustered Primary Index?
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray

View Replies !   View Related
Populating An Access Combo Box With Large Amount Of Data Causes Table Lock In SQL Server
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)

View Replies !   View Related
ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.
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 Replies !   View Related
A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List
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 Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved