asynchronous select statements

We have an application deployed on two seperate machines both reading from
the same database. The applications poll the database every few seconds and
find the first record in a table that has a bit field set to 0x0. The
application then updates the field to a value of 0x1 and then do some
processing based on the contents of the record. The problem we have is that
it is currently possible for each application to get the same record because
application 2 might select the record just before application 2 updates the
bit flag. we currently use two sql calls (in stored procs) such as:
select top 1 * from table1 where processed = 0x0
update table1 set processed = 0x1 where recid = @.ID
How can we avoid both apps getting the same recordHave the app call a single SP.
This SP updates the record, and then returns it to the client application.
This is in one transaction, so the other application can not get to the same
record.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:#GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
that
> it is currently possible for each application to get the same record
because
> application 2 might select the record just before application 2 updates
the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Jeremy,
This is how you would do it; put the desired row into lock until the update
is done. For rowlock to be effective, you need a primary key on the table!
ReadPast hint will allow you to bypass the locked row and process the next
available one. Without it, your #2 connection will have to wait until #1 is
done. You can try both scenarios out to gain some deeper insight.
e.g.
/*
--sample tb
create table t1(i int primary key, b bit)
insert t1 values(1,0)
insert t1 values(2,0)
insert t1 values(3,0)
*/
-- drop table t1
go
--on connection #1
--this will lock i=1
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
--on connnection #2
--this will lock i=2
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
> and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
> that
> it is currently possible for each application to get the same record
> because
> application 2 might select the record just before application 2 updates
> the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Magnificent! Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||Actually, testing discovered that this might not work, because if the sql
gets run at the same time, the select statements could select the same
record, because nothing is locked at that point.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||You could add another hint to the select to exclusively hold the lock. As
soon as the row is read, it's locked until you invoke commit/rollback.
e.g.
select *
from tb with (rowlock,xlock,readpast)
where pkid=@.para
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:uLpwUeIFFHA.464@.TK2MSFTNGP09.phx.gbl...
> Actually, testing discovered that this might not work, because if the sql
> gets run at the same time, the select statements could select the same
> record, because nothing is locked at that point.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> update
> is
> from
>

Asymmetric data syncronization: possible?

I have two sql server instances on two different machines with the same db;
on the first machine some tables need to be copied on the second machine,
and on the second machine some other tables need to be copied on the first;
all the tables are related with the others in some way.
It is possible to make that replication?
Thanks
Mik
I'm wondering if the same data can be changed on each node? If so, then it
sounds like you need either merge or updating transactional subscribers
(queued or immediate). I'd control this from one server designated as the
publisher and synchronize this way. If this is not the case, then both nodes
could be set up as transactional publishers.
Rgds,
Paul Ibison

ASPNET security on my sql server

Dear all

I had a problem connecting to my website (through lan) from other machines in the company here.

I googled some and figured out that I have to change the security settings of aspnet in my sql server express 2005.

I did as suggested and now its working

My question is - what should be the real security settings for aspnet on my sql server ?

Should I really add it to the sysadmin ? (what solved the problem...)

Guy

Security settings depend on your environment. They should by design be the least permissions that allow access, but in your case it would mean configuring a new account just for this purpose and you'd likely not add any additional security in an intranet environment.

Jeff

|||

Hi

Thanks for the reply

I am talking about the real world not the lan

I had the problem on lan and that led me to the question :)

Please tell me what should I do on an Internet case

Guy

|||It still depends on what the website needs to do with SQL Server. What was the problem with the SQL Server connection before you add IIS account (or other account?) to sysadmin role of SQL Server?

asp:login / ASP.NET Configuration menu / & login DB

All,

I may have bitten off a bit much here, but here goes. I'm trying to learn ASP.NET while at the same time learning SQL 2005. I have two machines. One is running Visual Studio 2005 and SQL 2005 Express. The other is my "production" box which is running VWD 2005 and SQL 2005 Standard. I picked up some WROX books and began learning. So far so good until about half way through the book on ASP (keep in mind I haven't learned SQL yet). I learned that I can use the ASP.NET configuration menu to automagically create a database, populate it with users, add them to profiles (groups), and grant them rights to pages. This was kind of neat and it worked fine on my dev box. So I looked up in my SQL book how to detach a DB, copy it over to the other machine and re-attach it. So I copied the whole web site (DB and all, under APP DATA) to the other machine and reattached it. This seemed to go well, however when I tried to log in I recieved an error.

***error

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

After doing some hunting around I discovered that I'm supposed to have a connection string. I checked my web.config file and the only thing I find there is

<

appSettings/>

<

connectionStrings/>

<

system.web>

As you can see this doesn't point to a database which the docs I found say is how you connect to them. Apparently one error someone can run into is between expree and SQL where you have to change the connection string. Well, it's not in the web.config. So I checked the default and login page (and code behind files), but found nothing. So, I'm at a loss. I have no idea where the problem is or where to go from here. Any assistance will be appreciated. While I'm enjoying the journey of learning this, the road seems to be kind of bumpy.

Thanks in advance - Stephen

Hi Stephen,

to change default connection string in web.config you can remove and add new one it's name LocalSqlServer like..

<connectionStrings>
<removename="LocalSqlServer"/>
<addname="LocalSqlServer"connectionString="your connection string..."/>
</connectionStrings>

and give look at this article aboutMembership you will find also how to config Membership

I hope this help