Hi Cetin

I am currently using the SQL Server approach on a VFP database to maintain a local copy of a portion of the a table on each users computer. At any one time this table holds about 2000 records from the main table which holds about 150,000 records.

It only recently occured to me that I could simply look at which records had been deleted and update from that thus removing the need for a seperate table. As I am in the process of providing a SQL Server solution I thought I'd better check out if I could do the same there. On thinking about it now I see that working without the second table could be problematic.

Thanks for you input, it has cleared things up.

Regards
Geoff Scott

>>Hi
>>
>>I wish to retreive deleted rows from a remote database (eg SQL Server or VFP).
>>
>>Can I do this via a view?
>>
>>If not what would the passthrough SQL be?
>>
>>Regards
>>Geoff Scott
>
>Geoff,
>It depends on server. If from VFP then using SPT you could "set deleted off" command and query for deleted(). ie:
>

>lcDBC = 'd:\cetin\ddrive\temp\testdata.dbc'
>lcConStr = 'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;'+;
>  'SourceDB='+m.lcDBC+';SourceType=DBC;Exclusive=No;Collate=Machine;'
>
>cSQLSelect1 = "select cust_id,company,contact," +;
>  "iif(deleted(),'Deleted',space(len('Deleted'))) as DelStat from customer"
>
>cSQLSelect2 = "select cust_id,company,contact" +;
>  " from customer where DELETED()"
>
>lnHandle = Sqlstringconnect(m.lcConStr)
>? m.lnHandle
>If lnHandle > 0
>  SQLExec(m.lnHandle,'set deleted off')
>  SQLExec(m.lnHandle,m.cSQLSelect1,'myResult1')
>  SQLExec(m.lnHandle,m.cSQLSelect2,'myResult2')
>  SQLDisconnect(m.lnHandle)
>  Select myResult1
>  Browse
>  Select myResult2
>  Browse
>Endif
>

>
>In SQL server things are a little different. You should get it from within a trigger if you really need, after that deleted records are gone. ie:
>
>

>lcDatabaseName = "myTestData"
>* Create Db and borrow data from Northwind
>TEXT to m.lcSQLDbCreate textmerge noshow
>IF EXISTS (SELECT name FROM master.dbo.sysdatabases
>	WHERE name = N'<<m.lcDataBaseName>>')
>	DROP DATABASE [<<m.lcDataBaseName>>]
>
>Create Database [<<m.lcDataBaseName>>]
>ENDTEXT
>
>TEXT TO m.lcBorrowData TEXTMERGE noShow
>SELECT * Into [<<m.lcDataBaseName>>]..[Customers]
>		from [NorthWind]..[Customers]
>ENDTEXT
>
>TEXT TO m.lcTrigger TEXTMERGE noshow
>CREATE TRIGGER delTrigger ON [Customers]
> For Delete as
> 	SELECT * FROM deleted
>ENDTEXT
>
>*Delete
>TEXT TO m.lcDelete TEXTMERGE noshow
>DELETE FROM [<<m.lcDataBaseName>>]..[Customers]
>  	where Country like 'U%'
>ENDTEXT
>* Check records
>m.lcSQL = Textmerge('SELECT * from [<<m.lcDataBaseName>>]..[Customers]')
>
>lnHandle = Sqlstringconnect('DRIVER=SQL Server;'+;
>  'SERVER=cetin;Trusted_connection=Yes')
>
>SQLExec(m.lnHandle, m.lcSQLDBCreate) && Create db
>SQLExec(m.lnHandle, m.lcBorrowData) && Get test data
>SQLExec(m.lnHandle, Textmerge('USE [<<m.lcDataBaseName>>]')) && Make it default db
>SQLExec(m.lnHandle, m.lcTrigger) && Create trigger
>SQLExec(m.lnHandle, m.lcSQL,'crsBefDel') && show records before deletion
>SQLExec(m.lnHandle, m.lcDelete,'crsDelList') && Delete and get deleted - same batch
>SQLExec(m.lnHandle, m.lcSQL,'crsAftDel') && Records after deletion
>SQLDisconnect(m.lnHandle)
>
>
>Select crsBefDel
>Browse Title 'Before delete'
>Select crsDelList
>Browse Title 'Deleted records'
>Select crsAftDel
>Browse Title 'After delete'
>

Cetin