Conversion Error

  • I have an update statement that used to work at one time, but is now giving me conversion errors.

    I am updating a table with values found in a linked Excel spreadsheet. MS SQL 2000 and Excel 2003.

    Here is the syntax used to create the Excel Link:

    sp_addlinkedserver N'Uniforms', N'Jet 4.0',

    N'Microsoft.Jet.OLEDB.4.0',

    N'C:\CardIssuanceNumbers.xls',Null,N'Excel 5.0'

    GO

    sp_Addlinkedsrvlogin N'Uniforms', false, sa, N'ADMIN', NULL

    GO

    I can successfully select the Excel data using the following statement:

    SELECT ApplicationID,CustomerID,FirstName,LastName,Grade,Name,Cast(UserString1 As int) AS UserString1

    FROMUniforms...Data

    ORDER BY UserString1

    GO

    But when I run the following update I get the "Error converting data type varchar to float" errors:

    UPDATECustomers

    SETCustomers.UserString1 = cast(Uniforms.Data.UserString1 as int)

    FROMCustomers join Uniforms...Data

    ONCustomers.CustomerID = Uniforms.Data.CustomerID

    WHERECustomers.CustomerID = Uniforms.Data.CustomerID

    Go

    The datatype of customers.userstring1 is varchar(30) and no data formatting has been done to the Excel data.

    The frustrating part is that this exact statement did work at one time.

    Anyone Please Help.

    Thank you,

    Dan Tuma

  • usually this is caused by an errant space in the cell.

    First you can try trimming the data before you cast it.

    To find the row with the issue try using top 10 in your update and if that works try top 20 and so on until you get the error. if you have a lot of rows try top 50 percent and move up or back until you locate the trouble maker.


  • Where you've got FLOAT field?

    _____________
    Code for TallyGenerator

  • Thank you all for your help. Due to it I have been able to resolve my issue.

    Dan Tuma

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply