Inserts Resulting In Exclusive Key Lock

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


ADVERTISEMENT

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 View Related

Exclusive Insert Lock On A Table

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

Can You Place An Exclusive Row Lock Using A Hint?

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

Deadlock -select With Exclusive Lock

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:€™OR€™Xdes:
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:€™OR€™Xdes:
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

Is It Possible To Read Data From A Table With Exclusive Lock ?

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

Replication Performance Degrade In Unidirectional Direction And Lock Time Out (Update Are High Than Inserts)

Feb 21, 2007

 

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1)   One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

 

on subscriber database  response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

 

looking for any experieced solution/suggestion.

Thanks in advance. 

View 3 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

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

Insert With 1 Value Resulting From Another Query

Oct 22, 2007

I'm not exactly sure how to do this in MS SQL. Here is my current (broken) MSSQL insert statement:

Code:


cmd2.CommandText = "INSERT INTO User_Courses ('course_id','register_date','credit_date','userid') VALUES (" +
"@classID,@regdate,@creditDate," +
"(SELECT userid FROM Users WHERE guid=@guid2))";



Of course this doesn't work, as the SELECT statement might contain more than one value (which it doesn't, guid is a unique hash).

How do I execute this statment without breaking it into a separate command?

View 3 Replies View Related

Row Lock Versus Page Lock In SQL 2000.

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

Duplicate Entries In The Resulting Table

Sep 20, 2007

Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.
Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins]
@Userid uniqueidentifier OUTPUT,@DisplayName varchar(200)
 
AS
 
SELECT *
FROM dbo.UserProfile INNER JOIN
dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName
and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date
Return

View 7 Replies View Related

Importing Of Data, Resulting In Duplicates, HELP!

Oct 18, 2004

hi all,

I have a problem with SQL 2000 here...
I juz imported the same set of data to my database table and it gave me duplicated records as all the data is imported although there is same existing data.

Can anyone help me with this?
How can i import the data such that the data which already exists in the database will not be imported in again?

Thanks in advance.

View 4 Replies View Related

SSRS Report Limits Resulting Pages

Oct 19, 2007

I created a report that uses a parameter to return a record per page basically, but it doesn't return all records that match the parameter. Thoughts?

Specifically, I have a table that tracks various sites my engineers are responsible for; each have about twelve. The problem is that every time I select an engineer I only see six pages (records). When I select two or three, then I see 12 or 18 pages, but still just six records per engineer.

Any help is greatly appreciated! Neil

View 1 Replies View Related

Evaluate Logic Output As Resulting Field From Query

Nov 25, 2005

have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.

For a very general example: In the .NET code, the following variables are recognized:
sex="M"
Paid=4350.00
Outstanding=28000.50

One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..

EVAL EXPR
BOOL Sex='F' and Paid/Outstanding < 27.50
BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F'
INT Paid*52.33

In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?

If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....

View 3 Replies View Related

Reporting Services :: Split Function Resulting In Error

Oct 7, 2015

I have a report that is using the following expression for one of the fields:

=Split(Fields!FromAccount.Value, " ")(1)

The format of the number is xxxxx-xxx xxxxxxxxxxxx

So the function is to grab the second section of numbers after the space. The query for this report can bring back 1 to many results which equals one to many pages.

I have an issue where the query returns three results. The first page will display the correct number, the second page displays #Error and the third displays the number.

When I run the query from Management Studio I see the numbers are as follows:

xxxxx-xxx  xxxxxxxxxxxx
xxxxx-xxx xxxxxxxxxxxx
xxxxx-xxx  xxxxxxxxxxxx

Note the first and and last lines have extra spaces which I thought would be the cause of the problem though I would've expected the #Error to appear on the first and third page rather than the second one.

When I remove the extra space for the first and third number all three pages display their values correctly.

However, there are many, many numbers in the table that have one to two spaces. These numbers are from a spreadsheet that is imported biweekly. So it was either I fix the spreadsheet before every import or I come up with a new expression to check for one or two spaces. This is what I came up with:

=IIF (Fields!FromAccount.Value.Contains("  "), Split(Fields!FromAccount.Value, "  ")(1),Split(Fields!FromAccount.Value, " ")(1))

Now, the first page displays the number correctly while the second and third page displays with the #Error. Basically I want to say if the value contains two spaces then split at the two spaces otherwise split at the one space.

View 3 Replies View Related

Forms Authentication Resulting In Blank Screen After A Few Minutes.

Jun 27, 2007

Hi €“ I hope someone can help,


I have developed a custom authentication extension to Reporting Services 2005, using Visual Studio 2005 C#.


In local integration tests the extension behaved as expected, honouring the role based security of our main system. Following the deployment steps laid out from numerous sources all worked perfectly. We were optimistic.


I've since deployed to a 'live' user acceptance staging server, using the same procedures used in Integration and it's behaving incorrectly.


Initially the log in page is displayed as expected, at this point SQL dumps occur in the RS Log directory, the same as is seen here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1750128&SiteID=1


Hopefully there'll be a resolution to that soon. As described in that topic, the main page of Report Manager appears.


After this it all goes wrong.


Sometimes we can browse to a folder and run a report. But after running the report, trying to run something else, or browse back to the containing folder we are presented with the error page with the message:


The permissions granted to user '' are insufficient for performing this operation.


Clicking on the home link brings up the top title and nothing else and you have no option but to close the site and re-login.


Sometimes you get the above message before you can even run a report. The error logs have the message listed at the end of this post.


Other times you log in to the website and the folders don't even appear, just the top title bar.


I have since added some verbose logging in each of the implemented methods in the extension DLL. The permission checking method return true and all the various data in each method appears as expected.


I have checked, checked again and re-checked all the configuration files, they match the local Integration files, barring the machine specific keys etc, they weren't just overwritten.


The location of the log directory where my internal logs are written is read from the configuration files via the SetConfiguration methods, if that wasn't set correctly then there wouldn't be any logs at all, so the configuration is being read correctly by the extension.


One other think that I've noticed is that the back door user that is configured in the config files works perfectly, but I can't see how this can make any sort of a difference as it returns the same result in the extension DLL as a 'normal' user does at the same point in the code.


Can someone please help me, and my poor scalp, it's losing hair at a rate of knots.


Set up:
Windows Server 2005
SQL Server 2005 Service Pack 2 (Developer)
IIS 6


Error message in the log files:


w3wp!library!1!27/06/2007-10:04:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation.
w3wp!ui!1!27/06/2007-10:04:20:: e ERROR: The permissions granted to user '' are insufficient for performing this operation.
w3wp!ui!1!27/06/2007-10:04:20:: e ERROR: HTTP status code --> 500
-------Details--------
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation.
w3wp!ui!1!27/06/2007-10:04:20:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)

View 3 Replies View Related

Querying Large Text Fields Resulting In Timeout Error

May 16, 2007

Hello:

I have designed a CV database with complete CV stored in a TEXT field. There is a keyword search which queries the TEXT field also. The query conditions are defined in T-SQL submitted through an ASP page. There is about 20,000 records now. Now while querying the database for keyword search I am receiving time out errors. Is there any solution other than Index server to rectify this situation. How can I speed up the query execution time. Please advise.

Rgds
pooja

View 3 Replies View Related

Drill Through Report -- Passing Date Parameters -- Resulting In RsReportParameterTypeMismatch

Jul 4, 2007

All,



I have two reports. One is the main/summary report and other one is drill through. When I pass the Start and End Date parameters from main to the drill, the original format of DateTime changes. For example, in main report the data is displayed for following date range:



4/7/2007 - 5/9/2007 (i.e 4 July 2007 to 5 Sept 2007)

which displays correct data.



However, when I click on the drill through link, it jumps to the drill through report but displays data for the following period:



7/4/2007 - 9/5/2007 (i.e. 7 Apr 2007 to 9 May 2007)



The reporting services is converting the value from one format to another of the report parameters when passing them from parent report to the drill through. When run individually, these two reports display data for correct date range. And you can imagine, the child report crashes with rsReportParameterTypeMismatch error if the start or end date had a day part greater than 12 (e.g 25/4/2007).



I can't understand what could be going wrong. All the parameters in both reports are datetime, so intrisically, it shouldn't matter even if the reporting services is converting or using different date formats as long as the data type remains the same. Is there a way to fix this and force the parameters to stay in the format they are provided in the main report?



Your help is much appreciated.



Thanks.

View 5 Replies View Related

Storing The Result In A Variable Resulting From A Dynamic TSQL Query

Aug 30, 2007

Hello all:

Here is a sample query:



DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'

DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'



DECLARE @KeyCol NVARCHAR(50);

SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'


DECLARE @KeyValue NVARCHAR (1000)


SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +

' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '



The value resulting from Executing @KeyValue is an integer.

I want to store this value in a new variable say @VAR2

When I do this

DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue

its giving me an error.


can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?

View 3 Replies View Related

Data Mining In 2005 - Same Input Columns Resulting In Different Decision Trees

Dec 12, 2007

While recently working with several mining models, I came across something that struck me as pretty odd - and I'm hoping to find an explanation for the behavior.

Consider the following setup:


A single table in the relational database represents the only case table
A single, continuous column is the predictable
A mining structure has been created

The mining structure contains a single model, based on the MS Decision Trees algorithm
Input columns were selected for the model via the BI Studio wizard (i.e., those provided via the "Suggest" button)
The structure has been fully processed
Now, the interesting parts:


I view the scatterplot for the mining model, under the Mining Accuracy Chart tab
Back on the Mining Structure tab, I delete one of the input columns
I add the same column back into the structure
The structure is fully processed again
When I view the scatterplot for the mining model, under the Mining Accuracy Chart tab, a different set of data points are presented for the model predictions
A different set of decision trees under the Mining Model Viewer tab confirms thisHow could different patterns have been found this second time around, even though all of the input columns were the same (as well as the training cases)?

(Note: I encountered this situation while creating a new mining model that was identical to an existing one. Even though the models received the exact same inputs and training cases, they yielded different results. I was able to reproduce the behavior by using steps 1-6 above, though.)

Can someone provide some insight on this behavior, or some kind of explanation of what may be happening?


Thanks,
Joe Miller

View 3 Replies View Related

ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.

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

A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List

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

Exclusive Mode

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

Non-bitwise Exclusive Or?

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

Exclusive Locks

Jun 11, 2008

Hi,

How do you lock a table in exclusie mode before running a query?

thanks,

View 5 Replies View Related

Many To Many - Exclusive Result

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

How To Obtain Exclusive Access Via SQL

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

Exclusive Access To DataBase

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

Trying To Get Exclusive Access To A DB For Restore.

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

Exclusive Access Could Not Be Obtained Because The

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

Intent Exclusive Locks

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

Exclusive Access Could Not Be Obtained Because The Database Is In Use.

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

Query Trouble Regarding Bitwise Exclusive

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







Copyrights 2005-15 www.BigResource.com, All rights reserved