Hi,

Scenario: Extract data from HP3000 Image/Sql database into SQL server 2008 every hour.
I am trying to get the connection working. I tried doing an 'Import Data' using the wizard in SQL Management Studio. Using '.Net Framework Data Provider for ODBC' and by using the string "Dsn=;Driver={}", I was able to dump data from one of the tables on the HP3000 db into sql server.But to pull data every hour, I considered creating a linked server. I selected 'Microsoft OLE DB Provider for ODBC Drivers' as the provider and connection string 'Dsn=;Driver=;Uid=;Pwd='. I get an error 'the data source object of OLE DB provider 'MSDASQL'. Does anyone know what is the correct connection string to be used? I have tried various combinations and none worked.

have you reviewed this...

ConnectionStrings.com - Forgot that connection string? Get it here!

Thanks

Yes I had checked this link out.

I have now been able to get data from the HP 3000 server into SQL server 2005 using the linked server concept. This errors out in SQL Server 2008 when trying to create a linked server.

I receive 'Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "ABC". Data source name not found and no default driver specified.'

Any ideas why this happened?

Have you installed the ODBC drivers for IMAGE on the machine that is running SQL 2008 ?

-PatP

Yes. I have installed the 32 bit MB Foster UDALink ODBC driver using the 32 bit ODBC application from the Syswow4 folder. The same I did on Sql server 2005.
The error I get is 'Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server"ABC".

Any ideas?

Also, I forgot to mention here that the Sql server 2008 is a clustered Sql server. Could this make things different?

You have to install the driver on all the nodes in the cluster than can host your SQL Server instance, apart from that it should not make anything different.