• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2166
  • Last Modified:

How to open Paradox 7.x Database files in Sql Server

Hi,
I want to open paradox database files (*.db) in sql server 2005 or 2008. The db files are in version paradox 7. Can anyone give me a solution to do that?
0
MickaelE
Asked:
MickaelE
  • 3
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
No, You can't open Paradox Database Files in SQL Server.
Instead open your (*.db) database file in Paradox and then Import records into SQL Server if required..
0
 
PJBXCommented:
You can use OpenQuery and create a link to the Paradox database:
--create the linked server
exec sp_addlinkedserver @server = 'MyLinkedServerName',  
@srvproduct = '',  
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = '\\network share\Paradox directory where .db files are located',  -- Please specify only folder where your DB file(s) are located
@location = '',  
@provstr = 'Paradox 5.X;'
 
 
select * from openquery(MyLinkedServerName, 'select * from city ')
select * from openquery(MyLinkedServerName, 'select * from city where city="testCity"')
 
insert into openquery(MyLinkedServerName,'select * from city')(city, county, taxjur)
  values('testCity','testCounty','testTax')
select * from openquery(MyLinkedServerName, 'select * from city where city="testCity"')
   
update openquery(MyLinkedServerName,'select * from city where city = "testCity"')
set county = 'test2County2'
select * from openquery(MyLinkedServerName, 'select * from city where city="testCity"')
 
delete openquery(MyLinkedServerName,'select * from city where city = "testCity"')
select * from openquery(MyLinkedServerName, 'select * from city

Please see:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_24067488.html
0
 
Mark WillsTopic AdvisorCommented:
Now, Paradox 7 files can be a little bit "testy" as you can see from the above link...

Think this one might be of more benefit : http://www.experts-exchange.com/Database/Miscellaneous/Q_24978238.html

So, there are a few ways of achieving it, and yes it can be done...

Best way I know how to do it is via a linked server. Make sure you have the ACE drivers (can get them from MS) http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en  happily lives side by side any existing stuff...


-- create a linked server - this (and the login) can be a one time step - or - on demand...
EXEC master.dbo.sp_addlinkedserver @server = N'MyPDOX'
           , @srvproduct=N'Paradox7'
           , @provider=N'Microsoft.ACE.OLEDB.12.0'
           , @datasrc=N'C:\WHATEVER\WORKING'
           , @provstr=N'Paradox 7.x'

--Set up login mappings (just ADMIN - wants something).
EXEC sp_addlinkedsrvlogin MyPDOX, FALSE, NULL, Admin, NULL
GO

--List the tables in the linked server - normally do this just as a test to see what SQL can see...
EXEC sp_tables_ex MyPDOX
GO

-- then use the four part identifier like any other table
SELECT * FROM mypdox...debtorversion7

-- at the end of it all, need to drop the server, or, keep it up (but then do not try to create it every time either)
sp_dropserver 'MyPDOX', 'droplogins';
GO


0
 
Mark WillsTopic AdvisorCommented:
Oh, and with the linked server, most definitely can use the openquery() as if it were a table as per PJBX post...

You will also note from my link above that the Jet engine did work in that other thread, and while it works fine for paradox 5 tables, it can run into problems for paradox 7 - so take the time to test and check which is the best solution for you.
0
 
Mark WillsTopic AdvisorCommented:
Well I know that my post works. ID:26129607
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now