[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

How to log execution time of a stored procedure ?

Hello !

I want to log in a file the execution time of a stored procedure.
So my idea was to do something like

1. Create a sql file like

DECLARE @BeginProcDate datetime
DECLARE @duration datetime
       
        EXEC myStoredProc

set @duration = @BeginProcDate - getdate()
select CAST(DATEPART(ss, @duration) AS VARCHAR(2)) + '.' + CAST(DATEPART(ms, @duration) AS VARCHAR(3))

2. Use a bat file :

osql -S xxx -U sa -P sa -i "Exec sp.sql" -n -o "Output.txt"
Copy mainfile.txt + outpu.txt mainfile.txt

I was using the latest line to concatenate the result.
It is working fine but the problem is that in the output file, I also have the stored procedure result, ie 400 records, which make the file unreadable.
So I modify my sql file to add SET FMTONLY around my EXEC command.
Problem, my stored procedure is using a temp table and so it doesn't work anymore.

Does anyone have an idea ?
0
Jarodtweiss
Asked:
Jarodtweiss
  • 7
  • 7
1 Solution
 
muzzy2003Commented:
Put a SET NOCOUNT ON at the top of your stored procedure to prevent the rowcount being returned, instead of SET FMTONLY.
0
 
JarodtweissAuthor Commented:
It was already the case.
Btw, the stored procedure I call in my sql file is an already existing one, and I would prefer not to change it
0
 
muzzy2003Commented:
Then you can create this SQL file instead:

DECLARE @BeginProcDate datetime
DECLARE @duration datetime

SET NOCOUNT ON
       
        EXEC myStoredProc

set @duration = @BeginProcDate - getdate()
select CAST(DATEPART(ss, @duration) AS VARCHAR(2)) + '.' + CAST(DATEPART(ms, @duration) AS VARCHAR(3))
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
JarodtweissAuthor Commented:
It is still the same.
I still have both the resultset and the time ! (in Query Analyser and with my bat file)
0
 
JarodtweissAuthor Commented:
or maybe a trick would be to encapsulate the execution of the stored procedure in a SELECT COUNT(*) for instance, so he will return us only the count instead of the complete resultset.
Is this possible ? doing a SELECT COUNT(*) FROM (EXEC myproc) or somethig like that ?
0
 
muzzy2003Commented:
Oh, sorry - you mean the stored procedure returns 400 records. I thought you were saying it returns a "400 records affected" type result. Hold on - let me think.
0
 
muzzy2003Commented:
Somthing like this work? Can you use xp_cmdshell, or is it prohibited?

DECLARE @BeginProcDate datetime
DECLARE @duration datetime
DECLARE @command varchar(8000)
       
        EXEC myStoredProc

set @duration = @BeginProcDate - getdate()
set @command = 'xp_cmdshell ''echo ' + CAST(DATEPART(ss, @duration) AS VARCHAR(2)) + '.' + CAST(DATEPART(ms, @duration) AS VARCHAR(3)) + ' > C:\Output.txt'
EXEC (@command)
0
 
JarodtweissAuthor Commented:
Well... I think it is the correct direction to search...
But it doesn't work properly....
Here are the tests I have performed:

xp_cmdshell 'dir *.exe'
--> gives me the content of WINNT/System32

xp_cmdshell 'echo Test'
--> gives me 2 records : Test and NULL

xp_cmdshell 'echo Test >> c:\\output.txt'
--> gives me nothing (NULL in Query Analyzer but no file created)
I have tried with >, >>, \, \\, no slash...
I have checked the doc of xp_cmdshell on the net, but I do not find anything... :S
0
 
muzzy2003Commented:
When I run xp_cmdshell 'echo Test > C:\File.txt' I get Null in QA, but the file does get created, and it contains the string "Test". Could it be permissions creating the file?
0
 
JarodtweissAuthor Commented:
Sh.......
How stupid I am..... (I was searching for a local file, but of course, it is created on the server)
Ok thanks, it's working perfectly !
I just check if I can make "everything" work together and I give you the points !
0
 
muzzy2003Commented:
Don't worry - I've done that so many times myself ...
0
 
JarodtweissAuthor Commented:
Ok it's working perfectly !
Just a last question about osql...

What I do not like in te solution is that I have a bat file (with several variable : server, login and password) which is executing the sql file with OSQL.
But in the sql file, I have a variable that's stores the path of the output file.
I would prefer to have everything to configure in the same place, ie the bat file.
Can I use osql to pass a paramter ? something like

osql xxxxx -i 'mySqlFile.sql' myParam

Is this possible ?
0
 
muzzy2003Commented:
You could create your query as a stored procedure with a parameter like this:

CREATE PROCEDURE spTestSP
   @fileName varchar(50)
AS

DECLARE @BeginProcDate datetime
DECLARE @duration datetime
DECLARE @command varchar(8000)
       
        EXEC myStoredProc

set @duration = @BeginProcDate - getdate()
set @command = 'xp_cmdshell ''echo ' + CAST(DATEPART(ss, @duration) AS VARCHAR(2)) + '.' + CAST(DATEPART(ms, @duration) AS VARCHAR(3)) + ' > ' + @fileName
EXEC (@command)

GO

and then use this command line:

osql -S xxx -U sa -P sa -q "EXEC spTestSP 'C:\Output.txt'" -n -o "Output.txt"
0
 
JarodtweissAuthor Commented:
Ok, thank you very much !
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now