SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Suppressing output from a stored procedure.


Suppressing output from a stored procedure.

Author
Message
David McKinney
David McKinney
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2697 Visits: 2090

Hi,

I'm calling DTExec from a stored procedure, using xp_cmdshell (trust me!).

It returns (selects) lots of status information which I'm not interested in, and which is causing problems as it's getting sent back to the web client - which doesn't like it.

(Even when the reporting switch is set to N - for No Reporting)

The only method I've found to 'swallow' the output is by exec-ing into a table which I then drop.

Is there a better way to suppress the output?

Thanks,

Dave McKinney.

select @cmd ='C:\"Program Files (x86)"\"Microsoft SQL Server"\90\DTS\Binn\DTExec.exe /DTS "' + @PackageName + '" /SERVER "' + @MachineName + '" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING N'

CREATE TABLE #output ( Line VARCHAR(8000) )

INSERT #output exec master..xp_cmdshell @cmd

drop table #output


bledu
bledu
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2879 Visits: 104

what sort of status information is it returning? if you don't mind my asking what is the dts package doing?

if you want to continue with your current solution you can replace the temp table with table variables then you don't have to worry about dropping them.

http://www.sqlservercentral.com/columnists/kjambu/2691.asp




Everything you can imagine is real.

David McKinney
David McKinney
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2697 Visits: 2090

I had considered a table variable but...

"Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement."

http://support.microsoft.com/kb/305977/en-us

Thank goodness for old style temp tables!

The SSIS package generates a couple of excel spreadsheets, and emails them.

With reporting set to OFF, the following sort of text is returned systematically by the dtexec program.

Microsoft (R) SQL Server Execute Package Utility

Version 9.00.1399.06 for 32-bit

Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

NULL

Started: 15:55:55

DTExec: The package execution returned DTSER_SUCCESS (0).

Started: 15:55:55

Finished: 15:56:02

Elapsed: 6.719 seconds


Ken McKelvey
Ken McKelvey
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3951 Visits: 8435

You could try re-directing the output to the nul console by adding > nul:

select @cmd ='C:\"Program Files (x86)"\"Microsoft SQL Server"\90\DTS\Binn\DTExec.exe /DTS "' + @PackageName + '" /SERVER "' + @MachineName + '" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING N > nul'


David McKinney
David McKinney
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2697 Visits: 2090

Thanks Ken - works a treat!


badOedipus
badOedipus
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 32
I'm posting on this old post mainly for completeness and to help anyone else out that has this same issue.

I'm not sure how far back this goes, but I know it works in SQL Server 2008 r2.
the xp_cmdshell command has an optional parameter to specify no output. In your example here your sp would have a line like this:

exec xp_cmdshell @cmd, no_ouput



BadOed
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38296 Visits: 14411
A proper solution would be to get rid of the use of xp_cmdshell all together.

The recommended way using either a SQL Agent job or a web service that invokes the package. Both are decoupled from the caller, which is how it should be:

Loading and Running a Remote Package Programmatically

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
David McKinney
David McKinney
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2697 Visits: 2090
badOedipus (4/26/2013)
I'm posting on this old post mainly for completeness and to help anyone else out that has this same issue.

I'm not sure how far back this goes, but I know it works in SQL Server 2008 r2.
the xp_cmdshell command has an optional parameter to specify no output. In your example here your sp would have a line like this:

exec xp_cmdshell @cmd, no_ouput



BadOed


Thanks for updating!...could come in handy.
badOedipus
badOedipus
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 32
opc.three (4/26/2013)
A proper solution would be to get rid of the use of xp_cmdshell all together.

The recommended way using either a SQL Agent job or a web service that invokes the package. Both are decoupled from the caller, which is how it should be:

Loading and Running a Remote Package Programmatically


Sure, that works great if you have a full blown version of SQL server but for those of us working for companies that won't/can't afford anything besides an extra box on the network with an express version, that's not an option.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38296 Visits: 14411
Ok, sounds like Agent is not an option. There are others shown in the article I linked to.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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