[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to exec SQL Server query w/o waiting

Posted on 1998-06-04
8
Medium Priority
?
794 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 300 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

650 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