WallyN


I am in the process of converting to SQL using remote views. The application opens up many tables, in many data sessions so to keep from downloading the same data over and over again I have used the NOREQUERY option of the USE command. This works great if I am in the same data session; however, if I open the same remote view in a different data session and then try to update multiple records it causes the error "File is in use by another user Error 108)".
Has anyone else run into this problem
The following program reproduces the error. It assumes that you have a local SQL Server and the ability to create a database and create a table.

CLEAR

CLEAR ALL

CLEAR PROGRAM

CLOSE DATABASES ALL

SET SAFETY OFF

LOCAL lnStatementHandle

lnStatementHandle = -1

IF TestSetup(@lnStatementHandle)

TestRemoteViewsNoRequeryInSameDataSession() && This works fine.

TestRemoteViewsNoRequeryInDifferentDataSessions() && This does not work.

ENDIF

TestShutdown(lnStatementHandle)

PROCEDURE TestSetup(tnStatementHandle)

LOCAL i

LOCAL ARRAY laTestRecords[10,2]

LOCAL lcTestConnectionString

lcTestConnectionString = "Driver=SQL Server;Server=(local);"

FOR i = 1 TO 10

laTestRecords[i,1] = i

laTestRecords[i,2] = SYS(2015)

ENDFOR

CREATE DATABASE TestDatabase

IF NOT DBUSED("TestDatabase")

RETURN .f.

ENDIF

CREATE TABLE TestTable (TestKey i, TestField c(10))

IF NOT USED("TestTable")

RETURN .f.

ENDIF

INSERT INTO TestTable FROM ARRAY laTestRecords

SQLSETPROP(0,"DispLogin",3)

SQLSETPROP(0,"PacketSize",8192)

SQLSETPROP(0,"ConnectTimeOut",120)

tnStatementHandle = SQLSTRINGCONNECT(lcTestConnectionString)

IF tnStatementHandle = -1

RETURN .f.

ENDIF

SQLSETPROP(tnStatementHandle,"QueryTimeOut",600)

IF SQLEXEC(tnStatementHandle,"CREATE DATABASE TestDatabase") = -1

RETURN .f.

ENDIF

SQLSETPROP(tnStatementHandle,"QueryTimeOut",45)

IF SQLEXEC(tnStatementHandle,"USE TestDatabase") = -1

RETURN .f.

ENDIF

IF SQLEXEC(tnStatementHandle,"CREATE TABLE TestTable (TestKey int, TestField char(10))") = -1

RETURN .f.

ENDIF

CREATE CONNECTION TestConnection CONNSTRING (lcTestConnectionString+"Database=TestDatabase")

IF NOT INDBC("TestConnection","CONNECTION")

RETURN .f.

ENDIF

CREATE SQL VIEW TestView CONNECTION TestConnection SHARE AS SELECT * FROM TestTable

IF NOT INDBC("TestView","VIEW")

RETURN .f.

ENDIF

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

DBSETPROP("TestView","VIEW","SendUpdates",.t.)

DBSETPROP("TestView","VIEW","WhereType",1)

DBSETPROP("TestView.TestKey","FIELD","KeyField",.t.)

USE TestView IN 0

IF NOT USED("TestView")

RETURN .f.

ENDIF

SELECT TestView

APPEND FROM TestTable

CLOSE DATABASES

ENDPROC

PROCEDURE TestShutdown(tnStatementHandle)

IF FILE("TestDatabase.dbc")

DELETE DATABASE TestDatabase

ENDIF

IF tnStatementHandle != -1

IF SQLEXEC(tnStatementHandle,"USE master") = -1

RETURN .f.

ENDIF

SQLSETPROP(tnStatementHandle,"QueryTimeOut",600)

IF SQLEXEC(tnStatementHandle,"DROP DATABASE TestDatabase") = -1

RETURN .f.

ENDIF

SQLSETPROP(tnStatementHandle,"QueryTimeOut",45)

SQLDISCONNECT(tnStatementHandle)

ENDIF

ENDPROC

PROCEDURE TestRemoteViewsNoRequeryInSameDataSession

LOCAL loDataSession

loDataSession = CREATEOBJECT("TestSession")

SET DATASESSION TO loDataSession.DataSessionID

OPEN DATABASE TestDatabase SHARED

USE TestView AGAIN ALIAS TestView1 IN 0

USE TestView AGAIN ALIAS TestView2 IN 0 NOREQUERY

SELECT TestView2

REPLACE TestField WITH SYS(2015) ALL

CLOSE DATABASES

ENDPROC

PROCEDURE TestRemoteViewsNoRequeryInDifferentDataSessions

LOCAL loDataSession1

loDataSession1 = CREATEOBJECT("TestSession")

LOCAL loDataSession2

loDataSession2 = CREATEOBJECT("TestSession")

SET DATASESSION TO loDataSession1.DataSessionID

OPEN DATABASE TestDatabase SHARED

USE TestView AGAIN ALIAS TestView1 IN 0

SET DATASESSION TO loDataSession2.DataSessionID

OPEN DATABASE TestDatabase SHARED

USE TestView AGAIN ALIAS TestView2 IN 0 NOREQUERY

SELECT TestView2

REPLACE TestField WITH SYS(2015) ALL && This produces the error "File is in use by another user (Error 108)"

CLOSE DATABASES

SET DATASESSION TO loDataSession1.DataSessionID

CLOSE DATABASES

ENDPROC

DEFINE CLASS TestSession AS Session

DataSession = 2

ENDDEFINE




Re: Update multiple records in remote view opened with NOREQUERY causes error: File is in use by another user (Error 108)

RVBoy


I don't have SQL Server on my local machine... but I recommend that when you open the RV, either use an alias different from its name in the DBC, or reference it in full with its database:

USE TestDatabase!TestView ALIAS TestView2 IN 0 NOREQUERY


HTH

RVBoy






Re: Update multiple records in remote view opened with NOREQUERY causes error: File is in use by another user (Error 108)

WallyN

Thank you very much for the suggestion, but that had no affect.