• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 877
  • Last Modified:

How to exec SQL Server query w/o waiting

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
pvs001d
Asked:
pvs001d
1 Solution
 
deightonprogCommented:
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
 
cymbolicCommented:
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
 
pvs001dAuthor Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
devthaCommented:
Use Doevents to return control to the O/S.
Devtha
0
 
pvs001dAuthor Commented:
Example?
0
 
tomookCommented:
The syntax is
Dim Q as QueryDef

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

Q.Execute dbRunAsync

0
 
pvs001dAuthor Commented:
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
 
tomookCommented:
'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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now