Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Capturing multiple SQL messages (rows affected etc) while executing a SQL statement using SSIS
Posted on 2010-11-11
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!
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!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
2- +2
14 Comments
use the @@rowcount
http://www.brettb.com/SQL_Help_Rowcount_Rows_Affected.asp
http://www.brettb.com/SQL_Help_Rowcount_Rows_Affected.asp
Update test set testfield = 1 where datefield = '1/1/'2010'
SELECT @@rowcount AS 'RowsChanged'
From a meta data perspective you will probably have to use the SET FMT_ONLY ON as well as the set nocount on.
If the author needs to capture the informational messages then they may be better off using:
SET NOCOUNT OFF
SET NOCOUNT OFF
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.
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.
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....
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
http://coppockscoolness.blogspot.com/2008/02/osql-query-to-text-file-thanks-to-brain.html
>> 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.
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.
Featured Post
![[Live Webinar] The Cloud Skills Gap](https://filedb.experts-exchange.com/files/public/2017/5/29/3249c5dc-214e-4f8f-afab-ef354630e19f.png)
As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.
Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash"
After Shake : http://www.videocopilot.net/presets/after_shake/
All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 8 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.