April 17, 2012 at 1:32 pm
I just upgraded my main SQL Server from a SQL 2000 on Windows 2000 Server to a new SQL 2008 on Windows server 2008 64 bit. I only have one issue that I cannot figure out how to solve. Our ERP system uses COBOL Vision files for it's database. I connect to those databases using an ODBC (called acuXDBC).
Here is my problem, one of the tables I pull in from the ODBC has a field called REF_NBR. This field is a string that contains information such as invoice # and line #. It is formatted like this 123456*001. Where 123456 is the invoice number and 001 is the line number.
It would appear that SQL 2008 does not like the format of this data. When I try to view this particular table in SQL management studio or query the table from anything else, this particular field of the table comes back with the word "Conversion" in the field instead of the actual string data.
I also used the same ODBC to create a linked server and have queried the linked server using the OPENQUERY command to see if my results were any different. They were not. It did the same exact thing.
Anyone know how I can fix this?
April 17, 2012 at 1:34 pm
Just a shot in the dark, but did you try to do an explicit convert when selecting it?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 17, 2012 at 1:51 pm
Yeah. Even if I do a "SELECT CONVERT(VARCHAR, REF_NBR) AS REF_NBR FROM INV_LRB" I get the same results.
Thanks though.
April 17, 2012 at 2:02 pm
Well Convert is a SQL Server function. Did you try Cast?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 17, 2012 at 2:33 pm
Turns out that this is actually what is in the database. I was unaware that the developer of our ERP system had changed the data in that field to "Conversion" a little while back when we did a conversion to a newer version of the software.
Sorry, I guess I was inventing problems where a problem didn't exist.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply