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 ?
LVL 4
JarodtweissAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.