SQL Server Performance

xp_cmdshell to output SP results to a document

Discussion in 'General Developer Questions' started by eramgarden, Aug 1, 2006.

  1. eramgarden New Member

    I want to dump results of my stored proc to a file, save it somewhere or call it from ASP.Net and display the results. I found an example:



    declare @db1 as varchar(100)
    declare @db2 as varchar(100)
    declare @cmd as varchar(500)
    set @db1='VQUEDTA001'
    set @db2='VQUEDTA500'
    Set @cmd= 'osql.exe -S servername -U UID -P PWD -Q "EXEC usp_comparedb ''VQUEDTA001'', ''VQUEDTA500'' " -o "h:eek:utput3.txt"'


    First , I couldnt get the @db1 and @db2 used paramters and ended up hardcoding them. How can I use the variables in there?

    Second, is there a better way of doing this? another example?
  2. Adriaan New Member

    When you need a single quote at the end or start of a fixed string, around the to-be-inserted variable, you have to use three single quotes in your code:

    Set @cmd = '........... "EXEC usp_comparedb ''' + @db1 + ''', ''' + @db2 + ''' " -o "' + @filename + '" '
  3. LearnSqlServer.com New Member

    Can you use DTS or SSIS? Those seem to be better suited for this type of activity. Or why don't you just write the code in VB/C# to execute a SqlCommand and just display the results on the web form?

    /*******************************

    Scott Whigham

    Check outhttp://www.LearnSqlServer.com/VideoTutorials/ - SQL Server 2005 and 2000 Tutorials

    *******************************/
  4. Adriaan New Member

    Scott,

    This way you can set it up as a stored procedure, to be executed as a job in a low activity period.

    You can also use a table to pre-select the databases to be compared, and have the sp look in that table to see which databases to compare.

    The only downside to this is the clumsy syntax, which gets worse when you need to compile a string with variables. It gets even worse than this with BCP commands ...

Share This Page