Ìå ôç äéáäéêáóßá SQL Server 2000 äéáíÝìïíôáé åñùôÞìáôá ìå ôá áñ÷åßá .dbf FoxPro

/* OPENROWSET and OPENQUERY examples with VFP via ODBC OLE DB provider */ 

/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc
Modify your code accordingly for differences in location or DBC name */ 

--====================================================
-- Using DBC file , read and update
--====================================================
-- OPENROWSET DSN-less example

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country != "USA" order by country')
go

select * from  openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
go

Update  openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="WA"')
set region = "Seattle" 
go

-- check to verify which rows were updated
select * from  openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where region="Seattle"') 
go

-- OPENROWSET DSN example
/* Note the DSN Example might fail if SQL Server is configured to use a local account.*/ 
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Database;
SourceDB=e:\VFP98\data\Testdata.dbc;
SourceType=DBC',
'select * from customer where country != "USA" order by country'
go

/* sp_addlinkedserver examples */ 
-- sp_addlinkedserver example with DSN

/* You will need to make a DSN and point it to the Testdata database. 
Modify your code accordingly for differences in location or DBC name */ 

/* Note this Example may fail if SQL Server is configured to use a local account.*/ 
sp_addlinkedserver 'VFP Testdata Database With DSN', 
    '', 
    'MSDASQL',
    'VFP System DSN'
go

sp_addlinkedsrvlogin 'VFP Testdata Database With DSN', FALSE, NULL, NULL, NULL
go 

SELECT *
FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where region = "Seattle"') 
go

-- Update using OpenQuery
Update OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where region="WA"') 
set region = "Seattle" 
go

/* SP_addlinkedserver example with DSN-less connection */ 

/* This example also depends on the sample files Testdata.dbc
Modify your code accordingly for differences in location or DBC name */ 

sp_addlinkedserver 'VFP Testdata Database With No DSN', 
    '', 
    'MSDASQL',
    NULL,
    NULL,
'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP98\data\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'
go

sp_addlinkedsrvlogin 'VFP Testdata Database With No DSN', FALSE, NULL, NULL, NULL
go

SELECT *
FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country != "USA" order by country') 
go

--====================================================
-- Using VFP 6.0 driver, read and update data from VFP sample dbf files
--====================================================

-- OPENROWSET DSN-less example

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country')
go

-- perform UPDATE

Update openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where region="Seattle"')
set region = "WA"
go

-- verify update

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where region = "WA"')
go<BR/>

-- OPENROWSET DSN example
-- DSN points to the folder where .dbf files are.
/* Note this Example may fail if SQL Server is configured to use a local account.*/ 
select * from openrowset('MSDASQL',
'DSN=Visual FoxPro Tables;			
SourceDB=e:\VFP98\data;
SourceType=DBF',
'select * from customer where country != "USA" order by country') 
go"?
-- SQL Server's QUOTED_IDENTIFIER has to be set to OFF.

 

SET QUOTED_IDENTIFIER OFF

 

            -- OPENROWSET DSN-less example

            

            select * from openrowset('MSDASQL',

            'Driver=Microsoft Visual FoxPro Driver;

            SourceDB=e:\VFP90\samples\data\Testdata.dbc;

            SourceType=DBC',

            'select * from customer where country = "USA" order by city')

            go

            

            select * from  openrowset('MSDASQL',

            'Driver=Microsoft Visual FoxPro Driver;

            SourceDB=e:\VFP90\samples\data\Testdata.dbc;

            SourceType=DBC',

            'select * from customer where region="WA"')

            go

            

            Update  openrowset('MSDASQL',

            'Driver=Microsoft Visual FoxPro Driver;

            SourceDB=e:\VFP90\samples\data\Testdata.dbc;

            SourceType=DBC',

            'select * from customer where city = "Seattle"')

            set region = "WW" 

            go

            

            -- check to verify which rows were updated

            select * from  openrowset('MSDASQL',

            'Driver=Microsoft Visual FoxPro Driver;

            SourceDB=e:\VFP90\samples\data\Testdata.dbc;

            SourceType=DBC',

            'select * from customer where region="WW"') 

            go

            

            -- OPENROWSET DSN example

            /* Note the DSN Example might fail if SQL Server is configured to use a local account.*/ 

            select * from openrowset('MSDASQL',

            'DSN=Visual FoxPro Database;

            SourceDB=e:\VFP90\samples\data\Testdata.dbc;

            SourceType=DBC',

            'select * from customer where country = "USA" order by city')

            go

            

            /* sp_addlinkedserver examples */ 

            -- sp_addlinkedserver example with DSN

            

            /* You will need to make a DSN and point it to the Testdata database. 

            Modify your code accordingly for differences in location or DBC name */ 

            

            /* Note this Example may fail if SQL Server is configured to use a local account.*/ 

            sp_addlinkedserver 'VFP Testdata Database With DSN', 

                '', 

                'MSDASQL',

                'VFP System DSN'

            go

            

            sp_addlinkedsrvlogin 'VFP Testdata Database With DSN', FALSE, NULL, NULL, NULL

            go 

            

            SELECT *

            FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ') 

            go

            

            -- We will set the region back to "WA" if it currently is "WW".

            -- Update using OpenQuery

            Update OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ') 

            set region = "WA" 

            go

            

            -- Make sure that the region got updated.

            SELECT *

            FROM OPENQUERY([VFP Testdata Database With DSN], 'select * from customer where city = "Seattle" ') 

            go

            

            /* SP_addlinkedserver example with DSN-less connection */ 

            

            /* This example also depends on the sample files Testdata.dbc

            Modify your code accordingly for differences in location or DBC name */ 

            

            sp_addlinkedserver 'VFP Testdata Database With No DSN', 

                '', 

                'MSDASQL',

                NULL,

                NULL,

            'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP90\samples\data\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'

            go

            

            sp_addlinkedsrvlogin 'VFP Testdata Database With No DSN', FALSE, NULL, NULL, NULL

            go

            

            SELECT *

            FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country = "USA" order by city') 

            go

            

            --====================================================

            -- Using VFP 6.0 driver, read and update data from VFP sample dbf files

            --====================================================

            

            -- OPENROWSET DSN-less example

            

            select * from openrowset('MSDASQL',

            'Driver=Microsoft Visual FoxPro Driver;

            SourceDB=e:\VFP90\samples\data;

            SourceType=DBF',

            'select * from customer where country != "USA" order by country')

            go

            

            -- perform UPDATE

            

            Update openrowset('MSDASQL',

            'Driver=Microsoft Visual FoxPro Driver;

            SourceDB=e:\VFP90\samples\data;

            SourceType=DBF',

            'select * from customer where city = "Seattle"')

            set region = "WW" 

            go

            

            -- verify update

            

            select * from openrowset('MSDASQL',

            'Driver=Microsoft Visual FoxPro Driver;

            SourceDB=e:\VFP90\samples\data;

            SourceType=DBF',

            'select * from customer where region = "WW"')

            go

            

            -- OPENROWSET DSN example

            -- DSN points to the folder where .dbf files are.

            /* Note this Example may fail if SQL Server is configured to use a local account.*/ 

            select * from openrowset('MSDASQL',

            'DSN=Visual FoxPro Tables;                              

            SourceDB=e:\VFP90\samples\data;

            SourceType=DBF',

            'select * from customer where country != "USA" order by country') 

            go

 '/* These OPENROWSET examples depend on the sample files VFP98\data\Testdata.dbc

'Modify your code accordingly for differences in location or DBC name */

 

--*====================================================

--* Using the DBC file, reading and updating data.

--*====================================================

--* A couple of OPENROWSET queries.

select * from openrowset('VFPOLEDB',

'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',

'select * from customer where country != "USA" order by country')

go

 

Select * from openrowset('VFPOLEDB',

'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',

'select * from customer where region="WA"')

go

 

--* Need to use an error trapping routine with the UPDATE and DELETE functions:

select * from

 openrowset('VFPOLEDB',

   'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

   'Update Customer Set city = "SEATTLE" where region = "WA" ') 

go

declare @upderror int

select @upderror = @@error

print ''

if @upderror != 7357 and @upderror != 0

            print  'Update failed with error '+convert(varchar(5),@upderror)

else

            print 'Ignore the error above, the Update succeeded'

go

 

 

-- check to verify which rows were updated

select * from  openrowset('VFPOLEDB',

'E:\VFP7junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

'select * from customer where region = "WA"')

go

 

--* Change the City field back to "Seattle".

 

select * from

 openrowset('VFPOLEDB',

   'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

   'Update Customer Set city = "Seattle" where region = "WA" ') 

go

declare @upderror int

select @upderror = @@error

print ''

if @upderror != 7357 and @upderror != 0

            print  'Update failed with error '+convert(varchar(5),@upderror)

else

            print 'Ignore the error above, the Update succeeded'

go

 

--* The DELETE fucntion also causes an error, but the DELETE works.

select * from

 openrowset('VFPOLEDB',

   'E:\VFP7Junk\Testdata.DBC';'Exclusive=No';'Data Source=DBC',

   'Delete from Customer where country = "Spain" ') 

go

declare @delerror int

select @delerror = @@error

print ''

if @delerror != 7357 and @delerror != 0

            print  'Delete failed with error '+convert(varchar(5),@delerror)

else

            print 'Ignore the error above, the Delete succeeded'

go

 

--* Check to see that the records are deleted.

Select * from openrowset('VFPOLEDB',

'e:\vfp7junk\Testdata.dbc';'Exclusive=No';'Data Source=DBC',

'select * from customer where country = "Spain"')

go

 

 

--* Here are some examples using the VFP OLE DB Provider to create Linked Servers.

--* Using sp_addlinkedserver to create the Linked Server.

sp_addlinkedserver @server='VFP_Linked_Server',

@srvproduct='Microsoft Visual FoxPro OLE DB Provider', 

@provider='VFPOLEDB',

@datasrc = 'E:\vfp7junk'

go

 

SELECT *

FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "Seattle"')

go

 

 

-- The Update command will update the table with the OPENQUERY function when using the 

-- linked server, but the same error 7357 error will occur.

select * from

 OPENQUERY([VFP_Linked_Server],

   'Update Customer Set city = "SEATTLE" where region = "WA" ') 

go

declare @upderror int

select @upderror = @@error

print ''

if @upderror != 7357 and @upderror != 0

            print  'Update failed with error '+convert(varchar(5),@upderror)

else

            print 'Ignore the error above, the Update succeeded'

go

 

 

-- Check and see if the City field is all uppercase with "SEATTLE".

SELECT *

FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where region = "WA"')

go

 

--* Let's check for how many records have the word "London" in the City field.

SELECT *

FROM OPENQUERY([VFP_Linked_Server], 'select * from customer where city = "London"')

go

 

-- We can also use the Delete command to remove records with the OPENQUERY function when using the 

-- linked server, but the same error 7357 error will occur.

select * from

 OPENQUERY([VFP_Linked_Server],

   'Delete from Customer where city = "London"') 

go

declare @delerror int

select @delerror = @@error

print ''

if @delerror != 7357 and @delerror != 0

            print  'Delete failed with error '+convert(varchar(5),@delerror)

else

            print 'Ignore the error above, the Delete succeeded'

go

 

 

/* SP_addlinkedserver example with DSN-less connection */

 

/* This example also depends on the sample files Testdata.dbc

Modify your code accordingly for differences in location or DBC name */

 

sp_addlinkedserver 'VFP Testdata Database With No DSN',

    '',

    'MSDASQL',

    NULL,

    NULL,

'Driver={Microsoft Visual FoxPro Driver};UID=;PWD=;SourceDB=e:\VFP8junk\Testdata.dbc;SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;'

go

 

 

SELECT *

FROM OPENQUERY([VFP Testdata Database With No DSN], 'select * from customer where country = "USA" order by country')

go