Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.sqlserver.programming,microsoft.public.excel.programming
|
|||
|
|||
Server timeout expired
A query (containing 4 left outer joins) takes above 6 minutes to
return a recordset of 212 rows. I am passing a sql string from MS Excel 2003 to MS Sql Server 2000 to run the query and return the recordset to a spreadsheet. I get the message "Timeout expired" from Excel. The database is from a legacy application which keeps transaction data in separate tables for each financial year. Hence, to use stored procedures would mean having one for each financial year, whereas I can conveniently change the table name in a cell in the spreadsheet based on the year required. Please help. Thanks a lot |
#2
Posted to microsoft.public.sqlserver.programming,microsoft.public.excel.programming
|
|||
|
|||
Server timeout expired
Mahen ) writes:
A query (containing 4 left outer joins) takes above 6 minutes to return a recordset of 212 rows. I am passing a sql string from MS Excel 2003 to MS Sql Server 2000 to run the query and return the recordset to a spreadsheet. I get the message "Timeout expired" from Excel. The database is from a legacy application which keeps transaction data in separate tables for each financial year. Hence, to use stored procedures would mean having one for each financial year, whereas I can conveniently change the table name in a cell in the spreadsheet based on the year required. The timeout is defined by Excel, or more exactly by ADO which it presumably uses. I don't know if there is a way to change it. (But I don't know much about connecting to SQL Server from Excelt at all.) Changes are good that it's possible to speed up your query so that it completes with the 30 seconds which is the default timeout. But in order to give suggestions, I would need to see the table definitions, including the indexes. And of course, I would need to see the query as well. It would also help to see the current query plan. -- Erland Sommarskog, SQL Server MVP, Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx |
#3
Posted to microsoft.public.sqlserver.programming,microsoft.public.excel.programming
|
|||
|
|||
Server timeout expired
On May 9, 9:26*pm, Erland Sommarskog wrote:
Mahen ) writes: A query (containing 4 left outer joins) takes above 6 minutes to return a recordset of 212 rows. I am passing a sql string from MS Excel 2003 to MS Sql Server 2000 to run the query and return the recordset to a spreadsheet. I get the message "Timeout expired" from Excel. The database is from a legacy application which keeps transaction data in separate tables for each financial year. Hence, to use stored procedures would mean having one for each financial year, whereas I can conveniently change the table name in a cell in the spreadsheet based on the year required. The timeout is defined by Excel, or more exactly by ADO which it presumably uses. I don't know if there is a way to change it. (But I don't know much about connecting to SQL Server from Excelt at all.) Changes are good that it's possible to speed up your query so that it completes with the 30 seconds which is the default timeout. But in order to give suggestions, I would need to see the table definitions, including the indexes. And of course, I would need to see the query as well. It would also help to see the current query plan. -- Erland Sommarskog, SQL Server MVP, Links for SQL Server Books Online: SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx Thanks for your response. I have solved the problem on two levels: 1) While configuring the ODBC data source connection to SQL server, I have set the "Save long running queries . . .(Long query time)" parameter to zero. 2) I have increased the RAM on my machine, I was using a notebook (with a personal edition of SQL server) with only 540 MB of ram. It now has 2GB. With both these solutions, the query now takes only 2 seconds! |
#4
Posted to microsoft.public.sqlserver.programming,microsoft.public.excel.programming
|
|||
|
|||
Server timeout expired
Mahen ) writes:
Thanks for your response. I have solved the problem on two levels: 1) While configuring the ODBC data source connection to SQL server, I have set the "Save long running queries . . .(Long query time)" parameter to zero. Ah, you found the switch. That's great. The default timeout of 30 seconds is even better. 2) I have increased the RAM on my machine, I was using a notebook (with a personal edition of SQL server) with only 540 MB of ram. It now has 2GB. With both these solutions, the query now takes only 2 seconds! Sometimes it does help to throw hardware at the problem. Yes, 540 MB of RAM for both SQL Server and Excel is a tad low. -- Erland Sommarskog, SQL Server MVP, Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find expired dates =IF((E11-TODAY()<0), "EXPIRED", "OK") | Excel Worksheet Functions | |||
Timeout Expired | Excel Programming | |||
TImeIn-TimeOut-TimeIn-TimeOut-HoursWorked | Excel Worksheet Functions | |||
Connection with SQL Server generate "Timeout expired" | Excel Programming | |||
COM server timeout | Excel Programming |