Create a View in MSSQL 2000

I have created a view in MS SQL2000 as followed:

Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;

my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.

Any HELP would be GREAT.
Thank You!!I have created a view in MS SQL2000 as followed:

Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;

my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.

Any HELP would be GREAT.
Thank You!!

Check out the CASE Statement in Books on Line.

Regards,

hmscott|||Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip, EMAILaddress
FROM orders;

Semi-Colon(";")

Is this for DB2 or Oracle 8i?

SQL Server would be

SELECT order_NO
, shiptoname
, Shiptoaddress
, Shiptocity
, shiptostate
, shiptozip
, EMAILaddress
, CASE WHEN EMAILaddress IS NULL THEN 'N' ELSE 'Y' END AS EMAILflag
FROM orders
GO|||Thank You!

Create a table using a Query

Hello,
With Oracle, we can create a table as following :
CREATE TABLE tab_name
AS SELECT
col1, col2...
from tab1, tab2
where ...
With MSSQL, can we do the same thing ?The SELECT INTO statement creates a new table and populates it with the result set of the SELECT. The structure of the new table is defined by the attributes of the expressions in the select list.

I suggest you to refer to books online for more information.|||Thanks a lot|||No mention, keep in touch with BOL:)

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your help
And if you are using SQL2005 SP2, you would find the newly released dashboard
reports rather useful in finding queries that may be consuming excessive CPU.
Linchi
"tabarnak@.gmail.com" wrote:

> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>
|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your helpAre you on SQL 2000.
Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
approach I take is normally, to analyse these figures , ascertain whether
it's an IO or network problem . Then drill down to the db level
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178200041.682518.327170@.n59g2000hsh.googlegroups.com...
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||And if you are using SQL2005 SP2, you would find the newly released dashboar
d
reports rather useful in finding queries that may be consuming excessive CPU
.
Linchi
"tabarnak@.gmail.com" wrote:

> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards|||I went to the link but got a 404
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/sql
<a href="http://links.10026.com/?link=http://www.itjobfeed.com/sql"> uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178703761.778779.133060@.e51g2000hsg.googlegroups.com...
> On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> (being away for some days)
> Hi Jack,
> I uploaded the output in http://rafb.net/p/6rXNqc96.html.
> CXPACKET, LATCH_EX and PAGEIOLATCH_SH
> Am I right, could you give some explanatations and hints.
> is it possible to clear these stats, in order to have them on a short
> sessions, along-side of the profiler for instance.
> Thanks
> Regards
>

CPU usage and troubleshoot (sp_who2, profiling)

(I have only little knowledge in mssql)
Hello,
We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
sticked at 100% during few hours. The dbs are used by customers, we
didn't designed them, we just manage the system until middleware level
(SQL Server)
We would like to trouble this CPU problem. I use some tricks found on
the net, as the sp_who2, which gave me some details on the commands
run. I have a lot of sleeping process with AWAITING COMMAND; the
sp_who2 reported me also CPU and IO usage but I don't know which
units.
The output is at http://rafb.net/p/DWkMQO35.html
Can I have some pointers on how use th output of sp_who2 to track
performance problems
Thanks for your helpAre you on SQL 2000.
Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
approach I take is normally, to analyse these figures , ascertain whether
it's an IO or network problem . Then drill down to the db level
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178200041.682518.327170@.n59g2000hsh.googlegroups.com...
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||And if you are using SQL2005 SP2, you would find the newly released dashboard
reports rather useful in finding queries that may be consuming excessive CPU.
Linchi
"tabarnak@.gmail.com" wrote:
> (I have only little knowledge in mssql)
> Hello,
> We have a 2 members MS SQL Cluster, which sometimes has its CPU usage
> sticked at 100% during few hours. The dbs are used by customers, we
> didn't designed them, we just manage the system until middleware level
> (SQL Server)
> We would like to trouble this CPU problem. I use some tricks found on
> the net, as the sp_who2, which gave me some details on the commands
> run. I have a lot of sleeping process with AWAITING COMMAND; the
> sp_who2 reported me also CPU and IO usage but I don't know which
> units.
> The output is at http://rafb.net/p/DWkMQO35.html
> Can I have some pointers on how use th output of sp_who2 to track
> performance problems
> Thanks for your help
>|||On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
> Are you on SQL 2000.
> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site. The
> approach I take is normally, to analyse these figures , ascertain whether
> it's an IO or network problem . Then drill down to the db level
> --
> Jack Vamvas
(being away for some days)
Hi Jack,
I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
CXPACKET, LATCH_EX and PAGEIOLATCH_SH
Am I right, could you give some explanatations and hints.
is it possible to clear these stats, in order to have them on a short
sessions, along-side of the profiler for instance.
Thanks
Regards|||I went to the link but got a 404
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/sql
<a href="http://links.10026.com/?link=">http://www.itjobfeed.com/sql"> uk it jobs</a>
"tabarnak@.gmail.com" <baptiste.millemathias@.gmail.com> wrote in message
news:1178703761.778779.133060@.e51g2000hsg.googlegroups.com...
> On 3 mai, 17:46, "Jack Vamvas" <DEL_TO_RE...@.del.com> wrote:
>> Are you on SQL 2000.
>> Try DBCC SQLPERF(WAITSTATS), and put the results up on your web site.
>> The
>> approach I take is normally, to analyse these figures , ascertain whether
>> it's an IO or network problem . Then drill down to the db level
>> --
>> Jack Vamvas
> (being away for some days)
> Hi Jack,
> I uploaded the output in http://rafb.net/p/6rXNqc96.html.
>>From what i'm seeing, the main causes of WAIT are RESOURCE_QUEUE,
> CXPACKET, LATCH_EX and PAGEIOLATCH_SH
> Am I right, could you give some explanatations and hints.
> is it possible to clear these stats, in order to have them on a short
> sessions, along-side of the profiler for instance.
> Thanks
> Regards
>

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions'
thx for help
--
PITPit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is often
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such a
> heave load on CPU?
> any suggestions'
> thx for help

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions?
thx for help
PIT
Pit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is often
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such a
> heave load on CPU?
> any suggestions?
> thx for help

CPU usage 50-70%

hi All,
we have cluster active-passive MSSQL 2000 Server + SP3.
The server experience high CPU usage, the counter % Processor Time is often
continually at 50 - 100 %.
Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
Counter LockWaitTime(ms) sometimes 200.
Are above values high? I mean whether Lock Request/sec might generate such a
heave load on CPU?
any suggestions'
thx for help
--
PITPit,
It depends. :-)
This could be perfectly normal. Then again it might not. These types of
questions can be answered very easily if you benchmark and baseline your
systems regularly. A third party enterprise monitoring tool would be
great for this. "high" or "low" are comparative terms to your normal
mode of operation. They should not be compared to mine or anyone else's
database system.
I would recommend you start collecting performance information so that
these questions can be answered more confidently in future. You could
also produce some nice graphs to show your boss how much of a proactive
DBA you are and earn some brownie points towards your next pay rise. ;-)
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Pit wrote:
> hi All,
> we have cluster active-passive MSSQL 2000 Server + SP3.
> The server experience high CPU usage, the counter % Processor Time is ofte
n
> continually at 50 - 100 %.
> Counter SQLServer:Locks:Lock Requests/sec is 60000 - 80000
> Counter LockWaitTime(ms) sometimes 200.
> Are above values high? I mean whether Lock Request/sec might generate such
a
> heave load on CPU?
> any suggestions'
> thx for help

CPU Distribution between Sql Server and OS

Hi,
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and database
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.
Hi
"Krish" wrote:

> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
> and the senario is
> the dataserver has to function for some big computations and data processing.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and database
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John

CPU Distribution between Sql Server and OS

Hi,
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing
.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and databas
e
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.Hi
"Krish" wrote:

> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU
's
> and the senario is
> the dataserver has to function for some big computations and data processi
ng.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and datab
ase
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS (
OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John

CPU Distribution between Sql Server and OS

Hi,
I am a DBA for SQL server and I have a poduct to be tested for performance
with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
and the senario is
the dataserver has to function for some big computations and data processing.
application is on .Net c# code also will be loaded on the same machine,
should also be scaleable with multiple users working on the app. and database
access.
My question is
1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
and App)
2) Are any documented(cases) white papers on the same(resourse charing
between OS and sql server).
Please share you experiencies.
Thanks,
fellow DBA.Hi
"Krish" wrote:
> Hi,
> I am a DBA for SQL server and I have a poduct to be tested for performance
> with backend MSSQL Server 2005 developer edition. I have a machine of 8CPU's
> and the senario is
> the dataserver has to function for some big computations and data processing.
> application is on .Net c# code also will be loaded on the same machine,
> should also be scaleable with multiple users working on the app. and database
> access.
> My question is
> 1) Is there a guodeline for spliting the CPU's with MSSQL server and OS ( OS
> and App)
> 2) Are any documented(cases) white papers on the same(resourse charing
> between OS and sql server).
> Please share you experiencies.
> Thanks,
> fellow DBA.
I don't know of any white papers, these things are not recommend to be on
the same machine; a general value would only be a starting point anyhow. You
should decide the configuration from the results of your load testing and
cover as many hardware combinations as possible to get the best results.
You can not use Developer Edition for a production system, if the hardware
you are testing on is not the same as the destination server you may only
have a general guideline to performance.
John

Couple of questions = )

1.
Does anyone know the max number of Databases a single MSSQL 2000 enterprise machine can handle ??
2.
Does anyone have an idea on how you could use the DTS in MSSQL server to kick a script off on a UNIX machine ??
Chris1. 32,767
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp