Post reply

Getting a timeout on stored proc and need to know what to do

  • September 20, 2016 at 1:23 pm

    #400749

    I am running a stored proc from EXEC in a ado.net source and getting the following error: "[ADO NET Source [49]] Error: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

    The actual procedure runs for 40 seconds in SSMS. I have tried resetting the Command Timeout, but if it were at 0 anyway, why would I need to mess with it? Is there somewhere else I should be looking or something I need to change? I have other stored procs for other SSIS jobs that are running fine. I also tried changing to OLEDB and get the same.

    Thanks

  • September 22, 2016 at 5:51 am

    #1902411

    Brad Allison (9/20/2016)

    I am running a stored proc from EXEC in a ado.net source and getting the following error: "[ADO NET Source [49]] Error: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

    The actual procedure runs for 40 seconds in SSMS. I have tried resetting the Command Timeout, but if it were at 0 anyway, why would I need to mess with it? Is there somewhere else I should be looking or something I need to change? I have other stored procs for other SSIS jobs that are running fine. I also tried changing to OLEDB and get the same.

    Thanks

    How long does the package run before timing out? Are there any other errors/warnings alongside this error (this error can be a red herring)? How are you running the package (in SSDT/BIDS, from the SSIS Catalog, an agent job, etc.)? Do you have a Remote Query Timeout setting other than 0 on the server?

    I'm on LinkedIn

  • September 22, 2016 at 5:59 am

    #1902415

    thanks for the response. I actually think it was the table that I was querying against as it stored millions of records. I changed to a different version of that data table and it works fine. I am NOT a DB expert. I, like many I am sure, am a jack of all trades with my main trade being .Net programmer, but I am a master of none. So, this issue has resolved itself in my directing the query to a more normalized table

  • September 22, 2016 at 6:24 am

    #1902427

    Brad Allison (9/22/2016)

    thanks for the response. I actually think it was the table that I was querying against as it stored millions of records. I changed to a different version of that data table and it works fine. I am NOT a DB expert. I, like many I am sure, am a jack of all trades with my main trade being .Net programmer, but I am a master of none. So, this issue has resolved itself in my directing the query to a more normalized table

    Errrm, okay. I'm glad you resolved it. No need to defend yourself here and I apologise if I made you feel you needed to 🙂

    I'm on LinkedIn

  • September 22, 2016 at 6:31 am

    #1902432

    Nope, I did not mean to sound defensive in anyway at all and sorry if I came across that way

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply