check database integrity

Folks, do you know some way or tool to check the database integrity and
repair error like corrupt entries to a d/b in sql 2k?
Thanks
DanielDBCC = Database consistency checker. Have a look in SQL BOL
Try DBCC CHECKDB to start
--
HTH
Ryan Waight, MCDBA, MCSE
"Daniel_A" <dabella@.empower.edu.uy> wrote in message
news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> Folks, do you know some way or tool to check the database integrity and
> repair error like corrupt entries to a d/b in sql 2k?
> Thanks
> Daniel
>
>|||Ryan,
DBCC is an abbreviation for Database Console Command.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> DBCC = Database consistency checker. Have a look in SQL BOL
> Try DBCC CHECKDB to start
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > Folks, do you know some way or tool to check the database integrity and
> > repair error like corrupt entries to a d/b in sql 2k?
> >
> > Thanks
> >
> > Daniel
> >
> >
> >
>|||If i'm wrong i stand corrected. Can't find a translation on Microsoft but...
I got the following from the Sybase site...
What Is the Database Consistency Checker?
The database consistency checker (dbcc) provides commands for checking the
logical and physical consistency of a database. Two major functions of dbcc
are:
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
HTH
Ryan Waight, MCDBA, MCSE
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> Ryan,
> DBCC is an abbreviation for Database Console Command.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > DBCC = Database consistency checker. Have a look in SQL BOL
> >
> > Try DBCC CHECKDB to start
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > Folks, do you know some way or tool to check the database integrity
and
> > > repair error like corrupt entries to a d/b in sql 2k?
> > >
> > > Thanks
> > >
> > > Daniel
> > >
> > >
> > >
> >
> >
>|||Ryan,
Its mentioned in BooksOnLine:
'DBCC'
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_217n.asp?frame=true
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%234aktjSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> If i'm wrong i stand corrected. Can't find a translation on Microsoft
but...
> I got the following from the Sybase site...
> What Is the Database Consistency Checker?
> The database consistency checker (dbcc) provides commands for checking the
> logical and physical consistency of a database. Two major functions of
dbcc
> are:
>
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
>
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> > Ryan,
> >
> > DBCC is an abbreviation for Database Console Command.
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > > DBCC = Database consistency checker. Have a look in SQL BOL
> > >
> > > Try DBCC CHECKDB to start
> > >
> > > --
> > > HTH
> > > Ryan Waight, MCDBA, MCSE
> > >
> > > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > > Folks, do you know some way or tool to check the database integrity
> and
> > > > repair error like corrupt entries to a d/b in sql 2k?
> > > >
> > > > Thanks
> > > >
> > > > Daniel
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, have a look at a response to Event ID 17055 by Scott Morris. It's
posted under the wrong subject but is about DBCC.
In his version of BOL searching for DBCC (T-SQL) returns Database
Consistency Checker
In my version it states Database Console Command
The acronym appears to have changed meaning between version.
From now on I'll be using Database Console Command
--
HTH
Ryan Waight, MCDBA, MCSE
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eH1lmpSfDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Ryan,
> Its mentioned in BooksOnLine:
> 'DBCC'
>
http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_217n.asp?frame=true
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%234aktjSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > If i'm wrong i stand corrected. Can't find a translation on Microsoft
> but...
> >
> > I got the following from the Sybase site...
> > What Is the Database Consistency Checker?
> > The database consistency checker (dbcc) provides commands for checking
the
> > logical and physical consistency of a database. Two major functions of
> dbcc
> > are:
> >
> >
>
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/asesag/@.ebt-link;pt=57217?target=%25N%15_57285_START_RESTART_N%25
> >
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:%23%23QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> > > Ryan,
> > >
> > > DBCC is an abbreviation for Database Console Command.
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> > > news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > > > DBCC = Database consistency checker. Have a look in SQL BOL
> > > >
> > > > Try DBCC CHECKDB to start
> > > >
> > > > --
> > > > HTH
> > > > Ryan Waight, MCDBA, MCSE
> > > >
> > > > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > > > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > > > Folks, do you know some way or tool to check the database
integrity
> > and
> > > > > repair error like corrupt entries to a d/b in sql 2k?
> > > > >
> > > > > Thanks
> > > > >
> > > > > Daniel
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Historically, DBCC was the acronym for DataBase Consistency Checker, and had
the same options as in Sybase.
The basic options have to do with checking the consistency of a table or the
whole database, and verifying the allocation structures. In later releases,
the DBCC command began to be overloaded, and everytime the dev engineers at
Microsoft wanted to add a new non-SQL command, they just tacked it on to
DBCC, so if you check the Books Online you'll see lots of options that have
nothing to do with consistency checking.
For this reason the BOL nows says the acronym means DataBase Console
Command, but that is a case of putting the cart before the horse. We already
had the command, and they just had to find something new for it to mean.
And actually, there is still a reference in BOL for DBCC having the old
meaning. If you look in the System Monitor section, under the description of
SQL Server: Database Object, it will tell you that the "DBCC Logical Scan
Bytes/sec" counter measures
"Number of logical read scan bytes per second for database consistency
checker (DBCC) statements."
So Daniel, DBCC is what you want, especially the CHECKDB and CHECKALLOC
options, but there might be a few others that are useful. Please read the
docs.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:##QpfeSfDHA.3896@.tk2msftngp13.phx.gbl...
> Ryan,
> DBCC is an abbreviation for Database Console Command.
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OBkItUSfDHA.1888@.TK2MSFTNGP12.phx.gbl...
> > DBCC = Database consistency checker. Have a look in SQL BOL
> >
> > Try DBCC CHECKDB to start
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "Daniel_A" <dabella@.empower.edu.uy> wrote in message
> > news:%23lG3hSSfDHA.2348@.TK2MSFTNGP12.phx.gbl...
> > > Folks, do you know some way or tool to check the database integrity
and
> > > repair error like corrupt entries to a d/b in sql 2k?
> > >
> > > Thanks
> > >
> > > Daniel
> > >
> > >
> > >
> >
> >
>

Charindex

I have atable where i store visitor data (from a website).
In table i have a column LanguageData
Typical entries are:
no
en-us
no,en-us
en; en-us; no
...
So i am trying to do a select where i remove every thing after the ,.
Like this:
LEFT(LanguageData, CHARINDEX(',',
REPLACE(RTRIM(LTRIM(LanguageData)), ';', ',')))
That result in
no,
en,
So i do not get:
no
en-us
no
en
Is there a way i can do a select so that i only get languagecode or the
first languagecode where the user has many (like no, en-us)
I am trying to do this so that i can join it to a table later and show the
full language name.
Best regards
TrondHi
Hi
May be one of
SELECT CASE WHEN Length > 1 THEN LEFT([language],Length-1)
ELSE [language]
END AS [language]
FROM ( SELECT CHARINDEX(',',REPLACE([language],';',','
)) AS LENGTH,
[language]
FROM (
SELECT 'no' as [language]
UNION ALL SELECT 'en-us'
UNION ALL SELECT 'no,en-us'
UNION ALL SELECT 'en; en-us; no' ) A ) B
or
SELECT LEFT([language],ISNULL(Length-1,LEN([Language]))) AS [language]
FROM (
SELECT NULLIF(CHARINDEX(',',REPLACE([language],
';',',')),0) AS LENGTH,
[language]
FROM (
SELECT 'no' as [language]
UNION ALL SELECT 'en-us'
UNION ALL SELECT 'no,en-us'
UNION ALL SELECT 'en; en-us; no' ) A ) B
You may also want to check out:
http://www.users.drew.edu/skass/sql...unction.sql.txt
John
"Trond" <thoiberg@.broadpark.no> wrote in message
news:42629435$1@.news.broadpark.no...
>I have atable where i store visitor data (from a website).
> In table i have a column LanguageData
> Typical entries are:
> no
> en-us
> no,en-us
> en; en-us; no
> ...
> So i am trying to do a select where i remove every thing after the ,.
> Like this:
> LEFT(LanguageData, CHARINDEX(',',
> REPLACE(RTRIM(LTRIM(LanguageData)), ';', ',')))
> That result in
> no,
> en,
> So i do not get:
> no
> en-us
> no
> en
> Is there a way i can do a select so that i only get languagecode or the
> first languagecode where the user has many (like no, en-us)
> I am trying to do this so that i can join it to a table later and show the
> full language name.
> Best regards
> Trond
>

Characters not allowed in SQL varchar?

I have been using MS SQL server (8.0.194) and I have been wondering whatacters should I strip from entries before putting them into a varchar() field?

I check for single quote (') and handle that, and malicious attempts. But is it ok to have the newline characters in there(\r\n)? The always show up as the ASCII-square box, so I was wondering if I need to be stripping them out as well?
What other "normally used" text characters do I also need to watch out for, if any?

Thanks.I wouldn't think it would matter what you "put into a varchar" as long as when you "pull" the text back out you DISPLAY it in the same manner from where you saved it. That is, if you used a simple text box for a line of entry then it likely won't matter. But if you use a Rich Text Box for input, then you should use a Rich Text Box for output once the data is retrieved from the database, Newline characters and all. Even a single-quote won't matter as long as your ADO objects are written to allow single-quotes w/out needing to use escape characters or methods (such as double-single-quotes, or \', or whatever).

Otherwise, don't use a Rich Text Box or input or use simpler ASCII codes, if you are building a string, such as {Carriage Return} {Line Feed} rather than {Newline}.

Hope that helps.