Cleaning up hardcode sections

Hey,

Does anyone know of a neat and easy way to modify this section of hardcode:

SELECT CASE dbo.requestsbyyeartemp.themonth when '1' then 'January' when '2' then 'Febuary' when '3' then 'March'
when '4' then 'April' when '5' then 'May' when '6' then 'June' when '7' then 'July' when '8' then 'August'
when '9' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as 'themonth', etc...

Requestsbyyeartemp is a table where the numbers corresponding to the months are stored. This statement is used to make a new table where the months are stored with the proper names and not numbers.

I would sooner not have any hardcode at all if there is a simple way to do it.

ThanksSELECT DATENAME(month, dbo.requestsbyyeartemp.themonth) AS 'themonth'|||Would this work if the <date> portion of DATENAME(<datepart>, <date>) is only a single number?

I ask this because I keep getting January no matter what number 'themonth' is?|||Any more suggestions anyone?|||How about:SELECT DateName(month, DateAdd(month
, dbo.requestsbyyeartemp.themonth, '1950-12-01')) AS themonth-PatP

City, State expression problem!

I have a report which lists the city and state. The problem is that
the city and state are in one field and were in Upper case. I used the
strconv but I don't know how to make the state portion Upper instead or
Proper.
Right now the output looks like this: Birminham, Al
Any help is appreciated.Try the following:
select replace(ColName,right(ColName,1), Upper(right(ColName,1)))
Good Luck
swtjen01 wrote:
> I have a report which lists the city and state. The problem is that
> the city and state are in one field and were in Upper case. I used the
> strconv but I don't know how to make the state portion Upper instead or
> Proper.
> Right now the output looks like this: Birminham, Al
> Any help is appreciated.

Choosing correct high availability setup

I have a client that wants me to start hosting their SQL and IIS for them.
They want to have the ability to have a "hot spare" in case the primary SQL
server fails. I have read and read until my eyes hurt about clustering and
mirroring and I am getting more confused on how to proceed.
My first question is, should I even be using IIS on the SQL box at all?
They have a web interface that gets it data from SQL.
My second question is, if I host their web site on a separate IIS box is
there any reason that I shouldn't go with database mirroring instead of any
other option?
Thanks for any help you can provide.
Marty
Never do both on the same box, read this -
http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
Server Clustering with make the entire node HA, SQL mirroring will make that
database HA. Which does your customer require?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>I have a client that wants me to start hosting their SQL and IIS for them.
> They want to have the ability to have a "hot spare" in case the primary
> SQL
> server fails. I have read and read until my eyes hurt about clustering
> and
> mirroring and I am getting more confused on how to proceed.
> My first question is, should I even be using IIS on the SQL box at all?
> They have a web interface that gets it data from SQL.
> My second question is, if I host their web site on a separate IIS box is
> there any reason that I shouldn't go with database mirroring instead of
> any
> other option?
> Thanks for any help you can provide.
> Marty
|||Well that is what I had always thought, but you see more and more people
consolidating these functions.
Basically there will be an Access database that arrives via FTP at the web
server, the SQL server will pick it up from a shared drive and import it.
That will happen about every 15 minutes or so. The same web server will host
a site that the client can access to see real time data that it pulls from
the SQL server. The only snag is that if the primary SQL server goes down
for some reason, IIS will not know that it has to get that data from the
backup server if I am using only mirroring.
"Rodney R. Fournier [MVP]" wrote:

> Never do both on the same box, read this -
> http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
> Server Clustering with make the entire node HA, SQL mirroring will make that
> database HA. Which does your customer require?
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>
>
|||Got it, use NLB for the IIS servers, Server Clustering for the backend
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...[vbcol=seagreen]
> Well that is what I had always thought, but you see more and more people
> consolidating these functions.
> Basically there will be an Access database that arrives via FTP at the web
> server, the SQL server will pick it up from a shared drive and import it.
> That will happen about every 15 minutes or so. The same web server will
> host
> a site that the client can access to see real time data that it pulls from
> the SQL server. The only snag is that if the primary SQL server goes down
> for some reason, IIS will not know that it has to get that data from the
> backup server if I am using only mirroring.
>
> "Rodney R. Fournier [MVP]" wrote:
|||NLB?
If I use clustering for the backend, will the web server be able to keep
serving the data from the SQL database to the web interface in the case of a
disaster? The web site will be looking for a specific instance of SQL
correct?
"Rodney R. Fournier [MVP]" wrote:

> Got it, use NLB for the IIS servers, Server Clustering for the backend
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...
>
>
|||Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
won't be supported/allowed with Windows Server 2008 Failover Clustering.
No matter how you do your SQL, IIS will need to know the instance name to
connect.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...[vbcol=seagreen]
> NLB?
> If I use clustering for the backend, will the web server be able to keep
> serving the data from the SQL database to the web interface in the case of
> a
> disaster? The web site will be looking for a specific instance of SQL
> correct?
> "Rodney R. Fournier [MVP]" wrote:
|||Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I am
very new on this subject. If one SQL box in the cluster fails, will the
other SQL box in that cluster pick up where the first left off and assume the
failed box's identity as far as IP address, DNS name, etc...?
Thanks for all of your help Rodney. I am glad there is a forum where people
like me can get real world help from people like yourself. Pat yourself on
the back man!!!
"Rodney R. Fournier [MVP]" wrote:

> Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
> won't be supported/allowed with Windows Server 2008 Failover Clustering.
> No matter how you do your SQL, IIS will need to know the instance name to
> connect.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...
>
>
|||It will failover to another node and continue on, but at a cost. SQL not
running on the node until the failover, so the databases goes through the
normal SQL startup, DB integrity check, roll back non-committed
transactions, roll forward committed ones, etc. Your application(s) have to
be cluster aware to handle the failover. Ping tests will fail during the
process, though maybe only 4-5 depending on the network, hardware, DB sizes,
etc.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...[vbcol=seagreen]
> Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I
> am
> very new on this subject. If one SQL box in the cluster fails, will the
> other SQL box in that cluster pick up where the first left off and assume
> the
> failed box's identity as far as IP address, DNS name, etc...?
> Thanks for all of your help Rodney. I am glad there is a forum where
> people
> like me can get real world help from people like yourself. Pat yourself
> on
> the back man!!!
> "Rodney R. Fournier [MVP]" wrote:
|||The only application that will depend on the SQL server would be IIS. How is
IIS going to react if one of the cluster nodes fail? I guess I would need to
point it to the alternate node's instance.
"Rodney R. Fournier [MVP]" wrote:

> It will failover to another node and continue on, but at a cost. SQL not
> running on the node until the failover, so the databases goes through the
> normal SQL startup, DB integrity check, roll back non-committed
> transactions, roll forward committed ones, etc. Your application(s) have to
> be cluster aware to handle the failover. Ping tests will fail during the
> process, though maybe only 4-5 depending on the network, hardware, DB sizes,
> etc.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...
>
>
|||Correct as Geoff "the SQL God and good buddy of mine" already stated.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:7871ABB2-D689-41A6-B9F7-30C5482A4EE3@.microsoft.com...[vbcol=seagreen]
> So the node that picks up truly is a "clone" of the failed one? Well then
> it
> sounds like I may be better off going with clustering than mirroring in my
> situation wouldn't you say?
> "Rodney R. Fournier [MVP]" wrote:

Choose a default hierarchy in a dimension

Hi all,

I explain my case. In a Article dimension I have 3 hierarchies (Natures, Staven, Service). When I browse my cube in bids with this exemple :

on rows axis I drag and drop article dimension

on columns axis I drag and drop a time dimension

on data area I made the same operation with a mesure

Then wen I expand article dimension the hierarchy I saw is Natures. I don't want it. I want Service one. How can i do that?

In advance thanks for your help.

Christophe

The first hierarchy you create in BIDS will be your default hierarchy. IE: The Top/left-most hierarchy you see in BIDS.|||

Hi,

Thanks for your reply. It's impossible to change that?

Christophe.

|||Hey,

You could recreate your hierarchies (if it's not too much of a pain). Or, I haven't tried this yet but assume it'll work, you could just change the order of your hierarchies in XMLA.

So, just script your dimension to a new query editor, search for your hierarchies, (you're looking for the <Hierarchies></Hierarchies> tags), and move the hierarchy you want to be default to the top of the list.

C

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul CahillHi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegroups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>|||Hi,
I think you can go for Transaction replication.
--
Herbert
"Paul Cahill" wrote:
> We wish to look into setting up a mirrored server at another site in case we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of running
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:
>> We wish to look into setting up a mirrored server at another site in case
>> we
>> lose access to our head office.
>> The company is based in London, fairly near potential bomb targets etc.
>> There are three other locations, one having a 4GB leased line to head
>> office.
>> Assuming we are not going to jump to Yukon in the short term it looks
>> like
>> log shipping is probably the way to go but are there other alternatives?
>> Note that currently the system is not set up well for disaster recovery.
>> Simple recovery model with over night backups. No experience here of
>> running
>> proper log backups on recovery mode "full". There's probably some "select
>> into" in the code plus we do some pretty big index rebuilds over night.
>> Database is currently 80GB across raid 10 arrays of 18Gb disks.
>> Thanks
>> Paul Cahill
>>

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul CahillHi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegroups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>|||Hi,
I think you can go for Transaction replication.
--
Herbert
"Paul Cahill" wrote:

> We wish to look into setting up a mirrored server at another site in case
we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of runni
ng
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...[vbcol=seagreen]
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:
>

Choices for a backup server at other site over WAN

We wish to look into setting up a mirrored server at another site in case we
lose access to our head office.
The company is based in London, fairly near potential bomb targets etc.
There are three other locations, one having a 4GB leased line to head
office.
Assuming we are not going to jump to Yukon in the short term it looks like
log shipping is probably the way to go but are there other alternatives?
Note that currently the system is not set up well for disaster recovery.
Simple recovery model with over night backups. No experience here of running
proper log backups on recovery mode "full". There's probably some "select
into" in the code plus we do some pretty big index rebuilds over night.
Database is currently 80GB across raid 10 arrays of 18Gb disks.
Thanks
Paul Cahill
Hi Paul,
We have the same problem.At that time we use to transfer all the table
and data on access and zip it and copied on wan.
Other option we tried was sending all those tables wich are updated
daily or transaction occured.we had 2000+tables.
the other option was that we send the backup to the otherplace and
created a log backup every 30 mins.
as the log backup completes it copies to the network.and as the log
backup copies restore takes place on that server.
hope u will find some way.
from
killer.
|||One of our problems is that we have no idea how much volume we could be
generating.
I'd like to be able to take the index rebuilds out of the equation. If we do
log shipping that won't be an option.
"doller" <sufianarif@.gmail.com> wrote in message
news:1125486973.550827.119140@.g43g2000cwa.googlegr oups.com...
> Hi Paul,
> We have the same problem.At that time we use to transfer all the table
> and data on access and zip it and copied on wan.
> Other option we tried was sending all those tables wich are updated
> daily or transaction occured.we had 2000+tables.
> the other option was that we send the backup to the otherplace and
> created a log backup every 30 mins.
> as the log backup completes it copies to the network.and as the log
> backup copies restore takes place on that server.
>
> hope u will find some way.
> from
> killer.
>
|||Hi,
I think you can go for Transaction replication.
Herbert
"Paul Cahill" wrote:

> We wish to look into setting up a mirrored server at another site in case we
> lose access to our head office.
> The company is based in London, fairly near potential bomb targets etc.
> There are three other locations, one having a 4GB leased line to head
> office.
> Assuming we are not going to jump to Yukon in the short term it looks like
> log shipping is probably the way to go but are there other alternatives?
> Note that currently the system is not set up well for disaster recovery.
> Simple recovery model with over night backups. No experience here of running
> proper log backups on recovery mode "full". There's probably some "select
> into" in the code plus we do some pretty big index rebuilds over night.
> Database is currently 80GB across raid 10 arrays of 18Gb disks.
> Thanks
> Paul Cahill
>
>
|||Thanks Herbert
We were thinking about this but could be quite complicated to
setup/admin/switch roles.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:EF5870A3-1962-4F00-9C2F-11812AC579EA@.microsoft.com...[vbcol=seagreen]
> Hi,
> I think you can go for Transaction replication.
> --
> Herbert
>
> "Paul Cahill" wrote:

Checking for null in Xtab query

I have the following query and it works. Where the left part of the join doe
s
not have any records i'd like to return a zero. I tried the case but, ...
thanks all
kes
select
c.calId,
c.calDate,
c.calShortDesc,
c.calDesc,
c.CalHoliday,
e3.evtAll,
CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
e3.SS,
e3.WS,
e3.C,
e3.WE,
e3.ED,
e3.NW
from cal c
LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
(select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdatetime
= e.evtdatetime) as NS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdatetime
= e.evtdatetime) as SS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdatetime
= e.evtdatetime) as WS,
(select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdatetime
= e.evtdatetime) as C,
(select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdatetime
= e.evtdatetime) as WE,
(select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdatetime
= e.evtdatetime) as ED,
(select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdatetime
= e.evtdatetime) as NW
from evt e
group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
where c.calDate >= '20050626' and c.calDate <= '20050806'
(I can post the Data def, but this is a simple question a bout returning a
zero for null)will this work?
CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
or is there a better idea?
thanks
kes
"WebBuilder451" wrote:

> I have the following query and it works. Where the left part of the join d
oes
> not have any records i'd like to return a zero. I tried the case but, ...
> thanks all
> kes
> select
> c.calId,
> c.calDate,
> c.calShortDesc,
> c.calDesc,
> c.CalHoliday,
> e3.evtAll,
> CASE e3.NS WHEN NULL then 0 else e3.NS END NS,
> e3.SS,
> e3.WS,
> e3.C,
> e3.WE,
> e3.ED,
> e3.NW
> from cal c
> LEFT Join (select e.evtDateTime, count(e.evtDateTime) EvtALL,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 1 and e2.evtdateti
me
> = e.evtdatetime) as NS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 2 and e2.evtdateti
me
> = e.evtdatetime) as SS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 3 and e2.evtdateti
me
> = e.evtdatetime) as WS,
> (select count(e2.evtLocID) from evt e2 where evtlocID = 4 and e2.evtdateti
me
> = e.evtdatetime) as C,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 1 and e2.evtdateti
me
> = e.evtdatetime) as WE,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 2 and e2.evtdateti
me
> = e.evtdatetime) as ED,
> (select count(e2.evtCatID) from evt e2 where evtCatID = 3 and e2.evtdateti
me
> = e.evtdatetime) as NW
> from evt e
> group by e.evtDatetime) e3 on e3.evtDateTime = c.calDate
> where c.calDate >= '20050626' and c.calDate <= '20050806'
> (I can post the Data def, but this is a simple question a bout returning a
> zero for null)
>
>|||Yes, that will work.
Why haven't you tried it?
ML|||You don't even need a Case expression. The following will work:
IsNull(e3.NS,0) as NS
"WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
> will this work?
> CASE WHEN e3.NS IS NULL then 0 else e3.NS END NS?
> or is there a better idea?
> thanks
> kes
> "WebBuilder451" wrote:
>|||that's the answer !!!
thanks
IOU1 kes
"JT" wrote:

> You don't even need a Case expression. The following will work:
> IsNull(e3.NS,0) as NS
> "WebBuilder451" <WebBuilder451@.discussions.microsoft.com> wrote in message
> news:F506A0DE-48CD-40DE-8982-532DC5390C05@.microsoft.com...
>
>

CHECKDB on master ?

If the corruption is limited to non-clustered indexes (with index ID > 1),
then you won't lose data. In your case, the corrupt page is in the
sysdepends heap so you will lose data from that table. This system table
stores info about the dependencies between views, triggers, sprocs and the
objects they reference. I'm not sure how the system will behave if you run
repair - which will delete the page (because the internal pointer in the
record that points to the variable-length column offset table is pointing
off the end of the record - and so the page could be grossly corrupt).
A far better course of action is to run restore in this case. Do you have a
valid backup?
See my blog post at
https://blogs.msdn.com/sqlserversto.../07/620319.aspx
for more details.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>Can I get data loss from running CHECKDB on master (or user databases)
if the consistency errors are in indexes?
Here are the results of CHECKDB on master:
Server: Msg 8928, Level 16, State 1, Line 2
Object ID 12, index ID 0: Page (1:364) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 2
Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
DBCC results for 'master'.
DBCC results for 'sysobjects'.
There are 1272 rows in 23 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 104 rows in 4 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 4884 rows in 85 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 2024 rows in 952 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 788 rows in 4 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 17 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 5282 rows in 28 pages for object 'sysdepends'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'sysdepends' (object ID 12).
DBCC results for 'sysreferences'.
There are 0 rows in 0 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysdatabases'.
There are 309 rows in 13 pages for object 'sysdatabases'.
DBCC results for 'sysxlogins'.
There are 465 rows in 14 pages for object 'sysxlogins'.
DBCC results for 'sysdevices'.
There are 8 rows in 1 pages for object 'sysdevices'.
DBCC results for 'sysmessages'.
There are 3831 rows in 165 pages for object 'sysmessages'.
DBCC results for 'sysconfigures'.
There are 38 rows in 1 pages for object 'sysconfigures'.
DBCC results for 'sysservers'.
There are 7 rows in 1 pages for object 'sysservers'.
DBCC results for 'syslanguages'.
There are 33 rows in 3 pages for object 'syslanguages'.
DBCC results for 'syscharsets'.
There are 114 rows in 33 pages for object 'syscharsets'.
DBCC results for 'sysaltfiles'.
There are 618 rows in 123 pages for object 'sysaltfiles'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'spt_monitor'.
There are 1 rows in 1 pages for object 'spt_monitor'.
DBCC results for 'spt_values'.
There are 730 rows in 7 pages for object 'spt_values'.
DBCC results for 'spt_fallback_db'.
There are 0 rows in 0 pages for object 'spt_fallback_db'.
DBCC results for 'spt_fallback_dev'.
There are 0 rows in 0 pages for object 'spt_fallback_dev'.
DBCC results for 'spt_fallback_usg'.
There are 0 rows in 0 pages for object 'spt_fallback_usg'.
DBCC results for 'spt_provider_types'.
There are 25 rows in 1 pages for object 'spt_provider_types'.
DBCC results for 'spt_datatype_info_ext'.
There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
DBCC results for 'MSreplication_options'.
There are 2 rows in 1 pages for object 'MSreplication_options'.
DBCC results for 'spt_datatype_info'.
There are 36 rows in 1 pages for object 'spt_datatype_info'.
DBCC results for 'spt_server_info'.
There are 29 rows in 1 pages for object 'spt_server_info'.
CHECKDB found 0 allocation errors and 2 consistency errors in database
'master'.
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKDB (master ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||If the corruption is limited to non-clustered indexes (with index ID > 1),
then you won't lose data. In your case, the corrupt page is in the
sysdepends heap so you will lose data from that table. This system table
stores info about the dependencies between views, triggers, sprocs and the
objects they reference. I'm not sure how the system will behave if you run
repair - which will delete the page (because the internal pointer in the
record that points to the variable-length column offset table is pointing
off the end of the record - and so the page could be grossly corrupt).
A far better course of action is to run restore in this case. Do you have a
valid backup?
See my blog post at
https://blogs.msdn.com/sqlserversto.../07/620319.aspx
for more details.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...
> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Thanks Paul!!!!!
I am trying to understand how to interpret the results of CHECKDB. I
can't seem to find enough detail to understand what is going on in BOL.
I think we are going to open a ticket with Microsoft support to assist
us.
Our latest backup overlaps the disk failure. Based off time, it looks
like the backup was 80-90% complete before the disk went bad.
The next backup we have is over a week old. It looks like our backup
to tape got bumped by other jobs which ran over
Paul S Randal [MS] wrote:[vbcol=seagreen]
> If the corruption is limited to non-clustered indexes (with index ID > 1),
> then you won't lose data. In your case, the corrupt page is in the
> sysdepends heap so you will lose data from that table. This system table
> stores info about the dependencies between views, triggers, sprocs and the
> objects they reference. I'm not sure how the system will behave if you run
> repair - which will delete the page (because the internal pointer in the
> record that points to the variable-length column offset table is pointing
> off the end of the record - and so the page could be grossly corrupt).
> A far better course of action is to run restore in this case. Do you have
a
> valid backup?
> See my blog post at
> https://blogs.msdn.com/sqlserversto...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...|||Before you undertake the effort of restoring your currently damaged master d
b
from backup a) make sure no more disk problems are happening - else you may
just get corruption again. If raid were the disks successfully rebuild
/and/or have they past consistency check after rebuild?
Restore your master backup first as a user DB e.g. master2 - watch out for
file location and physical filename - e.g just create a separate diretory.
Once restored run check db through it to see whether you already had
problems at the time this bacup was taken. If yes
Lookup rebuildm in BOL and on msdn - which will allow you to rebuild your
system databases.
Corruption in master and model are frowned upon and PSS will typically
recommend to restore from backup. In case you dont have a recent backup of
master. Make a backup to disk of your master and msdb before going any
further now. The rest is fairly cookie cutter - detach your user DBs script
out logins (see also sp_help_revlogin) run rebuildm bring back your logins -
attach userdbs back and restore the backup of msdb you took above.
Good luck.
"Dave" wrote:

> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
documented every error message that CHECKDB can return (SQL Server 2005 ones
are done but haven't made it out to the wild yet)
Can you select * from sysdepends in master? You may be lucky and there's
nothing there, in which case (once you've made sure you know exactly why the
problem occured and have taken steps to make sure it doesn't happen again),
you should be able to get away with running repair rather than going back to
your old backups. And, of course, get a much better backup strategy.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> Thanks Paul!!!!!
> I am trying to understand how to interpret the results of CHECKDB. I
> can't seem to find enough detail to understand what is going on in BOL.
> I think we are going to open a ticket with Microsoft support to assist
> us.
> Our latest backup overlaps the disk failure. Based off time, it looks
> like the backup was 80-90% complete before the disk went bad.
> The next backup we have is over a week old. It looks like our backup
> to tape got bumped by other jobs which ran over
>
> Paul S Randal [MS] wrote:
>|||Thanks Paul!!!!!
I am trying to understand how to interpret the results of CHECKDB. I
can't seem to find enough detail to understand what is going on in BOL.
I think we are going to open a ticket with Microsoft support to assist
us.
Our latest backup overlaps the disk failure. Based off time, it looks
like the backup was 80-90% complete before the disk went bad.
The next backup we have is over a week old. It looks like our backup
to tape got bumped by other jobs which ran over
Paul S Randal [MS] wrote:[vbcol=seagreen]
> If the corruption is limited to non-clustered indexes (with index ID > 1),
> then you won't lose data. In your case, the corrupt page is in the
> sysdepends heap so you will lose data from that table. This system table
> stores info about the dependencies between views, triggers, sprocs and the
> objects they reference. I'm not sure how the system will behave if you run
> repair - which will delete the page (because the internal pointer in the
> record that points to the variable-length column offset table is pointing
> off the end of the record - and so the page could be grossly corrupt).
> A far better course of action is to run restore in this case. Do you have
a
> valid backup?
> See my blog post at
> https://blogs.msdn.com/sqlserversto...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150822219.428726.201260@.c74g2000cwc.googlegroups.com...|||Before you undertake the effort of restoring your currently damaged master d
b
from backup a) make sure no more disk problems are happening - else you may
just get corruption again. If raid were the disks successfully rebuild
/and/or have they past consistency check after rebuild?
Restore your master backup first as a user DB e.g. master2 - watch out for
file location and physical filename - e.g just create a separate diretory.
Once restored run check db through it to see whether you already had
problems at the time this bacup was taken. If yes
Lookup rebuildm in BOL and on msdn - which will allow you to rebuild your
system databases.
Corruption in master and model are frowned upon and PSS will typically
recommend to restore from backup. In case you dont have a recent backup of
master. Make a backup to disk of your master and msdb before going any
further now. The rest is fairly cookie cutter - detach your user DBs script
out logins (see also sp_help_revlogin) run rebuildm bring back your logins -
attach userdbs back and restore the backup of msdb you took above.
Good luck.
"Dave" wrote:

> Can I get data loss from running CHECKDB on master (or user databases)
> if the consistency errors are in indexes?
>
> Here are the results of CHECKDB on master:
> Server: Msg 8928, Level 16, State 1, Line 2
> Object ID 12, index ID 0: Page (1:364) could not be processed. See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 2
> Table error: Object ID 12, index ID 0, page (1:364), row 50. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 86 and 24.
> DBCC results for 'master'.
> DBCC results for 'sysobjects'.
> There are 1272 rows in 23 pages for object 'sysobjects'.
> DBCC results for 'sysindexes'.
> There are 104 rows in 4 pages for object 'sysindexes'.
> DBCC results for 'syscolumns'.
> There are 4884 rows in 85 pages for object 'syscolumns'.
> DBCC results for 'systypes'.
> There are 26 rows in 1 pages for object 'systypes'.
> DBCC results for 'syscomments'.
> There are 2024 rows in 952 pages for object 'syscomments'.
> DBCC results for 'sysfiles1'.
> There are 2 rows in 1 pages for object 'sysfiles1'.
> DBCC results for 'syspermissions'.
> There are 788 rows in 4 pages for object 'syspermissions'.
> DBCC results for 'sysusers'.
> There are 17 rows in 1 pages for object 'sysusers'.
> DBCC results for 'sysproperties'.
> There are 0 rows in 0 pages for object 'sysproperties'.
> DBCC results for 'sysdepends'.
> There are 5282 rows in 28 pages for object 'sysdepends'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'sysdepends' (object ID 12).
> DBCC results for 'sysreferences'.
> There are 0 rows in 0 pages for object 'sysreferences'.
> DBCC results for 'sysfulltextcatalogs'.
> There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
> DBCC results for 'sysfulltextnotify'.
> There are 0 rows in 0 pages for object 'sysfulltextnotify'.
> DBCC results for 'sysdatabases'.
> There are 309 rows in 13 pages for object 'sysdatabases'.
> DBCC results for 'sysxlogins'.
> There are 465 rows in 14 pages for object 'sysxlogins'.
> DBCC results for 'sysdevices'.
> There are 8 rows in 1 pages for object 'sysdevices'.
> DBCC results for 'sysmessages'.
> There are 3831 rows in 165 pages for object 'sysmessages'.
> DBCC results for 'sysconfigures'.
> There are 38 rows in 1 pages for object 'sysconfigures'.
> DBCC results for 'sysservers'.
> There are 7 rows in 1 pages for object 'sysservers'.
> DBCC results for 'syslanguages'.
> There are 33 rows in 3 pages for object 'syslanguages'.
> DBCC results for 'syscharsets'.
> There are 114 rows in 33 pages for object 'syscharsets'.
> DBCC results for 'sysaltfiles'.
> There are 618 rows in 123 pages for object 'sysaltfiles'.
> DBCC results for 'sysfilegroups'.
> There are 1 rows in 1 pages for object 'sysfilegroups'.
> DBCC results for 'spt_monitor'.
> There are 1 rows in 1 pages for object 'spt_monitor'.
> DBCC results for 'spt_values'.
> There are 730 rows in 7 pages for object 'spt_values'.
> DBCC results for 'spt_fallback_db'.
> There are 0 rows in 0 pages for object 'spt_fallback_db'.
> DBCC results for 'spt_fallback_dev'.
> There are 0 rows in 0 pages for object 'spt_fallback_dev'.
> DBCC results for 'spt_fallback_usg'.
> There are 0 rows in 0 pages for object 'spt_fallback_usg'.
> DBCC results for 'spt_provider_types'.
> There are 25 rows in 1 pages for object 'spt_provider_types'.
> DBCC results for 'spt_datatype_info_ext'.
> There are 10 rows in 1 pages for object 'spt_datatype_info_ext'.
> DBCC results for 'MSreplication_options'.
> There are 2 rows in 1 pages for object 'MSreplication_options'.
> DBCC results for 'spt_datatype_info'.
> There are 36 rows in 1 pages for object 'spt_datatype_info'.
> DBCC results for 'spt_server_info'.
> There are 29 rows in 1 pages for object 'spt_server_info'.
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'master'.
> repair_allow_data_loss is the minimum repair level for the errors found
> by DBCC CHECKDB (master ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>|||Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
documented every error message that CHECKDB can return (SQL Server 2005 ones
are done but haven't made it out to the wild yet)
Can you select * from sysdepends in master? You may be lucky and there's
nothing there, in which case (once you've made sure you know exactly why the
problem occured and have taken steps to make sure it doesn't happen again),
you should be able to get away with running repair rather than going back to
your old backups. And, of course, get a much better backup strategy.
Thanks
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <daveg.01@.gmail.com> wrote in message
news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...
> Thanks Paul!!!!!
> I am trying to understand how to interpret the results of CHECKDB. I
> can't seem to find enough detail to understand what is going on in BOL.
> I think we are going to open a ticket with Microsoft support to assist
> us.
> Our latest backup overlaps the disk failure. Based off time, it looks
> like the backup was 80-90% complete before the disk went bad.
> The next backup we have is over a week old. It looks like our backup
> to tape got bumped by other jobs which ran over
>
> Paul S Randal [MS] wrote:
>|||Paul
Yes I can select * from sysdepends
Are you saying that I can repair the master database or I cannot?
It looks like our backups are corrupt too. I am not sure when the
original errors were introduced. It looks like I might have to use a
procedure similar to what Sassan described above.
Paul S Randal [MS] wrote:[vbcol=seagreen]
> Look in MSDN, and the recent SQL Server 2000 downloadable BOL refreshes. I
> documented every error message that CHECKDB can return (SQL Server 2005 on
es
> are done but haven't made it out to the wild yet)
> Can you select * from sysdepends in master? You may be lucky and there's
> nothing there, in which case (once you've made sure you know exactly why t
he
> problem occured and have taken steps to make sure it doesn't happen again)
,
> you should be able to get away with running repair rather than going back
to
> your old backups. And, of course, get a much better backup strategy.
> Thanks
> --
> Paul Randal
> Lead Program Manager, Microsoft SQL Server Storage Engine
> http://blogs.msdn.com/sqlserverstor...ne/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Dave" <daveg.01@.gmail.com> wrote in message
> news:1150827802.442472.143860@.u72g2000cwu.googlegroups.com...

check value in table upper case or lower

hi

i want to select * from table1 where name =petter?

now if there is many type of petter in table linke PETTER ,Petter Andpetter which record will come in display?

if i want all this three (PETTER,Petter,petter) will come in display which command is for this ?

regard

It depends on the column collation(default the same as database) setting, which you can check usingsyscolumns. If you want to ignore case, you can change all data into UPPER (or LOWER) case:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol where UPPER(name)=UPPER(@.n1)

|||

Or you can specify the collation used in the SELECT command:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol wherename=@.n1
collate SQL_Latin1_General_CP1_CI_AS

You can get descriptions of SQL collations using this statement:

SELECT * FROM ::fn_helpcollations()

|||

that was a good solution

but what it does

collate SQL_Latin1_General_CP1_CI_AS

any ideas

|||

This is used to specify collation used in the SELECT command. I choose SQL_Latin1_General_CP1_CI_AS because this collation is case insensitive (notice the CI). For more information, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5ell.asp