1. DTS running complex and long running queries - SQL Server DTS
3. Problem using Access or Query Designer to run queries in SQL S
4. Problem using Access or Query Designer to run queries in SQL Serve
Hi, I'm running a SQL server 2000 (recently upgraded to SP4, MDAC 2.81) on Win2k box with 1GB memory. I've two huge denormalized tables: one Master table with 70 columns and 5million rows and another Summary table with fewer columns and 2 million rows. Problem: When I try to run a simple <select * from table> kind of query from Access or Query Designer within Enterprise Manager, it works and I get data back. The minute I add a simple where condition or run a query with 3-4 columns with a group by, it fails with a: ODBC Call Failed [ODBC SQL Server Driver] Timeout Expired(#0) error in Access and in Query Designer (on the server box!) something similar happens: [ODBC SQL Server Driver] Timeout Expired Same query against the summary table may work most of the time using both clients. So, I've spent the last few days searching the forums, KB's etc and realize that this is a client issue and specifically a query timeout issue since I do NOT have problems running any kind of query using Query Analyzer or surprise: Microsoft Query (Excel) against either table. Not being able to use Query Designer doesn't matter much, but if running queries via Access doesn't work..then I'm in serious trouble. Being able to use Access to access the SQL server data is key, because of the large datasets and the ease with which financial analysts can customize their queries for their specific needs. While I work on normalizing my database, things I've tried so far with no success: 1. Added indexes to the tables, shrunk database, checked available space, updated statistics etc.. 2. Set Query Governor on server to unlimited, remote server connection timeout to unlimited too 3. Logged long running queries in Client configuration of SQL server DSN and set query time to max of 99999 milliseconds 4. Set OLE/DD timeout within Access's Tools-->Options to max of 300 5. Run a trace in SQL Profiler and see if I could get any clue ... 6. Restarted SQL server a few times to get rid of ghost sessions, locks etc.. 7. Used Access and Query Designer on the same box as the SQL server to eliminate network issues 8. Changed my ODBC DSN to use SQL pipes instead of TCP/IP This is driving me absolutely crazy. Especially the fact that Excel using the same client ODBC SQL server DSN used within Access, can run any query without timing out, but not Access! I've used Access to run queries against same 5million row table in MySQL (using MySQL ODBC driver) and it works fine..Only downside is that I've to install MySQL ODBC driver on all desktops and it is unsupported :( Can someone please help me make Access work with SQL server 2000 or is this just an ODBC bug that affects big tables? Thanks for listening John H.
5. Running queries on queries - SQL Server
6. query to find long running query
I want to write a query that will tell me if there are queries that are still running and are past 20 mins since it came in ? How can I do so ? I am using SQL 2000. If i can have one for 2005, that would be fine too. Thank you.
7. query analyzer runs query faster
I'm running XP Pro. When I downgrade Access2000 to 97, I
have to rename hatten.ttf and msaccess.exe (and later re-
name them back) to be able to convert to access97! I
wonder if that is causing the problem? Besides, when I
render the html page, it did not give me any errors, just
sit there seems unable to connect to the database.
I have to convert Access 2000 to 97, because most still
use Access97 in my company. There is an converting issue
if I use 2000...
Sheridan
>-----Original Message-----
>couple of questions
>1) XP home or Pro? you can only use Web sites on Pro
>2) are you getting any errors?
>
>if you have IIS 5.0 running you should not have any
problems
>The only concern I have is if you have drivers for access
97 on the machine.
>
>
>
>"Sheridan" < XXXX@XXXXX.COM > wrote in message
>news:11b901c33cea$3072ce70$ XXXX@XXXXX.COM ...
>> How can I create a html file that will link a query in
MS
>> Access database or query tables in SQL server and print
>> the result on the page?
>>
>> Somebody told me I need vbscript ADO. But I try the
>> follwoing sample codes and it seemed not finding the
>> Northwind.mdb I put in c.
>>
>> <html>
>> <body>
>> <%
>> set conn=Server.CreateObject("ADODB.Connection")
>> conn.Provider="Microsoft.Jet.OLEDB.4.0"
>> conn.Open "c:\northwind.mdb"
>> set rs = Server.CreateObject("ADODB.recordset")
>> rs.Open "Select * from Customers", conn
>> do until rs.EOF
>> for each x in rs.Fields
>> Response.Write(x.name)
>> Response.Write(" = ")
>> Response.Write(x.value & "<br />")
>> next
>> Response.Write("<br />")
>> rs.MoveNext
>> loop
>> rs.close
>> conn.close
>> %>
>> </body>
>> </html>
>>
>> Is there a software I need to make it work or can I
find a
>> free download somewhere?
>>
>> I'm using XP, but running Access97. Any advise?
>>
>> Thanks in advance.
>>
>> Sheridan
>
>
>.
>