We help IT Professionals succeed at work.

capture messages into flatfile from SSMS

When you run a query, you get a Results tab and also one called Messages:

Is there any way the 'Messages' values can be stored in a flat file automatically, upon execution, through code?

thanks
Comment
Watch Question

If you right clict at SQL editor window and select "Results To file" from context menu then messages appear in the output file. The standard message "Command(s) completed successfully." is not included because it is not SQL message but SSMS info message.
Top Expert 2012
Commented:
Perhaps you should know that the "Messages" tab is SSMS specific and there is no option to do that.  You should also understand that it shows the number of rows affected if the query was successful and the you do not have SET NOCOUNT ON, or any error messages if the query was not successful.

Author

Commented:
OK.

Let me mention what my real purpose in asking this:

The query is executes, but also gives a bunch of warnings, if you will, something like
Record does not exist, LEN:2000043 Pack ID: 3476 Packer ID: OPER Master Key: 35236

We right now scan this after every run. it would be nice to capture this part of the job, and have someone analyze it every day after the job has run.

any thoughts to circumvent this?

thanks
I would propose better solution:

Record each step result into some log table. This table should also contain a flag saying if the action was successful or not. Unsuccessful actions may be recognized different ways, e.g. by number of processed/created/deleted records, by TRY CATCH for more severe problems etc.

Then you don't need someone to analyze the log and program even may send an e-mail if something went wrong in the process. You may also send statistics claculated from correctly processed data.

Managers will love you.

Author

Commented:
pcelba,

i appreciate your input.

i dug in and found out that the error message is part of the SP that is run within the package. please see attached code. the foll code is within a cursor.

as you can see this is not a total failure.. but the programmer wrote in a way the records could be checked later to check and fix the data issue. how would you recommend in this situation?
IF (@RECORDFOUND = 1)		
BEGIN
					BEGIN TRANSACTION 
					UPDATE STATEMENT
					IF @@ERROR <> 0
						ROLLBACK
					ELSE
						COMMIT   -- Success..
			END
ELSE
				PRINT 'Record does not exist, LEN:' + @LEN + ' Pack ID: ' + @PACK_ID + ' Packer ID: ' + @PACKER_ID + ' Master Key: ' + str(@MASTER_KEY)
		

Open in new window

So, when you send the output to a text file in SSMS it will contain PRINT output also. To find certain text in text file ("Record does not exist") should not be problem.

Author

Commented:
are you envisioning the log table idea - for this SP in a package/job?

instead of PRINT, should I use an insert statement to capture the line to the log table?
Yes, exactly.

Author

Commented:
Thanks very much, pcelba.
You are welcome.