Import a tps file to SQL

[nionios]

Hi all,

This is my first post and I am happy cause after a long search on the web, I found at last posts, about clarion/topspeed database.

To my problem. I want to import a tps file to SQL Server 2005. I have install an odbc driver, I have made a DSN with SoftVelocity Topspeed driver at the ODBC Data Source in the Adminstative Tools and when I click on the import wizard of SQL Server it is not showing in the data sources of SQL the connection I ve made. I can’t figure out why this is happening and I don’t know if I am using a wrong method to accomplish the import for a tps file.

Thank you

 

[ShankarJ]

Hi!

The SV Topspeed ODBC driver is quite elementary and is only suited to server basic queries.

A few questions first :
- which version of the TS ODBC Driver?
- Is this an One-Time transfer of data or a regularly repeated transfer?
- Do you have the TopScan.exe utility program available on site?
- What errors are you getting and what query is being sent to the TS ODBC driver?
- Is purchasing a third party product an option?

Regards

 

[nionios]

Thank's Shankar for your reply.

In our company we bought a print loger program which use tps files. We contact with Clarion and they send us

- the Clarion Softvelocity Topspeed Odbc Driver v4.
-I dont think that is only one-time transfer. I haven't even make it work for once.
-I don't have TopScan.exe utility
-I am not getting any error. As i mentioned before the Sql Server is not showing in the DataSources of the import-export wizard the DSN i made in the Administrative Tools.
- I don't have an option from any third party product.

Thank you

 

[ShankarJ]

Hi!

By one-time transfer, I meant that if this transfer of data is taking place because of changing programs i.e. you are discontinuing an old one and starting a new one.

DSN's are of different kind - User,System & File. File is the best because it stores the connection parameters in a text file.

I just tried it out on MSDE by trying to create a DTS package and it worked fine. You have to choose Other Connection, choose the Driver (Softvelocity Topspeed driver) and then either the User/System DSN or the File DSN. I will try with SQL 2005 and post tomorrow.

Regards

 

[nionios]

Thank ShankarJ for the time you are dedicating to me..

No it is not one-time transfer. Once a week, or day after day, i will import the tps files to SQL for further analysis. I 'll post what i 've try so far..

1. At Choose a Data Source dialog box of SQL Server Import/Export Wizard i choose .Net Framework Data Provider for ODBC

2. At connection string i type Driver={SoftVelocity Topspeed driver (*.tps)};dbq=C:\Program Files\PrnLogServer\LOG.TPS\!;pwd=;extension=tps;oem=N;datefield=%date%;timefield=%time%;nullemptystr=N and it fills auto the rest fields except the DSN. I click next

3. At choose a destination dialog box i choose the destination database and next

4. At "Specify Table Copy or Query" dialog box the "Copy data from one or more tables or views" is disabled so i have to click on "write the a query to specify the data to transfer" and next

5. finally i have to write the SQL statement. I type "Select * From Log" and follows the error below

===================================

The statement could not be parsed. (SQL Server Import and Export Wizard)

===================================

ERROR [S0000] [SoftVelocity Inc.][TopSpeed ODBC Driver][ISAM]ISAM Table Not Found (C4OTSX.DLL)

------------------------------
Program Location:

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.SqlServer.Dts.DtsWizard.StepQuery.ParseStatement(Boolean bReportSuccess)

 

[nionios]

I passed the error above by completing the password for the tps file.. i think i am close..

 

[ShankarJ]

Hi!

Is the file password protected i.e. has an Owner ID? I have been told that the ODBC driver has problems with encrypted files.

Also, why the \! after the LOG.TPS. That is necessary only if the TPS file has multi-tables. Otherwise it should either be LOG.TPS or LOG.TPS\!UNNAMED. If it is multi-table, it should be LOG.TPS\!<TableName>.

Regards

 

[nionios]

The file is password protected but i did not face any problem with ODBC driver. The file is succesfully imported. It doesn't look like a multi-table but it is imported only with \!.

Now that the file is imported i noticed that the date and time fields are not shown correct. The created table has datatype Int for Date and Time fields. I tried to change the datatype but it still not showing correct. Any idea how can i correct the fields to a valid format;

Thank you!

 

[ShankarJ]

Hi!

Clarion Date is stored as the no of days from Dec 28, 1800. Clarion Time is the number of 1/100 seconds from midnight + 1.

Some functions to help you convert ::

CREATE FUNCTION dbo.SQL2ClaDate (@SQL_Date_Time DATETIME)
RETURNS INT AS
BEGIN
RETURN ISNULL(CAST(DATEDIFF(d, '1800-12-28', @SQL_Date_Time) AS INT), 0)
END

CREATE FUNCTION dbo.SQL2ClaTime (@SQL_Date_Time DATETIME)
RETURNS INT AS
BEGIN
RETURN ISNULL((CAST(DATEDIFF(ss, CONVERT(DATETIME, CONVERT(VARCHAR(10), @SQL_Date_Time, 120) + ' 00:00:00.000'), @SQL_Date_Time) AS INT) * 100) + 1, 0)
END

CREATE FUNCTION dbo.Cla2SQLDate (@Cla_Date INT)
RETURNS DATETIME AS
BEGIN
RETURN DATEADD(day, @Cla_Date, '1800-12-28')
END

CREATE FUNCTION dbo.Cla2SQLTime (@Cla_Time INT)
RETURNS DATETIME AS
BEGIN
RETURN DATEADD(second, INT((@Cla_Time - 1) / 100), 0)
END

CREATE FUNCTION dbo.Cla2SQLTimeStr (@Cla_Time INT)
RETURNS CHAR(12) AS
BEGIN
RETURN RTRIM( RIGHT( (CAST(DATEADD(second, FLOOR((@Cla_Time - 1) / 100), 0) AS CHAR(24))), 13 ) )
END

You would need to use the Cla2SQLDate & Cla2SQLTime/Cla2SQLTimeStr functions.

Regards

 

[MarkGoldberg]

Welcome Nionios,


You wrote: "This is my first post and I am happy cause after a long search on the web, I found at last posts, about clarion/topspeed database."

Clarion has many strong online communities.
Here are a couple...

newsgroups: comp.lang.clarion
newsgroups: news.softvelocity.com
(use your clarion serial number to login)

see

http://www.softvelocity.com/

-- authors of Clarion

http://www.clarionmag.com/

LONG LIST OF PRODUCTS:

http://www.encouragersoftware.com/profile/clarlinks.html

Par2

http://www.par2.com

IceTips on the Web

http://www.icetips.com

Capesoft

http://www.capesoft.com

HTH,
Mark Goldberg

 

[nionios]

Thank you ShankarJ.. Thank you very much..!

 

[nionios]

Thank's for the info MarkGoldberg.. I will have a tour..

 

[ShankarJ]

Hi!

Small typo on my part It should be ::

CREATE FUNCTION dbo.Cla2SQLTime (@Cla_Time INT)
RETURNS DATETIME AS
BEGIN
RETURN DATEADD(second, FLOOR((@Cla_Time - 1) / 100), 0)
END

Regards

 

[nionios]

You keep me nicely busy. Thank you once again..