xp_cmdshell

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » xp_cmdshell

14 posts, Page 1 of 2

1 2 »»

xp_cmdshell

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Monday, July 05, 2010 9:45 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Lowell

Posted Monday, July 05, 2010 9:50 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

steven.malone

Posted Tuesday, July 06, 2010 5:48 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 6:58 AM
Points: 96, Visits: 210

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

river1

Posted Tuesday, July 06, 2010 5:55 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

perfect solution Steve , thank you

Post #947839

Lowell

Posted Tuesday, July 06, 2010 6:01 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

steven.malone

Posted Tuesday, July 06, 2010 6:10 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 18, 2013 6:58 AM
Points: 96, Visits: 210

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

river1

Posted Tuesday, July 06, 2010 6:39 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Lowell

Posted Tuesday, July 06, 2010 7:00 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

river1

Posted Tuesday, July 06, 2010 7:08 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

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

Lowell

Posted Tuesday, July 06, 2010 7:14 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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 »

14 posts, Page 1 of 2

1 2 »»

Permissions