Cetin,

No joy. Well - partly.

DECLARE @Mva INT 
SET @Mva = 998937
 
UPDATE OPENQUERY(VFP_ADS_SERVER,'Select Mva, Vinterdak FROM Inventor2')
    SET Vinterdak = 'N'
    WHERE Mva = @Mva
SET @Mva = 998938

UPDATE OPENQUERY(VFP_ADS_SERVER,'Select Mva, Vinterdak FROM Inventor2')
    SET Vinterdak = 'Y'
    WHERE Mva = @Mva

SELECT * FROM OPENQUERY(VFP_ADS_SERVER,'Select Mva, Vinterdak FROM Inventor2')

This works fine.

Ideally I'd like to update

UPDATE I1 
    SET Vinterdak = I2.WinterTires
FROM 
    OPENQUERY(VFP_ADS_SERVER,'Select Mva, Vinterdak FROM Inventor2') I1
    INNER JOIN dbo.Inventory I2 
    ON I1... = I2....

But that does not work. You get Invalid object name 'I1'.

So I tried

DECLARE 
    @Mva CHAR(6)
   ,@WinterTires CHAR(1)
   ,@Command CHAR(100)

DECLARE MyCursor CURSOR FOR 
SELECT CAST(Mva AS CHAR(6)) AS Mva
       ,CASE WHEN WinterTires = 0 THEN 'N'
        WHEN WinterTires = 1 THEN 'J' END AS WinterTires 
FROM [DK_SQL_01].[Fleet].[dbo].[Inventory]
    WHERE [WinterTires] = 0 OR [WinterTires] = 1
    FOR UPDATE

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO
	@Mva,
    @WinterTires

WHILE (@@FETCH_STATUS <> -1)
   BEGIN
    UPDATE OPENQUERY(VFP_ADS_SERVER,'Select Mva, Vinterdak FROM Inventor2')
        SET Vinterdak = @WinterTires
        WHERE Mva = @Mva -- CURRENT OF MyCursor
    
    FETCH NEXT FROM MyCursor INTO
	    @Mva,
        @WinterTires
   END

CLOSE MyCursor
DEALLOCATE MyCursor

But that only gets you:

Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'Tbl1001'.
Msg 1018, Level 15, State 1, Line 11
Incorrect syntax near 'FASTFIRSTROW'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

And setting the transaction level to SERIALIZABLE of course gets you

OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].
Msg 7390, Level 16, State 1, Line 25
The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.

So, maybe this can only be done from VFP after all.

Anyway, thanks for your help, and if anything comes to mind I still appreciate your input.

Regards

Peter

>

>SELECT TOP 1 * FROM OpenQuery(VFP_ADS_SERVER, 'select * from INVENTOR')
>

>Cetin
>
>>Cetin,
>>
>>Great, thanks. It's there, I can see the tables but when I
>>
>>

>>SELECT TOP 1 * FROM [VFP_ADS_SERVER].[D:\FleetData]..[INVENTOR]
>>

>>
>>I get
>>
>>

>>Msg 7318, Level 16, State 1, Line 1
>>OLE DB provider 'MSDASQL' returned an invalid column definition.
>>

>>
>>I assume it's got something to do with the four part naming convention, and I've tried a few variations.
>>
>>So if you also happen to know the syntax for a select...
>>
>>
>>Peter
>>
>>>Setting linked server is the easy part but I am not sure if that would work for updating:
>>>
>>>

EXEC sp_addlinkedserver
>>>    @server = N'VFP_ADS_SERVER',  -- Your linked server name here
>>>    @srvproduct=N'Visual FoxPro Over ADS',  -- can be anything
>>>    @provider=N'MSDASQL',
>>>    @provstr=N'Driver={Advantage StreamLineSQL ODBC};
>>>ServerTypes=1;DefaultType=Visual FoxPro;
>>>DataDirectory=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\data'

>>>
>>>
>>>Cetin
>>>
>>>>Cetin,
>>>>
>>>>I've downloaded and installed Advantage ODBC.
>>>>I've searched the online docs without success - Do you know if - and if yes - how one sets it up as a linked server?
>>>>
>>>>Regards
>>>>
>>>>Peter
>>>>
>>>>>OK but you don't absolutely need to do that with T_SQL or using VFPOLEDB driver.
>>>>>1) Instead of T-SQL update via a linked server, use a remote view in VFP to update local data. You can take data offline and online.
>>>>>2) Instead of VFPOLEDB use 'Advantage Database Server' driver. It can read write VFP tables and would behave as a layer between SQL server and VFP (I didn't test with such a configuration but in theory it should work).
>>>>>http://devzone.advantagedatabase.com/dz/content.aspx?Key=20&Release=12
>>>>>
>>>>>Cetin
>>>>>
>>>>>>You would if you were in my shoes.
>>>>>>
>>>>>>When you have an old Clipper (sic!) application that you are -slowly- porting to SQL Server with a VFP front end, and have part of the app running in the new - and part in the old setup, with, I might add, the need to have your data consistent at one level or another, I'm quite sure that you also would see the need to update the old stuff with the new.
>>>>>>
>>>>>>That's just the way the cookie crumbles.
>>>>>>
>>>>>>:-)