Confused about Implicit Data Conversion Error

Good Afternoon

Hope that somone can shed some light...

I am using the HitSoftware driver to pass data to an AS400 from a SQL 7 database. Data makes it fine to 2 of the 4 tables but I cannot get the syntax correct to even get out of the S/P edtitor in SQL for the other 2.

I have fields in the SQL S/P defined as VARCHAR. The target fields on the AS400 are ALPHA. So I figured the insert statement to look like:

INSERT INTO CALICOTOTESTAS400.S105Z1NM.ORDTALIB.itmrva0# (TRID30, ITNO30, ITDS30, ITYP30, INVF30, UMST30, ITAC30, UUCA30, EGNO30, RTID30)
VALUES (@.v_action, @.v_modelNumber, @.v_modelDesc, '1', 1, 'EA', @.v_acctClass, @.v_modelyear, @.v_engRevision, @.v_spectype)

However, when I try to close the S/P window I get the error:

Error 257: Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

So, I changed the insert statement to this:

INSERT INTO CALICOTOTESTAS400.S105Z1NM.ORDTALIB.itmrva0# (TRID30, ITNO30, ITDS30, ITYP30, INVF30, UMST30, ITAC30, UUCA30, EGNO30, RTID30)
VALUES (CONVERT(binary, @.v_action), CONVERT(binary, @.v_modelNumber), CONVERT(binary, @.v_modelDesc), CONVERT(binary, '1'), 1, CONVERT(binary, 'EA'), CONVERT(binary, @.v_acctClass), CONVERT(binary, @.v_modelyear), CONVERT(binary, @.v_engRevision), CONVERT(binary,
@.v_spectype))

Now the S/P closes without the error. But when I send it to the AS400 I get unrecognizable characters in the fields.

Then I started thinking...I am not trying to do anything with binary fields.
So I am really lost. Please help.

Thanks,
Ed 330-273-7521Do you have the DDL of the table you're inserting in to?

Seems like a column may be binary...|||The INVF30 field is zoned decimal. The 1 with no CONVERT in the insert statement is that value I am trying to use.

If I change one of the CONVERT functions from binary to char where the target field is ALPHA, the implicit conversion comes. Just doesn't seem right...if I am dealing with ALPHA and VARCHAR how is it getting an error dealing with binary.

Thanks,
Ed|||What database is on the AS400?

ALPHA?

Is there a VARALPHA?

Anyway, just for kicks, convert the varcharts to char(max length) and see what happens..

Also have you done this successfully before?

Can you compare the 2 AS400 structures to see how they're different?|||Hey There -

I do not know what kind of db is on the 400. "They" kind of said "here is that tables you need to write to and the Hit Software driver we are evaluating". I did look at the table structure and the fields are CHAR not
the mis-informed ALPHA i mentioned earlier.

So, shouldn't VARCHAR to CHAR work? I did try declaring variables defined as CHAR and then Selecting the VARCHAR values into the variables. Then used those to send to the 400. Figured CHAR going to CHAR might net a different behavior. But, I could not get out of the S/P editor window due to the implicit erorr binary message.

I have never done this before. Currently, the production system is using Symbiator to perform the data transfer task. Symbiator is going away here and they need to get the data to the 400 via an alternative means.

Ed|||Sorry for not being helpful...

but can you do a select of the data from the as400 and see what it look like?

(My guess is it's DB2)

And place the results in to a temp table...

Also you say it worked for 2 tables already...

I would ask, what's different between these tables...|||OK...

I dumped the file to a spreadsheet and it appears to be in binary. I don't know how to convert binary and have to figure out how to make it
"readable".

One of the other tables only has 1 field and it is CHAR(15). The SQL source is VARCHAR(15). Second table has 3 fields that are CHAR and the SQL sources are all VARCHAR.

I can read the data fine and did not have to convert VARCHAR to binary just to get it to go across to a CHAR field.

very bizzare and frustraing at the same time

Ed