Writing from a SP directly to a flat file ( without using SSIS )

Question

Writing from a SP directly to a flat file ( without using SSIS )

mw_sql_developer

SSCoach

Points: 19445
More actions
June 14, 2017 at 9:32 am

#331078

I know this can be done via producing a C# assembly ( That is one way of doing it )
If anyone can direct me to a resource I'd be happy
if there is a way to do it without the use of C# le me know.
I just found an article in http://www.sqlservercentral.com/articles/U-SQL/157449/
But that is too advance and uses U-SQL ( I am not even sure what U-SQl is )
Below is the code from that URL above.
I am thinking there should be an easy way

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

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

Steve Jones - SSC Editor SSC Guru Points: 734469 More actions · Answer 1

U-SQL is used in Azure Data Lake, not SQL Server.

You can write out results with a few tools, but not sure a stored proc can do this without shelling out with something like xp_cmdshell and calling back in to get results. Easiest way might be to write to a table, then select out with bcp.

Mr. Brian Gale SSC-Insane Points: 24697 More actions · Answer 2

If you are just wanting to run the SP manually via SSMS and save the results to file, press ctrl+shift+f and then run the SP.

C# would be pretty easy though. You'd just need to get the data from the database and store it in a datatable (presuming it returns a table) then dump it to disk 1 row and 1 column at a time. For very large tables, this will be quite slow and memory intensive on the machine running the C# application.

I believe that SSRS could do this as well. Use the data query as the stored procedure and have it display the results in a table and then set up a subscription on the SSRS report to dump it to disk on a schedule.
Been a while since I"ve done any SSRS work, but I am pretty sure you can use a stored procedure as the data query.

Otherwise, outside of Steve Jones's or my suggestions, I think SSIS may be your best bet.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

thisted SSCrazy Points: 2131 More actions · Answer 3

This is what I use.

USE [GlobalDB]

GO

/****** Object: StoredProcedure [dbo].[sp_SaveFile] Script Date: 6/22/2017 9:09:53 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--=================(Documentation)=====================

--Author:Thisted

--Create date:20141117

--Description:This stored procedure is used for writing to a text file

--This will run as the SQL Service Account when accessing external Directories.

--Modification log

--2/27/2015 TJHAdded the @Unicode Parameter

--=====(Executable StoreProcedure with Parrameters )=====

/*

sp_SaveFile 'this is a test1', 'C:\', 'test.txt', @unicode=0

*/

ALTER PROCEDURE [dbo].[sp_SaveFile]

(

@String Varchar(max), --8000 in SQL Server 2000

@Path VARCHAR(255),

@Filename VARCHAR(100),

@unicode bit = false --by default it will create ansi-utf8, unless we specify true.

)

AS

DECLARE @objFileSystem int,

@objTextStream int,

@objErrorObject int,

@strErrorMessage Varchar(1000),

@Command varchar(1000),

@hr int,

@fileAndPath varchar(max)

set nocount on

DECLARE @DBEngineLogin VARCHAR(200)

EXECUTE master.dbo.xp_instance_regread

@rootkey = N'HKEY_LOCAL_MACHINE',

@key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',

@value_name = N'ObjectName',

@value = @DBEngineLogin OUTPUT

select @strErrorMessage='opening the File System Object'

EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+@filename

if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'" Check that the path exists and user ' + @DBEngineLogin + ' has access'

if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'

, @objTextStream OUT, @FileAndPath,2,@unicode

if @HR=0 Select @objErrorObject=@objTextStream,

@strErrorMessage='writing to the file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'

if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'

if @hr<>0

begin

Declare

@Source varchar(255),

@Description Varchar(255),

@Helpfile Varchar(255),

@HelpID int

EXECUTE sp_OAGetErrorInfo @objErrorObject,

@source output,@Description output,@Helpfile output,@HelpID output

Select @strErrorMessage='Error whilst '

+coalesce(@strErrorMessage,'doing something')

+', '+coalesce(@Description,'')

raiserror (@strErrorMessage,16,1)

end

EXECUTE sp_OADestroy @objTextStream