create a stored procedure to create a txt file

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » create a stored procedure to create a txt...

create a stored procedure to create a txt file

Rate Topic

Display Mode

Topic Options

Author

Message

river1

Posted Wednesday, April 21, 2010 3:37 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:56 AM
Points: 679, Visits: 953

Hi,

I need to create a stored procedure that accepts a parameter, this paramentes is a name. When i pass the name to the stored procedure, this procedure creates a empty txt file with the name that i passed as the parameter.

Is it possible to do this using t-sql?

Thank you

Post #907575

Mike Seddon

Posted Friday, July 09, 2010 4:12 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:17 AM
Points: 13, Visits: 28

I can't believe this is the slickest way to achieve what you're after, but this works w00t

CREATE PROCEDURE EXPORT_DATA
@FileName varchar(255)
as
begin

declare @bcpCommand varchar(255), @Result int
set @bcpCommand = 'bcp "Select 1 as a" queryout "' + @FileName + '" -c -t, -T -S '
exec @Result = master..xp_cmdshell @bcpCommand, no_output

end

Don't forget that xp_cmdshell is not enabled in the db configuration by default

Mike.

Post #949830

jain.ashish309

Posted Thursday, October 07, 2010 3:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 07, 2010 3:51 AM
Points: 1, Visits: 1

Is there any other way to create store procedure

Post #1000250

Mad Myche

Posted Friday, October 08, 2010 8:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:49 AM
Points: 133, Visits: 413

You can use sp_OACreate to access the the File System Object (Active X)

reading & writing files in sql server

Director of Transmogrification Services

Post #1001375

RamG

Posted Wednesday, March 02, 2011 9:39 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, November 22, 2012 5:47 AM
Points: 33, Visits: 103

Hi,

Here the txt file will be created in server. Is it possible to create a file in local machine?

Post #1072029

ZZartin

Posted Wednesday, March 02, 2011 10:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, February 11, 2013 2:30 PM
Points: 225, Visits: 350

The file will be created from the context of the sql server, so you should be able to write it to any drive or share the sql server has access to.

Post #1072051

Mad Myche

Posted Wednesday, March 02, 2011 10:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:49 AM
Points: 133, Visits: 413

A little more information would be nice as to what the big picture of this is... Is the parameter some data that needs to be retrieved from SQL? I could whip out a VBS script to hit the DB, grab the value, and pump it into the script. Powershell possibly?

Director of Transmogrification Services

Post #1072063

Steve-3_5_7_9

Posted Thursday, March 03, 2011 6:05 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 5:50 AM
Points: 810, Visits: 1,195

Why not use an SSIS package to generate the file?

Post #1072478

« Prev Topic | Next Topic »

Permissions