NOT SELECT ROWS SELECTED BY ANOTHER TRANSITION

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

NOT SELECT ROWS SELECTED BY ANOTHER TRANSITION

Author

Topic

paolomanfrin
Starting Member

6 Posts

Posted - 2007-05-13 : 03:44:16

Good Morning, I've this problem.
In a transition I do: Select ID from UserID Where Name=@NameUID.

I want If another transition do: Select ID from UserID
it select all the rows EXCEPT the rows selected by the first transition.

Note: The second transition can't know the value of @NameUID else it can be simple ( Select ID from UserID Where Name!=@NameUID)

It's possible? Thanks,
Paolo Manfrin

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-13 : 08:55:46

define transition.
or do you mean transaction?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

paolomanfrin
Starting Member

6 Posts

Posted - 2007-05-14 : 07:39:59

Yes Sorry... a TRANSACTION

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-14 : 10:38:21

then no. not that i know of.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-14 : 10:44:33

well, you COULD, but it would be one of the most horrific designs ever.

Add a "In a transaction" column to your table, then set the flag when your first transaction selects it. Remove the flag when the transaction is done.

See what i mean about horrible?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-14 : 16:19:43

Probably a bad idea to try and lock such resources. If you must, you might try Don's suggestion. There is a system stored procedure called "sp_getapplock" that might get you watn you need. I have not tested it, but I highly suggest that you find another way.

-Ryan

Kristen
Test

22859 Posts

Posted - 2007-05-16 : 02:45:59

We have a flag system to prevent someone else "getting a record".

For example, in our HelpDesk application the moment that an operator "looks" at a new HelpDesk issue then that record has the "Assigned Operator" colummn set to that operator 's UserID. The status is also changed from "New" to "Open".

Is that the sort of thing you mean?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-16 : 06:01:53

quote:
Originally posted by Lamprey

Probably a bad idea to try and lock such resources. If you must, you might try Don's suggestion. There is a system stored procedure called "sp_getapplock" that might get you watn you need. I have not tested it, but I highly suggest that you find another way.

-Ryan

i tried this but it doesn't work
the select always takes a shared lock and it doesn't matter what isolation level you have.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-16 : 10:01:17

instead of adding a column, perhaps read the keys into another table called "DontUseTheseRecordsBecauseTheyAreInAnotherTransactionRightNow", and join on that table

then remove the keys when you are done with them.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-16 : 10:43:03

<<
DontUseTheseRecordsBecauseTheyAreInAnotherTransactionRightNow
>>

Seems you are good in Naming the objects

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-16 : 10:47:29

Well, that or you could name it [439F3450-730F-4041-88F3-DE6E903E4894]. Either way, I am confident that there would be no other table named as such, nor would it be likely that anyone would guess its name.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2007-05-16 : 15:57:36

"Seems you are good in Naming the objects"

Shouldn't the object's name start with a lower-case letter?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-05-17 : 08:14:35

Quiet you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources