I have a utility written in VFP 6.0 that is using a combination of SQL-DMO and SQL Pass-through (SPT) to connect to and modify a SQL database. I'm using the SQL-DMO primarily to gather available servers and databases, as well as using it to add a stored procedure (SP) to the database. I'm using the SPT to gather some data from the server, as well as firing the SP that I add to the database. This SP gets called in a loop, each time passing parms.

I'm needing to set exclusive access to the database while this process is happening because of the nature of the data changes I am making. My experience has been limited to VFP, and I don't have the ease of grabbing the database exclusively like I'm used to. I'm trying to set dboption to SingleUser = .T. (thinking this is the closest and best equivalent to OPEN DB EXCULSIVE in fox, if there's a better way, please let me know) and I'm running into trouble because of my multiple connections through SQL-DMO and SPT. If I connect via SQL-DMO first and set oDatabase.dbOption.SingleUser = .T., then I can't connect to the database using my SQLSTRINGCONNECT and vice versa.

I'm sure I'm not understanding how connections work, because I would have assumed that I could use both a connection through SQL-DMO and sqlstringconnect from the same client when DBOPTION is set to SingleUser. I tried cheating by connecting via SQL-DMO first, and then use my Server.ConnectionId as a handle in my SPT calls, but this doesn't work. Is there a way to get around this?

If there is not a way to get around this other than seperating my SQL-DMO and SPT processing, I can try to work that out. I just have to figure out how to issue the sp_dbOption procedure via SPT and get a return value. Couldn't get that to work. Other option would be to somehow use the SQL-DMO to fire the stored procedure, but not sure if that's an option (calling the procedure and passing parms with a return value). I'll save those questions for another thread if needed.


Thanks for the help in advance.