October 3, 2007 at 5:12 pm
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
October 3, 2007 at 5:21 pm
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.
October 3, 2007 at 5:58 pm
Where you've got FLOAT field?
_____________
Code for TallyGenerator
October 4, 2007 at 9:07 am
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