Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


xp_cmdshell


xp_cmdshell

Author
Message
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14919 Visits: 38909
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steven.malone
steven.malone
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 227
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)
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
perfect solution Steve , thank you
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14919 Visits: 38909
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steven.malone
steven.malone
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 227
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
river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14919 Visits: 38909
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

river1
river1
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1350
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14919 Visits: 38909
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search