File Creation from sqlserver | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

File Creation from sqlserver

Hi All,
Continuing my previous post regarding the pdf file creation.
My requirement as such is to put the data directly from sqlserver table to flat file(.ps).
Now, i want to know.. How does sqlserver stored procedure helps us to create a file in o/s and write data into it ?
I have experience in doing the same in oracle.
But my requirement now is to workout from sqlserver.
Kindly suggest me how can we create a file in o/s from sqlserver stored procedure and write information into it. Regards
Abdul Lateef.
Polaris Software Labs.
001 91 44 4341964.
Here’s some sample code that you might be able to use as a model for your situation. The solution is not very elegant, but it works. Also, this sample code is limited to writing 8,000 characters at a time. This code was written by a co-worker, so I am not very familiar with it.<br /><br />/*<br />DECLARE @Query [varchar](8000)<br />SET @Query = ‘SELECT TOP 10<br />CAST([id] AS varchar) + ”,” +<br />RTRIM([xtype]) + ”,” +<br />USER_NAME([uid]) + ”,” +<br />[name]<br />FROM [dbo].[sysobjects]'<br />EXEC [dbo].[sp_WriteTableToFile]<br />@Query = @Query,<br />@Filename = ‘C:Testing1.txt’,<br />@IfExists = ‘CANCEL'<br /><br />@IfExists has these options<br />OVERWRITE<br />APPEND<br />CANCEL<br />*/<br />IF EXISTS<br />(<br />SELECT *<br />FROM [dbo].[sysobjects]<br />WHERE<br />[id] = OBJECT_ID(N'[dbo].[sp_WriteTableToFile]’)<br />AND OBJECTPROPERTY([id], N’IsProcedure’) = 1<br />)<br />DROP PROCEDURE [dbo].[sp_WriteTableToFile]<br />GO<br />CREATE PROCEDURE [dbo].[sp_WriteTableToFile]<br />(<br />@Query [varchar](7940),<br />@Filename [varchar](1000),<br />@IfExists [varchar](9)<br />)<br />AS<br />SET NOCOUNT ON<br />DECLARE<br />@FileExists [bit],<br />@LineData [varchar](8000),<br />@LineCount [int],<br />@cmdStatement [varchar](8000),<br />@sqlStatment [varchar](8000),<br />@hResult [int],<br />@errStatement [varchar](8000),<br />@ReturnCode [int]<br /><br />CREATE TABLE #FileExists<br />(<br />[doesexist] smallint,<br />[fileindir] smallint,<br />[direxist] smallint<br />)<br />INSERT<br />INTO #FileExists<br />(<br />[doesexist],<br />[fileindir],<br />[direxist]<br />)<br />EXEC master..xp_fileexist<br />@Filename<br />SELECT @FileExists = CAST([doesexist] AS bit)<br />FROM #FileExists<br /><br />IF @FileExists = 1 AND UPPER(@IfExists) = ‘OVERWRITE'<br />BEGIN<br />SET @cmdStatement = ‘DEL ‘ + @Filename + ‘ /Q'<br />EXEC @hResult = [master].[dbo].[xp_cmdshell]<br />@cmdStatement,<br />NO_OUTPUT<br />IF @hResult = 1<br />BEGIN<br />SET @ReturnCode = @hResult<br />SET @errStatement = ‘Unable to delete ‘ + @Filename + ‘.'<br />RAISERROR(@errStatement, 16, 1)<br />GOTO ExitProc<br />END<br />END<br /><br />IF @FileExists = 1 AND UPPER(@IfExists) = ‘CANCEL'<br />BEGIN<br />SET @ReturnCode = 1<br />SET @errStatement = ‘Execution canceled because ‘ + @Filename + ‘ already exists.'<br />RAISERROR(@errStatement, 16, 1)<br />GOTO ExitProc<br />END<br /><br />SET @ReturnCode = 0<br />SET @LineCount = 0<br />SET @sqlStatment = ‘DECLARE _Dynamic’ + CHAR(13) +<br />+ CHAR(9) + ‘CURSOR’ + CHAR(13) +<br />+ CHAR(9) + ‘FORWARD_ONLY’ + CHAR(13) +<br />+ CHAR(9) + ‘READ_ONLY’ + CHAR(13) +<br />+ CHAR(9) + ‘FOR’ + CHAR(13) +<br />@Query<br />EXECUTE (@sqlStatment)<br />OPEN _Dynamic<br />FETCH<br />NEXT<br />FROM _Dynamic<br />INTO @LineData<br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br />SET @LineCount = @LineCount + 1<br />SET @cmdStatement = ‘ECHO ‘ + @LineData + ‘&gt;&gt;’ + @Filename<br />EXEC @hResult = [master].[dbo].[xp_cmdshell]<br />@cmdStatement,<br />NO_OUTPUT<br />IF @hResult = 1<br />BEGIN<br />SET @ReturnCode = @hResult<br />SET @errStatement = ‘Unable to write line ‘ +<br />CAST(@LineCount AS varchar) + ‘. Continuing on to next line. Data in this line:’ + CHAR(13) +<br />ISNULL(LEFT(@LineData, 792<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, ”)<br />RAISERROR(@errStatement, 16, 1)<br />END<br />FETCH<br />NEXT<br />FROM _Dynamic<br />INTO @LineData<br />END<br />CLOSE _Dynamic<br />DEALLOCATE _Dynamic<br />GOTO ExitProc<br />ExitProc:<br />BEGIN<br />RETURN @ReturnCode<br />END<br />GO<br /><br /><br />——————<br />Brad M. McGehee<br />Webmaster<br />SQL-Server-Performance.Com