Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    5

    Unanswered: exec master..sp_executesql @sqlstmt

    Declare @sqlcommand nvarchar(1500),@path nvarchar(1500),@sqlstmt nvarchar(2500),@Cmd nvarchar(1500)
    SET @path = 'C:\output'+'_'+convert(char(8),getdate(),112)+'.t xt'
    SET @sqlcommand = 'echo "'+'This SQL Server Jobs on "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+ ' >> '+ @path
    select @Cmd = @sqlcommand
    exec master..xp_cmdshell @Cmd

    First part i got the result in 'c:\output20090907.txt, result is servername


    set @sqlstmt='SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B
    WHERE A.job_id = B.job_id AND B.last_run_outcome = 0'
    exec master..sp_executesql @sqlstmt, @path

    In second part could load the result in text file. can anyone suggestion me.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do you want to output the result of that SQL statement into the file that is stored in the variable @path?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    5

    exec master..sp_executesql @sqlstmt

    yes, second part i want load the result in same text file.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In that case look up BCP. It is a command line tool that creates a text file and outputs the results of queries into it.
    e.g.
    Code:
    DECLARE @string AS NVARCHAR(4000)
    
    SELECT    @string = 'BCP "SELECT * FROM myTable" QUERYOUT c:\myResult.txt -c -T -t'
    
    exec master.dbo.xp_cmdshell @string
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by anandu
    yes, second part i want load the result in same text file.
    or sqlcmd -E 'Q"query..." >> logfile

    >> appends text.

    NEVERMIND

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •