|
Home > Archive > MS SQL Server > October 2006 > inconsistent Timeout Expired with extended SP execute
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
inconsistent Timeout Expired with extended SP execute
|
|
| skeddy@gmail.com 2006-10-24, 6:29 pm |
| All,
I've successfully executed my extended stored proc multiple times via
the following. If I directly execute(e.g., right-click 'Execute'
within my DB editor) this stored procedure, it runs everytime and
finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
returns a 'Timeout Expired' message. But - not every time. I have got
the trigger to fire the SP a few (seemingly random) times without
timing out. Any help would be fantastic...
Pre-Thanks,
sara
my SP:
ALTER procedure dbo.WriteDNFiles
as
declare @return varchar
EXEC @return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
my trigger:
ALTER TRIGGER TriggerEmp
ON dbo.Employees
FOR INSERT, UPDATE
AS
exec WriteDNFiles
| |
| John Bell 2006-10-24, 6:29 pm |
| Hi sara
Running a program in a trigger is not a good idea as it will prolong the
transaction and there increase contention which could possibly be what you
are experiencing. You may want to make sure that you are not having any file
access problems if two process are wanting to call the program simultanously.
Another general point is that you should change @@ROWCOUNT in the trigger to
see if anything has changed, if not don't call your procedure.
John
"skeddy@gmail.com" wrote:
> All,
>
> I've successfully executed my extended stored proc multiple times via
> the following. If I directly execute(e.g., right-click 'Execute'
> within my DB editor) this stored procedure, it runs everytime and
> finishes in 1-2 seconds. If I try to execute the SP via my trigger, it
> returns a 'Timeout Expired' message. But - not every time. I have got
> the trigger to fire the SP a few (seemingly random) times without
> timing out. Any help would be fantastic...
>
> Pre-Thanks,
> sara
>
> my SP:
> ALTER procedure dbo.WriteDNFiles
> as
> declare @return varchar
> EXEC @return = master.dbo.XYRunProc 'C:\WRITE_DN_FILES.exe'
>
> my trigger:
> ALTER TRIGGER TriggerEmp
> ON dbo.Employees
> FOR INSERT, UPDATE
> AS
> exec WriteDNFiles
>
>
|
|
|
|
|