How to write the output of "print" messages to a file - path (c:\test.txt) ??

Question

How to write the output of "print" messages to a file - path (c:\test.txt) ??

thundersplash845

SSC Eights!

Points: 957
More actions
August 3, 2011 at 7:49 pm

#255162

Hi All,
Can you anyone tell me how to read the output of "print" messages to a file(c:\test.txt) ??
create PROCEDURE [dbo].[UserAcc]
@OldN varchar(256)
@NewN varchar(256)
AS
SET NOCOUNT ON
IF EXISTS (select fullname from master where fullname=@OldN)
Begin
update master set fullname=UPPER(@NewN) where fullname=@OldN
print 'Rows updated for fullname ' + CAST(@@ROWCOUNT AS NVARCHAR(255))
end
IF EXISTS (select moderator from master where moderator=@OldN)
Begin
update master set moderatorUPPER(@NewN) where moderator=@OldN
print 'Rows updated for Moderator ' + CAST(@@ROWCOUNT AS NVARCHAR(255))
end
output :
-------------------- (c:\test.txt) -------------------
Rows updated for fullname 1
Rows updated for Moderator 1

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

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

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

There is no answer to this question that will satiate the primordial fears of all the DBA's in the world. With that in mind, what will you allow? Ad Hoc queries (sp_OA*/OpenRowSet), a secure method of running xp_CmdShell (yes, more than one secure method exists), a special text based Linked Server, a CLR, executing the stored procedure from a DOS prompt (ie: script task using SSIS, etc), or just skip any of that and write to a log table?

Nope... not being sarcastic here. Every DBA has their threshold of pain on this very, very controversial subject. I just want to know which "pain" you will allow/tolerate so one of us can give you the best answer for this. 🙂

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

thundersplash845 SSC Eights! Points: 957 More actions · Answer 2

Hmmmm... i just want to print the output in a text file 🙁 ....any alternative way u could suggest??

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

You should be able to accomplish this using SSIS.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

thundersplash845 (8/4/2011)
Hmmmm... i just want to print the output in a text file 🙁 ....any alternative way u could suggest??

BWAA-HAA!!! Understood and very much appreciated. 🙂

SQL Server (T-SQL, really) isn't equipped real well for simply writing to files as you've requested. As I indicated, there are a whole lot of methods to do such a thing. Most folks have an absolute conniption if you give them a nice, simple xp_CmdShell method (even though it can be done quite easily and very securely), so I'll give you an "Ad Hoc" query method. It's a whole lot longer because of all the error checking you need to do along the way, but it certainly gets the job done.

As usual, the details of how it works are commented in the code. Please let me know how it works out for you. Don't forget to enable "Ad Hoc" queries to use it.

CREATE PROCEDURE dbo.WriteLineToFile

/**************************************************************************************************

Purpose:

Given a proper file name and message, write the message to the file name. The file will be

created if it doesn't exist.

Usage:

EXEC @Return = dbo.WriteLineToFile @pFullPath, @pMessage;

EXEC dbo.WriteLineToFile @pFullPath, @pMessage;

Return of 0 indicates success.

Return of -1 indicates failure and will be accompanied by a raised error and an error result set.

Revision History:

Rev 00 - 04 Aug 2011 - Jeff Moden

Built for - http://www.sqlservercentral.com/Forums/FindPost1153955.aspx

**************************************************************************************************/

--===== Declare the I/O parameters

(

@pFullPath VARCHAR(500),

@pMessage VARCHAR(500)

)

AS

--=================================================================================================

-- Presets

--=================================================================================================

--===== Suppress the auto-display of row counts to prevent false error returns to the GUI.

SET NOCOUNT ON

;

--===== Declare some obviously named local variables

DECLARE @FileExists TINYINT,

@FileHandle INT,

@FileMode INT,

@FSO INT,

@Return INT

;

BEGIN TRY

--=================================================================================================

-- Prepare the file to be written to

--=================================================================================================

--===== Create a "File System Object" to work with.

EXEC @Return = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT

;

-- Check for failure indication

IF @Return <> 0 OR @FSO < 0

RAISERROR ('File System Object could not be created.',16,1) WITH NOWAIT

;

--===== Check to make sure the file extension is ".txt" to prevent folks from messing up DLL's and

-- other important files.

IF RIGHT(@pFullPath,4) <> '.txt'

RAISERROR('Illegal file extension found.',16,1)

;

--===== Check to make sure there's no illegal characters in the file name.

IF @pFullPath LIKE '%[^A-Z0-9._ \:]%'

RAISERROR('Illegal characters found in file name.',16,1)

;

--===== Check to see if the file already exists or not and remember it

EXEC @Return = sp_OAMethod @FSO, 'FileExists', @FileExists OUT, @pFullPath

;

-- Check for failure indication

IF @Return <> 0

RAISERROR ('Existence check failed.',16,1) WITH NOWAIT

;

--===== If the file exists, remember to create it. Otherwise, remember to append it

SELECT @FileMode = CASE

WHEN @FileExists = 0 --File does not exist

THEN 2 --Create New

ELSE 8 --Append Existing

END

;

--===== Open the file in the correct file mode.

EXEC @Return = sp_OAMethod @FSO, 'OpenTextFile', @FileHandle OUT , @pFullPath, @FileMode, 1

;

-- Check for failure indication

IF @Return <> 0 OR @FileHandle < 0

RAISERROR ('Failed to open file.',16,1)

;

--=================================================================================================

-- Write the message to the file

--=================================================================================================

--===== If we made it to here, the file is open and ready. Write the message to the file.

EXEC @Return = sp_OAMethod @FileHandle , 'WriteLine' , NULL , @pMessage

;

-- Check for failure indication

IF @Return <> 0

RAISERROR ('Failed to write to file.',16,1)

;

--=================================================================================================

-- Housekeeping

--=================================================================================================

--===== All done. Close the file.

EXEC @Return = sp_OAMethod @FileHandle, 'Close'

;

-- Check for failure indication

IF @Return <> 0

RAISERROR ('Failed to close file.',16,1)

;

--===== Release the file handle

EXEC @Return = sp_OADestroy @FileHandle

;

-- Check for failure indication

IF @Return <> 0

RAISERROR ('Failed to release file handle.',16,1)

;

--===== Release the File System Object so we don't have connection/memory leaks

EXEC @Return = sp_OADestroy @FSO

;

-- Check for failure indication

IF @Return <> 0

RAISERROR ('Failed to release File System Object.',16,1)

;

END TRY

--|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

BEGIN CATCH

--===== Setup the error message for the outside world

DECLARE @ErrorMessage SYSNAME;

SELECT @ErrorMessage = ERROR_MESSAGE();

--===== Provide the error information

SELECT ErrorProcedure = ERROR_PROCEDURE(),

FileFullPath = @pFullPath,

ErrorMessage = ERROR_MESSAGE(),

ErrorNumber = ERROR_NUMBER(),

ErrorSeverity = ERROR_SEVERITY(),

ErrorState = ERROR_STATE(),

ErrorLine = ERROR_LINE(),

TranCount = @@TRANCOUNT

;

--===== If we still have a transaction going, ensure it gets rolled back.

IF @@TRANCOUNT > 0

ROLLBACK TRANSACTION

;

--===== Try to release resources one final time

EXEC sp_OADestroy @FileHandle;

EXEC sp_OADestroy @FSO;

--===== Raise an error to the outside world

RAISERROR(@ErrorMessage,16,1)

;

--===== Return an error indication to the outside world

RETURN -1; --Failure

END CATCH;

--|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

--===== If we made it to here, everything went well. Return "Success" to the calling proc.

RETURN 0; --Success (or "zero errors")

GO

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