Copy MS SQL table to Paradox table using ADO?

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
WaterstoneAsked:
Who is Participating?
 
Eddie ShipmanConnect With a Mentor All-around developerCommented:
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
 
Eddie ShipmanAll-around developerCommented:
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
 
WaterstoneAuthor Commented:

I've used the interactive SQL Server Enterprise manager tools to export data to paradox format, but not a saved DTS package.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Eddie ShipmanAll-around developerCommented:
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
 
Eddie ShipmanAll-around developerCommented:
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
 
WaterstoneAuthor Commented:

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
 
Eddie ShipmanAll-around developerCommented:
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
 
WaterstoneAuthor Commented:

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
 
Eddie ShipmanAll-around developerCommented:
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
 
WaterstoneAuthor Commented:

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
 
Eddie ShipmanAll-around developerCommented:
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
 
WaterstoneAuthor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.