Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1335
  • Last Modified:

Capturing multiple SQL messages (rows affected etc) while executing a SQL statement using SSIS

When I run a query in SQL, I get a result set and another tab of Messages....I have a large stored procedure that I want to run in SSIS and I want to capture the messages window (not a result set) for review.

For example, if I run the following query:
Update test set testfield = 1 where datefield = '1/1/'2010'
Update test set testfield = 2 where datefield = '2/1/'2010'

I would like to capture the messages screen which might read:
(1551 row(s) affected)
(8773 row(s) affected)

I would like to save those two (in the real case thousands) messaage lines in a file so that I can save the messages for our records....and perhaps review them for error messages, warnings, etc.

Thanks!
0
ryanmaves
Asked:
ryanmaves
  • 5
  • 5
  • 2
  • +2
1 Solution
 
Amgad_Consulting_CoCommented:
Hi,

use "SET NOCOUNT ON;' before your query


regards
0
 
AmmarRCommented:
use the @@rowcount

http://www.brettb.com/SQL_Help_Rowcount_Rows_Affected.asp
Update test set testfield = 1 where datefield = '1/1/'2010'
SELECT @@rowcount AS 'RowsChanged'

Open in new window

0
 
EvilPostItCommented:
From a meta data perspective you will probably have to use the SET FMT_ONLY ON as well as the set nocount on.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Anthony PerkinsCommented:
If the author needs to capture the informational messages then they may be better off using:

SET NOCOUNT OFF
0
 
ryanmavesAuthor Commented:
I think my question may have been worded poorly.  The SQL script is running inside an "Execute SQL Task" within a SSIS package.  When I run the same SQL script within the MSSQL Server Management Studio, I get a tab labelled "Result Set" and a tab labelled "Messages."  The Messages tab contains thousands of lines of messages that range from "0 rows affected" to "Warning NULL value may affect summary..." to "You do not have permissions or table X does not exist"....whatever the messages are....I want them ALL (not just the row count).  I can't find where to get them (and thus store them) from WITHIN the SSIS package.
0
 
EvilPostItCommented:
I don't think you can actually do that. Plus the error messages will stop the ssis package.
0
 
ryanmavesAuthor Commented:
No, the won't stop the execution of the query, or the SSIS package if it is set up correctly.
0
 
EvilPostItCommented:
When you say setup correctly do you mean to set the accepted errors higher?
0
 
ryanmavesAuthor Commented:
Well, that is one way to accept the messages (accepted errors set higher)....but the entire script will finish and I can set the next step to start on completion rather than success.  We're kind of off-topic here.  I just want to catch the messages whether they be errors or not.
0
 
EvilPostItCommented:
I suppose you may be able to run this using osql from the command line and output that to a file and import that file but it's not a pretty solution....
0
 
ryanmavesAuthor Commented:
Ok...can you give me an example of how to do that?
0
 
EvilPostItCommented:
Have a look at this for the osql part then just import the file created.

http://coppockscoolness.blogspot.com/2008/02/osql-query-to-text-file-thanks-to-brain.html
0
 
Anthony PerkinsCommented:
>> I get a tab labelled "Result Set" and a tab labelled "Messages." ...<<
This is a function of the SSMS tool and as such does not exist in SSIS.  If you need that then you will have to build a .NET app to retrieve those messages.

>>No, the won't stop the execution of the query, or the SSIS package if it is set up correctly. <<
That depends.  It is true that Informational messages (such as "x number of rows affected") and Warning messages (such as "Warning: Null value is eliminated by an aggregate or other SET operation") do not cause the task to fail, Error messages (such as "Cannot find the object "%.*ls" because it does not exist or you do not have permissions") will cause the task to fail.
0
 
ryanmavesAuthor Commented:
Sorry for the wait.  Points are yours.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now