Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jasone
Starting Member
2 Posts |
 Posted - 2005-12-07 : 12:59:25
|
I have a server running Win2003 Server with SQL Server 2000 sp4. On the same server, I use IIS to serve up asp pages.A number of these asp pages query SQL tables on the same server, but the queries all seem to be timing out. The same queries run fine when using Access or another Query tool. ASP queries to an external Teradata table also return results instantly. Is there something I'm missing? A setting in SQL Server 2000 or something?Here's the code I use to open the connection to SQL:myDSN="DSN=VIP;uid=XXXX;pwd=XXXX"Set Conn = Server.CreateObject("ADODB.Connection")Conn.Open myDSN |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-07 : 13:34:00
|
| There is an IIS setting for the timeout, but its normally 90 seconds, so I expect that your queries are "slow"I wouldn't use a DSN connection, the OLEDB ones are what are now "preferred"I expect the problem is in the makeup of your connection, and the way the query is being presented to SQL Server.Can you give us an example of a query? Is it a Stored Procedure or just Dynamic SQL? How big is the table it is running against?Kristen |
 |
|
|
jasone
Starting Member
2 Posts |
Posted - 2005-12-07 : 15:28:47
|
Here is an example of a query:myDSN="DSN=VIP;uid=XXXX;pwd=XXXX" Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open myDSN strSQL="SELECT count([index]) AS uniquecerts from master where status='IS' AND aadvno='" & aadvnoSQL & "' GROUP BY certno"set RS=Conn.Execute(strSQL) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-08 : 00:12:01
|
| Does it make any difference if you use:"Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDatabase;User Id=XXXX;Password=XXXX;" instead of the DSN type connection?I would change the query tostrSQL="SELECT count([index]) AS uniquecerts from dbo.master where status='IS' AND aadvno='" & aadvnoSQL & "' GROUP BY certno"assuming that you don't have other copies of your tables owned by individual users, but I don't expect that will make a significant difference to performance.Might be worth using SQL Profiler to see what's actually going on, and what the timings areKristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
ASP times out while querying SQL tables