Increase Timeout ?

I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com...
>> Hi,
>> The stored procedure is very straightforward. I am currently using an
>> under powered test server. I never get the timeout on the production
>> server...
>> So how do I increase the timeout ?
>> Thanks
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>> That doesn't really sound like a "fix" to me at all. You can increase
>> the timeout to infinity, but users are still going to get bored at some
>> point. Have you considered looking at the stored procedure itself, and
>> making it faster, instead of trying to find ways to make its slowness
>> more acceptable?
>>
>>
>> "Rob" <robc1@.yahoo.com> wrote in message
>> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com...
>>I am using VB.net to execute a stored procedure... Sometimes I get a
>>timeout, other times I do not. How can I increase the time out ? Is
>>this a server setting ?
>>
>>
>sql

Increase Timeout ?

I am using VB.net to execute a stored procedure... Sometimes I get a
timeout, other times I do not. How can I increase the time out ? Is
this a server setting ?
Can;t help you on the timeout...but would it makes sense to look at the
performance of your procedure?
How long is the VB.net timeout currently?
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||That doesn't really sound like a "fix" to me at all. You can increase the
timeout to infinity, but users are still going to get bored at some point.
Have you considered looking at the stored procedure itself, and making it
faster, instead of trying to find ways to make its slowness more acceptable?
"Rob" <robc1@.yahoo.com> wrote in message
news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>I am using VB.net to execute a stored procedure... Sometimes I get a
>timeout, other times I do not. How can I increase the time out ? Is
>this a server setting ?
>
|||Hi,
The stored procedure is very straightforward. I am currently using an under
powered test server. I never get the timeout on the production server...
So how do I increase the timeout ?
Thanks
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
> That doesn't really sound like a "fix" to me at all. You can increase the
> timeout to infinity, but users are still going to get bored at some point.
> Have you considered looking at the stored procedure itself, and making it
> faster, instead of trying to find ways to make its slowness more
> acceptable?
>
>
> "Rob" <robc1@.yahoo.com> wrote in message
> news:dZGdnVKfG_9Qlx3anZ2dnUVZ_s2tnZ2d@.comcast.com. ..
>
|||Rob
In VB sourcer when you connect to the server there's method called
CommandTimeout
Set Acn = New ADODB.Connection
Acn.CommandTimeout = 0
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||The default ADO.NET timeout is 30 seconds. This can be changed with the
SqlCommand object CommandTimeout property. For example:
myCommand.CommandTimeout = 60
However, I agree with the others that the timeout should be changed only
after ensuring proper index and query tuning is done. It could be that the
production server simply has enough power to compensate for inefficiencies.
Developing on an underpowered server can provide the motivation to improve
performance ;-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <robc1@.yahoo.com> wrote in message
news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
> Hi,
> The stored procedure is very straightforward. I am currently using an
> under powered test server. I never get the timeout on the production
> server...
> So how do I increase the timeout ?
> Thanks
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uOunlH$TIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
I just had to learn this one where I am. A query that basically returns the
DB as an XML feed (don't ask) now takes longer than 30 seconds. And even if
we improve it again, it's just going to get worse, again. Arrgh...

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Thanks !
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:701FF8B7-52A8-4431-98EB-D4F2E5F1C20F@.microsoft.com...
> The default ADO.NET timeout is 30 seconds. This can be changed with the
> SqlCommand object CommandTimeout property. For example:
> myCommand.CommandTimeout = 60
> However, I agree with the others that the timeout should be changed only
> after ensuring proper index and query tuning is done. It could be that
> the production server simply has enough power to compensate for
> inefficiencies. Developing on an underpowered server can provide the
> motivation to improve performance ;-)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <robc1@.yahoo.com> wrote in message
> news:Y6WdnRf7neuvgh3anZ2dnUVZ_s6mnZ2d@.comcast.com. ..
>

inconsistent Timeout Expired with extended SP execute

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 WriteDNFilesHi 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
>

inconsistent Timeout Expired with extended SP execute

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 WriteDNFilesHi 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
>