July 20, 2005 at 7:11 pm
Is it possible to save the results from a stored procedure to a text file?
Thank you
July 20, 2005 at 7:14 pm
where are you executing your procedure ?!
**ASCII stupid question, get a stupid ANSI !!!**
July 21, 2005 at 6:17 am
I downloaded this from 4GuysFromRolla a couple of years ago. They deserve the credit and I would recommend their website to anyone.
----------------------------
--From 4GuysFrom Rolla
--Demonstrate how to export a file from SQL Server
drop procedure dbo.xOutputToFile
go
create procedure dbo.xOutputToFile
@sServer varchar(30),
@sDB varchar(30),
@sUser varchar(30),
@sPWD varchar(30),
@sOutFile varchar(255),
@sSQL varchar(1000)
as
set nocount on
create table #trash (out varchar(1000) null )
declare @sXP varchar(1000),
@nMaxWidth int
select @nMaxWidth = 8000
select @sXP = 'isql' +
' -d' + @sDB +
' -U' + @sUser +
' -P' + @sPWD +
' -S' + @sServer +
' -h-1 ' +
' -w' + convert(varchar,@nMaxWidth) +
' -n' +
' -Q"' + @sSQL + '"' +
' -o' + @sOutFile
insert into #trash(out)
exec master..xp_cmdshell @sXP
go
---------END OF STORED PROCEDURE ---------
--Send results of a query to a text file (replace servername, username, password and this will query your PUBS.Authors table)
EXEC dbo.xOutputToFile 'servername', 'pubs', 'username', 'password', 'C:\trythis.txt', 'select au_fname, au_lname from authors'
--See the results
exec master..xp_cmdshell 'type c:\trythis.txt'
--- END OF PASTE ------------------
I hope this helps. It may not be exactly what your looking for, but it may provide a clue as to the solution you seek.
July 21, 2005 at 11:09 am
Sushila, I am executing the procedure in the query analyzer.
Thank you Johnson for the reply. I will work on the code that you sent and see if it helps.
Thank you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply