Hi,
I'd like to add a colunm that show me the information about who it is making the operation, for example
INSERT INTO testTable
SELECT ID, Name, Age, "Something . . ." as UserInfo FROM DAtaGeneral
WHERE . . . .
The "Something . . ." it is the SQL command that I need (User IP, Etc)
Thanks in advance for anu helpIf the user is making this call from a client machine it should be easy enough. You can use the user_name() function, provided each person has a separate login. This can get to be a pain to administer, unless you have them all on Windows Authentication.
If you have a website that is running this call, then it gets a lot trickier (and well out of my experience.) Good luck.|||You are limited to the niladic functions native to SQL server unless you set up an application-specific security system. For example, an application I developed required each user to login, after which they were given a session-specific security token which had to be submitted as a parameter to all future function calls. This made it easy to track who was doing what through any interface they might use.
blindman|||Thanks User_Name() was the solution|||Yeah, but as the blide dude suggests, it may not be that simple?
How does your application connect to the database?
Do you have an applciation, or are you just trying to bust some bs developers?|||Originally posted by Brett Kaiser
Yeah, but as the blide dude suggests, it may not be that simple?
How does your application connect to the database?
Do you have an applciation, or are you just trying to bust some bs developers?
The application is COM+ dll that it is call (instantiated) from another apllication that runs on IEclient Intranet App.
I want to know (log) Who is making a Query And From Where it is make, this is very important due to the cleint aplication is a intranet application. Client are fromn different parts of the oiraganizacion and I could'nt catch correctly the logging information.
thanks
Showing posts with label catch. Show all posts
Showing posts with label catch. Show all posts
Monday, March 19, 2012
How to catch stored procedure execution time?
Hello, everyone:
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:
Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.
ZYT
The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||Below batch query might help you to determine the performance in secounds
declare @.startproc datetime
declare @.endproc datetime
declare @.time integer
select @.startproc = getdate()
exec <stored procedure>
select @.endproc = getdate()
select @.time = DATEDIFF(second, @.startproc, @.endproc)
print str(@.time)|||Pat:
Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.
ZYT
Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!
Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.
I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!
As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).
The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!
-PatP|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.
Thank
ZYT
I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning
As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...
Logging the sproc call from code would not give you a true length due to other resources
I find that log so useful in so many ways
What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.
Well that's pure bull sheet
Blame it on profiler
How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:
By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.
Thanks
ZYT
No experience but Scenario 1in the paper describes exactly this.|||Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH|||Pootle:
I got it.
SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)
can work. or refer http://support.microsoft.com/kb/270599.
Thanks everyone. This is a good post.
ZYT
Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH
For performance issue, I need to catch the stored procedure execution time. Any suggestion will be appreciated. Thanks.
ZYTThe most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||Pootle:
Thanks for reply. The problem is I am not allowed to change stored procedures and use profiler. I am going to setup a group of queries or stored procedures by which insert the execution time to a record table.
ZYT
The most flexible & powerful method is to insert code into your sprocs that record the start and end times, as well as any other information you want to measure.
Otherwise you need to start using Profiler - you can find information about its use in Books Online.|||The problem is I am not allowed to change stored procedures and use profiler.Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||Below batch query might help you to determine the performance in secounds
declare @.startproc datetime
declare @.endproc datetime
declare @.time integer
select @.startproc = getdate()
exec <stored procedure>
select @.endproc = getdate()
select @.time = DATEDIFF(second, @.startproc, @.endproc)
print str(@.time)|||Pat:
Thanks for reply. You are right, I got sticky stats. The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.
ZYT
Time a run of your program. Subtract out all of the time it spends doing other things. Whatever time remains, is probably used by the stored procedure.
As you'll probably observe, this is impossible. Then again, measuring something when you are not allowed to measure it isn't possible either. This is like debating how many angels can dance on the head of a pin... You've been placed in a "no win" situation.
-PatP|||The boss is worried profiler slower production server, and developers don't like to change stored procedures. This is why I am asking another way.You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!
Do you use source control? If so you could write a script to parse the files and retro fit execution logging information. Verify everything on your test server. Devs don't need to lift a finger.
I agree with Pat though - you are not being given enough latitude to perform your task as things stand.|||I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||You would run profiler for a few hours on a different machine. Save the results to a file not a table. This is the most efficient way to use profiler and I would be surprised if you could notice any discernable difference on your prod server. Just never run the profiler app on the prod server!Yeah, what poots said!
As long as you run the profiler on a different machine, the only additional load you place on the SQL Server is the transmission of the profiler data. This is negligable (always less than 2 percent, normally much less than 1 percent in terms of performance of the SQL Server).
The only exception to this rule is if your SQL Server is severly "network bound" so that the NIC is flooded. If that is the case, the SQL processing will nearly halt immediately because the profiler will also flood the NIC. This is easy to check for using either Task Manager or Performance Monitor, and you'll find out nearly instantly when you turn the Profiler on if you forget!
-PatP|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company. So I cannot argue about that. I got the paper you recommend and want to know if someone has experience to catch execution time by this paper.
Thank
ZYT
I've never bothered - profiler run on another machine has never been detrimental enough to worry me - but you might find this interesting:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm|||No experience but Scenario 1in the paper describes exactly this.|||poor planning
As part of all development I make sure that every sproc contains code, outside of any transaction, to log the length of the sproc to a table...
Logging the sproc call from code would not give you a true length due to other resources
I find that log so useful in so many ways
What developers suck at coding, what developers aren't coding, and when it goes to prod, what sprocs need to be tuned...but I've already noticed that in dev, to the point where I don't need to do the logging|||Hi, Pootle:
Thanks for advice. The key point is there is a record shows production server was shut down by a profiler running from another machine in my company.
Well that's pure bull sheet
Blame it on profiler
How about blame it on the guy who set it up? What did he do, set it up let it run forever and fill up the disk...puuuleeeeze|||Pootle:
By the paper you recommended, I do catch the execution time of stored procedures for the given database. The execution time is output to a .trc file (SQL profiler-trace data file). So there is another question for you. Is it possible to save a .trc file to be a table by T-SQL, and how? I can use profiler to open .trc file and save as a table, but someone prefer to do that automatically.
Thanks
ZYT
No experience but Scenario 1in the paper describes exactly this.|||Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH|||Pootle:
I got it.
SELECT * INTO trace_table FROM ::fn_trace_gettable('c:\test.trc', default)
can work. or refer http://support.microsoft.com/kb/270599.
Thanks everyone. This is a good post.
ZYT
Hi
Just quickly logged on from home - you use can import\ export wizard. I'll need to check after the weekend when I get back to work for what I have there (can't remember lol). You could of course try googling - I am certain there are loads of things out there to get it
HTH
How to catch sql exceptions gracefully when deleting some records
I use the following function (in the BLL) to delete some records:
PublicFunction DeleteStep4Dashboards()AsBoolean
Try
adpDashboards.DeleteStep4Dashboards()
Catch exAs ExceptionReturnFalse
EndTry
ReturnTrue
EndFunction
How can I catch the sql database errors when deleting the records goes wrong.
You can add a Catch for a SqlException e.g.
Catch sqlExAs SqlException|||
You can specify the type of the exception that you want to catch, e.g. Catch ex as SqlException (rather than as Exception).
However, catching exceptions and returning a value indicating success or failure is in general bad coding style. An exception means that something has gone wrong with what you are trying to do, and you either need to take some remedial action such as correcting parameters and retrying, or report it to the user and/or administrator.
Returning false or true puts the onus of detecting and handling errorconditions on all your method's callers, and there could be several ofthose scattered through your code, making it difficult to maintain. On the other hand, throwing an exception means that your callers only need to write code to handle the error condition if they are actually able to do something about it. Otherwise they need to be allowed to bubble up to the top layer so that they can be logged and/or the user can be notified that something went wrong.
|||Another option is to put the delete into a stored procedure and handle it there -- in fact, you may be able to prevent any errors by checking the state of your data first to make sure the delete will work before actually trying it (eg, if a foreign key might prevent the delete)
|||Is it really best practise to bubble up the error.
Can't I show an general error message to the user.
And handle the error (logging, e-mail to administrator) in the BLL?
|||
JohanNL:
Is it really best practise to bubble up the error.
Without meaning to answer for James, I would say that you be misundestanding what he is suggesting. As he wrote, the rule of thumb is to catch an exception only if you intend to do something about it, and that may mean logging the error and showing the user a general message. I would say that it's an application specific decision about where you want to log the errors, but if you think about it, it's not worth the effort to put try...catch everywhere if all you're going to do is log it and move on. OTOH, updates often cry out for special handling in the event of a sql error, especially since it's often helpful to catch an error right where it happens.
The best thing to do, of course, is to anticipate every possible error and code in such a way that you prevent them from happening. If you think you might get a divide by zero exception, for instance, do the check before attempting the division and give the user a message. I do this in stored procedures all the time. However, if you could think of everything you probably wouldn't need an exception log anyway<g>
How To Catch Output from SP / Function
I want to catch
the resultset from a stored procedure
or
a table output parameter from a stored procedure
or
a table output from a function
into
a varchar variable.
Any tip?
Not possible?
(it is for mailing the result from a query)
/k"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
You cannot have a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
Not Possible.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||You cannot do that.
You can catch the output from a SP into a #Table. Thats the max you can do.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
> I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||On Fri, 25 Feb 2005 09:35:12 +0100, kurt sune wrote:
(snip)
>Any tip?
>Not possible?
Hi Kurt,
Roji and Vinod already informed you that what you want is not possible.
>(it is for mailing the result from a query)
But if that's what you want, then you should check out xp_sendmail in
Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
the resultset from a stored procedure
or
a table output parameter from a stored procedure
or
a table output from a function
into
a varchar variable.
Any tip?
Not possible?
(it is for mailing the result from a query)
/k"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
You cannot have a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
Not Possible.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||You cannot do that.
You can catch the output from a SP into a #Table. Thats the max you can do.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
> I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||On Fri, 25 Feb 2005 09:35:12 +0100, kurt sune wrote:
(snip)
>Any tip?
>Not possible?
Hi Kurt,
Roji and Vinod already informed you that what you want is not possible.
>(it is for mailing the result from a query)
But if that's what you want, then you should check out xp_sendmail in
Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
How to catch messages on Event Handlers
hello everyone
I'd like to know if there is a way to catch the error messages when a tasks fails, that's because i's like to store every message on a user variable, so i could log all of them later, I was thinking that it may be possible with the event handlers, could it be?
regards
Yes. When an executable errors the error message is in the @.[System::ErrorDescription] variable scoped to the OnError eventhandler.
-Jamie
|||Thanks Jammie, i'll try to do so.
best regards
How to catch io's for tsql execution.
Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.
You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.
You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>
How to catch io's for tsql execution.
Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>
How to catch io's for tsql execution.
Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system view
s.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system view
s.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>
How to catch errors
I have an statement
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
Thanks
On Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
Thanks
On Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?
How to catch errors
I have an statement
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
ThanksOn Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
ThanksOn Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?
How to catch errors
I have an statement
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
ThanksOn Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?
SELECT * from lkSFSAHousing where id = @.answer
Problem is that id is int and some times @.answer is not int
how would I catch either the error or not even get into not calling
this statement if @.answer is not digit (1 or 2 or 1000)
Can anybody help?
ThanksOn Feb 8, 11:59 am, "Sehboo" <MasoodAd...@.gmail.com> wrote:
> I have an statement
> SELECT * from lkSFSAHousing where id = @.answer
> Problem is that id is int and some times @.answer is not int
> how would I catch either the error or not even get into not calling
> this statement if @.answer is not digit (1 or 2 or 1000)
> Can anybody help?
> Thanks
Ideally, you should have some validation in place at the point where
@.answer is assigned a value. Where/how is this being done?
How to catch error and retry AFTER dts script step has ran
hello,
i am trying to figure out how to check for failure or success AFTER the script task has ran.
its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?
i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.
dts does not seem to have this one specific piece of functionality. am i overlooking something?alright... i am closer to figuring this out although the solution seems a bit complex.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6196&pg=2
the WROX book that i bought on DTS "DOES NOT EVEN COVER THIS TOPIC" . it covers reactive error handling but doesn't say a word about proactive error handling.
i have a java programming background. can anyone see this from my point of view and give me a hint?
this reminds me of using the "onStart" and "onLoad" methods of ASP programming but it doesn't seem to be quite as simple to use...
i am trying to figure out how to check for failure or success AFTER the script task has ran.
its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?
i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.
dts does not seem to have this one specific piece of functionality. am i overlooking something?alright... i am closer to figuring this out although the solution seems a bit complex.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=6196&pg=2
the WROX book that i bought on DTS "DOES NOT EVEN COVER THIS TOPIC" . it covers reactive error handling but doesn't say a word about proactive error handling.
i have a java programming background. can anyone see this from my point of view and give me a hint?
this reminds me of using the "onStart" and "onLoad" methods of ASP programming but it doesn't seem to be quite as simple to use...
How to catch an exception?
Is there something like exception handling in T-SQL?
For example, how to catch an error of convertion at this
sample:
For example, how to catch an error of convertion at this
sample:
CREATE PROCEDURE SP
@.param VARCHAR(50)
AS BEGIN
DELCARE @.var INT
-- try {
SET @.var = CONVERT( int, @.param)
-- } catch (error#245) {
-- handle an error right here
-- }
END
It must be invisible for a caller of SP if something wrong inside SP.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!>
> CREATE PROCEDURE SP
> @.param VARCHAR(50)
> AS BEGIN
> DELCARE @.var INT
> -- try {
> SET @.var = CONVERT( int, @.param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
In this situation you can use ISNUMERIC function.
In T-SQL there are not try..catch constructions and all errors you will get
on client :(.
ALTER PROCEDURE SP
@.param VARCHAR(50)
AS BEGIN
DECLARE @.var INT
-- try {
if ISNUMERIC(@.param) = 0
begin
RAISERROR('Error converting @.param -> @.var',16,10)
RETURN -1
end
SET @.var = CONVERT( int, @.param)
END
go
exec SP
@.param = '1a'
go|||Hi, Garry!
Thank you for your answer but my question was not about how to suppress
exactly convertion error. I'm looking for something like try-catch. Is
it truth that no way to handle an exception inside the server execution?
It is sad...
Ok, my problem is that: some of my procedures are able to generate both
correct rowset and some error messages at the same time. But when I try
to open the query with EXEC thru OLE DB I receive an error, not rowset
:( The best issue for me: if I would be able to handle all the errors
inside the stored procedure body...
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Evgeny Gopengauz (evgop@.ucs.ru) writes:
> Is there something like exception handling in T-SQL?
> For example, how to catch an error of convertion at this
> sample:
> CREATE PROCEDURE SP
> @.param VARCHAR(50)
> AS BEGIN
> DELCARE @.var INT
> -- try {
> SET @.var = CONVERT( int, @.param)
> -- } catch (error#245) {
> -- handle an error right here
> -- }
> END
> It must be invisible for a caller of SP if something wrong inside SP.
For SQL2000 the answer is very distinctively: NO. Error handling in
SQL Server 2000 is a mess. There are two articles on my web site about
the topic http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html.
The good news is that in the next version of SQL Server, SQL 2005 which
now is in beta, there are great improvements in this area, and there
is indeed a TRY-CATCH construct.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Garry (vadim@.viii.ntu-kpi.kiev.ua) writes:
> In this situation you can use ISNUMERIC function.
No, you can never use the isnumeric() function, because it is
virtually useless. isnumeric() tells you that a string can be
converted to some numeric data type, but you can find out which. A
string that can be converted to money may not convert to float or
vice versa.
For test of a positive integer number, this is the way to do:
@.x NOT LIKE '%[^0-9]%'
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)