Error moving to SQL Server 2000

[tfs]

I am trying to import some of the data in our vantage database to our Sql Server system to run reports and allow our other offices access to the data without letting them have access to the actual data. I have been using DTS to do the move.

The problem is that as we were copying the tables, some of the tables were giving us errors from the Merant ODBC driver that said something to the effect of:

"Column Description in JobAsmbl has value exceeding it max length or precision"

We are getting this from the Progress side that we are trying to read. How can that be? I would understand if we were moving data to Progress, but not from Progress.

I tried to solve the problem by doing the following:

select "BuyIt", substring(PurComment from 1 for 4) , "Ordered" ,"BackFlush" ,"EstScrap" ,"EstScrapType", "FixedQty" from "PUB"."JobMtl"

but was getting an error:

Syntax Error (7587) from ODBC Driver

I also found that I would pick the Merant ODBC driver for the connection object, but it would change to "Other(ODBC Driver)". Not sure why.

I did find that if I looked at the table with the Sql Explorer Tool (problem is it seems to die periodically as I am trying to run queries - fairly simple ones with one table and one filter), I also got the above error message about the illegal length, so the error did not come from the ODBC driver (unless the Sql Explorer Tool is using it). I have been told that I may have to use dbtool to fix it. I am curious at how this could have happened. We do have the error in about 13 of our tables. One of the tables has 84 of these records (out of about 300,000).

We need to find out how to solve this problem as we are behind on this project and have some deadline to reach.

Thanks,

Thomas Scheiderich

 

[tfs]

Oh, BTW I am running Progress 9.1d on an W2K Server.

 

[bound4doom]

Your probably running into a problem common problem most everyone has with progress. It is not compliant with anything. What I mean by that is you could be hitting a Date Time Field in progress, which isn't the same data as a date time field in any other database system. Instead you will have to do a manual query on the field and recieve everything and concantinate it as a string then convert it to a date time. Hitting progress Date and times with any language other than progress is a real pain.

For Example:
Select string(month(SomedateField)) + '/' + string(day(SomedateField)) + '/' + string(year(SomedateField)) FROM SomeTable

Then when reading the data you need to convert it to a date in what ever language your using.

There is another issue which you could be running into. Progress databases also store arrays in them. So a single field say a text field could be defined to have only 8 characters. But in reality it may contain a an array with a dimension of 100 but each one of those arrays elements are only 8 characters in length. This one really drives me nuts sometimes.

The only thing you can hope to do here is try to figure out how many dimensions deep the array is and then select each element of the array individually. Then put each element into its own collumn in SQL. First determin if you really must have this data. if you can skip it then do.

Our Main ERP System is all in Progress and Hopefully we will be finally getting rid of it soon because of all the integration problems. But I know what you are wanting to do. Running a query against progress that take 6-7 seconds takes only milliseconds against SQL server or Oracle. I actually ended up creating a windows service that goes out and then copies relevant tables out of progress on to SQL server just so we could have high volume web reporting and so on. I tried it against progress directly and after a few times it crashing the database from too many requests and requests taking so long It is just better to replicate it out to something a lot more robust. I found writing it out to an XML File and Parsing the XML file through .net to be a lot faster. Now progress against progress is fast but it just doesn't like to play in other sandboxes.

 

[tfs]

Unfortunately, we can't do this as the program we are using, Vantage, uses Progress exclusively.

All 13 tables we are having the problem with have the field set as x(1000). They are all comments, billing of lading discriptions. I assume the error is that somehow it accepted to much data (why I don't know) and when you try to do a query that uses the field, we get the error. At first I thought it was the Merant ODBC driver causing the problem, but we get it with the Sql Tool also.

I tried to get around it send the query:

select substring(x,1,900) from table

Got a syntax error.

Not sure if this would have fixed the problem or not, but I thought I would give it a try.

Tom.

 

[tfs]

Here is the command I tried:

select substring("PurComment",1,900) from JobMtl
where JobNum >= "015000" and <= JobNum "017000"

Here is the response.
=== SQL Exception 1 ===
SQLState=42S02
ErrorCode=-20005
[JDBC Progress Driver]:Table/View/Synonym not found (7519)

 

[tfs]

I made another change:

select substring(PurComment,1,900) from Pub.JobMtl where JobNum >= "015000" and JobNum <= "017000"

and got this error (JobNum is a valid column):

=== SQL Exception 1 ===
SQLState=42S22
ErrorCode=-20006
[JDBC Progress Driver]:Column not found/specified (7520)

I then tried

select substring(PurComment,1,900) from Pub.JobMtl

and I got the error:

"Column PurComment in JobMtl has value exceeding it max length or precision"
Apparently, it finds the error as it reads the data and before it applies the substring.

 

[knarf]

Work Round

We had similar problems here and ODBC always crashed as well

What we done was run CSV extracts every night from progress then used DTS to import the extracts. This has been working fine for well over a year.