how to manage concurrency between multiple, disconnected clients

I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".

All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row.

Thanks.

I have never used SQL Server 2005 timestamp field which is what you are referring to as the row version. This is an autoincrementing field when a change is detected and is useful in preventing dirty writes.

I have used this practice in managing phantom data back with SQL Server 2000 just by adding a column of type INT and incrementing it each time the record is changed. Basically this is how it works, when I retrieve the record, I get a version of 1, at the same time another person gets the same record with version 1 as well. This person then updates the record causing it to increment to 2. In the meantime, I decide to update the record as well, your application or database should compare the version number you have which is 1 with the current version number at the database, if it is the same then allow update as this mean no changes were detected. In this case the version is at 2 which means the copy of the data at version 1 when i retrieved it is stale and you would alert the user that either they cannot save until they do a refresh or inform them if the choose to proceed, data will be lost.

Using row versions is optimistic locking as many people can read the same record.

You can also use pessimistic looking by locking the record once you have it. You can do this by using Transactions in .NET (System.Data.SqlClient.Transaction) and specify the Isolation levels.

how to make Enforce Password Policy unchecked by default

I am trying to create a login using my application.

The application creates the user and password itself .

when working with sql server 2000 it works fine.

when i try to install my application with sql server 2005 it is displaying the error .

'Password does not meet windows policy requirements

because it is not complex enough'

I want to keep the same password.

Is there any way to disable/uncheck this option by default?

Prashant

You could modify the login creation script to specify CHECK_POLICY=OFF

USE [master]

GO

CREATE LOGIN [TestLogin] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

|||

This is good but for this i need to make changes in my code .My application integrates with sql server 2000,MSAccess ,Oracle.

the code for creating login is same .So can it be possible at sql server level to make Check_policy =OFF as default option.

|||I would be very interested to know this as well. I have a similar issue. If your SQL 2005 server is not part of a domain that enforces group security policies, one option you do have is to relax the default Local Security Policy on the 2003 server upon which your SQL 2005 is running. Using the "Local Security Policy" editor found in Administive Tools you can select "Password Policy" under Account Policies. From there you can change the settings for history, age, length, complexity requirements, and encryption. You will want to turn off the "Password must meet complexity requirements". You might have to change some of the other settings to meet your specific application needs.|||

I would be interested in this as well.

We have a third party Application that the client says works with SQL Server 2005, but the user logon creation they have built in dos not pass a correct password, and turning the poicy off may fix the problem.

|||No, many features which are implemeted in the engine cannot be changed by default:

CREATE LOGIN(...)

CHECK_POLICY = { ON | OFF }

Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

Maybe you post a bug / feature request on the connect forums to make it happen in further versions.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-

How to Maintain TransLog (Detach/attached)

hi guys, we have a translog database (.LDF) of 60 GB which the contain database itself (.MDF) is only 500 MB. I have detached the translog and succeeded, but when i tried to attached the MDF file, there was an error message says "physical device error D:\\...". Please advice, what should we do towards the .LDF file (60 Gigs) - our objective is to delete/decrease the LDF file mentioned. Thanks in advance.Try this page:

http://www.support.microsoft.com/?id=272318

Basically you want to eliminate the data within the log then shrink the footprint of the file. Also, if you do not require transactional recovery you might consider using simple recovery mode which truncates the log upon checkpoint of the data.

HTH