How can I save data in the Text File by SQL scripts (for logs)

Question

1.00/5 (1 vote)

See more:

Hey Guys.

I want to save a string in the text file by using the SQL Scripts.

Example : I have a string 'abcdefghijklmnopqrstuvwxyz', I want to save this string the text file in the particular location by using the SQL scripts.

Also i want to check that if there is a file already exist (@ advised location) with the same name.

Can you please help me to solve this query.

Posted 13-May-14 5:05am

Yash Goyal

Add a Solution

Comments

Maciej Los 13-May-14 14:44pm

What have you tried? Where are you stuck?

Yash Goyal 14-May-14 5:24am

Hi Maciej,

When we execute the below code then we got response NULL.

DECLARE @Text VARCHAR(MAX)
,@filelocation VARCHAR(MAX)
,@cmd VARCHAR(100)
SET @Text = 'abcdefghijklmnopqrstuvwxyz'
SET @filelocation = 'D:\out_vid.txt'
-- SET @cmd = 'bcp ' + @querytext + ' queryout ' + @filelocation + ' -T -c -U'
-- EXEC master..XP_CMDSHELL @cmd
SET @cmd ='echo ' + @Text + ' > D:\out_vid.txt'
EXECUTE Master.dbo.xp_CmdShell @cmd

Also no file generate and save @ the location.

Can you help

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2014-05-13T08:49:00

Solution 1

Here is code you can use
1) To create and write txt file
2) To check whether file exists

SQL

DECLARE @Text VARCHAR(MAX)
,@filelocation VARCHAR(MAX)
,@cmd VARCHAR(100)
    SET @Text = 'abcdefghijklmnopqrstuvwxyz'
    SET @filelocation = 'D:\out_vid.txt'
--    SET @cmd = 'bcp ' + @querytext + ' queryout ' + @filelocation + ' -T -c -U'
--    EXEC master..XP_CMDSHELL @cmd
SET @cmd ='echo ' +  @Text + ' > E:\out_vid.txt'
EXECUTE Master.dbo.xp_CmdShell  @cmd

DECLARE @isExists INT
exec master.dbo.xp_fileexist 'E:\out_vid.txt', 
@isExists OUTPUT
SELECT case @isExists 
when 1 then 'Yes' 
else 'No' 
end as isExists

Posted 13-May-14 8:49am

RDBurmon

Comments

Yash Goyal 14-May-14 5:44am

Hi All thanks for your Help and teaching me.

Can you also please advise me the uses of Internal Procedures Examples : Sp_oacreate, Sp_oadestroy, sp_OAMethod, sp_OADestroy.

I want to use them when i will write the code to call the web services.

RDBurmon 14-May-14 13:55pm

These links would be enough to understand the concept
Sp_oacreate
http://technet.microsoft.com/en-us/library/aa238863%28v=sql.80%29.aspx
Sp_oadestroy
http://technet.microsoft.com/en-us/library/ms173866.aspx
sp_OAMethod
http://technet.microsoft.com/en-us/library/ms174984.aspx

But if still you need any information then reach out to me with an example where you want to use these function :)

Maciej Los 14-May-14 11:46am

+5