Earlier question on ODBC compatibility

Hi Chris:

I am sure as you are aware only data adpaters in SQL 2005 out of the box are .NET Frameworks, in your answer to Stoobs, you state the ADO.Net framework does not have all the APIs.

We're experiencing the same problem with the product and ODBC. Unfortunately we have a legacy MUMPs DB (that KBSQLODBC layer sits on top of, its a flavor of SQL from Knowledge Based systems) that our vendor is not going to update. Given that, how can we address the ODBC driver problem with SQL 2005? Is there going to be an update to ADO.NET?

Thanks,

dfreshman

ADO.Net v2.0 hs new APIs to support metadata retrieval.

Note that you can transfer data via your existing ODBC driver and the .Net managed provider for ODBC, it's just that you have to provide a query in this case rather than being able to select from a list of tables.

|||But why is it like this?

Because at the moment it feels like ODBC has been deliberately ham-strung in order to promote OLEDB?|||

This is a tricky area, and to understand it you have to seperate concerns about the APIs and tools that use the APIs.

My responsibility is for OLE DB and ODBC so I'll limit my comments to them. If you want to follow up on tool issues or other APIs then you need to find appropriate forums and post there.

Neither OLE DB nor ODBC have been hamstrung in SQL Server 2005 in any way shape or form. They both expose new SQL Server 2005 features and datatypes to the maximum extent that is possible and consistent with each API. Microsoft is fully committed to both APIs. Yes, there was a period of time when OLE DB was being pushed harder than ODBC, but that is no longer the case. In fact for SQL Native Client we managed to speed ODBC up somewhat more that OLE DB, so we were definitely not holding back ODBC there.

|||

The trick to this was knowing not to use the Data Source View.

I had to create the Data Sources, add them to the connection manager make sure their uid and pwd were set, and then use the Data Flow and Data Source Reader with a query as you stated.

Thanks!

|||

Hi Chris

Apologies for backing off an old topic, but it seems to be the only one I have found on my search to the answer to my query. I have a large clinical system that uses MUMPS and has the KB_SQL engine. Can you with SQL 2005 connect to KB_SQL, to synconize all tables? I can connect to KB_SQL via access, but cannot do it via SQL Server, unless there is a different ODBC string required or to use a API or ADO method?

You help would be most appriciated!

M

|||

With an ODBC driver you could set up a linked server using the MSDASQL provider. SQL Server 2005 uses OLE DB internally and MSDASQL is an OLE DB to ODBC bridge. The starting point in Book Online is http://msdn2.microsoft.com/en-us/library/ms188279(SQL.90).aspx MSDASQL is described here http://msdn2.microsoft.com/en-us/library/ms191462(SQL.90).aspx. If you have an OLE DB provider for KB-SQL then it can be used directly to set up a linked server and you could also consider using SQL Server Integration Services http://msdn2.microsoft.com/en-us/library/ms141026(SQL.90).aspx.

I hope this helps,

Chris