We have an application that accesses a SQL Server 2005 database. The isolation level is set to Read Committed. When the app calls a procedure to insert,update, or delete it seems to change the isolation level after the commit. After the commit, it calls another proc to fill a drop down and it gets the following message
"You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels"
The proc that fills the drop down isn't even using a with readpast command, but it is being used in some of the procs used prior to the commit.
I have a problem where I have a software application that needed to be updated to a newer version. In order for the new version to work I needed to upgrade my SQL Server 7 database to SQL Server 2000. The upgrade went fine for SQL Server 2000 and also with the application. The only problem now is that the compatibility level for the databases stays at 70. Even when I change the compatibility level to 80 it will automatically go back to 80. What is wrong? Please help.
I am currently running a sqlServer 2000 database and am considering changing it to 2005 I see that I can do this via Properties> options and changing the dropdownbut are there any risks?
Dear All, what are the advantages of changing compatibility level from 80 to 90? are there any disadvantages doing this on production machine? will it take any downtime? i've searched the google but i didnt get the correct info.
Arnav Even you learn 1%, Learn it with 100% confidence.
We are using sql 2008r2 standard edition.One of our Production database is using default isolation Readcommitted.The transactions also using read committed. But we want change isolation level to read comitted snapshot isolation and test it to avoid deadlocks.
Is it possible to set in the transaction level for some queries or do we need to change entire database isolation level by using alter database "ALTER DATABASE AdventureWorks2008R2 SET READ_COMMITTED_SNAPSHOT ON"
I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.
I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
Does anyone have an example on how to make use of READPAST and ROWLOCK so that when a SELECT statement retrieves a record, other SELECT statements will 'Read Past' that record until it is unlocked?
I have been experiencing deadlock errors with two stored procs that I am using.
SP1 is a read query that gets all unprocessed orders (status = 0)
SP2 is an insert query that inserts new orders (Status = 0) uses a transaction.
I have a multithreaded application and once in a while the read query (SP1) will try to read a new row that has just been inserted but not committed yet hence the deadlock arises.
If i use a hint "With(NoLocks)" this will be a dirty read and still read the uncommitted insert from SP2 - is this correct?
Where as if I use hint "With(ReadPast)" this will now only read committed rows and hence the deadlock should not arise - it will not read any uncommitted rows - Correct?
So I think that it is better to use READPAST than NOLOCK. Any orders that have status = 0 not picked up will get picked up on the next round when SP1 is executed again.
Any thougths or suggestions are always appreciated.
Is it possible to use With (NOLOCK) and With (READPAST) in the sameSELECT query and what whould be the syntax?@param intSELECTmyRowFROMdbo.myTable WITH (NOLOCK)WHEREmyRow = @paramThanks,lq
This article instructed me on how to process rows from a table used as a data queue for multiple processes.
http://www.mssqltips.com/tip.asp?tip=1257
I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio).
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows --COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
COMMIT TRANSACTION
This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue. However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want)
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --is blocked until connection 1 commits transaction
COMMIT TRANSACTION
How do I prevent blocking when using these locking hints with ORDER BY?
I want to perform column level and database level encryption/decryption.... Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms... I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database... Can anybody help me out... If you have Store procedure in SQL for the same then also it ll do... Thanks in advance
When you utilize transactions in ADO.NET are the locks put on the entire TABLE used or at the row level? For instance if you do a SELECT within a transaction if you only pull 5 rows out of a 1000 row table can you just make it lock the rows that have been pulled? It seems like it locks the entire table?
Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database. Also what is the name of process which converts low level locking into high level locking and vise versa. -Sanjeev
Hi..I'd very much appreciate it if someone would tell me how to translatea statement level trigger written in Oracle to its equivalent (if there isone)in MS SQL Server. Ditto for a row level trigger.If this is an old topic, I apologize. I'm very much a newbie to SQL Server.Regards,Allan M. Hart
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states. Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL; SCOPE (Measures.[weighted measure state and above], Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE), Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE), ... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
The problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is: "The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. " The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that. Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online? Brian [ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
lets say user1 is reading row1, then user2 reads and updates row1, when user1 is about to update row1 i want him to be informed that his copy of row1 have been updated, so he has now the options to either get the new version of row1 or cancel his update process.
I have a busy transactional table , I wanna use row level locking mechanism in msSQL. SELECT * FROM PARTY WITH (UPDLOCK ROWLOCK) where LastName ='Clinton' is there any downsides of this approach?
Hi, I am asked to look into a DB which was upgraded from SQL65 to SQL70. Problems: 1.The SQL was renamed from Server_old to Server_NEW.The Sysservers tables still have entry with Server_old name and srvid = 0(local).The server is running with this config.from last few months w/o any problem(this is what the present DBA working on this DB says)...whereas the name has changed from server-old to server_new. 2.2 user DB on this Server has compatibility level of 65.Any suggestion as should I set the compatibility level to 70 or leave it as it is.client is planning to first,go for replication from sql70/EE to SQl70/EE on Nt4/SP6. Second,they are also planning to upgrade to sql2k. Any thoughts appreciated.
How do I check the compatability level of a database? The db in question was originally created in SQL 6.5 and converted to 7.0. Would this have reset the compatability level to 7 or left it at 6.5? If it is at 6.5 what are the implications of changing it to 7?
Ray, if you answer this can you please forward me your email address. Thanks.
I used sp_indexoption to allow row locks and disallow page locks on all indexes of a heavily contented table (lots of concurrent selects/inserts/updates). The first error I saw was "The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.". I restarted the sql server and everything is ok (the server is not very powerful and will be beefed up).
My question is that with the above settings, will the select statements acquire row-level shared locks as well? Since our select queries are fairly complicated, my main concern is that we may frequently run of of resource even with a more powerful machine. Is my concern valid or I got everything wrong?
yes, There is a 'insert row level locking' but Is there anything for delete or update row level locking?
We keep geting deadlock problem with our application with multi-users. First we ran into the deadlock on INSERT. By setting 'insert row level locking' help us to go a little bit further but later we run into deadlock with DELETE. For my knowlege, DELETE and UPDATE requires exclusive page lock. Might be I am out of date.
Hi, folks. Please guide. I have a VB application that is used for production and reporting. I 've been having alerts for deadlocks that popup after every 2 or 3 minutes. I am planning to seperate reporing server by using transactional replication from production server to the reporting server. However some reports update and insert data so i need reporting server to be enabled for DML.
Is there any option on the server-level where i can force each user to operate in READ-UNCOMMITTED mode instead of specifying WITH (NOLOCK) in the queries of my application. Dirty reads won't bother me in current situation, i guess the propotion of fast reads would be a better trade-off. New to SQL, Thanx for helping!!
