I've created a stored proc that archives all but the two most recent sets of records.The archive in question is to be sent to a text file via bcp.I came across a rights issue with manipulating xp_cmdshell access. I came up with an unorthodox method to enable xp_cmdshell access.... I can't seem to get this to work. I could use a code assessment (feel free to be brutal 
) and other options to go about archiving my records.SET NOCOUNT ON;DECLARE @first char(12)DECLARE @second char(12)DECLARE @bcpCommand varchar(2000)SET @first = (SELECT TOP 1 transactionid FROM transactions WHERE transactionid LIKE 'HKCF%' ORDER BY transactionid DESC)SET @second = (SELECT TOP 1 transactionid FROM (SELECT TOP 2 transactionid FROM transactions WHERE transactionid LIKE 'HKCF%' ORDER BY transactionid DESC) A ORDER BY transactionid ASC)SET @bcpCommand = 'bcp MyDB.cfbleep out c:\LouDobbs\export.txt -c -T -S SERVERNAME'DECLARE @MyTable Table (bleepid char(15), transactionid char(12), bloopid char(10), blaapid char(2), motts smallint)INSERT INTO @MyTableSELECT * FROM cfbleep WHERE transactionid = @first OR transactionid = @secondDELETE FROM cfbleepWHERE transactionid = @first OR transactionid = @second-- EXEC sp_configure 'show advanced options', 1 -- RECONFIGURE -- EXEC sp_configure 'xp_cmdshell', 1 -- RECONFIGURE -- EXEC master..xp_cmdshell @bcpCommand-- EXEC sp_configure 'show advanced options', 1-- RECONFIGURE -- EXEC sp_configure 'xp_cmdshell', 0-- RECONFIGURE -- EXEC sp_configure 'show advanced options', 0 -- RECONFIGURE DELETE FROM cfbleepINSERT INTO cfbleepSELECT * FROM @MyTable
bulk copy in stored proc
Posted - 2008-07-01 : 20:20:59
