Conditionally required field

How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds ou
t
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsRules.
Most people thing of rules in an IF.. THEN format which simply won't work.
Think of a rule as a boolean function where YES/TRUE accepts the row and
NO/FALSE rejects the row. Your requirements would lead to a rule like this:
Priv1 <> 0 OR Priv2<> 0 OR PRiv3 <> 0 OR Password <> ''
Look up CREATE RULE and sp_bindrule in BOL for syntax details.
Geoff N. Hiten
Microsoft SQL Server MVP
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
> How can I make a field required based on the status of other fields? I
> have
> a Users table for my app that I also reference in forms that are filled
> out
> by everyone. Most users don't need to use this table for login, so they
> don't require a password. Each user has a UserName, Password, and a bit
> for
> each privelege that I offer. If all priveleges are 0, I want to make the
> password an optional field so I don't have to some up with a bunch of
> passwords or use a random character generator. However, if they do have
> priveleges, they are required to have a password so that if someone finds
> out
> their UserName (not hard at all), they still can't log in under a
> priveleged
> account.
> Thanks in advance
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Look up CHECK constraints in SQL Server Books Online. You can easily write
one up based on the column values in a single row.
Anith|||Try:
create table t
(
PK int primary key
, UserID char (5) not null
, Password varchar (15) null
, priv1 bit not null
, priv2 bit not null
, priv3 bit not null
, constraint CK_t check (
case
when cast (priv1 as int) + priv2 + priv3 = 0 then 1
when Password is not null then 1
else 0
end = 1)
)
go
insert t values (1, 'Me', null, 0, 0, 0)
insert t values (2, 'You', null, 1, 0, 0) -- fails
insert t values (3, 'Him', 'pwd', 1, 0, 0)
go
drop table t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:848BFC64-0105-42CC-8F1D-E1C4BAF25D1E@.microsoft.com...
How can I make a field required based on the status of other fields? I have
a Users table for my app that I also reference in forms that are filled out
by everyone. Most users don't need to use this table for login, so they
don't require a password. Each user has a UserName, Password, and a bit for
each privelege that I offer. If all priveleges are 0, I want to make the
password an optional field so I don't have to some up with a bunch of
passwords or use a random character generator. However, if they do have
priveleges, they are required to have a password so that if someone finds
out
their UserName (not hard at all), they still can't log in under a priveleged
account.
Thanks in advance
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/Apps

Conditionally Parameterizing a SQLDataSource

I'm pararmeterizing a SQLDataSource, but I've encountered a problem. The users want all records returned if no ID is provided. So when they first hit the page, they will see all records, then they can filter down to one ID if needed. However, if I add a selectparameter of ID, I have to default it to a value, which won't give the users all records.

Suggestions?

The SQLDataSource has a CancelSelectOnNullParameter property and your parameters have a ConvertEmptyStringToNull property. These should help you achieve what you want. Read about these properties on MSDN.

CONDITIONAL 'WHERE'

THIS IS MY QUERY:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND STATUS IN (MMCOLPARAM7)
ORDER BY d.Attorney
THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
HOW CAN THIS BE DONE ?
THANKS FOR THE HELP GUYS.
AHi
Try something like:
SELECT *
FROM users a
WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5%'
AND ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
AND ( STATUS IN (MMCOLPARAM7)
OR MMCOLPARAM7 IS NULL )
John
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>|||With regards to

> AND STATUS IN (MMCOLPARAM7)
See if this works for status filtering
AND STATUS =coalesce(MMCOLPARAM7,STATUS)
HTH..
--
http://zulfiqar.typepad.com
BSEE, MCP
"Aleks" wrote:

> THIS IS MY QUERY:
>
> SELECT *
> FROM users a
> WHERE a.usertype = 'contact' AND ISNULL (a.FirstNm,'') LIKE 'MMColParam5
%'
> and ISNULL (a.LastNm,'') LIKE 'MMColParam6%'
> AND STATUS IN (MMCOLPARAM7)
> ORDER BY d.Attorney
> --
> THE LINE WHERE IT SAYS "AND STATUS IN (MMCOLPARM7), I WANT TO MAKE THAT
> CONDITIONAL AND ONLY RUN IT IF MMCOLPARAM7 IS NOT NULL, IF IT IS NULL TO
> SKIP THAT "AND" AND JUST EXECUTE THE REST OF THE QUERY.
> HOW CAN THIS BE DONE ?
> THANKS FOR THE HELP GUYS.
> A
>
>

conditional reading of data ...

I have a need to allow users select permission depending upon
a flag and userid. Is it possible to do it on the server instead of on clien
t?
Because using 'Access front end', I am unable to restrict table view to
secured records. However, I am able to restrict the view using forms.
Thanks for your help in advance!
-MeConsider creating SQL Server views with option VIEW_METADATA. This will
limit users to only data exposed by the views and Access won't try to access
the underlying tables directly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B781CADF-9100-4295-937D-87383BA4954B@.microsoft.com...
>I have a need to allow users select permission depending upon
> a flag and userid. Is it possible to do it on the server instead of on
> client?
> Because using 'Access front end', I am unable to restrict table view to
> secured records. However, I am able to restrict the view using forms.
> Thanks for your help in advance!
> -Me
>|||Dan,
Its a great idea, I will implement it in some cases. However, what do I do
when I have to add/modify the data? If I don't link the table directly, I
won't be able to save data.
Here is my situation. I have a table say 'Tally', only admin/s are allowed
to create records in this table. It has a flag indicating if a particular
record in 'Tally' is confidential. If so, all users aren't allowed to view
this record. Only users associated with particular 'Tally' can view/modify
records. Information about who can access is stored in another table which i
s
linked to 'Tally' with a key.
As far as viewing of the data is concerned your idea of creating a view will
work
fine. But if users have to modify the record, they will need access to the
table.
Any ideas?
Appreciate your help!
-Me
"Dan Guzman" wrote:

> Consider creating SQL Server views with option VIEW_METADATA. This will
> limit users to only data exposed by the views and Access won't try to acce
ss
> the underlying tables directly.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Me" <Me@.discussions.microsoft.com> wrote in message
> news:B781CADF-9100-4295-937D-87383BA4954B@.microsoft.com...
>
>|||> As far as viewing of the data is concerned your idea of creating a view
> will
> work
> fine. But if users have to modify the record, they will need access to the
> table.
> Any ideas?
You ought to be able to modify data via the view as long as only one base
table is affected. For modifications via Access, I believe you also need to
identify the unique column(s) to Access so that it can construct the needed
SQL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:5861EBE1-670C-4687-B8DF-13D264443643@.microsoft.com...[vbcol=seagreen]
> Dan,
> Its a great idea, I will implement it in some cases. However, what do I do
> when I have to add/modify the data? If I don't link the table directly, I
> won't be able to save data.
> Here is my situation. I have a table say 'Tally', only admin/s are
> allowed
> to create records in this table. It has a flag indicating if a particular
> record in 'Tally' is confidential. If so, all users aren't allowed to view
> this record. Only users associated with particular 'Tally' can view/modify
> records. Information about who can access is stored in another table which
> is
> linked to 'Tally' with a key.
> As far as viewing of the data is concerned your idea of creating a view
> will
> work
> fine. But if users have to modify the record, they will need access to the
> table.
> Any ideas?
> Appreciate your help!
> -Me
> "Dan Guzman" wrote:
>

Conditional Constraint - HowTo

In the table below:
===========
Roles
===========
UserID (int)
GroupID
===========
Considering that UserID is FK from Users Table and GroupID is FK from Groups
Table:
===========
Groups
===========
GroupID (int)
GroupName
===========
===========
Users
===========
UserID
Username
Password
Email
===========
And considering a User can have 0 or many Roles, exs:
Roles table
===========
1,1
1,2
1,3
===========
Meaning that this user have roles: 1,2,3
The problem is that I can have also:
Roles table
===========
1,1
1,1
1,1
===========
which is bad, so I need to have a constraint in the Roles table to force Gro
upID Unique ONLY in case UserID is already there!
Is it possible?I isn't this just a matter of letting both columns be the primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> In the table below:
> ===========
> Roles
> ===========
> UserID (int)
> GroupID
> ===========
> Considering that UserID is FK from Users Table and GroupID is FK from Grou
ps Table:
> ===========
> Groups
> ===========
> GroupID (int)
> GroupName
> ===========
>
> ===========
> Users
> ===========
> UserID
> Username
> Password
> Email
> ===========
> And considering a User can have 0 or many Roles, exs:
> Roles table
> ===========
> 1,1
> 1,2
> 1,3
> ===========
> Meaning that this user have roles: 1,2,3
>
> The problem is that I can have also:
> Roles table
> ===========
> 1,1
> 1,1
> 1,1
> ===========
> which is bad, so I need to have a constraint in the Roles table to force GroupID U
nique ONLY in
case UserID is already there!
> Is it possible?|||Hum, but then I believe there′ll be just 1 role per user?
"Tibor Karaszi" wrote:

> I isn't this just a matter of letting both columns be the primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> case UserID is already there!
>
>|||No, this enforces that you cannot have a duplicate of the *combination* of t
he columns. So, below is
fine:
1,2
1,3
2,2
2,3
Below is not fine:
1,2
1,2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...[vbcol=seagreen]
> Hum, but then I believe therell be just 1 role per user?
> "Tibor Karaszi" wrote:
>
in[vbcol=seagreen]|||Actually your Right! Sorry for misunderstanding and thanks very much for the
quick response :-)
"Tibor Karaszi" wrote:

> No, this enforces that you cannot have a duplicate of the *combination* of
the columns. So, below is
> fine:
> 1,2
> 1,3
> 2,2
> 2,3
>
> Below is not fine:
> 1,2
> 1,2
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...
> in
>
>|||We all lose ourselves from time to time. Glad it helped. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:60CD2318-C000-4EC4-9C76-992096AD5F71@.microsoft.com...[vbcol=seagreen]
> Actually your Right! Sorry for misunderstanding and thanks very much for t
he quick response :-)
> "Tibor Karaszi" wrote:
>
below is[vbcol=seagreen]
ONLY[vbcol=seagreen]

Conditional Constraint - HowTo

In the table below:
===========
Roles
===========
UserID (int)
GroupID
===========
Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
===========
Groups
===========
GroupID (int)
GroupName
===========
===========
Users
===========
UserID
Username
Password
Email
===========
And considering a User can have 0 or many Roles, exs:
Roles table
===========
1,1
1,2
1,3
===========
Meaning that this user have roles: 1,2,3
The problem is that I can have also:
Roles table
===========
1,1
1,1
1,1
===========
which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in case UserID is already there!
Is it possible?
I isn't this just a matter of letting both columns be the primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> In the table below:
> ===========
> Roles
> ===========
> UserID (int)
> GroupID
> ===========
> Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
> ===========
> Groups
> ===========
> GroupID (int)
> GroupName
> ===========
>
> ===========
> Users
> ===========
> UserID
> Username
> Password
> Email
> ===========
> And considering a User can have 0 or many Roles, exs:
> Roles table
> ===========
> 1,1
> 1,2
> 1,3
> ===========
> Meaning that this user have roles: 1,2,3
>
> The problem is that I can have also:
> Roles table
> ===========
> 1,1
> 1,1
> 1,1
> ===========
> which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in
case UserID is already there!
> Is it possible?
|||Hum, but then I believe there′ll be just 1 role per user?
"Tibor Karaszi" wrote:

> I isn't this just a matter of letting both columns be the primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> case UserID is already there!
>
>
|||No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
fine:
1,2
1,3
2,2
2,3
Below is not fine:
1,2
1,2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...[vbcol=seagreen]
> Hum, but then I believe therell be just 1 role per user?
> "Tibor Karaszi" wrote:
in[vbcol=seagreen]
|||Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
"Tibor Karaszi" wrote:

> No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
> fine:
> 1,2
> 1,3
> 2,2
> 2,3
>
> Below is not fine:
> 1,2
> 1,2
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...
> in
>
>
|||We all lose ourselves from time to time. Glad it helped. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:60CD2318-C000-4EC4-9C76-992096AD5F71@.microsoft.com...[vbcol=seagreen]
> Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
> "Tibor Karaszi" wrote:
below is[vbcol=seagreen]
ONLY[vbcol=seagreen]

Concurrent query optimization

I have a server with Windows 2000, SQL Server 2000, 2 1GHz
processors and 1.5GHz of memory. The users were
complaining of slowness today from tehir web application.
The only message that I found in any of the logs was the
following:
This SQL Server has been optimized for 8 concurrent
queries. This limit has been exceeded by 2 queries and
performance may be adversely affected.
I don't know if this is giving me any info cuz I don't
know what the message means. Is there anywhere (or
anyone) that can explain this to me?This probably means that you are not using Standard Edition or
Enterprise Edition of SQL-Server, but the MSDE or some other version
that cannot be licensed for real production loads.
The statement
SELECT @.@.version
should tell you which edition is installed.
Gert-Jan
Tom Griffin wrote:
> I have a server with Windows 2000, SQL Server 2000, 2 1GHz
> processors and 1.5GHz of memory. The users were
> complaining of slowness today from tehir web application.
> The only message that I found in any of the logs was the
> following:
> This SQL Server has been optimized for 8 concurrent
> queries. This limit has been exceeded by 2 queries and
> performance may be adversely affected.
> I don't know if this is giving me any info cuz I don't
> know what the message means. Is there anywhere (or
> anyone) that can explain this to me?|||Thanks for your reply. You are correct; this server was
installed with SQL Server 2000 Personal Edition. We have
the licensing for the Standard Edition, but apparently it
was not installed properly. Is there a "simple" method to
upgrade to the Standard Edition?
Tom
>--Original Message--
>This probably means that you are not using Standard
Edition or
>Enterprise Edition of SQL-Server, but the MSDE or some
other version
>that cannot be licensed for real production loads.
>The statement
> SELECT @.@.version
>should tell you which edition is installed.
>Gert-Jan
>
>Tom Griffin wrote:
>> I have a server with Windows 2000, SQL Server 2000, 2
1GHz
>> processors and 1.5GHz of memory. The users were
>> complaining of slowness today from tehir web
application.
>> The only message that I found in any of the logs was the
>> following:
>> This SQL Server has been optimized for 8 concurrent
>> queries. This limit has been exceeded by 2 queries and
>> performance may be adversely affected.
>> I don't know if this is giving me any info cuz I don't
>> know what the message means. Is there anywhere (or
>> anyone) that can explain this to me?
>.
>|||See
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/instsql/in_overview_2xtf.asp
>--Original Message--
>Thanks for your reply. You are correct; this server was
>installed with SQL Server 2000 Personal Edition. We have
>the licensing for the Standard Edition, but apparently it
>was not installed properly. Is there a "simple" method
to
>upgrade to the Standard Edition?
>Tom
>>--Original Message--
>>This probably means that you are not using Standard
>Edition or
>>Enterprise Edition of SQL-Server, but the MSDE or some
>other version
>>that cannot be licensed for real production loads.
>>The statement
>> SELECT @.@.version
>>should tell you which edition is installed.
>>Gert-Jan
>>
>>Tom Griffin wrote:
>> I have a server with Windows 2000, SQL Server 2000, 2
>1GHz
>> processors and 1.5GHz of memory. The users were
>> complaining of slowness today from tehir web
>application.
>> The only message that I found in any of the logs was
the
>> following:
>> This SQL Server has been optimized for 8 concurrent
>> queries. This limit has been exceeded by 2 queries and
>> performance may be adversely affected.
>> I don't know if this is giving me any info cuz I don't
>> know what the message means. Is there anywhere (or
>> anyone) that can explain this to me?
>>.
>.
>

Concurrent Procedure Execution

Hi,
Think of that there is a db which is used by many users. An there is a proc
which is called frequently by all users. For concurrent callings of this
proc by many differents users, Does there occur several execution time
intervals as parallel or are the concurrent callers wait each other? AND Can
we or Do we have to use BEGIN and COMMIT TRAN pairs for an atomic process in
such situations?
Thanks in Advance.
Emre GuldoganOn Thu, 10 Mar 2005 15:42:22 +0200, "Emre Guldogan" <ask me please...>
wrote:

>Think of that there is a db which is used by many users. An there is a proc
>which is called frequently by all users. For concurrent callings of this
>proc by many differents users, Does there occur several execution time
>intervals as parallel or are the concurrent callers wait each other?
Hi Emre,
Different users can execute the procedure simultaneously; they will
execute in parallel. Of course, if the procedure locks data, this will
block other users trying to use the same data, whether through the same
procedure or not. For most data retrieval procedures, this should not be
an issue, so execution would be simultaneous; for data modification
procedures, locking and blocking might lead to actual execution being
serial, but that depends on the code of the procedure, the transaction
isolation level and whether transactions are used or not.

>AND Can
>we or Do we have to use BEGIN and COMMIT TRAN pairs for an atomic process i
n
>such situations?
If you need atomic processing, you should indeed use BEGIN TRAN and
either COMMIT or ROLLBACK TRAN. If different users execute the proc
simultaneously, the transactions won't interfere (but they might block
each other, as indicated above).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Concurrent Database Users License

when the nubmer of licenses provided are referred to
as "concurrent database users licenses", does that imply
that multiple devices could have the possibility of
accessing the server but only the number given can access
it simultaneously or is this limit on the number of
devices that can have the potential of accessing the
server? example: 10 concurrent database users licenses
provided as part of package. does this allow for 50
devices to have an application loaded that could access a
SQL server database but limit the number of devices that
could actually access it to 10 or is the number of devices
that could have the potential to access the database only
10?
What product is this for? I might be wrong, but I don't think SQL
Server licensing works this way any more. Can you post more context?
Steve Kass
Drew University
anonymous@.discussions.microsoft.com wrote:

>when the nubmer of licenses provided are referred to
>as "concurrent database users licenses", does that imply
>that multiple devices could have the possibility of
>accessing the server but only the number given can access
>it simultaneously or is this limit on the number of
>devices that can have the potential of accessing the
>server? example: 10 concurrent database users licenses
>provided as part of package. does this allow for 50
>devices to have an application loaded that could access a
>SQL server database but limit the number of devices that
>could actually access it to 10 or is the number of devices
>that could have the potential to access the database only
>10?
>

Concurrent Connections

HI All
Could help me out to find the following information from the Query
Analyzer?
Number of Concurrent Connections/Users
Average Transaction Per/Sec
Database Growth Rate
RegardsHi,
You can querry system table master..sysprocesses and get required counters.
Amol Lembhe
"Praveen" wrote:
> HI All
> Could help me out to find the following information from the Query
> Analyzer?
> Number of Concurrent Connections/Users
> Average Transaction Per/Sec
> Database Growth Rate
> Regards
>

Concurrent Access - New Record, Primary Key problem....

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @.@.IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @.@.IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks."Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
> Can someone explain what happens when two users concurrently attempt to
> create a new record in a table with an autonumber primary key? For
> example, user 1 creates a new record and manipulates it within a
> transaction making use (perhaps) of the @.@.IDENTITY value when creating
> other, related records. Before this transaction is complete, user 2
> creates a new record and does the same thing. Presumably they will both
> have the same @.@.IDENTITY? If this is the case, how is it possible to
> manage such a situation?
> Thanks.

No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon|||Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ebea66$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:csgov0$4e6$1$830fa17d@.news.demon.co.uk...
>>
>> Can someone explain what happens when two users concurrently attempt to
>> create a new record in a table with an autonumber primary key? For
>> example, user 1 creates a new record and manipulates it within a
>> transaction making use (perhaps) of the @.@.IDENTITY value when creating
>> other, related records. Before this transaction is complete, user 2
>> creates a new record and does the same thing. Presumably they will both
>> have the same @.@.IDENTITY? If this is the case, how is it possible to
>> manage such a situation?
>>
>> Thanks.
>>
> No, each session will have a different value, so there's no problem with
> concurrency - check out SCOPE_IDENTITY(), IDENT_CURRENT() and @.@.IDENTITY
> in Books Online.
> Note that just defining a column as an identity column is not enough to
> guarantee uniqueness - you can still create duplicates manually (see SET
> IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
> sure it is declared as a PK when you create the table.
> Simon

Concurrency problem

Users A and B execute a simple "SELECT FROM ..." statement at the same
time.Both concurrent requests on SQL server 2000 fail with an error message:
Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC
SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access
denied.
The SQL requests are from ASP page on a shared SQL server. How do I enable
several users execute my sql statements at the same time (only reading
data). Is locking
causing this problem or something else? Should I use WITH(UNLOCK)?
Jared Burford
Computer IT/Support
support@.megatech-pc.comThe error has nothing to do with locking. Check the connection and if the
users have rights to access the databse and the table.
AMB
"Jared Burford" wrote:

> Users A and B execute a simple "SELECT FROM ..." statement at the same
> time.Both concurrent requests on SQL server 2000 fail with an error messag
e:
> Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC
> SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access
> denied.
> The SQL requests are from ASP page on a shared SQL server. How do I enable
> several users execute my sql statements at the same time (only reading
> data). Is locking
> causing this problem or something else? Should I use WITH(UNLOCK)?
>
> Jared Burford
> Computer IT/Support
> support@.megatech-pc.com
>
>|||It works ok with one, but fails with more than one user
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BF533DFA-EC96-4B46-89CD-FE3CD47FFB3C@.microsoft.com...
> The error has nothing to do with locking. Check the connection and if the
> users have rights to access the databse and the table.
>
> AMB
> "Jared Burford" wrote:
>|||just a quick note: both are trying to access the same table which takes in
exxcess of 30 secs.
"Jared Burford" <support@.megatech-pc.com> wrote in message
news:eqGN$IkVFHA.548@.tk2msftngp13.phx.gbl...
> It works ok with one, but fails with more than one user
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:BF533DFA-EC96-4B46-89CD-FE3CD47FFB3C@.microsoft.com...
>|||Do u think SELECT ... WITH(NOLOCK) ... can help?
If u only want to do dirty read, i think this is fine for u.
"Jared Burford" wrote:

> just a quick note: both are trying to access the same table which takes in
> exxcess of 30 secs.
> "Jared Burford" <support@.megatech-pc.com> wrote in message
> news:eqGN$IkVFHA.548@.tk2msftngp13.phx.gbl...
>
>|||Problem solved... I just open tabe, read it, and close, it, set the flag on
that one so it won't check it next time, and move to the next, and repeat.
This way other uses can read the same table without getting stuck.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BF533DFA-EC96-4B46-89CD-FE3CD47FFB3C@.microsoft.com...
> The error has nothing to do with locking. Check the connection and if the
> users have rights to access the databse and the table.
>
> AMB
> "Jared Burford" wrote:
>

Concurrency in asp.net

Hi everybody,

I need to understand how concurrency excatlywork in asp.net. For example, I'm confused what happens if two users atthe same time try to access the same record in a table or even the samevariable. Do ASP.NET handle this , I mean by locking one user andletting the other to have access OR it's up to the programmer to writesome code to lock shared resources such as database , objects andvariables?

If it's up to the programmer to do this task, I appreciate if you can show me an example that clarifies that.

Thank you

ADO.NET uses a disconnected data model, meaning that in theory, nothings knows 2 users are accessing the same record at the same time. When you read a record, and the record is displayed, there is no connection left on the database. Therefore there is no "concurrency", so to speak, because for all general purpose, the users only see the record's -content-, not the record itself. This is required because the web is a stateless environment...you cannot keep a live connection to the server.

Thus, the only really effective way of handling this (there are other ways, but this is the all purpose one), is called Optimistic Concurrency. The record is read, and when you attempt to update it, ADO.NET (only IF you ask it to do so), will check if the record changed (by storing the original values) since it was read by the user. If it did, it will complain and tell you that someone else changed the record between the last time you read it and your update. Otherwise, it will write it normally.

If 2 people attempt to write the record at the exact same time ( I mean, there are 2 open connections on the same record at the exact same instant), then it is up to the DBMS to handle this, ADO.NET has no control over it. Usualy what will happen is one of the users will be given the go (first come first serve), and the second update will trigger the Optimistic Concurrency mechanism I just talked about.

|||

This article tell you how to Handling Concurrency Issues in .NET

Concatenating Multiple Reports into One Output

Hello,
We have a number of different reports (all separate RDLs). We want to allow
the users to select one or more reports to generate. Basically, we want the
user to select a number of different reports into a report group, and then
generate the entire group of reports.
First question: is it possible to generate multiple reports at the same
time?
Next question: is it possible to output all the different reports into one
long report with each individual report separated by a page break?
Thanks.Hi Oliver,
If you implement a custom multiple selection box in your user interface,
what you want is possible, I think.
Create a list box. Put the subreport field inside the list box. Set the
dataset for the list box.
Hope this helps.
Regards,
Cem Demircioglu
"Oliver" <oliver@.nospam.com> wrote in message
news:%23UaqGLvCFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hello,
> We have a number of different reports (all separate RDLs). We want to
> allow
> the users to select one or more reports to generate. Basically, we want
> the
> user to select a number of different reports into a report group, and then
> generate the entire group of reports.
> First question: is it possible to generate multiple reports at the same
> time?
> Next question: is it possible to output all the different reports into one
> long report with each individual report separated by a page break?
> Thanks.
>|||Oliver,
I think that you can do this, however it is not very pretty.
First create a "master" report. The master report will have a bunch of
subreports, one for each report the user could display. The master
report will also have a series of parameters, one for each subreport,
with the values Yes & No. In each subreport set some custom Visibility
expression like "=iif(Parameters!1stReportName.Value = "Yes", true,
false)" This will display the subreport based on the value of the
parameter.
If you put each subreport in a separate group of a table, you can force
a page break at the end.
hopefully this will give you some ideas