using a dsn in a select clause

[MzKitty]

Hi. I am using SQL 2005 and need to know if I can use a ODBC DSN in my select from statement? I would like to get the records from a PSQL database and insert them into the new table in the SQL database. I tried using a linked server with an openquery but I keep getting an error 7303
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "RSG_JWSSRV". I thought if I could access it thru a OBDC dsn, I could bypass the problem with the linked server.

Thanks for any help.
Cathy

 

[Crowley16]

it is possible with openquery or openrowset, check BOL on how to use them.

Personally, I would want to get your linked server sorted out. There shouldn't be any problems using a linked server to connect to another db...

check permissions first, maybe try impersonation?

Also check if some components of sql server are disabled or erroring. Could be a driver isn't working...

--------------------
Procrastinate Now!

 

[MzKitty]

this is my code when creating the linked server:

exec sp_addlinkedserver
@server = 'RSG_JWSSRV' ,
@srvproduct = 'RSGJWSSRV' ,
@provider = 'PervasiveOLEDB' ,
@datasrc = 'RSGJWSSRV' ,
@location = 'RSG-JWSSRV' ,
@provstr = '' ,
@catalog = 'JWSDATA1' ;

It creates just fine but when I try to use it in my select openquery it bombs with the 7303 error message.

select *
from openquery (rsg_jwssrv, 'select * from JWS_EOMNonFinPltLst');

Cannot initialize the data source object of OLE DB provider "PervasiveOLEDB" for linked server "rsg_jwssrv".

Cathy

 

[jbenson001]

You don't need to use OpenQuery if you have a linked server, you just need the 4 part structure.

select * from server.db.owner.table

 

[MzKitty]

I tried what j suggested:
select * from RSG_JWSSRV.JWSDATA1..JWS_EOMNonFinPltLst;

and I still get the error 7303 message. I'm going to keep researching this and see if I can get to the bottom of the problem.

Thanks j
Cathy