Any valid login can access Enterprise Manager

Hi.
When creating a SQL Server2000 login (NT Authen) with read-only rights to
user tables in a user database, this very same login can:
1. Login into EM
2. Though cannot change any objects, but can
- 1. view all system objects (logins, DTS etc)
3. STOP SQL Server Agent
4. RESTART SQL SERVER!!!!
This all seem to be traced back to the fact every login is a member of the
PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
Can anyone tell me how to:
1. Prevent user (not DBA, DBO's etc) login into EM?
2. Prevent user login into QA?
Cheers!> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
prevent access to msdb. This will prevent viewing DTS packages. See
http://support.microsoft.com/defaul...b;en-us;282463.
You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users from
enumerating logins via EM.

> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
The ability to stop and start services is controlled through Windows
permissions, not SQL Server security. If the account is a member of the
Windows 'Administrators' or 'Power Users' groups, then the user can stop and
start services using any tool or command. EM will not allow non-privileged
users to stop/start services.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
> Hi.
> When creating a SQL Server2000 login (NT Authen) with read-only rights to
> user tables in a user database, this very same login can:
> 1. Login into EM
> 2. Though cannot change any objects, but can
> - 1. view all system objects (logins, DTS etc)
> 3. STOP SQL Server Agent
> 4. RESTART SQL SERVER!!!!
> This all seem to be traced back to the fact every login is a member of the
> PUBLIC role, and the PUBLIC role allow u to do all of the above!!!
> Can anyone tell me how to:
> 1. Prevent user (not DBA, DBO's etc) login into EM?
> 2. Prevent user login into QA?
> Cheers!|||Thks Dan - it sure works - but still no way of preventing a valid SQL Login
to access other objects on EM or seeing them using other tools (such as
Visual Studio).
Thks again!
"Dan Guzman" wrote:

> You can disable the msdb guest user (EXEC msdb..sp_dropuser 'guest') to
> prevent access to msdb. This will prevent viewing DTS packages. See
> http://support.microsoft.com/defaul...b;en-us;282463.
> You can 'REVOKE SELECT FROM syslogins' to prevent non privileged users fro
m
> enumerating logins via EM.
>
> The ability to stop and start services is controlled through Windows
> permissions, not SQL Server security. If the account is a member of the
> Windows 'Administrators' or 'Power Users' groups, then the user can stop a
nd
> start services using any tool or command. EM will not allow non-privilege
d
> users to stop/start services.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:2DB045CF-4B2F-4493-BEB5-FA684D5800A1@.microsoft.com...
>
>|||By default, SQL 2000 users can read catalog meta data in those databases
they have permissions to access. It's possible to revoke public permissions
from some of the catalog objects but this can break data access API's so
proceed at your own risk. SQL 2005 provides more control over meta data
access.
Hope this helps.
Dan Guzman
SQL Server MVP
"Oddie" <Oddie@.discussions.microsoft.com> wrote in message
news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...[vbcol=seagreen]
> Thks Dan - it sure works - but still no way of preventing a valid SQL
> Login
> to access other objects on EM or seeing them using other tools (such as
> Visual Studio).
> Thks again!
> "Dan Guzman" wrote:
>|||Thks Dan for all your help!
"Dan Guzman" wrote:

> By default, SQL 2000 users can read catalog meta data in those databases
> they have permissions to access. It's possible to revoke public permissio
ns
> from some of the catalog objects but this can break data access API's so
> proceed at your own risk. SQL 2005 provides more control over meta data
> access.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Oddie" <Oddie@.discussions.microsoft.com> wrote in message
> news:5D081158-8F2B-428B-ABE2-32892258C3C0@.microsoft.com...
>
>

Any special way to create an index

Hi All,
I am not very experienced in creating indexes. Is there a preferred way
one should follow. I created come indexes using EM. When I do that, and
hit save on the table, does the SQL server at that point generate the
indexes?
Thanks for your comments/help.
*** Sent via Developersdex http://www.examnotes.net ***yes.
Or you can run a script from the query analyzer.
CREATE INDEX MyNewIndex ON TableName (ColumnName)
this is a very simple example. Look up "Create Index" in books on line (BOL)
for more detailed explanation
Greg Jackson
PDX, Oregon|||For what it's worth, within the Enterprise Manager's table designer, after
adding entries for your indexes, you can click the "save change script"
button instead of the "save" button, and it will display a dialog with the
SQL scripts that would be applied. You may want to save off the script, if
you are going to re-apply the indexes on other occasions.
"Vik Mohindra" <vikmohindra@.hotmail.com> wrote in message
news:Oq6yebVXFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I am not very experienced in creating indexes. Is there a preferred way
> one should follow. I created come indexes using EM. When I do that, and
> hit save on the table, does the SQL server at that point generate the
> indexes?
> Thanks for your comments/help.
> *** Sent via Developersdex http://www.examnotes.net ***

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
Thanks
Hi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:

> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:

> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go o
ut
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take th
e
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncroniz
e
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in th
e
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

Any functions to replace NZ in SQL Server?

I'm moving some queries out of an Access front end and creating views out of
them in SQL Server 2005 express. In some of the numeric fields, I use nz
quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
Is there anything equivalent to this in SQL Server? Right now I'm using
CASE WHEN ... but it seems like an awful lot of script to write just to
replace null with a zero.

Any help would be greatly appreciated.

Thanks!use coalesce or isnull

declare @.v int
select coalesce(@.v,0),isnull(@.v,0)

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @. h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>I'm moving some queries out of an Access front end and creating views out of
>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is null.
>Is there anything equivalent to this in SQL Server? Right now I'm using
>CASE WHEN ... but it seems like an awful lot of script to write just to
>replace null with a zero.
>Any help would be greatly appreciated.
>Thanks!

Hi Rico,

Use COALESCE:

COALESCE (arg1, arg2, arg3, arg4, ...)

returns the first non-NULL of the supplied arguments. You need at least
two arguments, but you can add as many as you like.

--
Hugo Kornelis, SQL Server MVP|||Thanks Guys,

I wound up finding ISNULL before I had a chance to post back. (why do I
always find the solution right after I post).

Is there an argument for using Coalesce over IsNull?

Thanks!

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:j7sf42hg4b78p8u1v5nj283av4kovqivur@.4ax.com...
> On Thu, 20 Apr 2006 20:25:47 GMT, "Rico" <r c o l l e n s @. h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>>I'm moving some queries out of an Access front end and creating views out
>>of
>>them in SQL Server 2005 express. In some of the numeric fields, I use nz
>>quite often, ( i.e. nz([MyField],0)) to return a zero if the field is
>>null.
>>Is there anything equivalent to this in SQL Server? Right now I'm using
>>CASE WHEN ... but it seems like an awful lot of script to write just to
>>replace null with a zero.
>>
>>Any help would be greatly appreciated.
>>
>>Thanks!
>>
> Hi Rico,
> Use COALESCE:
> COALESCE (arg1, arg2, arg3, arg4, ...)
> returns the first non-NULL of the supplied arguments. You need at least
> two arguments, but you can add as many as you like.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @. h e m m i n
g w a y . c o mREMOVE THIS PART IN CAPS> wrote:

>Thanks Guys,
>I wound up finding ISNULL before I had a chance to post back. (why do I
>always find the solution right after I post).
>Is there an argument for using Coalesce over IsNull?

Hi Rico,

Three!

1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
on SQL Server.

2. COALESCE takes more than two arguments. If you have to find the first
non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
COALESCE.

3. Data conversion weirdness. The datatype of a COALESCE is the datatype
with highest precedence of all datatypes used in the COALESCE (same as
with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
the same as the first argument. This is extremely non-standard and can
cause very nasty and hard-to-track-down bugs.

--
Hugo Kornelis, SQL Server MVP|||Rico (r c o l l e n s @. h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> I wound up finding ISNULL before I had a chance to post back. (why do I
> always find the solution right after I post).
> Is there an argument for using Coalesce over IsNull?

In theory, coalesce is what you always should use, because:

1) It's ANSI-compatible.
2) coalesce can accept list of several values, whereas isnull accepts
exactly two.

Unfortunately, there are contexts were isnull() is preferable, or the
only choice. The ones I'm thinking of are:
1) In definition of indexed views you may need to use isnull to make
the view indexable.
2) I've seen reports where using coalesce resulted in a poor query plan
whereas isnull did not. I should add that that was not really a plain-
vanilla query.

So despite these excpetions, I would recommend coalesce. Even if it's
more difficult to spell.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Null is not zero. Null is not a zero length string.

I believe that nulls were not designed to be placeholders for these
values.
We should be extremely careful when we convert nulls to values. Such
conversion could lead to error. Often it is persons without strong
grounding in mathematics and logic who make these conversions,
increasing the likelihood of such error. The best practice is likely to
be the exclusion of records with nulls in the columns we are processing
and to enter values in those where a value is appropriate. There may be
some cases where it's a good idea to substitute a zls for a null value,
but none comes to my mind at this time.

IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
corresponding Coalesce function.

[Yes, I've probably posted IsNull(Field,Value) solutions here; that was
then; this is now.]|||Excellent! Thanks!

"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:vttf42107btt07jbk21cvb6953kediiarp@.4ax.com...
> On Thu, 20 Apr 2006 20:58:14 GMT, "Rico" <r c o l l e n s @. h e m m i n
> g w a y . c o mREMOVE THIS PART IN CAPS> wrote:
>>Thanks Guys,
>>
>>I wound up finding ISNULL before I had a chance to post back. (why do I
>>always find the solution right after I post).
>>
>>Is there an argument for using Coalesce over IsNull?
> Hi Rico,
> Three!
> 1. COALESCE is ANSI-standard and hence more portable. ISNULL works only
> on SQL Server.
> 2. COALESCE takes more than two arguments. If you have to find the first
> non-NULL of a set of six arguments, ISNULL has to be nested. Not so with
> COALESCE.
> 3. Data conversion weirdness. The datatype of a COALESCE is the datatype
> with highest precedence of all datatypes used in the COALESCE (same as
> with any SQL expression). Not so for ISNULL - the datatype of ISNULL is
> the same as the first argument. This is extremely non-standard and can
> cause very nasty and hard-to-track-down bugs.
> --
> Hugo Kornelis, SQL Server MVP|||Read about IsNull Vs Coalesce
http://www.sqlservercentral.com/col...tweenisnull.asp

Madhivanan|||On 20 Apr 2006 15:57:53 -0700, Lyle Fairfield wrote:

>Null is not zero. Null is not a zero length string.
>I believe that nulls were not designed to be placeholders for these
>values.
(snip)

Hi Lyle,

Thus far, I agree with yoour post.

(snip)
> There may be
>some cases where it's a good idea to substitute a zls for a null value,
>but none comes to my mind at this time.

First, you should be awarer that COALESCE and ISNULL on SQL Server, or
Nz on Access, can not just be used to replace NULL with 0 or zero length
string - you can replace them with anything you like. Common uses are
COALESCE (SomeColumn, 'n/a') in a report. Or
COALESCE (UserSpecifiedColumn, DefaultValue) in any query or view.

>IMNSHO SQL would be more rigorous if it had no IsNull(Field,Value) or
>corresponding Coalesce function.

I disagree with this statement. As I've shown above, COALESCE and ISNULL
can be used in very useful ways. That they might also be abused by
people who fail to think their solutions through is sad, but no reason
to abolish them. That's like forbidding cars because someone might cause
an accident while drinking and driving.

Besides, since COALESCE is just a shorthand for a specific CASE
expression, removing COALESCE from the language would have no effect;
people would just use the equivalent CASE expression.

--
Hugo Kornelis, SQL Server MVP|||Lyle Fairfield wrote:
> We should be extremely careful when we convert nulls to values. Such
> conversion could lead to error. Often it is persons without strong
> grounding in mathematics and logic who make these conversions,
> increasing the likelihood of such error.

You think so? Nulls as formulated in SQL totally defy any standard
mathematics or logic. Any system that permits the predicate (x=x) to
evaluate to anything other than true isn't likely to win many votes
from persons with a strong grounding in mathematics. It is precisely
because nulls are so counter-intuitive that they lead to so many
mistakes in SQL. However, I do agree with your basic point that if you
regularly need to convert nulls like this it may indicate weakness in
your design or requirements.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||I probably shouldn't open my mouth in the presence of some posters, but with
regard to converting nulls being bad design; I have a bunch of reports that
show loans and payments (just to make things simple). If I have no payment
record (a null) then I have zero payments applied to the loan. By
converting these null payment records to zero payments, is this considered
in theory bad design? Or is this an exception to that rule. Is there a
definition between what would be considered bad design and what is
considered an exception?

Not trying to raise a debate really, just asking for clarification.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145653630.430902.73680@.z34g2000cwc.googlegro ups.com...
> Lyle Fairfield wrote:
>> We should be extremely careful when we convert nulls to values. Such
>> conversion could lead to error. Often it is persons without strong
>> grounding in mathematics and logic who make these conversions,
>> increasing the likelihood of such error.
> You think so? Nulls as formulated in SQL totally defy any standard
> mathematics or logic. Any system that permits the predicate (x=x) to
> evaluate to anything other than true isn't likely to win many votes
> from persons with a strong grounding in mathematics. It is precisely
> because nulls are so counter-intuitive that they lead to so many
> mistakes in SQL. However, I do agree with your basic point that if you
> regularly need to convert nulls like this it may indicate weakness in
> your design or requirements.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/...US,SQL.90).aspx
> --|||Rico wrote:
> I probably shouldn't open my mouth in the presence of some posters, but with
> regard to converting nulls being bad design; I have a bunch of reports that
> show loans and payments (just to make things simple). If I have no payment
> record (a null) then I have zero payments applied to the loan. By
> converting these null payment records to zero payments, is this considered
> in theory bad design? Or is this an exception to that rule. Is there a
> definition between what would be considered bad design and what is
> considered an exception?
> Not trying to raise a debate really, just asking for clarification.

If you have no payment record then why do you have a null?

Nulls are a source of complexity and error. On the other hand, avoiding
them can lead to complexity of a different kind - often requiring the
creation of additional tables for example. Whether to use nulls at all
is a controversial topic about which a huge amount has been written and
argued over. In practice, SQL database systems tend to make it very
hard to avoid them altogether.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Rico (me@.you.com) writes:
> I probably shouldn't open my mouth in the presence of some posters, but
> with regard to converting nulls being bad design; I have a bunch of
> reports that show loans and payments (just to make things simple). If I
> have no payment record (a null) then I have zero payments applied to the
> loan. By converting these null payment records to zero payments, is
> this considered in theory bad design? Or is this an exception to that
> rule. Is there a definition between what would be considered bad design
> and what is considered an exception?

In practice there are many cases where NULL and 0 or the empty string
are more or less the same thing.

Of course, if we have a table:

CREATE TABLE loans (loanno char(11) NOT NULL,
...
no_of_payments int NULL,
...

A NULL in no_of_payments taken to the letter would mean "we don't
know how many payments that has not been done on this loan, if any
at all" or "this is a loan on which you do not make payments at all,
so it is not applicable".

But I don't believe for a second that this is how your table design looks
like. And with a more complex design, there could easily appear a NULL in
a query.

There are many cases were isnull or coalesce comes in handy. For some
computations, equating NULL with 0 makes sense. But coalesce can
also be used to get a value from multiple places. Assume, for instance,
that a customer can have a fixed discount, or he can be part of a
group that can have a common rebate. Assuming that an individual
discount overrides the group discount, that would be:

coalesce(Customers.discount, Groups.discount, 0)

The 0 at the end is really needed here, if we assume that a customer
may not belong to any group. That is, the Groups table comes in with
a left join, so it does not help if Groups.discount is not nullable.
And Customers.discount needs to be NULL, so we can have some logic
to get the group instead. It would not be good to have 0 to mean
"use group instead", because we may actually want to deprive the
customer of the group rebate.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||FROM BOL:
A value of NULL indicates that the value is unknown. A value of NULL is
different from an empty or zero value. No two null values are equal.
Comparisons between two null values, or between a NULL and any other
value, return unknown because the value of each NULL is unknown.

Null values generally indicate data that is unknown, not applicable, or
that the data will be added later. For example, a customer's middle
initial may not be known at the time the customer places an order.

Following is information about nulls:

To test for null values in a query, use IS NULL or IS NOT NULL in the
WHERE clause.

When query results are viewed in SQL Server Management Studio Code
editor, null values are shown as (null) in the result set.

Null values can be inserted into a column by explicitly stating NULL in
an INSERT or UPDATE statement, by leaving a column out of an INSERT
statement, or when adding a new column to an existing table by using
the ALTER TABLE statement.

Null values cannot be used for information that is required to
distinguish one row in a table from another row in a table, for
example, foreign or primary keys.

In program code, you can check for null values so that certain
calculations are performed only on rows with valid, or not NULL, data.
For example, a report can print the social security column only if
there is data that is not NULL in the column. Removing null values when
you are performing calculations can be important, because certain
calculations, such as an average, can be inaccurate if NULL columns are
included.

If it is likely that null values are stored in your data and you do not
want null values appearing in your data, you should create queries and
data-modification statements that either remove NULLs or transform them
into some other value.

Important:
To minimize maintenance and possible effects on existing queries or
reports, you should minimize the use of null values. Plan your queries
and data-modification statements so that null values have minimal
effect.

When null values are present in data, logical and comparison operators
can potentially return a third result of UNKNOWN instead of just TRUE
or FALSE. This need for three-valued logic is a source of many
application errors. These tables outline the effect of introducing null
comparisons.

---
I think that null should not be referred to as a value, in the same way
that celibacy should not be referred to as sex.

In addition, the statements:
"A value of NULL is different from an empty or zero value."
and
"you should create queries and data-modification statements that
either ... or transform them into some other value."
conflict.|||This is just crap!|||Lyle Fairfield (lylefairfield@.aim.com) writes:
> This is just crap!

At least that was a concise comment.

Nevertheless, exactly what you think is crap? How would you model
discounts that can be applied on several levels?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Any DOS utility for MDX?

Hi,

Is there any DOS utility can run MDX query ( like sqlcmd and osql for TSQL.)?

We need schedule a MDX query for creating a local cube file.

Appreciate any help on this.

The ASCMD utility, provided as one of the SSAS samples, can be used for this kind of thing. Here's a link on how to use it:

http://msdn2.microsoft.com/en-us/library/ms365187.aspx

Samples can be downloaded at:

http://msdn2.microsoft.com/en-us/library/ms365187.aspx

Please note, you will need to compile the utility per the instructions provided.

Good luck,
Bryan

|||Hi Bryan,

Thank you so much for the answer, I am going to try that.

Appreciated your help.

Any difference between SQL Server 2000 and MSDE 2000?

I am creating an install program and I'm wondering if there is a

difference between SQL Server 2000 and MSDE 2000? Do they have

different entries in the Registry?

From the documentation that I've read it seems as if they are one and the same.

However, if someone knows how to differentiate between them in the Registry it would be greatly appreciated.
ThanksMSDE 2000 is the desktop engine of the SQL Server 2000.|||So as far as Registry entries go, there would be no noticeable difference between the two....is that correct?