Sunday, October 5, 2008

Accessing your dbf tables via a linked server in MSSQL

In my previous post I wrote about accessing dBASE tables through MSSQL. Primarily I discussed accessing dBASE data using the OpenRowset function.

If you tried this, you would have seen that it works pretty well. But as I mentioned, this is used as an adhoc procedure. Only when you need to access dBASE data a few times, would OpenRowset be a good choice. You must admit that the syntax is long. I mean its not just a select * from mytable, but you have to include the driver, and the data path etc etc. with the select statement.

This can get very frustrating if you have to do lot of dbf access. Enter in the linked server.

“A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked servers offer the following advantages:

  • Remote server access.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly.”

So in a nutshell, when you create a linked server to your dbf files, it will act as a normal server object in your applications and Enterprise Manager. You can easily use the select statement with a fully qualified table address, this will still be much shorter than using OpenRowset.

Setting up a Linked Server

First things first. Set up the linked server. I am going to be discussing how to do this through enterprise manager. As it is easier to understand.

Note: You need to have the correct version of MDAC installed. At least MDAC 2.6 I believe.

Open EM, and navigate to Server Objects in the Object Browser. If your Object Browser is not active you can activate it via the view menu or just press F8. Expand the Server Objects node, then navigate to the Linked Servers node. Right click on the Linked Servers node and select “Create New Linked Server” form the pop-up menu. A New Linked server form will appear.

Enter in the name of your linked server in the text box that appears next to the “Linked Server” label. This can be any name that you would recognise to describe the object.

Then under Server Type, choose the “Other Data Source” Radio button. The rest of the form below will then become active to fill in.

Expand the drop down box under “Provider” and select “Microsoft Jet 4.0 OLE DB Provider”. You might be tempted to select the driver for OLE DB ODBC Drivers, but there are some restrictions. See "Books Online”.

For “Product Name” enter “Microsoft Jet

In the “Data Source” text box, enter the full folder path to your dbf files. For mine I entered this: “D:\dBASE Work\R3\Santam\Data”.

For the the “Provider String” Enter the dbf table level. Because MDAC cannot understand level 7 dbf tables, entering level 7 for table version will not help. But if you enter “dBASE 5.0” this works. Don’t ask me why but it does. So for the “Provider string” enter “dBASE 5.0”.

Then go to the security page. Select “Be made using this security context” radio button found near the bottom of the window. The “Remote login” and “With password” text boxes become active to be filled in.

In the “Remote login”, enter “Admin” as the login user. Leave the password text box blank. I have tried to search the Microsoft site and the rest of cyberspace, using Google why this is needed like this, but as of yet have not found an answer. Anyway, it works as is.

You can cow click the “OK” button. Your linked server is now created as an object under linked servers node. Expand your linked server node. Then expand the “Default” Node. You will see two nodes, a Table and a View node. Expand the Tables node to see a list of table objects for the folder you stipulated.

Selecting Data

So now that you have created a linked server to your dbf files, and have seen that you can actually view the table objects. You have to conclude that it is working, right. Well in order for this to actually work you have to retrieve data. So lets go ahead and try and retrieve data.

In EM, open up a query window from within a valid MSSQL database, and type in a select statement. Now there is a slight difference to the select statement. In fact, it would be to the actual table name. You have to include the linked server and table name in a four part address. Namely, [LinkedServername]…[TableName]. Notice the three (3) periods between the linked server and the table name.

So type in a select statement like this, obviously substituting your Linked Server name and Table name and Column names.

select * from Santam…gl.

Now you can use almost any select statement. Things like joins, sub-queries, order by, where, cast, case etc. Lets try a order by and a where clause. Type in your query, I tried:

SELECT * FROM Santam...gl where Glcode > 30000 order by Date desc

I retrieved over 10000 rows in 2 seconds.

Joins are just as easy. You can join your dbf tables as well as other SQL tables as well. Lets try a simple dbf Join. To join two dbf tables I typed in:

SELECT a.account,a.date, a.amount, b.glm_stdesc FROM Santam...gl a
inner join Santam...glacc b on a.glcode = b.glm_accountglacc

Do sub-queries work? Well lets try it out. I typed in:

SELECT a.account,a.date, a.amount, b.glm_stdesc FROM Santam...gl a
inner join (select glm_accountglacc,glm_stdesc from Santam...glacc where glm_type = 'Capital') b on a.glcode = b.glm_accountglacc

And retrieved 5 rows.

So the next question that one asks, is can we join current SQL tables with linked dBASE tables. After all, you wouldn’t want to go through a whole import procedure every time you want relevant data from your dbf tables in your MSQL app. Especially if your dbf table and data is still live.

Sure you can. Test it out. I typed in:

SELECT a.account,a.date, a.amount, b.glm_stdesc, b.glm_type, c.description FROM Santam...gl a
inner join Santam...glacc b on a.glcode = b.glm_accountglacc
inner join Insyear c on a.period = c.period

So as you can see, we can use almost any select statement to retrieve our dbf data. I have not tested every SQL statement, but the most common ones work. Also note, that you can view dBASE memo fields, but you cannot see or view binary, blob data. This is because this type of data is generally stored in a separate file (DBT) and I don’t think MDAC understands it or the link to the external DBT file.

Updating Data.

What about updating data. Well this can also be achieved. I would be cautious though, as I haven't tested every possibility, I therefore do not know if it is totally compatible. But I have been able to update data.

I tried this statement:

Update Santam...claims set notes = 'This is a note update from MSSQL' where policyno >0

A note: “notes” is a memo field in my dbf table and it updated fine, with no problems. I viewed the table in dBASE, and it was perfect. Next I tried to update an indexed field, but not a keyfield though.

Update Santam...claims set policyno = 770 where claimkey = 63

Another note: I had the dbf table open in the IDE, and my row pointer was sitting on the intended row. It update fine with no problems.

Importing Data

If the above worked flawlessly, then importing data is a breeze. To import data you can then make use of one of two SQL statements. Namely the select into and the insert into. Both will work perfectly.

Conclusion.

If you want to include you dBASE data in your new MSSQL app, but still want to keep the dBASE data alive. If want the ability to update you dbf data through a MSSQL query, the the good news is, you can, with a linked server to you dbf data folder.

This means that you can create views, write SP’s and use you dbf tables in any MS product, like C#, that can access MSSQL.

Your dBASE dbf data is not dead yet.

Note:Make sure that the 'Distributed Transaction Coordinator' service is running
on your server.

8 comments:

Dave K said...

Excellent article, I followed your instructions and it worked perfectly.

I found another option for linking dbf files - instead of using the Jet database engine, you can use the OLE DB Provider for Visual FoxPro 9.0 which seems to work slightly faster (although I didn't properly test this).

You can download the OLE DB Provider for Visual FoxPro 9.0 from Microsoft at http://www.microsoft.com/downloads/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&displaylang=en

From EM Linked Server Properties, You then select:
"OLE DB Provider for Visual FoxPro" as the Provider name
Product name: leave blank
Data source: location of the folder containing the dbf files
Provider string: VFPOLEDB

Website design, web development, web hosting and Search Engine Optimization
Gil said...

Thank you so much for this article. Keep up the good work.

Website design, web development, web hosting and Search Engine Optimization
Anonymous said...

Interesting. Thanks.
Regarding MDAC: It will open v.7 dBase tables, as you say. But my experience (linking to dBase 7 tables in Access) is that long fields names get messed up. After the tenth character, the table name is inserted.

Website design, web development, web hosting and Search Engine Optimization
Robert Bravery said...

You will find anomolies with MDAC and version 7 tables. Truth be told, MDAC only supports level 5 tables. So such things as Long field names, ie greater than 10, will be a problem.

Website design, web development, web hosting and Search Engine Optimization
Anonymous said...

Robert;
I have found that v.7 dbf tables with long column names can be copied to MS Access (via ODBC and updateset) without the column names being modified. Similarly, Access tables with long column names (<32 chars) can be copied to dBase without the column names being changed. So Access seems like a useable intermediary. Access can link to v.7 dbfs, but unfortunately long column names are messed up.
E.L.

Website design, web development, web hosting and Search Engine Optimization
Radu said...

Excellent, but for the Tables and Views nodes. They don't appear... I did however run a select query and it worked!!

Sucks that I can't see tables though. Anyone have any insight on the issue? Please drop me a line at info@rad3tech.com

Thanks!

Website design, web development, web hosting and Search Engine Optimization
Delphia said...

Many thanks for your "How to ..." I had trouble with .dbf files having names longer than 8 characters. Used David K's hint about Visual FoxPro; downloaded and installed in C:\Program Files\Common Files\System\Ole DB. Used Provider choice Microsoft OLE DB Provider for Visual FoxPro. Long table names now work!

Website design, web development, web hosting and Search Engine Optimization
F.J.R.A. said...

excellent!! This is just what i needed!!! congratulations, really!!

Website design, web development, web hosting and Search Engine Optimization
Website design, web development, web hosting and Search Engine Optimization