Using Results from an extended stored procedure
-
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!!! -
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
-
That is exactly it!! Thanks!!!
-
HTH
. -
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply