Problem modifying customer number in Professional Tools

(0) Share

Report

Posted on by Community Member

Anyone see this before? We are trying to change an existing customer number from one alphanumeric to another. When running the validate it comes back with no errors, but when trying to convert it throws the following error:

[Microsoft][SQL Native Client][SQL Server] Conversion failed when converting the varchar 'SC9237728389' to data type int.

the new customer number is the same length as the existing customer number. ('SC9237728389' to 'SD9237726910')

Any help is appreciated.

Thanks

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

Re: Problem modifying customer number in Professional Tools

Brent,

Based on the error, the issue is that somewhere in your GP company database you have a table that has a column for a Customer ID that is set up as an integer data type instead of a string. The difficult part about this is that the column for the Customer ID is not always called the same in all the tables. Below is a list of the possible column names:

CUSTNMBR

STCUSTID

ENCUSTID

CPRCSTNM

CustomerVendor_ID

Bill_To_Customer

customer_id

Bill_Customer_Number

aaCustID

This is from several years ago, but should hopefully still be a valid list. You can use the following code to search for this, plugging in one of the columns names from above at a time on the line where it says 'change this' at the end:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME in

(SELECT name

FROM sysobjects

WHERE xtype = 'U')

and COLUMN_NAME like '%CUSTNMBR%' --change this

ORDER BY COLUMN_NAME, TABLE_NAME

What you're looking for in the results is anything that does not have 'char' in the DATA_TYPE column. Once you identify what table(s) are causing the problem, the fix may be different depending on exactly what those tables are. The last time I had this happen it was caused by AA tables from an installation that was 3 versions back and never upgraded. Our customer was not actually using AA, so we removed the AA objects from their database and that fixed the issue. Depending on what you find, your fix may be different, but hopefully this gets you closer to tracking this down.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: Problem modifying customer number in Professional Tools

Thanks for the reply Victoria!

I used you statement and did find a table that has CPRCSTNM being used as an INT, but I am told it is a custom table (CE001003Hold) and the values are all either -1, 0 or 1.

At this point I am going to take a copy of the db, drop the table in question, and then try running it again to see if that works (although I am consused as to why prof tools would be accessing the table if it is custom)

thanks again.

Was this reply helpful? Yes No
Verified answer

Victoria Yudin 22,768 on at

Like (0)

Report

Re: Problem modifying customer number in Professional Tools

Brent,

Hopefully someone will chime in if this is not right, but my understanding is that the PSTL tools actually look at EVERY table in your company database to make the updates. Thus, if you have 3rd party products and they have followed the column naming that GP uses, those will be updated as well, which is a good thing. However, in your case you have a custom table that you do not want updated, so it's a bad thing. This is an important consideration when creating custom tables in your GP database.

Was this reply helpful? Yes No
Richard Wheeler 75,848 Moderator on at

Like (0)

Report

Re: Problem modifying customer number in Professional Tools

CPRCSTNM does not sound like a customer number field if its only values can only be 1, 0 or -1. How would the PSTL even find this custom table unless there is a trigger tied to it from some other GP table? You want to run SQL Profiler while attempting this and then see what other tables get involved.

Do you own Changer fro CR Group? You could try running that to see if have the same issue. I would imagine you would. You could try checking for dependencies for this table to see what tables to which it has been tied.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Re: Problem modifying customer number in Professional Tools

You were correct Victoria! Changing the column name in the test company allowed Prof Tools to change customer number.

Talking with the developer it turned out the table where the column resided was for development purposes anyway and he had forgotten to drop it.

Was this reply helpful? Yes No
Victoria Yudin 22,768 on at

Like (0)

Report

Re: Problem modifying customer number in Professional Tools

Brent,

That's great, thanks for the follow up. :-)

Was this reply helpful? Yes No