August 5, 2005 at 9:43 am
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
August 7, 2005 at 4:14 pm
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
Change is inevitable... Change for the better is not.
August 8, 2005 at 9:10 am
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.
August 8, 2005 at 9:11 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply