create a text file on a network folder using stored procedure and SQL server agent?

How do I use an existing SQL stored Procedure to schedule  and create a file on a particular network folder? trying not to use the SSIS.
Abdul KhanAsked:
Who is Participating?
 
MishaProgrammerCommented:
There is an article about creating files with SQL stored procedure:
http://www.kodyaz.com/t-sql/bcp-and-sp_helptext-to-script-store-procedures-into-seperate-files.aspx
You can use this code to create text file:
declare @cmd varchar(1000)
declare @filepath varchar(500) = 'c:\MyFolder\TextFile.txt'
SET @cmd = 'bcp "select 1 as test" queryout "' + @filepath + '" -c -UTF8 -T -Slocalhost'
EXEC master..xp_cmdshell @cmd

Open in new window


Put  path of your network folder in variable  @filepath
0
 
ste5anSenior DeveloperCommented:
Can you elaborate the use-case and context. It's pretty uncommon to do that. Especially as you need network privileges in this case, which is a security issue.

The "normal" approach without SSIS would be using a scheduled task using either PowerShell script or a command line batch using SQLCMD.
0
 
Abdul KhanAuthor Commented:
The above code worked fine. thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.