How to lock a set of tables??

Hi all,

Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

thnxWhy not begin a transaction before changing the first table and then commit or rollback after changing the last table?|||You can do it by 2 ways

1) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2)By Giving Lock hints on target tables (HOLDLOCK,TABLOCKX) --which is equivalent to SERIALIZABLE

more information on BOL for "lock hints"

vish

Originally posted by armen_gg
Hi all,

Let's image I have a set of tables (for ex. 3 tables) which are logically connected with each other (i.e. every time I'm changing something in one table I necessarily will make some changes to other 2 tables).
Well, now I have two application - one is changing these tables (synchronising tables' content with application's environment) and another one which time by time reading these tables and makes some operations base on tables' content.
The problem is - if the first application started changes but not commited transaction yet and the second application running SELECT clause, then I could get a "wrong" data in the second app, i.e. content of some tables will be updated but content of other tables are old.
The ways to solve this... I think that it's possible to lock these tables before making changes in the first application and unlock tables after changes are complete. Then the second application will be secured with up to date content. But I can't find out how to lock a set of tables. If anyone knows how to do it plase help! Any kind of help would be appreciated!

thnx

How to know which node connected to

Hi ,
Is ther any way to know which node am connecting to or which node the
cluster is running on .
Can we find out the same from Query Analyzer .
Thanks
ARR
From
http://www.microsoft.com/technet/pro.../failclus.mspx
"From a SQL Server perspective, the node hosting the SQL Server resource
does a looks-alive check every 5 seconds. This is a lightweight check to see
whether the service is running and may succeed even if the instance of SQL
Server is not operational. The IsAlive check is more thorough and involves
running a SELECT @.@.SERVERNAME Transact SQL query against the server to
determine whether the server itself is available to respond to requests; it
does not guarantee that the user databases are up. If this query fails, the
IsAlive check retries five times and then attempts to reconnect to the
instance of SQL Server. If all five retries fail, the SQL Server resource
fails. Depending on the failover threshold configuration of the SQL Server
resource, Windows Clustering will attempt to either restart the resource on
the same node or fail over to another available node. The execution of the
query tolerates a few errors, such as licensing issues or having a paused
instance of SQL Server, but ultimately fails if its threshold is exceeded."
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23G0a6WP4EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> Is ther any way to know which node am connecting to or which node the
> cluster is running on .
> Can we find out the same from Query Analyzer .
> Thanks
> ARR
>
|||Thanks for the reply ,
But I want to know a query or some way to know which node is currently
hosting sql services .
select @.@.servername returns instance name not hostname or nodename .
Thanks
ARR
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:uP3$9SR4EHA.1524@.TK2MSFTNGP09.phx.gbl...
> From
>
http://www.microsoft.com/technet/pro.../failclus.mspx
> "From a SQL Server perspective, the node hosting the SQL Server resource
> does a looks-alive check every 5 seconds. This is a lightweight check to
see
> whether the service is running and may succeed even if the instance of SQL
> Server is not operational. The IsAlive check is more thorough and involves
> running a SELECT @.@.SERVERNAME Transact SQL query against the server to
> determine whether the server itself is available to respond to requests;
it
> does not guarantee that the user databases are up. If this query fails,
the
> IsAlive check retries five times and then attempts to reconnect to the
> instance of SQL Server. If all five retries fail, the SQL Server resource
> fails. Depending on the failover threshold configuration of the SQL Server
> resource, Windows Clustering will attempt to either restart the resource
on
> the same node or fail over to another available node. The execution of the
> query tolerates a few errors, such as licensing issues or having a paused
> instance of SQL Server, but ultimately fails if its threshold is
exceeded."
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Aju" <ajuonline@.yahoo.com> wrote in message
> news:%23G0a6WP4EHA.2624@.TK2MSFTNGP11.phx.gbl...
>
|||Here's one way:
If you know the group name of the SQL Server resource,
EXEC xp_cmdshell 'cluster group'
If you know the resource name of the SQL Server resource,
EXEC xp_cmdshell 'cluser resource'
Note that group names and resource names can be arbitrary. In our
enviornments, we follow certain naming conventions, so it's easy to tell
which virtual server is in which group.
Also, note that you may consider using EXEC xp_cmdshell 'hostname'.
Unfortunately, on a clustered instance, this gives you the virtual server
name instead of the hostname of teh node. Similarly, none of the following
will give you the node name:
SERVERPROPERTY('MachineName')
SERVERPROPERTY('InstanceName')
SERVERPROPERTY('ServerName')
@.@.servername
Linchi
"Aju" <ajuonline@.yahoo.com> wrote in message
news:%23G0a6WP4EHA.2624@.TK2MSFTNGP11.phx.gbl...
> Hi ,
> Is ther any way to know which node am connecting to or which node the
> cluster is running on .
> Can we find out the same from Query Analyzer .
> Thanks
> ARR
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank youStored Procedure 'sp_who' => Provides information about current Microsoft?
SQL Server? users and processes.
sp_who2 => list the active processes.
Lemme know if you were looking for this or something else.
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>|||Were you looking something like below query?
Select COUNT(loginame) Count, loginame=rtrim(loginame)
From master.dbo.sysprocesses
GROUP BY loginame
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank youStored Procedure 'sp_who' => Provides information about current Microsoft®
SQL Serverâ?¢ users and processes.
sp_who2 => list the active processes.
Lemme know if you were looking for this or something else.
Thanks,
Sree
"Asking" wrote:
> how to know simultaneous connected count to a Database?
> Thank you
>
>|||Were you looking something like below query?
Select COUNT(loginame) Count, loginame=rtrim(loginame)
From master.dbo.sysprocesses
GROUP BY loginame
Thanks,
Sree
"Asking" wrote:
> how to know simultaneous connected count to a Database?
> Thank you
>
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank you
Stored Procedure 'sp_who' => Provides information about current Microsoft?
SQL Server? users and processes.
sp_who2 => list the active processes.
Lemme know if you were looking for this or something else.
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>
|||Were you looking something like below query?
Select COUNT(loginame) Count, loginame=rtrim(loginame)
From master.dbo.sysprocesses
GROUP BY loginame
Thanks,
Sree
"Asking" wrote:

> how to know simultaneous connected count to a Database?
> Thank you
>
>

how to know simultaneous connected count to a Database?

how to know simultaneous connected count to a Database?
Thank youTo determine the number of current user database connections:
SELECT COUNT(*)
FROM master..sysprocesses
WHERE
spid > 50 AND
dbid = DB_ID('MyDatabase')
Hope this helps.
Dan Guzman
SQL Server MVP
"Asking" <asking@.ispro.net.tr> wrote in message
news:OSvVy06NGHA.3272@.tk2msftngp13.phx.gbl...
> how to know simultaneous connected count to a Database?
> Thank you
>

How to know how many users are connected to my sql server 2000 and how many resources are

how can i know it? can i do it with sql 2000 tools or i need other kind of
tool?
Regards
Rick
EXEC master.dbo.sp_who2;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick
>
|||Hello,
For SQL 2000 query the MASTER..SYPROCESSES table. Use the filtering and
group based on your requirement.
For SQL 2005 you could the dynamic management view (DMV)
sys.dm_exec_sessions
SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51 - All user SessionsSee the below URL for more
details:-
http://www.codeproject.com/useritems/Dynamic_Management_Views.asp
Thanks
Hari
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick

>

How to know how many users are connected to my sql server 2000 and how many resources are

how can i know it? can i do it with sql 2000 tools or i need other kind of
tool?
Regards
RickEXEC master.dbo.sp_who2;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick
>|||Hello,
For SQL 2000 query the MASTER..SYPROCESSES table. Use the filtering and
group based on your requirement.
For SQL 2005 you could the dynamic management view (DMV)
sys.dm_exec_sessions
SELECT
session_id,login_name,
last_request_end_time,cpu_time
FROM sys.dm_exec_sessions
WHERE session_id >= 51 - All user SessionsSee the below URL for more
details:-
http://www.codeproject.com/useritems/Dynamic_Management_Views.asp
Thanks
Hari
"Rick" <zatankloz@.gmail.com> wrote in message
news:%23aXjgLGqHHA.2044@.TK2MSFTNGP04.phx.gbl...
> how can i know it? can i do it with sql 2000 tools or i need other kind of
> tool?
> Regards
> Rick
>