PROGRESS import of field freezes SQL Server

hi,
I've got a SQL 2005 with an odbc connection to a PROGRESS database. We can
transfer data from the progress to the sql but there is 1 field we can not
transfer. This field, proclaimed by the PROGRESS administration, is a field
wich can contain an empty string or up to 8 characters.
But when we try to do a 'select problemfield, goodfield1,goodfield2 from
pub.table' in the import and export wizard and try to preview this select,
SQL Server freezes for a loooooong time resulting in an error "defined by
component".
We have tried to put the problem field into the select clause, with the
where clause without the where clause... always the same result. The datatyp
e
that we use is a varchar(8) or more but it always freezes.
Does anyone have an idea?
Note: when progress exports to a flat file and we import that flat file,
this works without any problems. So instead of using the odbc connection to
transfer the data overnight, we would like to use flat files and import them
with Integration S. Good solution or not?Ok,
We managed to get the import; but via a flatfile source not with the odbc
connector. Wat went wrong I cant say, it wasn't possible to import the field
s
via a ODBC connection, the flatfime however imported like nothing (although
it where millions of rows)
We have to do a lot of cleansing, the progress software sets a ? in fields
that are not filled in. In this case, implicit conversions to smalldatatime
fails. Now that we have everything in nvarchar, I'm sure SSIS will bring us
where we want.
Thx,
"Philippe" wrote:

> hi,
> I've got a SQL 2005 with an odbc connection to a PROGRESS database. We can
> transfer data from the progress to the sql but there is 1 field we can not
> transfer. This field, proclaimed by the PROGRESS administration, is a fiel
d
> wich can contain an empty string or up to 8 characters.
> But when we try to do a 'select problemfield, goodfield1,goodfield2 from
> pub.table' in the import and export wizard and try to preview this select,
> SQL Server freezes for a loooooong time resulting in an error "defined by
> component".
> We have tried to put the problem field into the select clause, with the
> where clause without the where clause... always the same result. The datat
ype
> that we use is a varchar(8) or more but it always freezes.
> Does anyone have an idea?
> Note: when progress exports to a flat file and we import that flat file,
> this works without any problems. So instead of using the odbc connection t
o
> transfer the data overnight, we would like to use flat files and import th
em
> with Integration S. Good solution or not?

Progress Database ODBC Source

Hello,

I need to pull in data from a progress database into a table in SQL server 2005. I have an ODBC conncetion on the server where I am working.

I am new to SSIS but have used DTS. I need to succsefully pull the data into a table in SQL server 2005 from this progress database.

I have setup my ODBC connection but I cannot pull the data using this connection. The connection test succeeded for the connection manager. When I attempt to use the datasource reader and point to the ODBC Connection Manager entry I recieve Cannot acquire a managed connection from the run time connection manager

Any help is appreciated.

Thanks,

Tony

Hi Tony,

you need to configure your connection as ADO.NET for ODBC. DataReader source can only work with ADO.NET connections.

|||Try to create connection using .NET Provider\ODBC Data Provider I have tried it at my end it's working fine.

Program type out of range - S1003

We have a Powerbuilder 4.0 application that used to run on SQL Server 7.0. Recently, we migrated our database to SQL Server 2000. We can do ODBC and can connect to the database from any tools such as Access, Excel and register the new SQL Server 2000 to other SQL Server, but when we try to use the client application, the user can not connect to the database. We receive the error message as the subject line. The user interface opens up but there is no data. The orginal program was written in Powerbuilder 4.0 by our vendor who probably does not have a copy of the source codes in proper state as I understand from talking to them. My question is, is there something that I can do to help my user connect to the database by using the client interface? This is getting a bit frustrating as no one really has an answer for it. I tried to install Powerbuilder 9.0 just to see if that updates any driver file that my machine has and that would be recongnize by the client programs, but it will not.

What can we do or what suggestions anyone can extend? Thanks for any inputs.What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.|||Originally posted by rnealejr
What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.

Actually, in the SQL Server log there is nothing since no connection was established from the client side.|||Originally posted by wellsound
Actually, in the SQL Server log there is nothing since no connection was established from the client side.

Well then it's not a sql server problem or issue...|||Yes I knew that, but since this is a Powerbuilder-client app with SQL Server backend, I have known that there are problems with the 16 bit ODBC driver that Powerbuilder used in their earlier version which is what the client app is built on, that is Powerbuilder Version 4.0 and that is exactly why I thought of asking in this forum to see if someone may have a related answer to that. But thanks for your reply.|||Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ?|||I think I've seen this error before when a variable or column is unable toa accept a value submitted to it. Is there a procedure that runs when the user logs in, and are there any parameters passed to the procedure?

blindman|||Originally posted by rnealejr
Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ? This is what we have in the configuration file
[<name of the sytem]
DBMS=SYB SQL Server v4.x
ServerName=, server name>
Database= <database name>
DbParm=appname='name of the app>',release='4.2'

We made a number of changes to this such as change the SYB SQL Server v4.x to DSN and on the DbParm we have used = dsn=<data source name> database = <database> name

None of these seem to work. The only thing we had was the client user interface opens up but no data comes up.