If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Pass-through Query to SQL Server
I'm getting a "Query timeout expired" error when running my Access
pass-through query. The query works when I retrieve a small date range, but when I expand the date range I get the timeout error. I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native Client}; Server=10.20.10.149,5151; Connect Timeout=0; Database=master;UID=myUID;PWD=mypassword; Is there something I need to add to the connect string syntax to allow the query to complete? |
#2
|
|||
|
|||
Access Pass-through Query to SQL Server
Kirk,
I'd focus on the query. What does the SQL string look like? Do you have indexes on all the appropriate fields? Dale Kirk P. wrote: I'm getting a "Query timeout expired" error when running my Access pass-through query. The query works when I retrieve a small date range, but when I expand the date range I get the timeout error. I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native Client}; Server=10.20.10.149,5151; Connect Timeout=0; Database=master;UID=myUID;PWD=mypassword; Is there something I need to add to the connect string syntax to allow the query to complete? -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200906/1 |
#3
|
|||
|
|||
Access Pass-through Query to SQL Server
Kirk P. wrote:
I'm getting a "Query timeout expired" error when running my Access pass-through query. The query works when I retrieve a small date range, but when I expand the date range I get the timeout error. I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native Client}; Server=10.20.10.149,5151; Connect Timeout=0; Database=master;UID=myUID;PWD=mypassword; Is there something I need to add to the connect string syntax to allow the query to complete? It's not in the connect string - it's in the query properties: it's the ODBC Timeout property. However, as Dale says, you should probably work on optimizing the query. Perhaps an index on the backend database would help performance, if the problem is not the sheer number of records being returned. If the latter, you should probably consider filtering to get a more manageable result set. It's really not a good idea to be pulling the whole table across the wire very often. -- HTH, Bob Barrows |
#4
|
|||
|
|||
Access Pass-through Query to SQL Server
The query runs fine when running it through SQL Server Management Studio, but
your comment on the ODBC timeout property of the Access query was right on. I had that setting to 0 for the pass-through query. But the pass-through query is the source for an append query, and the append query had the setting to 60. Changing it to 0 in the append query solved the problem. Thanks! "Bob Barrows" wrote: Kirk P. wrote: I'm getting a "Query timeout expired" error when running my Access pass-through query. The query works when I retrieve a small date range, but when I expand the date range I get the timeout error. I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native Client}; Server=10.20.10.149,5151; Connect Timeout=0; Database=master;UID=myUID;PWD=mypassword; Is there something I need to add to the connect string syntax to allow the query to complete? It's not in the connect string - it's in the query properties: it's the ODBC Timeout property. However, as Dale says, you should probably work on optimizing the query. Perhaps an index on the backend database would help performance, if the problem is not the sheer number of records being returned. If the latter, you should probably consider filtering to get a more manageable result set. It's really not a good idea to be pulling the whole table across the wire very often. -- HTH, Bob Barrows |
#5
|
|||
|
|||
Access Pass-through Query to SQL Server
I would like to suggest using a different technology to tackle this
problem. Given the amount of time needed for the task, it does not sound suitable for passthrough queries. Either Get External Data or a SQL SSIS package would likely be more efficient. Kirk P. wrote: The query runs fine when running it through SQL Server Management Studio, but your comment on the ODBC timeout property of the Access query was right on. I had that setting to 0 for the pass-through query. But the pass-through query is the source for an append query, and the append query had the setting to 60. Changing it to 0 in the append query solved the problem. Thanks! "Bob Barrows" wrote: Kirk P. wrote: I'm getting a "Query timeout expired" error when running my Access pass-through query. The query works when I retrieve a small date range, but when I expand the date range I get the timeout error. I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native Client}; Server=10.20.10.149,5151; Connect Timeout=0; Database=master;UID=myUID;PWD=mypassword; Is there something I need to add to the connect string syntax to allow the query to complete? It's not in the connect string - it's in the query properties: it's the ODBC Timeout property. However, as Dale says, you should probably work on optimizing the query. Perhaps an index on the backend database would help performance, if the problem is not the sheer number of records being returned. If the latter, you should probably consider filtering to get a more manageable result set. It's really not a good idea to be pulling the whole table across the wire very often. -- HTH, Bob Barrows -- HTH, Bob Barrows |
#6
|
|||
|
|||
Access Pass-through Query to SQL Server
I'm having a similar problem with Time-Outs as follows:
The Error message I'm getting from the Query is: ODBC--call failed. [Microsoft][ODBC SQL Server Driver]Timeout expired (#0) I'm doing an Append to a Local Access table from an MS-SQL 2000 Data Source. (Using Access 2003, which I just converted to "Access 2003 Format" using the DB tools.) The SQL Code is as follows: INSERT INTO ITEMSALE_L ( IS_DATE, IS_ITEM, IS_QTY, IS_SALES, IS_COMPANY ) SELECT dbo_ITEMSALE.IS_DATE, dbo_ITEMSALE.IS_ITEM, dbo_ITEMSALE.IS_QTY, dbo_ITEMSALE.IS_SALES, dbo_ITEMSALE.IS_COMPANY FROM dbo_ITEMSALE WHERE (((Month([dbo_ITEMSALE].[IS_DATE]))=[Forms]![Period_Choice]![Form_Period]) AND ((Year([IS_DATE]))=[Forms]![Period_Choice]![Years_List])); The Form noted is one I created. I looked all over the DB and the Query, yet couldn't find where to modify the Time-out Setting. I did see one in my SQL 2005 SQL Server Management Studio for that particular Server. However, I am loath to make any changes there. Is it correct that if I make a change, it will only be for MY connection to that server? Is there anywhere else to make a change to the Time-out setting? Currently it is set for 600 Seconds (10 Minutes), although I'll get the Time-Out error Before 10 Minutes has passed. Hope that I've been specific enough in my posting. I'll be monitoring this issue if anyone needs any additional information. Regards, J. Falk "Bob Barrows" wrote: I would like to suggest using a different technology to tackle this problem. Given the amount of time needed for the task, it does not sound suitable for passthrough queries. Either Get External Data or a SQL SSIS package would likely be more efficient. Kirk P. wrote: The query runs fine when running it through SQL Server Management Studio, but your comment on the ODBC timeout property of the Access query was right on. I had that setting to 0 for the pass-through query. But the pass-through query is the source for an append query, and the append query had the setting to 60. Changing it to 0 in the append query solved the problem. Thanks! "Bob Barrows" wrote: Kirk P. wrote: I'm getting a "Query timeout expired" error when running my Access pass-through query. The query works when I retrieve a small date range, but when I expand the date range I get the timeout error. I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native Client}; Server=10.20.10.149,5151; Connect Timeout=0; Database=master;UID=myUID;PWD=mypassword; Is there something I need to add to the connect string syntax to allow the query to complete? It's not in the connect string - it's in the query properties: it's the ODBC Timeout property. However, as Dale says, you should probably work on optimizing the query. Perhaps an index on the backend database would help performance, if the problem is not the sheer number of records being returned. If the latter, you should probably consider filtering to get a more manageable result set. It's really not a good idea to be pulling the whole table across the wire very often. -- HTH, Bob Barrows -- HTH, Bob Barrows |
#7
|
|||
|
|||
Access Pass-through Query to SQL Server
Like Roseann Rosannadanna said: "Never Mind!"
When researching Pass-Through Queries, I found the way to change the Time-Out Properties on the given Query. My other question is: As a relative newcomer to Access, would there be an advantage to using a "Pass-Through Query"? Is the overall concept that instead of sending MS-Access Commands to the SQL Database, that instead I'm sending a direct SQL Query? Thanks in advance for any assistance you can give. "MC_FRx_99" wrote: I'm having a similar problem with Time-Outs as follows: The Error message I'm getting from the Query is: ODBC--call failed. [Microsoft][ODBC SQL Server Driver]Timeout expired (#0) I'm doing an Append to a Local Access table from an MS-SQL 2000 Data Source. (Using Access 2003, which I just converted to "Access 2003 Format" using the DB tools.) The SQL Code is as follows: INSERT INTO ITEMSALE_L ( IS_DATE, IS_ITEM, IS_QTY, IS_SALES, IS_COMPANY ) SELECT dbo_ITEMSALE.IS_DATE, dbo_ITEMSALE.IS_ITEM, dbo_ITEMSALE.IS_QTY, dbo_ITEMSALE.IS_SALES, dbo_ITEMSALE.IS_COMPANY FROM dbo_ITEMSALE WHERE (((Month([dbo_ITEMSALE].[IS_DATE]))=[Forms]![Period_Choice]![Form_Period]) AND ((Year([IS_DATE]))=[Forms]![Period_Choice]![Years_List])); The Form noted is one I created. I looked all over the DB and the Query, yet couldn't find where to modify the Time-out Setting. I did see one in my SQL 2005 SQL Server Management Studio for that particular Server. However, I am loath to make any changes there. Is it correct that if I make a change, it will only be for MY connection to that server? Is there anywhere else to make a change to the Time-out setting? Currently it is set for 600 Seconds (10 Minutes), although I'll get the Time-Out error Before 10 Minutes has passed. Hope that I've been specific enough in my posting. I'll be monitoring this issue if anyone needs any additional information. Regards, J. Falk "Bob Barrows" wrote: I would like to suggest using a different technology to tackle this problem. Given the amount of time needed for the task, it does not sound suitable for passthrough queries. Either Get External Data or a SQL SSIS package would likely be more efficient. Kirk P. wrote: The query runs fine when running it through SQL Server Management Studio, but your comment on the ODBC timeout property of the Access query was right on. I had that setting to 0 for the pass-through query. But the pass-through query is the source for an append query, and the append query had the setting to 60. Changing it to 0 in the append query solved the problem. Thanks! "Bob Barrows" wrote: Kirk P. wrote: I'm getting a "Query timeout expired" error when running my Access pass-through query. The query works when I retrieve a small date range, but when I expand the date range I get the timeout error. I've got this in the ODBC Connect String property: ODBC;Driver={SQL Native Client}; Server=10.20.10.149,5151; Connect Timeout=0; Database=master;UID=myUID;PWD=mypassword; Is there something I need to add to the connect string syntax to allow the query to complete? It's not in the connect string - it's in the query properties: it's the ODBC Timeout property. However, as Dale says, you should probably work on optimizing the query. Perhaps an index on the backend database would help performance, if the problem is not the sheer number of records being returned. If the latter, you should probably consider filtering to get a more manageable result set. It's really not a good idea to be pulling the whole table across the wire very often. -- HTH, Bob Barrows -- HTH, Bob Barrows |
#8
|
|||
|
|||
Access Pass-through Query to SQL Server
MC_FRx_99 wrote:
Like Roseann Rosannadanna said: "Never Mind!" When researching Pass-Through Queries, I found the way to change the Time-Out Properties on the given Query. My other question is: As a relative newcomer to Access, would there be an advantage to using a "Pass-Through Query"? Is the overall concept that instead of sending MS-Access Commands to the SQL Database, that instead I'm sending a direct SQL Query? That's exactly the advantage. -- Microsoft MVP - ASP/ASP.NET - 2004-2007 Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
Thread Tools | |
Display Modes | |
|
|