openRowSet returning " Could not create an instance of OLE DB provider 'SQLOLEDB'. "

i am migrating a mssql7 db on nt4 to a mssql7 database on win2k.

on the new server there is an openrowset query returning " Could not create an instance of OLE DB provider 'SQLOLEDB'. "
     SELECT *
     FROM OPENROWSET ( 'SQLOLEDB', 'GROVER';'uid';'pswd', 'stored procedure' ) AS Hierarchy,
     FH_Media_Cat_213 AS Media_Ref WHERE Hierarchy.V_ID = 213 AND Hierarchy.H_ID = Media_Ref.Cat_ID

this query works fine on the old server, but for some reason doesn't on the new one.  i have added grover to the system odbc datasources with no change.

any ideas?
LVL 1
meyerc74Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
BulZeyEConnect With a Mentor Commented:
Sounds like an issue with dlls and possibly versions.  Have you tried getting the latest MDAC from Microsoft ?

http://msdn.microsoft.com/data/downloads/updates/default.aspx
0
 
meyerc74Author Commented:
yes...installed mdav 2.8 prior to post
0
 
BoyPupaConnect With a Mentor Commented:
You code says you're using sqloledb but you mention a dsn named GROVER.

Typically you would use sqloledb to establish a direct server connection without a DSN

eg:
 SELECT *
     FROM OPENROWSET ( 'SQLOLEDB', 'ServerName';testUser;'pswd', 'exec tempdb.dbo.sampleProc' )

to use a dsn it makes more sense to use msdasql odbc provider

eg:
SELECT *
FROM OPENROWSET('MSDASQL','DSN=sqlTest;UID=testUser;PWD=pswd;','exec sampleProc')
0
 
BoyPupaCommented:
Sorry the sqloledb should read
 SELECT *
     FROM OPENROWSET ( 'SQLOLEDB', 'ServerName';'testUser';'pswd', 'exec tempdb.dbo.sampleProc' )
0
 
meyerc74Author Commented:
this actually ended up beinga versions issue
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.