Prev: How to show chinese charaters?
Next: Sql server 2008 r2 hangs up
From: Mukesh on 10 Aug 2010 03:59
Hi All
I'm developing an administrative tool to fix some of the frequent
occuring issue just in time. For that I need to run DBCC CHECKTABLE
with some parameters. I created a stored procedure for this and
everything works fine except returning text message of DBCC commands
back to calling application (Reporting Services). Any idea on
enabeling my stored procedure to return DBCC result text?
Thanks
Mukesh
From: John Bell on 10 Aug 2010 15:17
On Tue, 10 Aug 2010 00:59:19 -0700 (PDT), Mukesh <cmukesh19(a)gmail.com>
wrote:

>Hi All
>I'm developing an administrative tool to fix some of the frequent
>occuring issue just in time. For that I need to run DBCC CHECKTABLE
>with some parameters. I created a stored procedure for this and
>everything works fine except returning text message of DBCC commands
>back to calling application (Reporting Services). Any idea on
>enabeling my stored procedure to return DBCC result text?
>Thanks
>Mukesh


That may depend on what you are doing, if it is a method that only
returns messages and not a resultset then you can use SQLCMD, if you
use a method that returns a resultset you don't need SQLCMD:

USE ADVENTURWORKS
GO

CREATE TABLE #out ( msg varchar(100) ) ;

INSERT INTO #out ( msg )
EXEC xp_cmdshell 'SQLCMD -E -S (local) -d Adventureworks -Q "DBCC
CHECKTABLE ( ErrorLog )"' ;

SELECT * FROM #out ;

TRUNCATE TABLE #out ;


INSERT INTO #out ( msg )
EXEC ( 'DBCC CHECKTABLE ( ErrorLog ) WITH ESTIMATEONLY' ) ;

SELECT msg AS [Estimated TEMPDB space needed for CHECKTABLES (KB)]
FROM #out ;


DROP table #out ;
GO


John
From: Mukesh on 11 Aug 2010 05:44
On Aug 11, 5:17 am, John Bell <jbellnewspo...(a)hotmail.com> wrote:
> On Tue, 10 Aug 2010 00:59:19 -0700 (PDT), Mukesh <cmukes...(a)gmail.com>
> wrote:
>
> >Hi All
> >I'm developing an administrative tool to fix some of the frequent
> >occuring issue just in time. For that I need to runDBCCCHECKTABLE
> >with some parameters. I created a stored procedure for this and
> >everything works fine except returning text message ofDBCCcommands
> >back to calling application (Reporting Services). Any idea on
> >enabeling my stored procedure to returnDBCCresulttext?
> >Thanks
> >Mukesh
>
> That may depend on what you are doing, if it is a method that only
> returns messages and not a resultset then you can  use SQLCMD, if you
> use a method that returns a resultset you don't need SQLCMD:
>
> USE ADVENTURWORKS
> GO
>
> CREATE TABLE #out ( msg varchar(100) ) ;
>
> INSERT INTO #out ( msg )
> EXEC xp_cmdshell 'SQLCMD -E -S (local) -d Adventureworks -Q "DBCC
> CHECKTABLE ( ErrorLog )"' ;
>
> SELECT * FROM #out  ;
>
> TRUNCATE TABLE #out  ;
>
> INSERT INTO #out ( msg )
> EXEC ( 'DBCCCHECKTABLE ( ErrorLog ) WITH ESTIMATEONLY' ) ;
>
> SELECT msg AS [Estimated TEMPDB space needed for CHECKTABLES (KB)]
> FROM #out  ;
>
> DROP table #out ;
> GO
>
> John

Thanks John
I need only messages, so the first appoach works fine. But in our
corporate setup xp_cmdshell component is switched off as a security
configuration.
Second solution is not so optimal in my case for various reasons.
Still, I configured DBCC CHECKTABLE and CHECKDB WITH TABLERESULTS.
But we need to know in advance how many columns they will return and
create temp tables accordingly. And consolidation of these tables
(with different number of columns) in one resultset is even more
clumsy and ad-hoc.
So, do we have any alternative of xp_cmdshell? Or for that matter any
alternative approach for capturing only message?
Thanks
Mukesh
From: John Bell on 11 Aug 2010 15:51
On Wed, 11 Aug 2010 02:44:52 -0700 (PDT), Mukesh <cmukesh19(a)gmail.com>
wrote:

>On Aug 11, 5:17�am, John Bell <jbellnewspo...(a)hotmail.com> wrote:
>> On Tue, 10 Aug 2010 00:59:19 -0700 (PDT), Mukesh <cmukes...(a)gmail.com>
>> wrote:
>>
>> >Hi All
>> >I'm developing an administrative tool to fix some of the frequent
>> >occuring issue just in time. For that I need to runDBCCCHECKTABLE
>> >with some parameters. I created a stored procedure for this and
>> >everything works fine except returning text message ofDBCCcommands
>> >back to calling application (Reporting Services). Any idea on
>> >enabeling my stored procedure to returnDBCCresulttext?
>> >Thanks
>> >Mukesh
>>
>> That may depend on what you are doing, if it is a method that only
>> returns messages and not a resultset then you can �use SQLCMD, if you
>> use a method that returns a resultset you don't need SQLCMD:
>>
>> USE ADVENTURWORKS
>> GO
>>
>> CREATE TABLE #out ( msg varchar(100) ) ;
>>
>> INSERT INTO #out ( msg )
>> EXEC xp_cmdshell 'SQLCMD -E -S (local) -d Adventureworks -Q "DBCC
>> CHECKTABLE ( ErrorLog )"' ;
>>
>> SELECT * FROM #out �;
>>
>> TRUNCATE TABLE #out �;
>>
>> INSERT INTO #out ( msg )
>> EXEC ( 'DBCCCHECKTABLE ( ErrorLog ) WITH ESTIMATEONLY' ) ;
>>
>> SELECT msg AS [Estimated TEMPDB space needed for CHECKTABLES (KB)]
>> FROM #out �;
>>
>> DROP table #out ;
>> GO
>>
>> John
>
>Thanks John
>I need only messages, so the first appoach works fine. But in our
>corporate setup xp_cmdshell component is switched off as a security
>configuration.
>Second solution is not so optimal in my case for various reasons.
>Still, I configured DBCC CHECKTABLE and CHECKDB WITH TABLERESULTS.
>But we need to know in advance how many columns they will return and
>create temp tables accordingly. And consolidation of these tables
>(with different number of columns) in one resultset is even more
>clumsy and ad-hoc.
>So, do we have any alternative of xp_cmdshell? Or for that matter any
>alternative approach for capturing only message?
>Thanks
>Mukesh


You have options outside SQL Server! How messages are handled is
dependent on the client.

John
From: Erland Sommarskog on 11 Aug 2010 17:21
Mukesh (cmukesh19(a)gmail.com) writes:
> I need only messages, so the first appoach works fine. But in our
> corporate setup xp_cmdshell component is switched off as a security
> configuration.
> Second solution is not so optimal in my case for various reasons.
> Still, I configured DBCC CHECKTABLE and CHECKDB WITH TABLERESULTS.
> But we need to know in advance how many columns they will return and
> create temp tables accordingly. And consolidation of these tables
> (with different number of columns) in one resultset is even more
> clumsy and ad-hoc.
> So, do we have any alternative of xp_cmdshell? Or for that matter any
> alternative approach for capturing only message?

As John says, it may be better to handle this client-side.

But there is one more option to do it SQL Server: use the CLR. You would
need to use WITH TABLERESULTS in this case. PRINT messages still are
still passed to the client as I recall.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: How to show chinese charaters?
Next: Sql server 2008 r2 hangs up