Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create a stored procedure to create a txt file Expand / Collapse
Author
Message
Posted Wednesday, April 21, 2010 3:37 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:25 AM
Points: 715, Visits: 1,018
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
Posted Friday, July 09, 2010 4:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 8:32 AM
Points: 13, Visits: 34
I can't believe this is the slickest way to achieve what you're after, but this works

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
Posted Thursday, October 07, 2010 3:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, October 08, 2010 8:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 136, Visits: 440
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
Posted Wednesday, March 02, 2011 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 04, 2013 7:54 AM
Points: 35, Visits: 110
Hi,

Here the txt file will be created in server. Is it possible to create a file in local machine?
Post #1072029
Posted Wednesday, March 02, 2011 10:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, January 09, 2014 9:16 AM
Points: 231, Visits: 391
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
Posted Wednesday, March 02, 2011 10:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:00 PM
Points: 136, Visits: 440
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
Posted Thursday, March 03, 2011 6:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 5:22 AM
Points: 881, Visits: 1,352
Why not use an SSIS package to generate the file?




Post #1072478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse