Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Suppressing output from a stored procedure. Expand / Collapse
Author
Message
Posted Wednesday, November 22, 2006 1:24 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794

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

 

Post #324828
Posted Wednesday, November 22, 2006 5:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 4:24 AM
Points: 685, 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.

Post #324858
Posted Wednesday, November 22, 2006 8:01 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794

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

Post #324923
Posted Wednesday, November 22, 2006 9:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:54 AM
Points: 834, Visits: 5,322

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'

 

Post #324953
Posted Wednesday, November 22, 2006 9:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794

Thanks Ken - works a treat!

Post #324960
Posted Friday, April 26, 2013 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 6, Visits: 14
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
Post #1447117
Posted Friday, April 26, 2013 12:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1447129
Posted Monday, April 29, 2013 12:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
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.
Post #1447368
Posted Monday, April 29, 2013 7:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 6, Visits: 14
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.
Post #1447500
Posted Monday, April 29, 2013 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1447583
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse