SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


create a stored procedure to create a txt file


create a stored procedure to create a txt file

Author
Message
river1
river1
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4480 Visits: 1366
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
Mike Seddon
Mike Seddon
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 36
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.
jain.ashish309
jain.ashish309
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 1
Is there any other way to create store procedure
Mad Myche
Mad Myche
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 520
You can use sp_OACreate to access the the File System Object (Active X)

reading & writing files in sql server

Director of Transmogrification Services
RamG
RamG
SSC-Enthusiastic
SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)SSC-Enthusiastic (115 reputation)

Group: General Forum Members
Points: 115 Visits: 110
Hi,

Here the txt file will be created in server. Is it possible to create a file in local machine?
ZZartin
ZZartin
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6167 Visits: 10232
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.
Mad Myche
Mad Myche
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 520
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
Steve-3_5_7_9
Steve-3_5_7_9
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2966 Visits: 1609
Why not use an SSIS package to generate the file?



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search