| Author |
Topic |
|
cobrinha
Starting Member
8 Posts |
 Posted - 2009-08-19 : 11:59:11
|
| I have coordinate values such as 1783510.64913 that I need to get into a SQL Server 2008 database. The target column(s) are varchar(20). The source values are in an Access 2003 database in Number columns.I can't change the data type of the source or destination columns.How can I get this data from Access to SQL Server 2008 using T-SQL? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-19 : 13:16:58
|
>>I can't change the data type of the source or destination columns.Why in the world would you someone design a system where "coordinate values" are stored in a varchar column???Be One with the OptimizerTG |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-19 : 13:21:13
|
| But to get Access data into sql server (using t-sql) you can do an INSERT/SELECT where the SELECT is a query using OPENROWSET.Be One with the OptimizerTG |
|
|
|
cobrinha
Starting Member
8 Posts |
Posted - 2009-08-19 : 13:46:13
|
| Excellent question. Unfortunately, it's not one I can answer. It is a third party schema that I am stuck with.I have tried OPENROWSET with the following error:OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "'I:\Web Site\db.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".Not sure why that pops up, as the path is valid. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-19 : 13:55:57
|
| Probably the I: drive is not mapped for the sql service account. Try full UNC path naming convention.Be One with the OptimizerTG |
|
|
|
cobrinha
Starting Member
8 Posts |
Posted - 2009-08-19 : 13:59:05
|
| You, my friend, are a genius. |
|
|
|
cobrinha
Starting Member
8 Posts |
Posted - 2009-08-19 : 14:00:54
|
| Problem, though... the coordinates are still coming across incorrectly. Example: 1.78258e+006 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-19 : 14:19:00
|
| in your OPENROWSET query, you'll need to to a convert. Probably a nested convert:convert(varchar(39), convert(decimal(38,9), <coordinatesCol>))Be One with the OptimizerTG |
|
|
|
cobrinha
Starting Member
8 Posts |
Posted - 2009-08-19 : 14:23:05
|
| That did the trick. Thank you for all the help, I appreciate it! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-08-19 : 14:24:24
|
| You're welcome - glad it worked :)Be One with the OptimizerTG |
|
|
|
|
Data Conversion Issue