How to deal when ODBC throw up 'Connection is busy with results for another hstmt'?

Hi all,

I insert 183 rows to a specific table in SQL Server (say, T035). The
method I used was I created a remote view rv_location with (CREATE SQL
VIEW rv_location REMOTE CONNECTION "Conn" AS SELECT T035.* FROM dbo.T035
T035), and do the insertion to the remote view.

After the insertion, I couldn't run all forms that have remote views
related to T035. Whenever I run the form, ODBC sent "Connection is busy
with results for another hstmt" error.
It's really confusing because:
1. The database is on my local machine, and I am not connected to any
network. This means nobody use the server except vfp.
2. Although I cannot open the rv from my form's DE (Data Environment),
when I use the remote view from VFP IDE, the cursor filled with
appropriate value. No error from ODBC.

I searched SQL Server Online Books, and I found out that the cause
either SQL Server doesn't support SELECT * FROM... (???) or SQL Server
treat it as bulk copy. It is said that "the solution is to call
bcp_done" --- and I don't know how to call bcp_done (tried it with
SQLEXEC)

I'm really confused here. I tried to exit and re-run vfp with no luck. I
tried to reboot my computer --- still with no luck. I even (stupidly ---
it's funny how we suddenly do something that obviously has nothing to do
with the error when unknown error happened!---) tried to delete the view
from my form's DE, pack the .scx, and attached the view back again; of
course with no luck!

Can anybody help me what to do with this error? I hate to admit that the
only solution I got is restore the database from the backup :-(

TIA,
Willianto

Leemi
Tue Dec 23 10:56:58 CST 2003

Hi Willianto:

This seems to be a problem with the SQL Server ODBC driver according to
this article. You may be able to add a Service pack to SQL Server to fix
this problem. See:

321255 FIX: ODBC Driver for SQL Server 2000 Incorrectly Returns SQL_SUCCESS
on
http://support.microsoft.com/?id=321255


I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Merry Christmas and Happy New Year!

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP8 HAS ARRIVED!! --*
Read about all the new features of VFP8 here:
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
Purchase VFP8 here:
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retires Sept. 30th, 2003


>Hi all,

>I insert 183 rows to a specific table in SQL Server (say, T035). The
>method I used was I created a remote view rv_location with (CREATE SQL
>VIEW rv_location REMOTE CONNECTION "Conn" AS SELECT T035.* FROM dbo.T035
>T035), and do the insertion to the remote view.

>After the insertion, I couldn't run all forms that have remote views
>related to T035. Whenever I run the form, ODBC sent "Connection is busy
>with results for another hstmt" error.
>It's really confusing because:
>1. The database is on my local machine, and I am not connected to any
>network. This means nobody use the server except vfp.
>2. Although I cannot open the rv from my form's DE (Data Environment),
>when I use the remote view from VFP IDE, the cursor filled with
>appropriate value. No error from ODBC.

>I searched SQL Server Online Books, and I found out that the cause
>either SQL Server doesn't support SELECT * FROM... (???) or SQL Server
>treat it as bulk copy. It is said that "the solution is to call
>bcp_done" --- and I don't know how to call bcp_done (tried it with
>SQLEXEC)

>I'm really confused here. I tried to exit and re-run vfp with no luck. I
>tried to reboot my computer --- still with no luck. I even (stupidly ---
>it's funny how we suddenly do something that obviously has nothing to do
>with the error when unknown error happened!---) tried to delete the view
>from my form's DE, pack the .scx, and attached the view back again; of
>course with no luck!

>Can anybody help me what to do with this error? I hate to admit that the
>only solution I got is restore the database from the backup :-(

>TIA,
>Willianto>

Alex
Tue Dec 23 10:58:40 CST 2003

Hi Willianto,

No worries, you will not have to restore from backup. That is not a SQL
Server error, it's an ODBC error. My guess is that you are having your
remote views share your ODBC connection?


"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:%23fY%23k$WyDHA.2084@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I insert 183 rows to a specific table in SQL Server (say, T035). The
> method I used was I created a remote view rv_location with (CREATE SQL
> VIEW rv_location REMOTE CONNECTION "Conn" AS SELECT T035.* FROM dbo.T035
> T035), and do the insertion to the remote view.
>
> After the insertion, I couldn't run all forms that have remote views
> related to T035. Whenever I run the form, ODBC sent "Connection is busy
> with results for another hstmt" error.
> It's really confusing because:
> 1. The database is on my local machine, and I am not connected to any
> network. This means nobody use the server except vfp.
> 2. Although I cannot open the rv from my form's DE (Data Environment),
> when I use the remote view from VFP IDE, the cursor filled with
> appropriate value. No error from ODBC.
>
> I searched SQL Server Online Books, and I found out that the cause
> either SQL Server doesn't support SELECT * FROM... (???) or SQL Server
> treat it as bulk copy. It is said that "the solution is to call
> bcp_done" --- and I don't know how to call bcp_done (tried it with
> SQLEXEC)
>
> I'm really confused here. I tried to exit and re-run vfp with no luck. I
> tried to reboot my computer --- still with no luck. I even (stupidly ---
> it's funny how we suddenly do something that obviously has nothing to do
> with the error when unknown error happened!---) tried to delete the view
> from my form's DE, pack the .scx, and attached the view back again; of
> course with no luck!
>
> Can anybody help me what to do with this error? I hate to admit that the
> only solution I got is restore the database from the backup :-(
>
> TIA,
> Willianto
>
>

Willianto
Tue Dec 23 11:26:41 CST 2003

> No worries, you will not have to restore from backup. That is not a
> SQL Server error, it's an ODBC error. My guess is that you are
> having your remote views share your ODBC connection?

Yes, I set share connections on all of my remote views. Yet, I'm still
worry, because you don't specify any solution in your reply ;-)

Looking forward to hearing more from you (or anybody know the
solution...)

TIA,
Willianto

Bill
Tue Dec 23 11:47:02 CST 2003


"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:%23fY%23k$WyDHA.2084@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I insert 183 rows to a specific table in SQL Server (say, T035). The
> method I used was I created a remote view rv_location with (CREATE SQL
> VIEW rv_location REMOTE CONNECTION "Conn" AS SELECT T035.* FROM dbo.T035
> T035), and do the insertion to the remote view.
>
> After the insertion, I couldn't run all forms that have remote views
> related to T035. Whenever I run the form, ODBC sent "Connection is busy
> with results for another hstmt" error.
> It's really confusing because:
> 1. The database is on my local machine, and I am not connected to any
> network. This means nobody use the server except vfp.
> 2. Although I cannot open the rv from my form's DE (Data Environment),
> when I use the remote view from VFP IDE, the cursor filled with
> appropriate value. No error from ODBC.
>
> I searched SQL Server Online Books, and I found out that the cause
> either SQL Server doesn't support SELECT * FROM... (???) or SQL Server
> treat it as bulk copy. It is said that "the solution is to call
> bcp_done" --- and I don't know how to call bcp_done (tried it with
> SQLEXEC)
>
> I'm really confused here. I tried to exit and re-run vfp with no luck. I
> tried to reboot my computer --- still with no luck. I even (stupidly ---
> it's funny how we suddenly do something that obviously has nothing to do
> with the error when unknown error happened!---) tried to delete the view
> from my form's DE, pack the .scx, and attached the view back again; of
> course with no luck!
>
> Can anybody help me what to do with this error? I hate to admit that the
> only solution I got is restore the database from the backup :-(
>
> TIA,
> Willianto
>
>

Is your connection Asynchronous ? This may cause this problem.
Bill

Willianto
Tue Dec 23 11:34:50 CST 2003

Hi Lee,

Thanks for you response,

> 321255 FIX: ODBC Driver for SQL Server 2000 Incorrectly Returns
> SQL_SUCCESS on
> http://support.microsoft.com/?id=321255

I've read the article, but it's said that the problem was fixed on SP3.
My SQL Server ODBC driver comes from the one in the VFP8 installation
disk. Isn't that SP3 already? My ODBC version is 2000.81.9030.04 which
superceed those which are mention in the article:

/from the article
The information in this article applies to:
a.. Microsoft ODBC Driver for SQL Server 2000 2000.81.7713.0, when
used with:
Microsoft Data Access Components 2.6
Microsoft Data Access Components 2.7
b.. Microsoft ODBC Driver for SQL Server 2000 2000.80.194, when used
with:
Microsoft Data Access Components 2.6
Microsoft Data Access Components 2.7
c.. Microsoft ODBC Driver for SQL Server 2000 2000.80.380, when used
with:
Microsoft Data Access Components 2.6
Microsoft Data Access Components 2.7
/end

I'm aware that there is SP3a. Do I have to apply SP3a?


TIA,
Willianto

Willianto
Tue Dec 23 12:35:39 CST 2003


> Is your connection Asynchronous ? This may cause this problem.
No Bill.
Asynchronous execution = False

Any idea what went wrong and how to fix it?

TIA,
Willianto

swdev2
Tue Dec 23 12:49:24 CST 2003

Hiya -
please confirm / deny that you are doing this with a SQL Server View.
[not vfp, SQL Server].

tia ! mondo regards [Bill]
--

Willianto
Tue Dec 23 13:46:07 CST 2003

I'm doing this with VFP8 (Microsoft Visual FoxPro 8.0) remote views
*not* SQL Server view.
The view definition is:
*!* Conn is a connection specified in the dbc
CREATE SQL VIEW rv_location REMOTE CONNECTION "Conn" AS ;
SELECT T035.* FROM dbo.T035

I already specify appropriate pk as well as check the 'Send SQL Update'
checkbox
What I did was:
*!* the field are simplified for better understanding.
USE rv_location IN 0
USE local_dbf_table
SCAN
SELECT local_dbf_table
lcField1 = field_1
lcField2 = field_2
INSERT INTO rv_location (field_1_on_SQL_Server,
field_2_on_SQL_Server) ;
VALUES (lcField1, lcField2)
ENDSCAN

After running this simple program, the behaviour occured.

Ideas?

TIA,
Willianto

swdev2
Tue Dec 23 14:21:40 CST 2003

Willianto - I know you've been banging on this - but
what happens if you add a tableupdate after your insert line ?

I seems to me like T035 has some other rules on it in the back end that are
preventing the update buffer to actually write to disk. would suggest to
take a look at any error thrown on the vfp side during the tableupdate()
call, and also see if the backend table has :
1. constraints on any field besides the PK.
2. enforced foreign keys
3. stored procedures / triggers that will fire if a record insert is
performed ..

FWIW - a forced tableupdate() is rather good about immediately returning
errors if indeed there is some problem..

hth - mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
FREE LONG DISTANCE -> mailto:excel-info@efgroup.net
Free Satellite Receivers and installation ->
http://www.vmcsatellite.com/?aid=58456
mySql / VFP / MS-SQL
"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:eBHdGsYyDHA.1996@TK2MSFTNGP12.phx.gbl...
> I'm doing this with VFP8 (Microsoft Visual FoxPro 8.0) remote views
> *not* SQL Server view.
> The view definition is:
> *!* Conn is a connection specified in the dbc
> CREATE SQL VIEW rv_location REMOTE CONNECTION "Conn" AS ;
> SELECT T035.* FROM dbo.T035
>
> I already specify appropriate pk as well as check the 'Send SQL Update'
> checkbox
> What I did was:
> *!* the field are simplified for better understanding.
> USE rv_location IN 0
> USE local_dbf_table
> SCAN
> SELECT local_dbf_table
> lcField1 = field_1
> lcField2 = field_2
> INSERT INTO rv_location (field_1_on_SQL_Server,
> field_2_on_SQL_Server) ;
> VALUES (lcField1, lcField2)
tableupdate call here....
> ENDSCAN
>
> After running this simple program, the behaviour occured.
>
> Ideas?
>
> TIA,
> Willianto
>
>

Igor
Tue Dec 23 19:20:16 CST 2003

Hi, Willianto!
You wrote on Wed, 24 Dec 2003 00:26:41 +0700:

??>> No worries, you will not have to restore from backup. That is not a
??>> SQL Server error, it's an ODBC error. My guess is that you are
??>> having your remote views share your ODBC connection?

W> Yes, I set share connections on all of my remote views. Yet, I'm still
W> worry, because you don't specify any solution in your reply ;-)

W> Looking forward to hearing more from you (or anybody know the
W> solution...)

Try to set "FetchSize" proprty for your views to -1, or "MaxRecords"
proprerty to the same value as "FetchSize". So type in command window

DBSETPROP("MyView", "VIEW", "FetchSize", -1)

with your VFP database (containing this view) being opened and set as a
current one.
IMHO the problem is in fact that VFP tried to fetch data from all of your
views simultaneously, so it open view1, fetch "FetchSize" records from it
(100 as a default), and immediately after it tried to open and fetch data
from another view (that is using the same shared connection). So you have to
force VFP not to "stop" after fetching only 100 first records into cursor,
or "pause" common DE opening process untill SQLGETPROP(m.lnConnHandle,
"ConnectBusy") tell you that previous fetch operation is done.

And as you use VFP8, look as "AllowSimultaneousFetch" property, maybe it can
also affect.

--
WBR, Igor

Alex
Wed Dec 24 13:48:00 CST 2003

Sorry I wasn't more clear, my message was meant to imply that you should try
turning connection sharing off.... Igor's idea should also work (trying to
fetch all records)... Let me know if either (or both) of those work for ya
:)


"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:%230hmeeXyDHA.4064@tk2msftngp13.phx.gbl...
> > No worries, you will not have to restore from backup. That is not a
> > SQL Server error, it's an ODBC error. My guess is that you are
> > having your remote views share your ODBC connection?
>
> Yes, I set share connections on all of my remote views. Yet, I'm still
> worry, because you don't specify any solution in your reply ;-)
>
> Looking forward to hearing more from you (or anybody know the
> solution...)
>
> TIA,
> Willianto
>
>

Willianto
Thu Dec 25 10:19:23 CST 2003

Hi Alex, Hi Igor,

Thanks for sharing your ideas. I'll give it a try tomorrow. I'm tired
and sleepy now. I think I ate too much. Don't know how many cakes and
cookies went into my mouth today ;-)

Merry Christmas to you...

Regards,
Willianto

Alex B wrote:
> Sorry I wasn't more clear, my message was meant to imply that you
> should try turning connection sharing off.... Igor's idea should
> also work (trying to fetch all records)... Let me know if either (or
> both) of those work for ya :)
>
>
> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
> news:%230hmeeXyDHA.4064@tk2msftngp13.phx.gbl...
>>> No worries, you will not have to restore from backup. That is not a
>>> SQL Server error, it's an ODBC error. My guess is that you are
>>> having your remote views share your ODBC connection?
>>
>> Yes, I set share connections on all of my remote views. Yet, I'm
>> still worry, because you don't specify any solution in your reply ;-)
>>
>> Looking forward to hearing more from you (or anybody know the
>> solution...)
>>
>> TIA,
>> Willianto

Willianto
Fri Dec 26 02:39:59 CST 2003

Hello everyone,

Turning off connection sharing on one of the view involved in the form
does the trick. Fetch All Records alone didn't work. The 'Share
Connection' is the problem.

I'm still confused though, what's wrong with sharing connection? Can
anybody explain that? Come on sqlserver.programming guys... I wanted to
hear from you, that's why I cross-posted in the first place.

Anyway, thanks to all participants in this thread. I appreciate your
time and effort. Merry Christmas to you! :-)

Best Regards,
Willianto



Alex B wrote:
> Sorry I wasn't more clear, my message was meant to imply that you
> should try turning connection sharing off.... Igor's idea should
> also work (trying to fetch all records)... Let me know if either (or
> both) of those work for ya :)
>
>
> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
> news:%230hmeeXyDHA.4064@tk2msftngp13.phx.gbl...
>>> No worries, you will not have to restore from backup. That is not a
>>> SQL Server error, it's an ODBC error. My guess is that you are
>>> having your remote views share your ODBC connection?
>>
>> Yes, I set share connections on all of my remote views. Yet, I'm
>> still worry, because you don't specify any solution in your reply ;-)
>>
>> Looking forward to hearing more from you (or anybody know the
>> solution...)
>>
>> TIA,
>> Willianto

Alex
Fri Dec 26 09:54:35 CST 2003

Because by sharing a connection, each remote view doesn't have it's own
connection to the backend, so if one remote view is busy (i.e. still
querying, in the middle of a transaction, or whatever), your other remote
views using that handle will throw up the "connection busy" error... And
that error is pretty descriptive to the problem - the remote view in
question can't access that backend at that moment because the connection
that you chose to share is being used. It's like if you have one phone line
but two phones in your house. If your kids are talking on one phone but you
need to make a call you either need to (a) get another line or (b) tell them
to hang up so you can make a call.




"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:OwFaLs4yDHA.1764@TK2MSFTNGP10.phx.gbl...
> Hello everyone,
>
> Turning off connection sharing on one of the view involved in the form
> does the trick. Fetch All Records alone didn't work. The 'Share
> Connection' is the problem.
>
> I'm still confused though, what's wrong with sharing connection? Can
> anybody explain that? Come on sqlserver.programming guys... I wanted to
> hear from you, that's why I cross-posted in the first place.
>
> Anyway, thanks to all participants in this thread. I appreciate your
> time and effort. Merry Christmas to you! :-)
>
> Best Regards,
> Willianto
>
>
>
> Alex B wrote:
> > Sorry I wasn't more clear, my message was meant to imply that you
> > should try turning connection sharing off.... Igor's idea should
> > also work (trying to fetch all records)... Let me know if either (or
> > both) of those work for ya :)
> >
> >
> > "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
> > news:%230hmeeXyDHA.4064@tk2msftngp13.phx.gbl...
> >>> No worries, you will not have to restore from backup. That is not a
> >>> SQL Server error, it's an ODBC error. My guess is that you are
> >>> having your remote views share your ODBC connection?
> >>
> >> Yes, I set share connections on all of my remote views. Yet, I'm
> >> still worry, because you don't specify any solution in your reply ;-)
> >>
> >> Looking forward to hearing more from you (or anybody know the
> >> solution...)
> >>
> >> TIA,
> >> Willianto
>
>

Erland
Fri Dec 26 10:00:04 CST 2003

Willianto (willianto@remove-me.telkom-and-me.net) writes:
> I insert 183 rows to a specific table in SQL Server (say, T035). The
> method I used was I created a remote view rv_location with (CREATE SQL
> VIEW rv_location REMOTE CONNECTION "Conn" AS SELECT T035.* FROM dbo.T035
> T035), and do the insertion to the remote view.
>
> After the insertion, I couldn't run all forms that have remote views
> related to T035. Whenever I run the form, ODBC sent "Connection is busy
> with results for another hstmt" error.
> It's really confusing because:
> 1. The database is on my local machine, and I am not connected to any
> network. This means nobody use the server except vfp.

The error message from ODBC occurs when you have an connection open,
submit a statement that generates one or more result sets, and before
you have consumed all rows/result sets, you submit a new statement.

In this case, I would assume that the result set in question is a
closed result set which only carries with it the number of rows inserted.
(Compare the messages "7 row(s) affected" that you see in Query Analyzer.)
It could also be that there is a trigger on the SQL Server table which
produces rows for some reason.

In any case, after the insertion add a loop that just discards all the
result sets.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Willianto
Fri Dec 26 10:30:34 CST 2003

Hi Alex,
Thanks for your reply!

> Because by sharing a connection, each remote view doesn't have it's
> own connection to the backend, so if one remote view is busy (i.e.
> still querying, in the middle of a transaction, or whatever), your
> other remote views using that handle will throw up the "connection
> busy" error... And that error is pretty descriptive to the problem -
> the remote view in question can't access that backend at that moment
> because the connection that you chose to share is being used. It's
> like if you have one phone line but two phones in your house. If
> your kids are talking on one phone but you need to make a call you
> either need to (a) get another line or (b) tell them to hang up so
> you can make a call.
>
Ok. As far as I understand, 'something' is using the connection to my
database, and the only way I can do is to use other line. I tried to
reboot the computer, clean up the project and the vfp database
container. I even tried to detached and re-attach the database, but the
problem still apear (of course, unless I clear the 'Share Connections')

Question:
How to tell the 'something' (is it the odbc driver?) to hang up?

Willianto

Willianto
Fri Dec 26 10:45:49 CST 2003

Hi Erland,
First of all, Merry Christmas to you!

[...sorry, skipped...]
> In any case, after the insertion add a loop that just discards all the
> result sets.

I cannot add the loop because the program that do the insertion is long
gone. I mean, it's really just a small program that insert 183 rows from
a local dbf table. The program just scans the table row-by-row (this is
xbase method) and insert each row to the table in SQL Server utilizing
odbc. I didn't even save the program.
The insertion went very well. The table filled with appropriate values
when I checked it from the Query Analyzer.
Problem began when I tried to open another connection to the SQL Server.
I don't know what went wrong, but it seems like the connection is stay
there permanently.

Looks like the problem is on the odbc driver. Do you have any idea how
to disconnect a connection like that?

TIA,
Willianto

Erland
Fri Dec 26 17:47:47 CST 2003

Willianto (willianto@remove-me.telkom-and-me.net) writes:
> I cannot add the loop because the program that do the insertion is long
> gone. I mean, it's really just a small program that insert 183 rows from
> a local dbf table. The program just scans the table row-by-row (this is
> xbase method) and insert each row to the table in SQL Server utilizing
> odbc. I didn't even save the program.

If the program is gone, how can it execute?

> The insertion went very well. The table filled with appropriate values
> when I checked it from the Query Analyzer.
> Problem began when I tried to open another connection to the SQL Server.
> I don't know what went wrong, but it seems like the connection is stay
> there permanently.
>
> Looks like the problem is on the odbc driver. Do you have any idea how
> to disconnect a connection like that?

Since you work in FoxPro, you are in alien lands for me. I'm not an ODBC
wizard either, but I do know that the ODBC message means precisely what
I explained: you are trying to submit a new command on a connection that
is not ready for it yet.

I would not think there is a bug in the ODBC Driver, but since this
Share Connections seems to affect matters, it is possible that FoxPro
is doing something behind your back, that you are not aware of.

In any case, attaching/detaching databses, rebooting machines etc is not
going to help you. The error is a pure programming error, and that is
the end you should look in.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Willianto
Fri Dec 26 11:32:04 CST 2003

Hi Bill,

> Willianto - I know you've been banging on this - but
> what happens if you add a tableupdate after your insert line ?
Can't do. The insertion program is already run and it's already deleted.
Problem is, after the insertion (until this afternoon -- this afternoon
I tried Igor's and Alex's suggestion) my apps cannot access that
specific table in SQL server. The strange thing was when I used the rv
from VFP IDE, everything seems fine. The cursor opened appropriately and
the values are there. *But* when I run the form, the error described
showed again and (obviously) vfp cannot run the form.

While the problem is solved by turning off the 'Share Connections', I
still don't know how to close the unclosed-properly connection. Got any
idea?

> I seems to me like T035 has some other rules on it in the back end
> that are preventing the update buffer to actually write to disk.
> would suggest to take a look at any error thrown on the vfp side
> during the tableupdate() call, and also see if the backend table has :
> 1. constraints on any field besides the PK.
> 2. enforced foreign keys
> 3. stored procedures / triggers that will fire if a record insert is
> performed ..
>
No constraint, no enforced foreign keys, and no sp involve with this
table. In fact, the server itself (SQL Server Developer Edition) runs on
my notebook and my apps runs on the same box.

Any idea?

TIA,
Willianto

> FWIW - a forced tableupdate() is rather good about immediately
> returning errors if indeed there is some problem..
>
> hth - mondo regards [Bill]
>
>> I'm doing this with VFP8 (Microsoft Visual FoxPro 8.0) remote views
>> *not* SQL Server view.
>> The view definition is:
>> *!* Conn is a connection specified in the dbc
>> CREATE SQL VIEW rv_location REMOTE CONNECTION "Conn" AS ;
>> SELECT T035.* FROM dbo.T035
>>
>> I already specify appropriate pk as well as check the 'Send SQL
>> Update' checkbox
>> What I did was:
>> *!* the field are simplified for better understanding.
>> USE rv_location IN 0
>> USE local_dbf_table
>> SCAN
>> SELECT local_dbf_table
>> lcField1 = field_1
>> lcField2 = field_2
>> INSERT INTO rv_location (field_1_on_SQL_Server,
>> field_2_on_SQL_Server) ;
>> VALUES (lcField1, lcField2)
> tableupdate call here....
>> ENDSCAN
>>
>> After running this simple program, the behaviour occured.
>>
>> Ideas?
>>
>> TIA,
>> Willianto

Willianto
Sat Dec 27 10:07:46 CST 2003

Hi Erland,

W>>I didn't even save the program.
>
E> If the program is gone, how can it execute?
Well, I execute it once, and then delete it. I tought I would never use
it again, since the main purpose of the program was to insert some
values to the table in the SQL Server, and the program did its goal
already.

>
>W> The insertion went very well. The table filled with appropriate
>W> values when I checked it from the Query Analyzer.
>W> Problem began when I tried to open another connection to the SQL
>W> Server. I don't know what went wrong, but it seems like the
>W> connection is stay there permanently.
>>
>W> Looks like the problem is on the odbc driver. Do you have any idea
>W> how to disconnect a connection like that?
>
E> Since you work in FoxPro, you are in alien lands for me.
<LOL> No sweat, I'm driving a flying soucer anyway!

E>I'm not an
E> ODBC wizard either, but I do know that the ODBC message means
E> precisely what I explained: you are trying to submit a new command on
E> a connection that is not ready for it yet.
>
E> I would not think there is a bug in the ODBC Driver, but since this
E> Share Connections seems to affect matters, it is possible that FoxPro
E> is doing something behind your back, that you are not aware of.


Yes Erland, I will explore about this. A lot of FoxPro gurus has
successfully created rich apps with SQL Server as the backend database.
I'm sure I just stumbled to a small stone along the way.

Thanks for your time. I appreciate it very much.

WBR,
Willianto