July 5, 2010 at 9:45 am
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.
July 5, 2010 at 9:50 am
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
July 6, 2010 at 5:48 am
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)
July 6, 2010 at 5:55 am
perfect solution Steve , thank you
July 6, 2010 at 6:01 am
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
July 6, 2010 at 6:10 am
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
July 6, 2010 at 6:39 am
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
July 6, 2010 at 7:00 am
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
July 6, 2010 at 7:08 am
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.
July 6, 2010 at 7:14 am
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
July 6, 2010 at 7:17 am
Ok, no problem, and thank you for your reply.
July 6, 2010 at 9:28 am
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
July 6, 2010 at 10:00 am
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
July 6, 2010 at 10:07 am
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