1

I currently have two servers. One is a server with .dbf files that get updated very frequently, and the other is a server with SQL Server workgroup edition on it.

Problem: I need to update the SQL Server tables from these remote .dbf files, how is it done? I need these files to be pulled automatically from the server every X amount of hours.

Research thus far:

I believe I understand how to update tables having the .dbf file on the same server as the SQL Server database, you would use the DTS management studio provided with SQL Server. I don't believe this solution would work trying to pull these .dbf files remotely. I have looked into a Linked Server, but don't understand what that really is. These .dbf files come from a visual foxpro server. As far as i know visual foxpro has no interface like SQL? If anyone has any advice in order to point me into the right direction, this would be much appreciated. I can usually find the answers, but feel pretty helpless on this one... Thanks in advance for any effort put into this problem.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Marrs
  • 706
  • 7
  • 10
  • Are the servers networked together, and if so, can you connect to the .dbf file over the network? – mellamokb Sep 13 '11 at 02:41
  • these servers are not networked together, one is a go daddy dedicated server, the other is a dedicated server somewhere else – Marrs Sep 13 '11 at 06:58

1 Answers1

3

A linked server is basically just a pointer that lets the code within your database interact with external data sources as if they were also within your database/instance. There are some examples here for creating linked servers to FoxPro DBFs:

http://fox.wikis.com/wc.dll?Wiki~VisualFoxProDataFromSQLServer

So for example if you had a linked server to a FoxPro database called MyFoxProDB you could run a query within SQL Server like this:

UPDATE t
    SET t.foo = x.bar
    FROM dbo.LocalTable AS t
    INNER JOIN MyFoxProDB...TableName AS x
    ON t.key = x.key;

You could also consider using OPENROWSET as described in this question:

Import DBF files into Sql Server

(But personally I think the linked server will be a lot more flexible.)

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thank you for the great fast post. I think this is becoming more clear to me with your great example, but i think i am still confused on the actual physical connection of the servers. . . like shouldnt there be a connection string or something of that nature? i dont really understand how to connect to that server. Do i need to remove all remote logins to the VFP database so it can make the link happen? – Marrs Sep 13 '11 at 04:33
  • The "connection string" is part of the linked server setup - you only need to establish that once, then SQL Server negotiates it from then on. I'll admit I have literally zero experience with FoxPro, so I'm not fully aware of the security model there, or whether you're asking about concurrency... – Aaron Bertrand Sep 13 '11 at 05:04
  • @Mootz - FoxPro is a file database and the *physical connection* to the server is a file share where you actually use the .dbf files directly. There no difference if you use a linked server or SSIS, you have to access the files through a share. – Mikael Eriksson Sep 13 '11 at 05:16