Dumping columns to files with Microsoft SQL Server >2012


DECLARE @xml NVARCHAR(MAX);
DEClARE @ID INT;
DECLARE @filename NVARCHAR(1024);
DECLARE @sqlStr NVARCHAR(1024);
DECLARE @sqlCmd NVARCHAR(1024);

DECLARE meh CURSOR FOR
select InputMessage, Id from messagelog where date > '2016-02-05' and outputmessage like '%Unknown message: Equipment%' order by date desc;

OPEN meh
FETCH NEXT FROM meh INTO @xml, @ID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @filename = 'C:\temp\EquipmentMaintainNotification_' + CAST(@ID AS NVARCHAR) + '.xml';
PRINT @filename
SET @sqlStr = 'USE SIBP_Logging;SELECT InputMessage FROM MessageLog WHERE Id = ' + CAST(@ID AS NVARCHAR);

SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout "' + @filename + '" -w -T -S SEDNT2434\DEV'
PRINT @sqlCmd

EXEC xp_cmdshell @sqlCmd

FETCH NEXT FROM meh INTO @xml, @ID
END
CLOSE meh
DEALLOCATE meh

Leave a Reply

Your email address will not be published. Required fields are marked *