Suppressing output from a stored procedure.

  • 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

     

  • 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.

  • 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

  • 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'

     

  • Thanks Ken - works a treat!

  • 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

  • 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

  • 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.

  • 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.

  • 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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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

  • 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.

  • 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

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

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