Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Any way to see or view the results of an execute sql task ? Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 9:47 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
Luis Cazares (10/30/2013)
below86 (10/30/2013)
So until someone can tell me how I can see this same type of 'log' information for an execution of a SQL job, I will not be using the execute SQL task.

If you just want to know where and why the task failed, you could use SQL Server error handling to get an even better error log. To avoid changes on the SSIS package, you could simply use Stored Procedures instead of the full query.
I'm not sure that your way is the way to go, but if it works for you, then I can't suggest you to stop using it, just wanted to give some ideas.


I know how to tell where my SSIS package failed it's just finding out where in the SQL it failed and what error it gave. And from what I've done, granted not a lot, I don't see that I can get that detail from the execute SQL task. If you have a 1,000 line SQL file you are executing with say 20 individual SQL 'steps' within it. I need to know that SQL 'step' 10 failed for 'duplicate key' or whatever the error is. My log file will give me that and I can also see how many rows were processed by each step. I haven't done much with stored procedure so I am not aware of any 'log' that is created when it is executed. Where/and how would I find that information? Does the stored procedure need to be set up in any special way for this to happen? Or is is a setting on the database or server?
Post #1509850
Posted Wednesday, October 30, 2013 9:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 3,374, Visits: 7,301
I wasn't saying that stored procedures save a log automatically, but are a simple way to manage your T-SQL code without touching the SSIS packages.
For the error logging, take a look at TRY...CATCH documentation. It might seem to be more work to do, but it's more flexible than normal logging.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1509851
Posted Wednesday, October 30, 2013 10:18 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
Luis Cazares (10/30/2013)
I wasn't saying that stored procedures save a log automatically, but are a simple way to manage your T-SQL code without touching the SSIS packages.
For the error logging, take a look at TRY...CATCH documentation. It might seem to be more work to do, but it's more flexible than normal logging.


Sorry I miss understood your prior post. I'll keep the TRY..Catch in mind, but with thousands of individual SQL jobs out there it would be a long time to migrate to that. I was just hoping there was something out there that I wasn't aware of. Thanks.
Post #1509858
Posted Wednesday, October 30, 2013 10:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 15, 2014 8:51 PM
Points: 183, Visits: 480
I am doing only select statements.

Response "to below86" - regarding execute SQL indirectly by executing a bat file which executes SQL - I am trying something similar now. Need your help to figure it out.

I made posts on it here and haven't got much help so far -

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dbcdd59d-469d-49db-a33e-f554d5f915c3/need-help-to-make-sqlcmd-run-as-per-requirements?forum=sqltools#cbff0250-8809-4708-9eb4-f0622360f887

http://stackoverflow.com/questions/19674377/need-to-understand-output-of-sqlcmd-exe
Post #1509870
Posted Wednesday, October 30, 2013 10:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, July 27, 2014 2:42 PM
Points: 123, Visits: 321
Hi mate, if you are only doing select statments then my solution would work...
Post #1509873
Posted Wednesday, October 30, 2013 10:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
We have the following in a bat file:

REM This BAT file uses the userid and password of the account that is running the BAT file.
REM %1 = SQLServer name
REM %2 = database
REM %3 = path to .sql and .log files folder
REM %4 = file name w/o the file extension for .sql and .log files


osql -S %1 -E -d %2 -i "%3%4.sql" -o "%3%4.log" -b

We have another version that requires username and password.

In SSIS we send the 'varaiables' this needs in the Arguments part of the execute process task and the Executable portion points to this bat file.

Let me know if you need more than this.
Post #1509878
Posted Wednesday, October 30, 2013 1:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
below86 (10/30/2013)
...
osql -S %1 -E -d %2 -i "%3%4.sql" -o "%3%4.log" -b
...


Just for your information:
osql is deprecated (and it has some issues. I remember it not working with utf8 files are something like that.)
You can replace it with sqlcmd.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1509922
Posted Wednesday, October 30, 2013 1:33 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
Koen Verbeeck (10/30/2013)
below86 (10/30/2013)
...
osql -S %1 -E -d %2 -i "%3%4.sql" -o "%3%4.log" -b
...


Just for your information:
osql is deprecated (and it has some issues. I remember it not working with utf8 files are something like that.)
You can replace it with sqlcmd.


Thanks for the info, I'll keep it in mind if we start to see issues.
Post #1509924
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse