Post reply

Using Results from an extended stored procedure

  • January 8, 2007 at 11:09 am

    #176243

    I want to use the results from the extended stored procedure xp_cmdshell 'Dir C:\DailyBackups\2007-*.'  When I run this stored procedure I get 13 rows back. I'm only interested in one row. Is there a way to return the results to a cursor so I can further query the results from the extended stored procedure?

    Thanks in advance for any help that you can give!!!

     

  • January 8, 2007 at 12:04 pm

    #681849

    Sounds like we may use this code for the same purpuse .

     

    USE SSC

    GO

    CREATE TABLE #Results (txtResult varchar(2000) NULL)

    INSERT INTO #Results (txtResult)

    EXEC master.dbo.xp_cmdshell 'DIR D:\*_BACKUP_*.*'

    SELECT * FROM #Results

    DROP TABLE #Results

  • January 8, 2007 at 12:44 pm

    #681858

    That is exactly it!! Thanks!!!

  • January 8, 2007 at 12:58 pm

    #681864

    HTH .

  • January 9, 2007 at 2:21 pm

    #682187

    I do this for my weekly pull of backups to our test environment. You can make this easy by including some options in you Dir command. Here is a snippet of the code (test is in a different domain, so I create a mapped drive to the production domain so that I can specify a specific user account after dialing the VPN):

    /*

    Get a directory listing of all full backups to determine the newest one

    /O-D Sort directory list by date descending

    /TW Date column sorted on = date last written. Change 2nd letter to C for Date Created.

    /B Bare format: no attributes, descriptions, or summaries. Just file names.

    */

    Insert

    Into @Backups (BackupName)

    Exec

    xp_cmdshell N'dir Y:\*.BAK /O-D /TW /B'

     

    /* Delete the empty or otherwise invalid rows returned by xp_cmdshell */

    Delete

    From @Backups

    Where

    BackupName Is Null

    Or

    Charindex('_', BackupName) = 0

     

    /* File names are sorted by date written descending, so first file in list is newest */

    Select

    @BackupName = BackupName, @Database = Left(BackupName, Charindex('_', BackupName) - 1)

    From

    @Backups

    Where

    BackupID = 1

     

    /* Copy the backup file from the remote server */

    Set

    @cmdshell = 'copy /B /Y ' + @Path + '\' + @BackupName + ' D:\backups\'

    Exec xp_cmdshell @cmdshell

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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