script to find out the databses which is in SUSPECT MODE

Dear All,
I have 120 dedicated database servers. Many of the times , some databses
were marked as suspect because of some unexpected reboot, hardware errors
etc. So i just want to know Is there any script to fetch all the databases
that is marked as suspect? So that i can run the script from a central server
and make sure that all are working fine.
Thanks in advance,
JJYou can use the DATABASEPROPERTYEX() function, the status attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> Dear All,
> I have 120 dedicated database servers. Many of the times , some databses
> were marked as suspect because of some unexpected reboot, hardware errors
> etc. So i just want to know Is there any script to fetch all the databases
> that is marked as suspect? So that i can run the script from a central server
> and make sure that all are working fine.
> Thanks in advance,
> JJ|||Hi Tibor,
Thanks a lot for your quick reply. This will do the needful.
How can i collect these info to a central server from all database servers?
Thanks,
JJ
"Tibor Karaszi" wrote:
> You can use the DATABASEPROPERTYEX() function, the status attribute.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> > Dear All,
> >
> > I have 120 dedicated database servers. Many of the times , some databses
> > were marked as suspect because of some unexpected reboot, hardware errors
> > etc. So i just want to know Is there any script to fetch all the databases
> > that is marked as suspect? So that i can run the script from a central server
> > and make sure that all are working fine.
> >
> > Thanks in advance,
> > JJ
>|||I can see a few possible options:
On the central server set up linked servers to all the other servers, so you can do something like:
SELECT ... FROM srvname.master.sys.databases...
Use the SMO API for this, and there connect to each server and then store the result in the central
server.
Use a more classic API, like ADO.NET to do the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:21BC8917-2138-4EC5-991E-D253C75FD338@.microsoft.com...
> Hi Tibor,
> Thanks a lot for your quick reply. This will do the needful.
> How can i collect these info to a central server from all database servers?
> Thanks,
> JJ
> "Tibor Karaszi" wrote:
>> You can use the DATABASEPROPERTYEX() function, the status attribute.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
>> news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
>> > Dear All,
>> >
>> > I have 120 dedicated database servers. Many of the times , some databses
>> > were marked as suspect because of some unexpected reboot, hardware errors
>> > etc. So i just want to know Is there any script to fetch all the databases
>> > that is marked as suspect? So that i can run the script from a central server
>> > and make sure that all are working fine.
>> >
>> > Thanks in advance,
>> > JJ
>>|||On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
wrote:
> Hi Tibor,
> Thanks a lot for your quick reply. This will do the needful.
> How can i collect these info to a central server from all database servers?
> Thanks,
> JJ
>
> "Tibor Karaszi" wrote:
> > You can use the DATABASEPROPERTYEX() function, the status attribute.
> > --
> > Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://sqlblog.com/blogs/tibor_karaszi
> > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> > > Dear All,
> > > I have 120 dedicated database servers. Many of the times , some databses
> > > were marked as suspect because of some unexpected reboot, hardware errors
> > > etc. So i just want to know Is there any script to fetch all the databases
> > > that is marked as suspect? So that i can run the script from a central server
> > > and make sure that all are working fine.
> > > Thanks in advance,
> > > JJ- Hide quoted text -
> - Show quoted text -
Try , Not tested
SELECT Servername.master..sysdatabases
WHERE status & 256 != 256|||Hi Tibor,
i just tried to get the result using osql.
i have used the following query:-
osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
jj74.txt
the txt file is created successfully but when i checked the file and found
that it has nothing other than the caption "name".
Any idea'
Thanks,
JJ
"M A Srinivas" wrote:
> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> wrote:
> > Hi Tibor,
> >
> > Thanks a lot for your quick reply. This will do the needful.
> > How can i collect these info to a central server from all database servers?
> >
> > Thanks,
> > JJ
> >
> >
> >
> > "Tibor Karaszi" wrote:
> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
> >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > >http://www.karaszi.com/sqlserver/default.asp
> > >http://sqlblog.com/blogs/tibor_karaszi
> >
> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> > > > Dear All,
> >
> > > > I have 120 dedicated database servers. Many of the times , some databses
> > > > were marked as suspect because of some unexpected reboot, hardware errors
> > > > etc. So i just want to know Is there any script to fetch all the databases
> > > > that is marked as suspect? So that i can run the script from a central server
> > > > and make sure that all are working fine.
> >
> > > > Thanks in advance,
> > > > JJ- Hide quoted text -
> >
> > - Show quoted text -
> Try , Not tested
> SELECT Servername.master..sysdatabases
> WHERE status & 256 != 256
>|||Scroll down...
I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
suitable...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
> Hi Tibor,
> i just tried to get the result using osql.
> i have used the following query:-
> osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> jj74.txt
> the txt file is created successfully but when i checked the file and found
> that it has nothing other than the caption "name".
> Any idea'
> Thanks,
> JJ
>
> "M A Srinivas" wrote:
>> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
>> wrote:
>> > Hi Tibor,
>> >
>> > Thanks a lot for your quick reply. This will do the needful.
>> > How can i collect these info to a central server from all database servers?
>> >
>> > Thanks,
>> > JJ
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
>> >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > >http://www.karaszi.com/sqlserver/default.asp
>> > >http://sqlblog.com/blogs/tibor_karaszi
>> >
>> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
>> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
>> > > > Dear All,
>> >
>> > > > I have 120 dedicated database servers. Many of the times , some databses
>> > > > were marked as suspect because of some unexpected reboot, hardware errors
>> > > > etc. So i just want to know Is there any script to fetch all the databases
>> > > > that is marked as suspect? So that i can run the script from a central server
>> > > > and make sure that all are working fine.
>> >
>> > > > Thanks in advance,
>> > > > JJ- Hide quoted text -
>> >
>> > - Show quoted text -
>> Try , Not tested
>> SELECT Servername.master..sysdatabases
>> WHERE status & 256 != 256
>>|||Great!! That works......
this can be executed succesfully against the local server, but if i want to
execute this a gainst remote sql server what extra things has to be added?
Thanks,
JJ
"Tibor Karaszi" wrote:
> Scroll down...
> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
> suitable...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
> > Hi Tibor,
> >
> > i just tried to get the result using osql.
> >
> > i have used the following query:-
> >
> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> > jj74.txt
> >
> > the txt file is created successfully but when i checked the file and found
> > that it has nothing other than the caption "name".
> >
> > Any idea'
> > Thanks,
> > JJ
> >
> >
> > "M A Srinivas" wrote:
> >
> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> >> wrote:
> >> > Hi Tibor,
> >> >
> >> > Thanks a lot for your quick reply. This will do the needful.
> >> > How can i collect these info to a central server from all database servers?
> >> >
> >> > Thanks,
> >> > JJ
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
> >> >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > >http://www.karaszi.com/sqlserver/default.asp
> >> > >http://sqlblog.com/blogs/tibor_karaszi
> >> >
> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> >> > > > Dear All,
> >> >
> >> > > > I have 120 dedicated database servers. Many of the times , some databses
> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
> >> > > > etc. So i just want to know Is there any script to fetch all the databases
> >> > > > that is marked as suspect? So that i can run the script from a central server
> >> > > > and make sure that all are working fine.
> >> >
> >> > > > Thanks in advance,
> >> > > > JJ- Hide quoted text -
> >> >
> >> > - Show quoted text -
> >> Try , Not tested
> >>
> >> SELECT Servername.master..sysdatabases
> >> WHERE status & 256 != 256
> >>
> >>
>|||You need to specify appropriate values for the /S parameter and appropriate login...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
> Great!! That works......
> this can be executed succesfully against the local server, but if i want to
> execute this a gainst remote sql server what extra things has to be added?
> Thanks,
> JJ
> "Tibor Karaszi" wrote:
>> Scroll down...
>> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
>> suitable...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
>> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
>> > Hi Tibor,
>> >
>> > i just tried to get the result using osql.
>> >
>> > i have used the following query:-
>> >
>> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
>> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
>> > jj74.txt
>> >
>> > the txt file is created successfully but when i checked the file and found
>> > that it has nothing other than the caption "name".
>> >
>> > Any idea'
>> > Thanks,
>> > JJ
>> >
>> >
>> > "M A Srinivas" wrote:
>> >
>> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
>> >> wrote:
>> >> > Hi Tibor,
>> >> >
>> >> > Thanks a lot for your quick reply. This will do the needful.
>> >> > How can i collect these info to a central server from all database servers?
>> >> >
>> >> > Thanks,
>> >> > JJ
>> >> >
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
>> >> >
>> >> > > --
>> >> > > Tibor Karaszi, SQL Server MVP
>> >> > >http://www.karaszi.com/sqlserver/default.asp
>> >> > >http://sqlblog.com/blogs/tibor_karaszi
>> >> >
>> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
>> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
>> >> > > > Dear All,
>> >> >
>> >> > > > I have 120 dedicated database servers. Many of the times , some databses
>> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
>> >> > > > etc. So i just want to know Is there any script to fetch all the databases
>> >> > > > that is marked as suspect? So that i can run the script from a central server
>> >> > > > and make sure that all are working fine.
>> >> >
>> >> > > > Thanks in advance,
>> >> > > > JJ- Hide quoted text -
>> >> >
>> >> > - Show quoted text -
>> >> Try , Not tested
>> >>
>> >> SELECT Servername.master..sysdatabases
>> >> WHERE status & 256 != 256
>> >>
>> >>
>>|||Ohhh !!!! Sorry....
:)
I got it...
Thanks a ton Tiber....
"Tibor Karaszi" wrote:
> You need to specify appropriate values for the /S parameter and appropriate login...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
> > Great!! That works......
> >
> > this can be executed succesfully against the local server, but if i want to
> > execute this a gainst remote sql server what extra things has to be added?
> >
> > Thanks,
> > JJ
> > "Tibor Karaszi" wrote:
> >
> >> Scroll down...
> >>
> >> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
> >> suitable...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> >> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
> >> > Hi Tibor,
> >> >
> >> > i just tried to get the result using osql.
> >> >
> >> > i have used the following query:-
> >> >
> >> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> >> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> >> > jj74.txt
> >> >
> >> > the txt file is created successfully but when i checked the file and found
> >> > that it has nothing other than the caption "name".
> >> >
> >> > Any idea'
> >> > Thanks,
> >> > JJ
> >> >
> >> >
> >> > "M A Srinivas" wrote:
> >> >
> >> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> >> >> wrote:
> >> >> > Hi Tibor,
> >> >> >
> >> >> > Thanks a lot for your quick reply. This will do the needful.
> >> >> > How can i collect these info to a central server from all database servers?
> >> >> >
> >> >> > Thanks,
> >> >> > JJ
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
> >> >> >
> >> >> > > --
> >> >> > > Tibor Karaszi, SQL Server MVP
> >> >> > >http://www.karaszi.com/sqlserver/default.asp
> >> >> > >http://sqlblog.com/blogs/tibor_karaszi
> >> >> >
> >> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> >> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> >> >> > > > Dear All,
> >> >> >
> >> >> > > > I have 120 dedicated database servers. Many of the times , some databses
> >> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
> >> >> > > > etc. So i just want to know Is there any script to fetch all the databases
> >> >> > > > that is marked as suspect? So that i can run the script from a central server
> >> >> > > > and make sure that all are working fine.
> >> >> >
> >> >> > > > Thanks in advance,
> >> >> > > > JJ- Hide quoted text -
> >> >> >
> >> >> > - Show quoted text -
> >> >> Try , Not tested
> >> >>
> >> >> SELECT Servername.master..sysdatabases
> >> >> WHERE status & 256 != 256
> >> >>
> >> >>
> >>
> >>
>|||Hi Tibor,
One more help....
osql /d master /S inhpsmshp0 /Q "SELECT name,cast(DATABASEPROPERTYEX(name,
'Status') as varchar(50))FROM master.dbo.sysdatabases" -E -w 100 -o jaison.txt
I want to capture the same result for 170 servers :)
is it possibple to pass all 170 server name with /S parameter...
like
osql /d master /S inhpsmshp0;usarsmss3s;usrtsmser0 /Q "SELECT
name,cast(DATABASEPROPERTYEX(name, 'Status') as varchar(50))FROM
master.dbo.sysdatabases" -E -w 100 -o jaison.txt
(this won't work ...)
or anyway to append the outputfile (jaison.txt)?
"Jaison Jose" wrote:
> Ohhh !!!! Sorry....
> :)
> I got it...
> Thanks a ton Tiber....
> "Tibor Karaszi" wrote:
> > You need to specify appropriate values for the /S parameter and appropriate login...
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> > news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
> > > Great!! That works......
> > >
> > > this can be executed succesfully against the local server, but if i want to
> > > execute this a gainst remote sql server what extra things has to be added?
> > >
> > > Thanks,
> > > JJ
> > > "Tibor Karaszi" wrote:
> > >
> > >> Scroll down...
> > >>
> > >> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
> > >> suitable...
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://sqlblog.com/blogs/tibor_karaszi
> > >>
> > >>
> > >> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> > >> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
> > >> > Hi Tibor,
> > >> >
> > >> > i just tried to get the result using osql.
> > >> >
> > >> > i have used the following query:-
> > >> >
> > >> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> > >> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> > >> > jj74.txt
> > >> >
> > >> > the txt file is created successfully but when i checked the file and found
> > >> > that it has nothing other than the caption "name".
> > >> >
> > >> > Any idea'
> > >> > Thanks,
> > >> > JJ
> > >> >
> > >> >
> > >> > "M A Srinivas" wrote:
> > >> >
> > >> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> > >> >> wrote:
> > >> >> > Hi Tibor,
> > >> >> >
> > >> >> > Thanks a lot for your quick reply. This will do the needful.
> > >> >> > How can i collect these info to a central server from all database servers?
> > >> >> >
> > >> >> > Thanks,
> > >> >> > JJ
> > >> >> >
> > >> >> >
> > >> >> >
> > >> >> > "Tibor Karaszi" wrote:
> > >> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
> > >> >> >
> > >> >> > > --
> > >> >> > > Tibor Karaszi, SQL Server MVP
> > >> >> > >http://www.karaszi.com/sqlserver/default.asp
> > >> >> > >http://sqlblog.com/blogs/tibor_karaszi
> > >> >> >
> > >> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> > >> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> > >> >> > > > Dear All,
> > >> >> >
> > >> >> > > > I have 120 dedicated database servers. Many of the times , some databses
> > >> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
> > >> >> > > > etc. So i just want to know Is there any script to fetch all the databases
> > >> >> > > > that is marked as suspect? So that i can run the script from a central server
> > >> >> > > > and make sure that all are working fine.
> > >> >> >
> > >> >> > > > Thanks in advance,
> > >> >> > > > JJ- Hide quoted text -
> > >> >> >
> > >> >> > - Show quoted text -
> > >> >> Try , Not tested
> > >> >>
> > >> >> SELECT Servername.master..sysdatabases
> > >> >> WHERE status & 256 != 256
> > >> >>
> > >> >>
> > >>
> > >>
> >
> >|||Perhaps, this little tool can help out in your multi-server situation.
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/09/msql-a-little-wrapper-goes-a-long-way.aspx
Linchi
"Jaison Jose" wrote:
> Hi Tibor,
> One more help....
>
> osql /d master /S inhpsmshp0 /Q "SELECT name,cast(DATABASEPROPERTYEX(name,
> 'Status') as varchar(50))FROM master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> I want to capture the same result for 170 servers :)
> is it possibple to pass all 170 server name with /S parameter...
> like
> osql /d master /S inhpsmshp0;usarsmss3s;usrtsmser0 /Q "SELECT
> name,cast(DATABASEPROPERTYEX(name, 'Status') as varchar(50))FROM
> master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> (this won't work ...)
> or anyway to append the outputfile (jaison.txt)?
> "Jaison Jose" wrote:
> > Ohhh !!!! Sorry....
> > :)
> > I got it...
> > Thanks a ton Tiber....
> >
> > "Tibor Karaszi" wrote:
> >
> > > You need to specify appropriate values for the /S parameter and appropriate login...
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://sqlblog.com/blogs/tibor_karaszi
> > >
> > >
> > > "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> > > news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
> > > > Great!! That works......
> > > >
> > > > this can be executed succesfully against the local server, but if i want to
> > > > execute this a gainst remote sql server what extra things has to be added?
> > > >
> > > > Thanks,
> > > > JJ
> > > > "Tibor Karaszi" wrote:
> > > >
> > > >> Scroll down...
> > > >>
> > > >> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
> > > >> suitable...
> > > >>
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> http://www.karaszi.com/sqlserver/default.asp
> > > >> http://sqlblog.com/blogs/tibor_karaszi
> > > >>
> > > >>
> > > >> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> > > >> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
> > > >> > Hi Tibor,
> > > >> >
> > > >> > i just tried to get the result using osql.
> > > >> >
> > > >> > i have used the following query:-
> > > >> >
> > > >> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> > > >> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> > > >> > jj74.txt
> > > >> >
> > > >> > the txt file is created successfully but when i checked the file and found
> > > >> > that it has nothing other than the caption "name".
> > > >> >
> > > >> > Any idea'
> > > >> > Thanks,
> > > >> > JJ
> > > >> >
> > > >> >
> > > >> > "M A Srinivas" wrote:
> > > >> >
> > > >> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> > > >> >> wrote:
> > > >> >> > Hi Tibor,
> > > >> >> >
> > > >> >> > Thanks a lot for your quick reply. This will do the needful.
> > > >> >> > How can i collect these info to a central server from all database servers?
> > > >> >> >
> > > >> >> > Thanks,
> > > >> >> > JJ
> > > >> >> >
> > > >> >> >
> > > >> >> >
> > > >> >> > "Tibor Karaszi" wrote:
> > > >> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
> > > >> >> >
> > > >> >> > > --
> > > >> >> > > Tibor Karaszi, SQL Server MVP
> > > >> >> > >http://www.karaszi.com/sqlserver/default.asp
> > > >> >> > >http://sqlblog.com/blogs/tibor_karaszi
> > > >> >> >
> > > >> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> > > >> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> > > >> >> > > > Dear All,
> > > >> >> >
> > > >> >> > > > I have 120 dedicated database servers. Many of the times , some databses
> > > >> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
> > > >> >> > > > etc. So i just want to know Is there any script to fetch all the databases
> > > >> >> > > > that is marked as suspect? So that i can run the script from a central server
> > > >> >> > > > and make sure that all are working fine.
> > > >> >> >
> > > >> >> > > > Thanks in advance,
> > > >> >> > > > JJ- Hide quoted text -
> > > >> >> >
> > > >> >> > - Show quoted text -
> > > >> >> Try , Not tested
> > > >> >>
> > > >> >> SELECT Servername.master..sysdatabases
> > > >> >> WHERE status & 256 != 256
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > >
> > >|||Thanks Linchi.
I don't have more idea in C#, but still let me check with someone.
So there is no direct option in osql which will give me the result right?!!!
"Linchi Shea" wrote:
> Perhaps, this little tool can help out in your multi-server situation.
> http://sqlblog.com/blogs/linchi_shea/archive/2007/01/09/msql-a-little-wrapper-goes-a-long-way.aspx
> Linchi
> "Jaison Jose" wrote:
> > Hi Tibor,
> >
> > One more help....
> >
> >
> > osql /d master /S inhpsmshp0 /Q "SELECT name,cast(DATABASEPROPERTYEX(name,
> > 'Status') as varchar(50))FROM master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> >
> > I want to capture the same result for 170 servers :)
> > is it possibple to pass all 170 server name with /S parameter...
> >
> > like
> > osql /d master /S inhpsmshp0;usarsmss3s;usrtsmser0 /Q "SELECT
> > name,cast(DATABASEPROPERTYEX(name, 'Status') as varchar(50))FROM
> > master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> >
> > (this won't work ...)
> >
> > or anyway to append the outputfile (jaison.txt)?
> >
> > "Jaison Jose" wrote:
> >
> > > Ohhh !!!! Sorry....
> > > :)
> > > I got it...
> > > Thanks a ton Tiber....
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > > You need to specify appropriate values for the /S parameter and appropriate login...
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://sqlblog.com/blogs/tibor_karaszi
> > > >
> > > >
> > > > "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> > > > news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
> > > > > Great!! That works......
> > > > >
> > > > > this can be executed succesfully against the local server, but if i want to
> > > > > execute this a gainst remote sql server what extra things has to be added?
> > > > >
> > > > > Thanks,
> > > > > JJ
> > > > > "Tibor Karaszi" wrote:
> > > > >
> > > > >> Scroll down...
> > > > >>
> > > > >> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
> > > > >> suitable...
> > > > >>
> > > > >> --
> > > > >> Tibor Karaszi, SQL Server MVP
> > > > >> http://www.karaszi.com/sqlserver/default.asp
> > > > >> http://sqlblog.com/blogs/tibor_karaszi
> > > > >>
> > > > >>
> > > > >> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
> > > > >> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
> > > > >> > Hi Tibor,
> > > > >> >
> > > > >> > i just tried to get the result using osql.
> > > > >> >
> > > > >> > i have used the following query:-
> > > > >> >
> > > > >> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> > > > >> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> > > > >> > jj74.txt
> > > > >> >
> > > > >> > the txt file is created successfully but when i checked the file and found
> > > > >> > that it has nothing other than the caption "name".
> > > > >> >
> > > > >> > Any idea'
> > > > >> > Thanks,
> > > > >> > JJ
> > > > >> >
> > > > >> >
> > > > >> > "M A Srinivas" wrote:
> > > > >> >
> > > > >> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> > > > >> >> wrote:
> > > > >> >> > Hi Tibor,
> > > > >> >> >
> > > > >> >> > Thanks a lot for your quick reply. This will do the needful.
> > > > >> >> > How can i collect these info to a central server from all database servers?
> > > > >> >> >
> > > > >> >> > Thanks,
> > > > >> >> > JJ
> > > > >> >> >
> > > > >> >> >
> > > > >> >> >
> > > > >> >> > "Tibor Karaszi" wrote:
> > > > >> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
> > > > >> >> >
> > > > >> >> > > --
> > > > >> >> > > Tibor Karaszi, SQL Server MVP
> > > > >> >> > >http://www.karaszi.com/sqlserver/default.asp
> > > > >> >> > >http://sqlblog.com/blogs/tibor_karaszi
> > > > >> >> >
> > > > >> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> > > > >> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> > > > >> >> > > > Dear All,
> > > > >> >> >
> > > > >> >> > > > I have 120 dedicated database servers. Many of the times , some databses
> > > > >> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
> > > > >> >> > > > etc. So i just want to know Is there any script to fetch all the databases
> > > > >> >> > > > that is marked as suspect? So that i can run the script from a central server
> > > > >> >> > > > and make sure that all are working fine.
> > > > >> >> >
> > > > >> >> > > > Thanks in advance,
> > > > >> >> > > > JJ- Hide quoted text -
> > > > >> >> >
> > > > >> >> > - Show quoted text -
> > > > >> >> Try , Not tested
> > > > >> >>
> > > > >> >> SELECT Servername.master..sysdatabases
> > > > >> >> WHERE status & 256 != 256
> > > > >> >>
> > > > >> >>
> > > > >>
> > > > >>
> > > >
> > > >|||> is it possibple to pass all 170 server name with /S parameter...
No. But the 2005 replacement for OSQL (called SQLCMD) has some new nice features that might be
useful. You can even download SQLCMD separately and use it against older versions.
> or anyway to append the outputfile (jaison.txt)?
I think that you can append by not using -o, but instead specifying >> (windows CMD functionality).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
news:E6277300-ACC9-4D17-8FAB-8C2949921870@.microsoft.com...
> Hi Tibor,
> One more help....
>
> osql /d master /S inhpsmshp0 /Q "SELECT name,cast(DATABASEPROPERTYEX(name,
> 'Status') as varchar(50))FROM master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> I want to capture the same result for 170 servers :)
> is it possibple to pass all 170 server name with /S parameter...
> like
> osql /d master /S inhpsmshp0;usarsmss3s;usrtsmser0 /Q "SELECT
> name,cast(DATABASEPROPERTYEX(name, 'Status') as varchar(50))FROM
> master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> (this won't work ...)
> or anyway to append the outputfile (jaison.txt)?
> "Jaison Jose" wrote:
>> Ohhh !!!! Sorry....
>> :)
>> I got it...
>> Thanks a ton Tiber....
>> "Tibor Karaszi" wrote:
>> > You need to specify appropriate values for the /S parameter and appropriate login...
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
>> > news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
>> > > Great!! That works......
>> > >
>> > > this can be executed succesfully against the local server, but if i want to
>> > > execute this a gainst remote sql server what extra things has to be added?
>> > >
>> > > Thanks,
>> > > JJ
>> > > "Tibor Karaszi" wrote:
>> > >
>> > >> Scroll down...
>> > >>
>> > >> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or
>> > >> something
>> > >> suitable...
>> > >>
>> > >> --
>> > >> Tibor Karaszi, SQL Server MVP
>> > >> http://www.karaszi.com/sqlserver/default.asp
>> > >> http://sqlblog.com/blogs/tibor_karaszi
>> > >>
>> > >>
>> > >> "Jaison Jose" <JaisonJose@.discussions.microsoft.com> wrote in message
>> > >> news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
>> > >> > Hi Tibor,
>> > >> >
>> > >> > i just tried to get the result using osql.
>> > >> >
>> > >> > i have used the following query:-
>> > >> >
>> > >> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
>> > >> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
>> > >> > jj74.txt
>> > >> >
>> > >> > the txt file is created successfully but when i checked the file and found
>> > >> > that it has nothing other than the caption "name".
>> > >> >
>> > >> > Any idea'
>> > >> > Thanks,
>> > >> > JJ
>> > >> >
>> > >> >
>> > >> > "M A Srinivas" wrote:
>> > >> >
>> > >> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
>> > >> >> wrote:
>> > >> >> > Hi Tibor,
>> > >> >> >
>> > >> >> > Thanks a lot for your quick reply. This will do the needful.
>> > >> >> > How can i collect these info to a central server from all database servers?
>> > >> >> >
>> > >> >> > Thanks,
>> > >> >> > JJ
>> > >> >> >
>> > >> >> >
>> > >> >> >
>> > >> >> > "Tibor Karaszi" wrote:
>> > >> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
>> > >> >> >
>> > >> >> > > --
>> > >> >> > > Tibor Karaszi, SQL Server MVP
>> > >> >> > >http://www.karaszi.com/sqlserver/default.asp
>> > >> >> > >http://sqlblog.com/blogs/tibor_karaszi
>> > >> >> >
>> > >> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
>> > >> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
>> > >> >> > > > Dear All,
>> > >> >> >
>> > >> >> > > > I have 120 dedicated database servers. Many of the times , some databses
>> > >> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
>> > >> >> > > > etc. So i just want to know Is there any script to fetch all the databases
>> > >> >> > > > that is marked as suspect? So that i can run the script from a central server
>> > >> >> > > > and make sure that all are working fine.
>> > >> >> >
>> > >> >> > > > Thanks in advance,
>> > >> >> > > > JJ- Hide quoted text -
>> > >> >> >
>> > >> >> > - Show quoted text -
>> > >> >> Try , Not tested
>> > >> >>
>> > >> >> SELECT Servername.master..sysdatabases
>> > >> >> WHERE status & 256 != 256
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> >
>> >|||Tibor Karaszi wrote:
>> is it possibple to pass all 170 server name with /S parameter...
> No. But the 2005 replacement for OSQL (called SQLCMD) has some new nice features that might be
> useful. You can even download SQLCMD separately and use it against older versions.
>
>> or anyway to append the outputfile (jaison.txt)?
> I think that you can append by not using -o, but instead specifying >> (windows CMD functionality).
>
If you are looking at creating a script to perform this, I would suggest
looking into Powershell
http://www.microsoft.com/windowsserver2003/technologies/management/powershell/default.mspx
Jeff|||On May 26, 12:05 am, Jaison Jose
<JaisonJ...@.discussions.microsoft.com> wrote:
> Thanks Linchi.
> I don't have more idea in C#, but still let me check with someone.
> So there is no direct option in osql which will give me the result right?!!!
>
> "Linchi Shea" wrote:
> > Perhaps, this little tool can help out in your multi-server situation.
> >http://sqlblog.com/blogs/linchi_shea/archive/2007/01/09/msql-a-little...
> > Linchi
> > "Jaison Jose" wrote:
> > > Hi Tibor,
> > > One more help....
> > > osql /d master /S inhpsmshp0 /Q "SELECT name,cast(DATABASEPROPERTYEX(name,
> > > 'Status') as varchar(50))FROM master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> > > I want to capture the same result for 170 servers :)
> > > is it possibple to pass all 170 server name with /S parameter...
> > > like
> > > osql /d master /S inhpsmshp0;usarsmss3s;usrtsmser0 /Q "SELECT
> > > name,cast(DATABASEPROPERTYEX(name, 'Status') as varchar(50))FROM
> > > master.dbo.sysdatabases" -E -w 100 -o jaison.txt
> > > (this won't work ...)
> > > or anyway to append the outputfile (jaison.txt)?
> > > "Jaison Jose" wrote:
> > > > Ohhh !!!! Sorry....
> > > > :)
> > > > I got it...
> > > > Thanks a ton Tiber....
> > > > "Tibor Karaszi" wrote:
> > > > > You need to specify appropriate values for the /S parameter and appropriate login...
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > >http://www.karaszi.com/sqlserver/default.asp
> > > > >http://sqlblog.com/blogs/tibor_karaszi
> > > > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> > > > >news:B4A69E29-CD0C-444F-9F45-7CEF80FCA937@.microsoft.com...
> > > > > > Great!! That works......
> > > > > > this can be executed succesfully against the local server, but if i want to
> > > > > > execute this a gainst remote sql server what extra things has to be added?
> > > > > > Thanks,
> > > > > > JJ
> > > > > > "Tibor Karaszi" wrote:
> > > > > >> Scroll down...
> > > > > >> I suggest you CAST the result from DATABASEPROPERTYEX to something like varchar(50) or something
> > > > > >> suitable...
> > > > > >> --
> > > > > >> Tibor Karaszi, SQL Server MVP
> > > > > >>http://www.karaszi.com/sqlserver/default.asp
> > > > > >>http://sqlblog.com/blogs/tibor_karaszi
> > > > > >> "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> > > > > >>news:40D01B38-42A5-40E0-B8C3-041B29D7ED37@.microsoft.com...
> > > > > >> > Hi Tibor,
> > > > > >> > i just tried to get the result using osql.
> > > > > >> > i have used the following query:-
> > > > > >> > osql /U sa /P MjEfKa4ChVjX /d master /S rumosmsru0 /Q "SELECT
> > > > > >> > name,DATABASEPROPERTYEX(name, 'Status')FROM master.dbo.sysdatabases" -o
> > > > > >> > jj74.txt
> > > > > >> > the txt file is created successfully but when i checked the file and found
> > > > > >> > that it has nothing other than the caption "name".
> > > > > >> > Any idea'
> > > > > >> > Thanks,
> > > > > >> > JJ
> > > > > >> > "M A Srinivas" wrote:
> > > > > >> >> On May 25, 1:39 pm, Jaison Jose <JaisonJ...@.discussions.microsoft.com>
> > > > > >> >> wrote:
> > > > > >> >> > Hi Tibor,
> > > > > >> >> > Thanks a lot for your quick reply. This will do the needful.
> > > > > >> >> > How can i collect these info to a central server from all database servers?
> > > > > >> >> > Thanks,
> > > > > >> >> > JJ
> > > > > >> >> > "Tibor Karaszi" wrote:
> > > > > >> >> > > You can use the DATABASEPROPERTYEX() function, the status attribute.
> > > > > >> >> > > --
> > > > > >> >> > > Tibor Karaszi, SQL Server MVP
> > > > > >> >> > >http://www.karaszi.com/sqlserver/default.asp
> > > > > >> >> > >http://sqlblog.com/blogs/tibor_karaszi
> > > > > >> >> > > "Jaison Jose" <JaisonJ...@.discussions.microsoft.com> wrote in message
> > > > > >> >> > >news:C3775259-328D-4CE2-9EA8-09F3872427F9@.microsoft.com...
> > > > > >> >> > > > Dear All,
> > > > > >> >> > > > I have 120 dedicated database servers. Many of the times , some databses
> > > > > >> >> > > > were marked as suspect because of some unexpected reboot, hardware errors
> > > > > >> >> > > > etc. So i just want to know Is there any script to fetch all the databases
> > > > > >> >> > > > that is marked as suspect? So that i can run the script from a central server
> > > > > >> >> > > > and make sure that all are working fine.
> > > > > >> >> > > > Thanks in advance,
> > > > > >> >> > > > JJ- Hide quoted text -
> > > > > >> >> > - Show quoted text -
> > > > > >> >> Try , Not tested
> > > > > >> >> SELECT Servername.master..sysdatabases
> > > > > >> >> WHERE status & 256 != 256- Hide quoted text -
> - Show quoted text -
Also check out PowerSMO (http://www.simple-talk.com/sql/database-
administration/managing-sql-server-using-powersmo/) which shows how to
use the SMO objects to manage SQL Server.
HTH,
Jeff