November 12, 2013 at 7:50 am
Currently we execute most of our SQL by executing a bat file that we can direct the execution log information to a .log file(see example below). This comes in very handy when you get that call in the middle of the night that a SQL step has failed. If I can see the results below easily it will help me know where and why it failed. Can I get this type of 'log' info if this had been executed as a stored procedure? Knowing that the third 'step' below returned zero records could be huge in what needs to be done to correct the problem. I'm just wanting to avoid having to rerun parts or all the SQL to find the error.
(50289 row(s) affected)
(49759 row(s) affected)
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 19
Divide by zero error encountered.
In our shop almost all of our processing is batch driven, runs at night, almost nothing has to return real time type info. So while the speed of these processes getting done is important, I don't know that having them as stored procedures is going to help that much. Yes I understand it could be faster, but how much? No one here has tested that. And yes I have been warned about the executing the SQL with a bat file, but after 10+ years we haven't run into an issue.
As a developer I prefer to keep it the way it is, DBA's are pushing for stored procedures. I feel that since I, and others on my team, are the ones that will get the call in the night and have to fix the problem this 'log' information is critical. Now if I can get the same detail from a stored procedure then I will gladly change. But from other posts I've read I don't think this is possible.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
November 12, 2013 at 8:29 am
Unless you create your own logging infrastructure, no. SQL does not keep execution history.
Now, you can create it as a stored procedure and call the procedure from your batch file....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2013 at 8:44 am
Thanks, Gail. That is what I was thinking but wanted some else's opinion.
If I execute the stored procedure from the bat file will I still be able to direct the log info to a file?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
November 12, 2013 at 9:03 am
If you just replace the ad-hoc SQL with an EXEC <procedure>, you'll get the same information logged as you currently are getting.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply