Varchar code being converted to a strange number? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Varchar code being converted to a strange number?

Author

Topic

dabooj
Starting Member

7 Posts

Posted - 2009-06-03 : 05:39:33

Hi guys,

I've got a data import process which imports data from an Excel spreadsheet to a local Access Database table. From here, the data is then transfered to a master table in SQL Server 2005.
There is a code within the data which is causing me a problem. When it is imported from the spreadsheet to the temporary local table, the code data looks fine & is in the correct format BUT when it is then transfered to the sql server table, the code shows up as a long scientific numeric value.
(i.e. code "1022023" in the excel spreadsheet comes out as "1022023" in the import to the local table but when transfered to the sql server table (using an append query) it appears as "1.022023e0").
This happens for quite a few of the codes but not all. I have checked the field types in all the tables (all varchar or equivalents) and i've made sure there are enough characters allocated but it still does the same thing.

Any ideas why this is happening?

Regards,

Shuja

Shuja Ahmad
Systems Developer

jasraja
Starting Member

22 Posts

Posted - 2009-06-03 : 09:20:09

Which driver are you using to access the temp local table? Try using an alternate driver.

jholovacs
Posting Yak Master

163 Posts

Posted - 2009-06-03 : 09:33:14

in your excel sheet it's possible it's referencing these numbers by scientific notation. I think there's a way to prevent that... easiest way I think would be to treat the column in excel as a text field.

Also, check your field type for the destination sql column, make sure it's set to int.

SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources