Solved

How to exec SQL Server query w/o waiting

Posted on 1998-06-04
8
765 Views
Last Modified: 2012-08-14
How can I run a query against SQL Server and return control to user without waiting for the query to finish.
What I want  to  call stored procedure that does some processing on the server and is not returning any results to the client. So ideally user should be able just click the button on the form ,that runs pass-through 'EXECUTE sp_process_data' query and forget about it.
0
Comment
Question by:pvs001d
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 1974833
My understandin is that Access runs on the client, and is therefore a remote system rather than a true Client/Server - but maybe someone else can enlare on this.
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1974834
Although the DAO/Jet engine that is access inherently runs in an asynchronous mode using ODBC, that is it issues a command without waiting, and polls for completeion, that asynchronous control is not available to you in your software.  You will either need to do it using the ODBC API, or an easier mode is to use a VBA module and RDO access method.  This means you also have to have VB5 Enterprise edition to get the RDO 2.0 control.

A good book for details is "Hitchiker's Guide to Visual Basic & SQL server" by W. R. Vaughn.
0
 

Author Comment

by:pvs001d
ID: 1974835
Are you sure that it can't be done in access only? I've read somewhere,that it is possible in Access'98 using ODBCDirect, but have not seen any explanation how to do it.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:devtha
ID: 1974836
Use Doevents to return control to the O/S.
Devtha
0
 

Author Comment

by:pvs001d
ID: 1974837
Example?
0
 
LVL 4

Expert Comment

by:tomook
ID: 1974838
The syntax is
Dim Q as QueryDef

(Make or get a querydef for the pass through query)

Q.Execute dbRunAsync

0
 

Author Comment

by:pvs001d
ID: 1974839
Dim ws As Workspace
Dim dbs As Database
Dim myqry As QueryDef
Set ws = DBEngine.Workspaces(0)
        Set dbs = ws.Databases(0)
Set myqry = dbs.CreateQueryDef("")
            myqry.ODBCTimeout = 300
            myqry.Connect_
 =ODBC;DSN=DNET;UID=;PWD=;DATABASE=callsys"
            myqry.SQL = "EXECUTE sp_notify"
            myqry.ReturnsRecords = False
            myqry.Execute dbRunAsync
At this point I get "Invalid argument" error (No 3001)
Comments?

0
 
LVL 4

Accepted Solution

by:
tomook earned 100 total points
ID: 1974840
'dbRunAsync' is valid only in ODBC Direct workspaces. In Access, Workspace(0) is always Jet. I have a snippet of code which may solve your problem.

Dim ws As Workspace
Dim myconn As Connection
Dim myqry As QueryDef
   
Set ws = DBEngine.CreateWorkspace("A", "Admin", "", dbUseODBC)
Set myconn = ws.OpenConnection("TestConnection", dbRunAsync, False, "ODBC;DSN=DNET;UID=;PWD=;DATABASE=callsys")
Set myqry = myconn.CreateQueryDef("", "EXECUTE sp_notify")  
myconn.Execute "EXECUTE sp_notify", dbRunAsync
Set myconn=Nothing
Set ws=Nothing

This opens a new ODBC Direct workspace (you could have one already opened, but this is just an example) and runs the query text directly on it. Note the asynchronous option. I tried this on our SQL Server and it works.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question