Updating DBase File From SQL Server Table

[stevesaved]

Greetings,

Does anyone know how to connect SQL Server to a DBase (.dbf) file and update it using rows from a SQL Server table.

Thanks for your help.

 

[DLSQL]

You have two approaches: Linked servers or Data Transformation Services (DTS).

With Linked Servers you create a linked server connection (go to Security in EM). Then you right four part queries:
Insert LinkedServer.Catalog.owner.table (col1, col2)
SELECT col1, col2 from mytable.

With DTS you create a connection to the DBF file through ODBC and then use a DATA Pump Task or a Data Driven Query to pump your data to the DBF. (See Professional SQL Server 2000 Dts by Mark Chaffin, T. Robinson, Brian Knight, Todd Robinson.)

DL
MCDBA, MCSD, MCT, etc.

 

[stevesaved]

I would like to use the linked server, but I'm having problems with it.

Any help will be greatly appreciated:

I created the linked server using:

sp_addlinkedserver
@server='PDMdBASE',
@srvproduct='dBASE 4',
@provider='MSDASQL',
@datasrc='PDMdBASE'

PDMdBASE is a System DSN (ODBC data source) which points to directory O:\ESPEC\PDM

When I run: exec sp_catalogs PDMdBASE
I get: O:\ESPEC\PDM

When I run: SELECT * FROM PDMdBASE.[O:\ESPEC\PDM]..[ATAB]

I get: Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'PDMdBASE' does not contain table '`O:\ESPEC\PDM`\`ATAB`'. The table either does not exist or the current user does not have permissions on that table.

I do not understand this error as ATAB.DBF exists in O:\ESPEC\PDM.

Any clues?

Thanks for your help.

 

[DLSQL]

Since you have the linked server setup you can use the following stored procedures to see what catalaogs and tables exist:
sp_linkedservers
sp_primarykeys
sp_catalogs
sp_indexes
sp_column_privileges
sp_table_privileges
sp_columns_ex
sp_tables_ex
sp_foreignkeys

like so
exec sp_catalogs 'PDMdBASE'
and then
sp_tables_ex @table_server = 'PDMdBASE' ,
@table_type = 'table'



DL
MCDBA, MCSD, MCT, etc.

 

[stevesaved]

Thanks for all your help.

I decided to use the OPENDATASOUCE cammonad to access and update the dBASE files.

Regards.