server via ODBC - help!

Hi All
I have set up several linked SQL servers which work ok - but I'm having
trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
The ODBC points to an 'Alchemy' database and uses an Alchemy driver
ironically i can set up linked tables in an Access DB, via ODBC - and I can
pull the same data into Excel via ODBC - so there is no problem connecting to
Alchemy using other MS apps.
When I add the linked server in SQL, it appears to be added but no tables
are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
returns an error "Cannot initialize the data source object of OLE DB provider
'MSDASQL' "
Its driving me crazy...Anyone have any ideas?
Thanks in advance
Make sure the driver is compatible with the MDAC version on
the SQL Server box. Make sure you are using the latest
driver. Make sure the driver is correctly installed on the
SQL Server box.
Sometimes those issues can be related to issues specific to
the driver and interactions with service accounts. Try
logging onto the server using the service account for SQL
Server and install the driver while logged in under the
service account SQL Server is running under.
-Sue
On Thu, 28 Sep 2006 15:29:02 -0700, grasshopper
<nospam@.nospam.com> wrote:

>Hi All
>I have set up several linked SQL servers which work ok - but I'm having
>trouble setting up a linked server via ODBC (in either SQL 2000, or 2005).
>The ODBC points to an 'Alchemy' database and uses an Alchemy driver
>ironically i can set up linked tables in an Access DB, via ODBC - and I can
>pull the same data into Excel via ODBC - so there is no problem connecting to
>Alchemy using other MS apps.
>When I add the linked server in SQL, it appears to be added but no tables
>are displayed (enterprise mgr) and OPENQUERY in SQL2005 management studio
>returns an error "Cannot initialize the data source object of OLE DB provider
>'MSDASQL' "
>Its driving me crazy...Anyone have any ideas?
>Thanks in advance