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.
| Author |
Topic |
|
Charoniv
Starting Member
21 Posts |
 Posted - 2009-02-10 : 09:24:39
|
| Not sure if this should be under transact sql or SSIS so have put it here.I have an odbc driver to access a datasource. The driver was written by the datasource provider and doesn't give much functionality i.e. we can't do much other than select columns - can't convert or transform them at all in the query.If I run select createddate from tblthen from openquery using an oledb for odbc linked server it gives an error and there's no way round that.Is it possible to access the odbc datasource by another method from openquery/rowset?In SSIS I can set it to ignore errors in that column and transform later but there's another problem.Time columns are default to 8 byte signed integers - I can change the datatype of the external column on the datsource but not the output column - means that when a value appears in the column it fails on an overflow in the datasource. That's not a problpem with openquery but just with SSIS.When run from access there is not problem importing the data so I guess it's an oledb issueThere's no hope of getting the odbc driver changed or any other access to the datasource.I'm getting to the opinion that I need to create an access database as an intermediary to get the data unless someone can come up with a better idea. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-10 : 11:27:30
|
| Using a system DSN created in windows Administration Tools:SELECT a.*FROM OPENROWSET('MSDASQL','DSN_Name';'DSN_User';'DSN_Password','select top 10 * FROM [Mydatabase].dbo.[Mytable]') AS aYou may need to replace 'MSDASQL' with your own provider |
 |
|
|
|
|
|
odbc via oledb error