xp_cmdshell

  • 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.

  • 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!

  • 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)

  • perfect solution Steve , thank you

  • 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:

    cmdoutid

    NULL1

    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!

  • 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

  • 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

  • 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!

  • 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.

  • 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!

  • Ok, no problem, and thank you for your reply.

  • i'm having problems with this command:

    declare @FileNameOut as varchar(250)

    declare @cmd as varchar(8000)

    set @FileNameOut ='c:\OutputConsistencia_'+@DB+'.txt'

    select @cmd = 'osql -U'+@user+' -P'+@pass+' -S -Q"dbcc checkdb ('''+@db+''') with no_infomsgs" -o"'+@FileNameOut+'" -w500'

    Exec master..xp_cmdshell @cmd

    When i execute it, it says that the system can not locate the specified file.

    Shouldn't this command create the file?

    Thank you

  • don't put the drive in it, only the path:

    'c:\OutputConsistencia_'+@DB+'.txt'

    gets turned into :'c:\c:\OutputConsistencia_SandBox.txt'

    use '\data\OutputConsistencia_'+@DB+'.txt' to put it in a sub folder off of the root od the drive, for example, or just 'OutputConsistencia_'+@DB+'.txt' to put it on the root of the c drive

    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!

  • hum.... didn't undestood your answer 🙂

    i have made what you say like:

    USE [SecurDB]

    GO

    /****** Object: StoredProcedure [dbo].[DBCCCheckDB] Script Date: 07/06/2010 15:40:18 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER procedure [dbo].[DBCCCheckDB] (@DB as varchar(100), @User as varchar(100), @pass as varchar (250))

    as

    BEGIN

    declare @FileNameOut as varchar(250)

    declare @cmd as varchar(8000)

    set @FileNameOut ='OutputConsistencia_'+@DB+'.txt'

    select @cmd = 'osql -U'+@user+' -P'+@pass+' -S -Q"dbcc checkdb ('''+@db+''') with no_infomsgs" -o"'+@FileNameOut+'" -w500'

    Exec master..xp_cmdshell @cmd

    END

    Still i receive the same message...

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply