Suppressing output from a stored procedure.

Question

Suppressing output from a stored procedure.

David McKinney

SSChampion

Points: 10472
More actions
November 22, 2006 at 1:24 am

#67886

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

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

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

bledu SSChampion Points: 12335 More actions · Answer 1

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 SSChampion Points: 10472 More actions · Answer 2

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

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 SSCoach Points: 18843 More actions · Answer 3

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 SSChampion Points: 10472 More actions · Answer 4

David McKinney

SSChampion

Points: 10472

November 22, 2006 at 9:11 am

#673498

Thanks Ken - works a treat!

badOedipus SSC Veteran Points: 202 More actions · Answer 5

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 SSC Guru Points: 182279 More actions · Answer 6

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 SSChampion Points: 10472 More actions · Answer 7

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 SSC Veteran Points: 202 More actions · Answer 8

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 SSC Guru Points: 182279 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

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

Heh... so far as I'm concerned, the "proper" way would be to get rid of the SSIS package and do it in TSQL. No call to anything required for that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

Jeff Moden (4/29/2013)
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
Heh... so far as I'm concerned, the "proper" way would be to get rid of the SSIS package and do it in TSQL. No call to anything required for that. 😉

I guess so, unless you consider xp_cmdshell nothing. No free lunch Jeff.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

Just for fun, how would you rewrite the SSIS package using only T-SQL to "generate a couple of excel spreadsheets, and email them."?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

TaylorMade SSC Eights! Points: 967 More actions · Answer 13

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

Thank you so much for this info, was banging my head up against this problem.

However, for completeness again, there is a typo in the above line, should be "no_output".

For whoever this might help, this helped solve the issue I had when trying to run an ssrs report that called a stored procedure which initially called a ssis package, then built up a dataset. Without the no_output parameter, the ssrs report was trying to work against the "output" results information from the ssis package.

Again, THANK YOU so much.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 14

TaylorMade (11/13/2013)
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
Thank you so much for this info, was banging my head up against this problem.
However, for completeness again, there is a typo in the above line, should be "no_output".
For whoever this might help, this helped solve the issue I had when trying to run an ssrs report that called a stored procedure which initially called a ssis package, then built up a dataset. Without the no_output parameter, the ssrs report was trying to work against the "output" results information from the ssis package.
Again, THANK YOU so much.

How creative. May I ask you a question about your solution? How is the SSRS data source setup? I am curious to know how the principal running the report, whether it be the user running the report or some service account (SQL or Windows), is granted access to run xp_cmdshell. You could have left yourself exposed in terms of who can run xp_cmdshell.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato