'SQL Server- How to write variables and query results to Text file?

How do I write using T-SQL, variable values and query results to a text file, I want to actually keep appending.

sql-servertsqlsql-server-2008


Solution 1:[1]

You can use SQL CLR to write whatever you want out to a file. You can either code this yourself or you can use the SQL# library (I am the author and it is not free -- for this function -- but not expensive either) and use the File_WriteFile function to write out the text. It allows for appending data. The library is free for most functions but not for the File System functions. It can be found at: http://www.SQLsharp.com/

If you want to try coding this yourself, I found this example which is the basic concept I think you are asking for: http://www.mssqltips.com/tip.asp?tip=1662

Solution 2:[2]

This works but it's a little arcane. Adjust to your query. Essentially you'd be creating command shell echo lines for execution through xp_cmdshell and looping through them.

declare @cmd varchar(500), @minid int, @maxid int;
declare @output table(
    id int identity(1,1) primary key,
    echo varchar(500) not null
    )
set @cmd = 'select top 10 ''ECHO ''+name+'' >> C:\test.txt'' from master.dbo.sysobjects'


insert into @output(echo)
exec(@cmd)

select @minid=Min(ID),@maxid=Max(ID) from @output

while @minid <= @maxid
begin
    select @cmd = echo from @output where id = @minid;
    exec xp_cmdshell @cmd;
    set @minid=@minid+1;
end

Solution 3:[3]

Enabling and using xp_cmdshell exposes your instance to additional security risk. Using CLR is safer but also not advised for this purpose. These two options only make a poor design decision worse.

Why are you trying to write to a file from T-SQL? Resist the urge to turn SQL Server into a scripting platform. Use an application language like SSIS, PowerShell or C# to interact with SQL Server and write output to a log file from there as needed.

Solution 4:[4]

I had problem in this subject, so I want to propose what I did. I Declared a Cursor for Select results and then I write them to file. For example:

 DECLARE @cmd NVARCHAR(400)
 DECLARE @var AS NVARCHAR(50) 
         ,@Count = (SELECT COUNT(*) FROM Students) 
DECLARE Select_Cursor CURSOR FOR 
SELECT name FROM Students OPEN Select_Cursor 
 FETCH NEXT FROM Select_Cursor INTO @var 
WHILE (@Count>0) 
 SET @cmd = 'echo ' + @var +' >> c:\output.txt' 
 EXEC master..xp_cmdshell @cmd 
SET @Count = @Count - 1 
FETCH NEXT FROM Select_Cursor INTO @var 
END
 CLOSE SelectCursor
 DEALLOCATE  SelectCursor

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 artofsql
Solution 3 Orlando Colamatteo
Solution 4 user1971285

Related Questions