Query VFP Table through SQL Linked server creates temp files in VFP directory

[BeatriceC]

I have a legacy Visual FoxPro application with flat tables that I needed to access the tables from SQL.
I create a Linked server on SQL Server Management studio.
The created a Database.
Create a stored procedure that copies certain tables to the database using openquery.
I scheduled a job to run the stored procedure at 6 minute intervals.

All works fine, except at the source location of the flat tables on FoxPro share, there are temp files being created. one file for each table in the stored procedure being written out at the 6 minute intervals.

What would cause these temp files form being written out?
Is there a configuration on the job or a command on the stored procedure that would prevent these files from being created?

I attached a screen pint of the temp files that are being written.
Here is the stored procedure.

**************
USE [DatabaseName]
GO
/****** Object: StoredProcedure [dbo].[CopyTables] Script Date: 2/19/2021 9:43:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CopyTables]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;


--1. Pull 5 tables from EPRMS to get live tool states
Drop Table TableName
SELECT *
INTO TableName
FROM OPENQUERY(LinkServerName,'select * from TableName
')

 

[Chris Miller]

In short, you can delete those in the usual TEMP folder maintenance you do (ie at startup or in regular intervals).

Those are just temp files the VFP driver creates during a query.

Edit: as an afterthought: They should be created in the user's TEMP folder. But when using it through the MSSQL service user (usually a syastem account), this might cause these files to be created in VFPs HOME() dir iinstead. Regulating the permissions of the server account - system principal or not - may help.

Chriss

 

[BeatriceC]

Great, thank you.
I will look into the permissions first. If that does not resolve it, I will plan to have the files deleted.
Thanks so much!

 

[Chris Miller]

Just btw., when deleting the files is the only way out, you might not have much to do:

If VFP (or VFP drivers) create such files and exit regularly (no crash or fatal error) VFP also removes these files itself.

But in long-running usage of VFP or a VFP driver, I have seen such files remain in the TEMP folder. You can't cause damage when you succeed to delete the files, because VFP will have files it currently needs open in an exclusive mode so you can't delete them, not even as an admin. If you succeed to delete such files, VFP doesn't need them anymore. As you see they don't grow very much, so it will be sufficient to clean up once at startup.


Chriss