December 2, 2011 at 12:47 am
hi,
how to write a .txt file from Sp.
I have used this but not working
exec master..xp_cmdshell 'echo appended data >> c:\New.txt'
could any one plz suggest,..
December 2, 2011 at 2:55 am
Create a SSIS package to populate the file, add the package to a job step and run sp_start_job.
Personally, I would try to avoid xp_cmdshell.
-- Gianluca Sartori
December 5, 2011 at 3:56 am
could u plz explain what u r trying to say..
December 5, 2011 at 3:58 am
Is writing File from sql server 2005 needed some rights for users,
I am not able to write a text file from Server 2005...
can any better suggestions that might work here
December 5, 2011 at 4:35 am
SQL Server will use the credentials of the connected user to access the external resources, unless you explicitly impersonate a different user. In the case of a Windows Login, you will try to write to disk using xp_cmdshell using the credentials of the Windows login. You can work around that using proxies.
When you use SQL Server Agent jobs, you can set up a proxy account to run job steps other than T-SQL. I see this as a much more secure approach to the problem.
That said, you can create a SSIS package that populates the file.
When the package is ready and working, you can add it to a SQL Server Agent job step.
From your stored procedure code, you can start the job using msdb..sp_start_job.
-- Gianluca Sartori
December 5, 2011 at 7:09 am
Alternate Solution: FileSystem Object (FSO)
Reading and Writing Files in SQL Server using T-SQL
December 16, 2011 at 10:22 am
very simple echo > creates a file echo >> writes to the next line.
declare @filename varchar(300)
declare @sql varchar(200)
declare @sql2 varchar(200)
set @sql = ' echo off > ' + @filename
exec xp_cmdshell @sql
set @sql ='write to a file '
set @sql2 = ' echo ' + @sql + ' >> ' + @filename
exec xp_cmdshell @sql2
December 16, 2011 at 11:39 am
and to read a file into a temp table
CREATE TABLE #file (
line varchar(255) null
);
INSERT #file EXEC master..xp_cmdshell 'type c:\downloads\createjobs.sql';
SELECT * FROM #file;
--drop table #file
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply