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: Yesterday @ 4:09 AM
Points: 761, Visits: 1,083
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 9, 2010 4:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 8, 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 7, 2010 3:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 7, 2010 3:51 AM
Points: 1, Visits: 1
Is there any other way to create store procedure
Post #1000250
Posted Friday, October 8, 2010 8:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:48 AM
Points: 138, Visits: 456
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 2, 2011 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 4, 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 2, 2011 10:17 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 421, Visits: 1,461
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 2, 2011 10:31 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, November 21, 2014 9:48 AM
Points: 138, Visits: 456
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 3, 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: Tuesday, December 2, 2014 5:58 AM
Points: 924, Visits: 1,445
Why not use an SSIS package to generate the file?




Post #1072478
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse