How to save stored procedure result to a file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to save stored procedure result to a file

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-10-27 : 20:59:31

I have a SQL 2008 stored procedure (see below) which returns results to the screen. I would like to have the results
to be saved to a specific file path on my server (\\SCGOTA04\MO_Data$\Outputs). I
would like to save the results with a filename of Report1_NewAccts.txt

Can someone help me with the code on how to save this to a file?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[testRPT1]
AS
BEGIN

SET NOCOUNT ON;

PRINT ' ------------------------------------------'
PRINT ' Report Name: TestRPT1 '
PRINT ' ------------------------------------------'
PRINT ''
PRINT 'RUN DATE: ' + CONVERT(VarChar, GETDATE())
PRINT ''
PRINT 'Provides a list of IDs '
PRINT ''

select count(*) as 'count',ID,ID_DESC,planning_acct,
SUM(INCOME) as 'Income Balance',
SUM(EXPENSE) as 'Expense Balance',
SUM(DEPOSIT) as 'Deposit Balance',
SUM(ACCRUAL) as 'Accrual Balances',

from GBC_db.dbo.bts_loans
where ID not in (select ID from GBC_db.dbo.static_ids)
group by ID,ID_DESC,planning_acct
order by ID,ID_DESC,planning_acct
END

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-28 : 00:18:01

you could use sqlcmd.exe or bcp.exe for this. sqlcmd may be preferrable as it will give you the column headers. no simple way to get the headers with bcp.

example cmd line:

sqlcmd -Q "exec testRPT1" -d mydatabase -S myserver -E -o \\SCGOTA04\MO_Data$\Outputs\Report1_NewAccts.txt

elsasoft.org

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-10-28 : 10:26:44

my apologies, but where would I enter this command. Do I place it after my end statement in my stored procedure?
I tried this, but it returned an error indicating "Incorrect syntax near 'Q' "

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-28 : 12:13:26

ah. sqlcmd is a cmd line app. you open a console window and run it from there. or you could put that command in a .bat file and run the .bat file.

see: http://msdn.microsoft.com/en-us/library/ms162773.aspx

elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-28 : 12:21:03

or from xp_cmdshell

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-10-28 : 20:58:27

thanks everyone. I was able to get it going.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources