Remote SQL Error (Connection is Busy) 1

[lashwarj]

I can attach to my SQL Serve using the remote view, when trying to view some of the tables I recieve this error:

"Connectivity error:[Microsoft][ODBC SQL Server Drive] Connection is busy with results for another hstmt.



HELP !!!!!!!!!!!!!!!!!!! This database is associate with another application that is used by others, how would i get rid of this error. Thank you all

 

[DSummZZZ]

You do need to let it complete it's call, and 'ask' the connection if it's done. Test the status with the SQLMoreResults() call. As an example:

h = SQLConnect('MyConnection', '')
STORE 0 TO nResult
nResult = SQLExec(h, 'SELECT * FROM SomeTable', 'MyCursor')
DO CASE
   CASE nResult = -1
      ?'Error with connection'

   CASE nResult = 0
      ?'SQLExec still running'

   CASE nResult = 1
      ?'SQLExec completed'

   OTHERWISE
      ?nResult - 1
      ?? ' outstanding result sets'
ENDCASE

DO WHILE .T.
   IF

SQLMoreResults(h)

 # 2
      WAIT WINDOW 'pause...' TIMEOUT 1
   ELSE
      EXIT
   ENDIF
ENDDO

BROWSE &&nowait
?SQLDisconnect(h)

-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[lashwarj]

So your saying it would just be easier to create an access table with linked tables

 

[DSummZZZ]

Well I don't know your scenario, I just had in my head this was a programmatic thing. As soon as I posted I thought you may have a view in your data environment rather than a canned procedure.
If that is the case, there are other settings in the DE you will have to twiddle with. But like I said in the other post, that post is just an example of the programmatic method.
Now, I guess I don't really understand what you're asking on you next post. 'Create an access table with linked tables'.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[lashwarj]

yeah, i figure let access handle the remote view to the sql, it works and that was how I was currently doing it. Both utilize sql technologies. By creating a ODBC to access and from within access linking to the SQL it runs fine. Its just when I create a SQL remote view direct I Get the above error. If I close the table and try to open it again it works.

 

[Mike Lewis]

Lashwarj,

If this is happening with a remote view, go to the Advanced Options dialogue in the view designer, and make sure that "Share connections" is not ticked.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk