 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008stored procedure query
I have a stored procedure that I use in a pass-through query and have many such queries working fine. The select statement in the stored procedure is complex but populates in one second when run from SQL query analyzer. From the MS Access (2003) pass-through, I keep getting a time-out error. If the query runs in 1 second from SQL, shouldn't it run in about the same from Access? It's not a typo or syntax error. I've posted the exact syntax in SQL and it runs fine. I've checked by ODBC connection and that is fine. What else should I look at? Sam > I have a stored procedure that I use in a pass-through query and have > many such queries working fine. The select statement in the stored [quoted text clipped - 8 lines] > > Sam In query analyzer do you see results in one second or does the status bar indicate that the query completed in one second? You can see results immediately and still not have the query "finish" for quite a while. Access will not show anything until the entire query is finished.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com I see the results and the status bar reads 1 second. > > I have a stored procedure that I use in a pass-through query and have > > many such queries working fine. The select statement in the stored [quoted text clipped - 13 lines] > immediately and still not have the query "finish" for quite a while. Access > will not show anything until the entire query is finished. > I see the results and the status bar reads 1 second. That's a puzzle then. I have never noticed any perceptable difference in the speed of running a query in QA compared to using an Access Passthrough. Are you using the latest SS driver? SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com Interesting observation that I had not seen before: I got the query running. But the first time it ran, it took 20+ seconds. My timeout was 30 seconds and apparently if I had reset the timeout longer, it would have run. I tweaked the stored procedure, cut it down slightly, enough for the query to get under 30 seconds. Once it had run that first time, it now runs in under 1 second, same as the stored procedure. I had run the query several times from QA, so that the stored procedure would compile, but inspite of this it seemed like it had to compile separately on the Access side. Sam > > I see the results and the status bar reads 1 second. > > That's a puzzle then. I have never noticed any perceptable difference in > the speed of running a query in QA compared to using an Access Passthrough. > > Are you using the latest SS driver? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.