How to update a SQL Server text field through a SQL Server view by using a remote view in Visual FoxPro 6.0 SP3 and later versions

This article was previously published under Q231894
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
Specific settings are required when you update a Microsoft SQL Server text field by using a Microsoft Visual FoxPro remote view if you are accessing the text field through a SQL Server view.

Note This behavior only works correctly in Visual FoxPro 6.0 with Service Pack 3 or a later version installed.
MORE INFORMATION
When setting up a remote view to a SQL Server view that has a text field in the SQL Server view, it is important to remember that there are specific settings that will make updating the text field fail.

If you do not set the view property CompareMemo to Off, the following settings for the SQL Where Clause Includes causes an update to the text field to fail:
  • Key and Updateable Fields
  • Key and Modified Fields
These settings succeed with the Compare Memo property set to true or false
  • Key Fields Only
  • Key and Timestamp
When the text field is included in the Where clause, the SQL Server ODBC driver uses the WriteText update method. This method will not update a text field in a SQL View, and it returns the following error:
Update Conflict
At this point you can use the following command to clear the changes:
?TABLEREVERT(.T.)				

The following code sample assumes correct values for the Password, UserId, and permissions:
CLOSE DATABASES ALLSET SAFETY OFFCREATE DATABASE TextTestCREATE CONNECTION conn1 CONNSTRING 'driver=sql server;server=sphinxsql;' + ;   'database=pubs;uid=UserName;pwd=StrongPassword'myconn=SQLCONNECT('conn1')IF myconn=-1 then   MESSAGEBOX("Connection Failed")   AERROR(myerr)   DISPLAY MEMORY LIKE myerr   RETURNENDIF*!* Test for the view in SQL Server.*!* If it exists, drop it.iTestForView=sqlexec(myconn,"if exists (select * from sysobjects where name='test1'" + ;   " and type = 'V')" + CHR(13) + ;   "   begin" + CHR(13) + ;   "   drop view test1" + CHR(13) + ;   "   end")*!* Test for the table in SQL Server.*!* If it exists, drop it and recreate it.iSucc=sqlexec(myconn,"if exists(select * from sysobjects where name='ttest')" + CHR(13) + ;   "   begin" + CHR(13) + ;   "   drop table ttest" + CHR(13) + ;   "   end" + CHR(13) + ;   "create table dbo.ttest (ID char(10) primary key," + CHR(13) + ;   "   myTime timestamp,tnotes text)")IF iSucc=-1 then   MESSAGEBOX('Create Table Failed')   AERROR(myerr)   DISPLAY MEMORY LIKE myerr   SQLDISCONNECT(myconn)   RETURNENDIFMyNotes=REPLICATE('This is a test of the emergency broadcast system. ' + ;   ' If this were an actual emergency, you would be told ' + ;   ' where in your area to contact for news and additional ' + ;   'information.' ,50) + CHR(13) + CHR(10) + ;   'End of Original Memo'+ CHR(13) + CHR(10)iSucc=sqlexec(myconn,"insert into ttest (ID,tnotes) values ('1',?MyNotes)")IF iSucc=-1 then   MESSAGEBOX("Insert Failed")   AERROR(myerr)   DISPLAY MEMO LIKE myerr   sqldisconn(myconn)   RETURNENDIFiSucc=sqlexec(myconn,"create view dbo.test1 as select * from ttest")IF iSucc=-1 then   MESSAGEBOX("Create View Failed")   AERROR(myerr)   DISPLAY MEMO LIKE myerr   SQLDISCONNECT(myconn)   RETURNENDIFCREATE SQL VIEW MyTest REMOTE CONNECTION conn1 ;   AS SELECT test1.ID,test1.tNotes FROM dbo.test1DBSETPROP('Mytest','view','sendupdates',.T.)DBSETPROP('MyTest','view','WhereType',3)DBSETPROP('MyTest','view','tables','dbo.test1')DBSETPROP('mytest.id','field','keyfield',.T.)DBSETPROP('mytest.id','field','updatable',.T.)DBSETPROP('mytest.id','Field','updatename','dbo.test1.id')DBSETPROP('mytest.tnotes','Field','updatable',.T.)DBSETPROP('mytest.tnotes','Field','updatename','dbo.test1.tNotes')DBSETPROP('MyTest','view','CompareMemo',.F.)USE MyTestBROWSE NOWAITUPDATE MyTest SET tNotes=tNotes + "This is added on to the end!"?TABLEUPDATE()REQUERY()MODIFY MEMORY tNotes NOWAIT				
To see the error, please comment out the last DBSETPROP line:
DBSETPROP('MyTest','view','CompareMemo',.F.)				
REFERENCES
For additional information on SQL Views in SQL Server, search the Index for "Views" in SQL Server Books Online.

For additional information on Visual FoxPro Remote Views, search on "Remote Views" in the Index of Visual FoxPro Books Online.
kbdse
Properties

Article ID: 231894 - Last Review: 01/16/2015 19:51:30 - Revision: 3.4

  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft Visual FoxPro 7.0 Professional Edition
  • Microsoft Visual FoxPro 8.0 Professional Edition
  • Microsoft Visual FoxPro 9.0 Professional Edition
  • kbnosurvey kbarchive kbcodesnippet kbdatabase kbhowto KB231894
Feedback