?
Solved

Copy MS SQL table to Paradox table using ADO?

Posted on 2005-05-03
12
Medium Priority
?
1,383 Views
Last Modified: 2013-11-23
Hi,

I'm using Delphi 5 to pass data between a Paradox 9 database and an SQL 2000 database on seperate servers.  Is there an easy way to copy an SQL table to the Paradox DB.  I use an ADOQuery to create the table and want to move it to the Paradox database folder as a Paradox Table.  Is there an easy way to do this, without reading through the table and writing out each record?

TIA,
WS
0
Comment
Question by:Waterstone
  • 7
  • 5
12 Comments
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 13921230
TBatchMove but it would require the BDE.

Now you could do this with a DTS package if you setup an ODBC connection to your
Paradox data. Do you know how to do DTS using SQL2K? If so, I can show you how
to run the package from Delphi.
0
 

Author Comment

by:Waterstone
ID: 13921439

I've used the interactive SQL Server Enterprise manager tools to export data to paradox format, but not a saved DTS package.
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 13921923
When finishing building the DTS package, that's what you are doing when using the DTS Wizard,
you have an option to run immediately or save to a file. I just tested this.
I was only able to do if I used a FileDSN but I did not create an ODBC DSN using the ODBC
Administrator, I created the FileDSN in the DTS Wizard when selecting the destination.


uses .., COMObj; {for the CreateOLEObject call}

function ExecDTS(const APackageName: String): Boolean;
var
   pVarHost: OleVariant;
   EmptyStr: OleVariant;
   pkg:      OleVariant;
   i:        Integer;
begin
   pkg := CreateOLEObject('DTS.Package');
   try
     EmptyStr := WideString('');
     pkg.LoadFromSQLServer('yourdbservername',
                           'yourdbuserid',
                           'yourdbpassword',
                           0,
                           EmptyStr,
                           EmptyStr,
                           EmptyStr,
                           WideString(APackageName),
                           pVarHost);
     pkg.FailOnError := True;
     for i := 1 to pkg.Steps.Count do
       pkg.Steps.Item(i).ExecuteInMainThread := True;
     pkg.Execute;
     Result := True;
   finally
     ShowMessage('Done...');
     pkg.UnInitialize;
   end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ExecDTS('Vendors');
end;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 13921936
I imported the Vendors Data from the vendors table in DBDEMOS into SQL2K. Then I
deleted all the vendor data from the paradox table, needed to delete items and parts, too, BTW.

Then I create the DTS package using the DTS Wizard and saved as SQL Server package named
Vendors.

You can save as a file but you need to use LoadFromFile, I think, to load it.

Good Luck.
0
 

Author Comment

by:Waterstone
ID: 13922121

Thanks, Eddie.  I created the DTS package using Enterprise Manager.  When I execute it interactively, by selecting the DTS name and selecting Execute, it works fine and creates the new table.  I delete the table is created, then try t using the code you supplied and it gets an error:  Operation not supported on a Paradox table with no primay key.

Any idea why?
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 13922183
I guess your SQL server table has an index and your Paradox one doesn't?
Do you have an autoinc field?

It worked fine with the Vendors table from DBDEMOS.

0
 

Author Comment

by:Waterstone
ID: 13927970

Hi Eddie,

Very frustrating that the DTS does not run from the delphi program but does if executed from Enterprise manager.  Can you tell me what these
parameters are, and/or where I can find the documentation for them?

                           0,
                           EmptyStr,
                           EmptyStr,
                           EmptyStr,
                           WideString(APackageName),
                           pVarHost);
0
 
LVL 26

Accepted Solution

by:
Eddie Shipman earned 2000 total points
ID: 13929487
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspmethjz_8gx0.asp

Package.LoadFromSQLServer(ServerName, [ServerUserName], [ServerPassword],
                                           [Flags], [PackagePassword], [PackageGuid], [PackageVersionGuid],
                                           [PackageName], [pVarPersistStgOfHost]);

Above I said you'd have to use LoadFromFile to open a DTS package stored as a file,
well you actually use LoadFromStorageFile, here's the syntax:

Package.LoadFromStorageFile(UNCFile, Password, [PackageID],
                                           [VersionID], [Name], [pVarPersistStgOfHost]);

The parameters between the [] are optional in VB and default to null in C++ so you
can use EmptyStr for the String params and 0 for the pointer and Int params.

However, upon further review, I'm not sure if you can do that based upon this statement:
"The LoadFromStorageFile method loads the Data Transformation Services (DTS) package from the specified structured storage file"

Hint..."specified structured storage file"

Let me see if I can do it if I save the package to a local File.

0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 13929549
OK, you have to save a a structured storage file (.DTS) the option is in the types.
It works just like if you ran it from the wizard.
0
 

Author Comment

by:Waterstone
ID: 13930203

Okay, I used the code below and got the same error regarding a paradox key.  Might be a driver issue?

   pkg.LoadFromStorageFile('\\Laptop\LapTop C\MTC\Public\MTCData\MTSGWebTerms.dts',
                             'password',
                             EmptyStr,
                             EmptyStr,
                             EmptyStr,
                             EmptyStr);
0
 
LVL 26

Expert Comment

by:Eddie Shipman
ID: 13936340
May be. The ODBC only uses Paradox 4 or 5.
If your table is a later version, it may not work.

Maybe you need to update your BDE, which would
update the Paradox ODBC drivers.

http://info.borland.com/devsupport/bde/bdeupdate.html
0
 

Author Comment

by:Waterstone
ID: 13936608

Thanks for all your help.  I'll have to play with it when I have more time.  I can use it if I export to a CSV, then import it on the Paradox end.  A little bit clumsy, but it works okay.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question