The query that was slowing the sproc was an UPDATE statement and it's condition was comparing an attribute that was of bigint type. I changed the attribute to varchar type...

I run profiler. I run TSQL duration, Tuning and TSQL SPs templates and they all told me the same thing ( which I already knew) that one query was slowing...

Ok, so I checked the execution plans of each individual query in the sproc and compared them to the entire sproc - the plans are the same.

After running the sproc...

Duh, thanks...

sorry if this seems like a silly question but how can I print the wall clock time in the sproc?

I've tried dropping anf re-entering and sp_recompile. Didn't make a difference.

I've beed playing around with different isolation levels but they all give me the same results. 10 minutes execution time.

Should...

Turns out the problem was the server address I was inputing. It was wrong! I put the wrong smtp ip...

So it all works great now.

Thanks for everyone's help.

Thanks Martin!

I'll check it out.

Sandra

I've added the following error checking to my SPROC:

EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = ' Source:...

Here is my SPROC:

CREATE PROCEDURE dbo.usp_sendmail (@recipients varchar(200), @message varchar(2000))

AS

declare @object int, @hr int, @v_returnval varchar(1000), @serveraddress varchar(100)

Set @serveraddress = 'xxx.xxx.xxx.xxx'

exec @hr = sp_OACreate 'SimpleCDO.Message', @object OUT

exec @hr =...

one second! It's great!

It's a part of a SPROC that has about 10 other queries so time is everything.

Thanks again!

Thanks guys for all your help.

I see the error of my ways now.

🙂

Sandra

Thank you Remi,

This query runs instantly, the only problem is it gives me a different record set. It finds more records to be inserted.

It may be because my old query...

This is great sblock!

I'll try it.

Thanks!

Never mind, I figured it out.

If anyone is interested:

system("dtsrun /S serverName /N \"PackageName\" /U userName /P password");