Output to file from proc

Question

Output to file from proc

mjschwenger

SSCarpal Tunnel

Points: 4019
More actions
August 5, 2005 at 9:43 am

#66480

Hi All,
I have a procedure for upgrade. It is usual run manually from QA. It's very important for the users to print what is doing, time for everything, errors(if any), Success-Fail. It works perfect, but now DEV wants to call it from some kind of a script - like upgrade script called from CMD. It's not app and it doesn't have UI for it. It'll print some message to the users, but cannot print the output of my proc. The proc will be called multiple times with different params as user input or pre-defined, by users choice. They want to keep the same output form the proc.
What will be the most efficiant way to print/save to file the old SP output in the new situation?
Thanks a lot for any idea.mj

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

If running from a scheduled batch job in a CMD window, convert the script to a stored proc, call it from OSQL, and use (> ) or the (-o) option to route the output to a file name. Then, have the batch email the output file to the users.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 2

No this will not work for me... The SP will be called from a cmd script among a lot of other stuff, which somebody else writes...still no idea what's inside. Is there a way to print the SP output, or to save it to file. I do not think that the script will be able to handle the SP output. Also, I cannot change the calling script provided.

Thanks a lot.

scking SSCrazy Points: 2598 More actions · Answer 3

I use the following proc that originally came from 4GuysFromRolla but modified it from isql to osql.

ALTER procedure dbo.xOutputToFile

(

@sServer varchar(30),

@sDB varchar(30),

@sUser varchar(30),

@sPWD varchar(30),

@sOutFile varchar(255),

@sSQL varchar(1000)

)

as

--From 4GuysFrom Rolla

--Demonstrate how to export a file from SQL Server

set nocount on

CREATE TABLE #trash (out varchar(1000) null )

declare @sXP varchar(1000),

@nMaxWidth int

select @nMaxWidth = 8000

select @sXP = 'osql' +

' -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

SELECT * FROM #trash