Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

xp_cmdshell Expand / Collapse
Author
Message
Posted Monday, July 5, 2010 9:45 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 23, 2014 2:26 PM
Points: 752, Visits: 1,071
Hi,

I have a stored procedure that uses xp_cmdshell to execute a command and send the result to a text file.


This is the code:


set @FileNameOut ='OutputConsistencia_'+@db
select @cmd = 'osql -U'+@user+' -P'+@pass+' -S -Q"dbcc checkdb ('''+@db+''') with no_infomsgs" -o"c:\'+@FileNameOut+'.txt" -w500'
Exec master..xp_cmdshell @cmd



I need to read the result of the dbcc command to a variable too (not only save the result to a txt file). How can i do this?


Thank you.


Post #947569
Posted Monday, July 5, 2010 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 12,927, Visits: 32,331
i have this saved in my snippets that might help...instead of a linked server you could loop back tot eh current server as well:
SELECT * FROM OPENQUERY( [linked server],'SET FMTONLY OFF; EXEC (''USE DATABASE; DBCC showfilestats WITH NO_INFOMSGS '')')



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #947573
Posted Tuesday, July 6, 2010 5:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 7:19 AM
Points: 98, Visits: 226
set @FileNameOut ='OutputConsistencia_'+@db
select @cmd = 'osql -U'+@user+' -P'+@pass+' -S -Q"dbcc checkdb ('''+@db+''') with no_infomsgs" -o"c:\'+@FileNameOut+'.txt" -w500'

create table #t (cmdout nvarchar(255), id int identity)

Insert #t Exec master..xp_cmdshell @cmd

Select * from #t

This way does not rely on setting up linked servers or Openquery being allowed at all.
(of course if xp_cmdshell is allowed then probably everything else is too)
Post #947834
Posted Tuesday, July 6, 2010 5:55 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 23, 2014 2:26 PM
Points: 752, Visits: 1,071
perfect solution Steve , thank you
Post #947839
Posted Tuesday, July 6, 2010 6:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 12,927, Visits: 32,331
steve your example would just return the results from the cmd prompt; I think the original poster wanted to capture the results of his dbcc command in both a table and in the output file.


what's wierd for me is this command:

dbcc checkdb (SandBox) with no_infomsgs does not return any results....so why bother capturing the results in a text file in the first place? maybe it only returns results if there was a problem/failure? {edit} dbcc only returns messages, so no_infomsgs will never return results at all...so there's no need to capture it in both a table and a file...it would be better to not include that i think.{/edit}

anyway

this complete command returns this for results:
cmdout	id
NULL 1

the code:
declare 
@cmd varchar(1000),
@FileNameOut varchar(200),
@user varchar(30),
@pass varchar(30) ,
@db varchar(30)

SET @user='sa'
SET @pass='NotARealPassword'
SET @db ='SandBox'

set @FileNameOut ='OutputConsistencia_'+ @db
select @cmd = 'osql -U' + @user + ' -P' + @pass + ' -S -Q"dbcc checkdb (''' + @db + ''') with no_infomsgs" -o"c:\' + @FileNameOut + '.txt" -w500'

create table #t (cmdout nvarchar(255), id int identity)

Insert #t Exec master..xp_cmdshell @cmd

Select * from #t




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #947845
Posted Tuesday, July 6, 2010 6:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 16, 2013 7:19 AM
Points: 98, Visits: 226
DOH!
I meant to remove the -o when I copied and pasted the command.

If the file is desired/required then run a second command to type out the file.

@cmd varchar(1000),
@FileNameOut varchar(200),
@user varchar(30),
@pass varchar(30) ,
@db varchar(30)

SET @user='sa'
SET @pass='NotARealPassword'
SET @db ='SandBox'

set @FileNameOut ='OutputConsistencia_'+ @db
select @cmd = 'osql -U' + @user + ' -P' + @pass + ' -S -Q"dbcc checkdb (''' + @db + ''') with no_infomsgs" -o"c:\' + @FileNameOut + '.txt" -w500'

Exec master..xp_cmdshell @cmd

create table #t (cmdout nvarchar(255), id int identity)

set @cmd = 'type "c:\' + @FileNameOut + '.txt"
Insert #t Exec master..xp_cmdshell @cmd


Select * from #t

Post #947853
Posted Tuesday, July 6, 2010 6:39 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 23, 2014 2:26 PM
Points: 752, Visits: 1,071
Thank you for reply lowel.

I say:

dbcc checkdb (SandBox) with no_infomsgs does not return any results....so why bother capturing the results in a text file in the first place? maybe it only returns results if there was a problem/failure? {edit} dbcc only returns messages, so no_infomsgs will never return results at all...so there's no need to capture it in both a table and a file...it would be better to not include that i think.{/edit}



Do you say that the DBCC command does not return any results? even if there is a problem with the DB?


What i want is that, if there is a problem with the DB, then the results should be returned but if there are no errors, then no results need to be returned (the file can be created even empty, no prob.)


So if i do DBCC with no_infosmsg will the SQL Server return results if the dabase as an error, or not?

Thank you
Post #947874
Posted Tuesday, July 6, 2010 7:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 12,927, Visits: 32,331
river, the way i read the NO_INFOMSG parameter is no results at all...seems ideal for an unattended job, but if you want results, i think you need to to just run dbcc checkdb and then filter the results; for example, one of the messages i just got on a table was this:

...

DBCC results for 'GMCDBGACCOMP_OLD'.
Msg 2508, Level 16, State 3, Line 2
The In-row data RSVD page count for object "GMCDBGACCOMP_OLD", index ID 1, partition ID 377040118808576, alloc unit ID 377040118808576 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

There are 0 rows in 0 pages for object "GMCDBGACCOMP_OLD".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'GMCDBGACCOMP_OLD' (object ID 1458208345).

...
CHECKDB found 0 allocation errors and 1 consistency errors in database 'AZ_900_TEST'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



so if you searched the results for something that was NOT like '%0 allocation errors%' or NOT like
'%0 consistency errors%' , you'd knwo there was a problem,a nd can drill down for the error messages themselves.


http://msdn.microsoft.com/en-us/library/ms176064.aspx
river1 (7/6/2010)

Do you say that the DBCC command does not return any results? even if there is a problem with the DB?


What i want is that, if there is a problem with the DB, then the results should be returned but if there are no errors, then no results need to be returned (the file can be created even empty, no prob.)


So if i do DBCC with no_infosmsg will the SQL Server return results if the dabase as an error, or not?

Thank you


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #947896
Posted Tuesday, July 6, 2010 7:08 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 23, 2014 2:26 PM
Points: 752, Visits: 1,071
i'm not choore that it does not return any messages if the database as errors, what i think (but not choore) is that this command does not print results to screen if the database as no error, meaning that it only returns results when they realy nedd to be displayed, but, onde again, not choore of this.


Can you try to pass the DBCC no_infomsgs in the database that gave you this error?

DBCC results for 'GMCDBGACCOMP_OLD'.
Msg 2508, Level 16, State 3, Line 2
The In-row data RSVD page count for object "GMCDBGACCOMP_OLD", index ID 1, partition ID 377040118808576, alloc unit ID 377040118808576 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
There are 0 rows in 0 pages for object "GMCDBGACCOMP_OLD".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'GMCDBGACCOMP_OLD' (object ID 1458208345).

...
CHECKDB found 0 allocation errors and 1 consistency errors in database 'AZ_900_TEST'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



I would do the test, but i do not have a database with problems at this point.
Post #947900
Posted Tuesday, July 6, 2010 7:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 12,927, Visits: 32,331
river i am sorry; you are correct...if there is an error, NO_INFOMSGS returns them;
here's the results you asked for...4 lines referencing jsut the single table that has the issue...the other 1000 + tables are ignored.

dbcc checkdb with NO_INFOMSGS
--results
Msg 2508, Level 16, State 3, Line 1
The In-row data RSVD page count for object "GMCDBGACCOMP_OLD", index ID 1, partition ID 377040118808576, alloc unit ID 377040118808576 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'GMCDBGACCOMP_OLD' (object ID 1458208345).
CHECKDB found 0 allocation errors and 1 consistency errors in database 'AZ_900_TEST'.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #947908
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse