SQL Server 2005 Linked Server to Intersystems Cache Database

[darcanion]

I'm trying to get a linked server working in SQL Server 2005
Management Studio. Target is an Intersystems Cache server. I have all
the basic information needed to connect and have created plenty of
linked servers manually and scripted, but I can't seem to get this one
working. I'd appreciate it if someone who has this working could send
the syntax of their linked server script.

Here's my ODBC connection string: "provider=MSDASQL.1;Extended
Properties="DRIVER={InterSystems ODBC};SERVER=myCacheServer;
PORT=1972;DATABASE=MYCACHEDB;UID=LIVE:MYUID;PWD=MYPWD"


This is as far as I could get building a linked server script (raises
a connection error when I try to use it):
EXEC sp_addlinkedserver
@server = 'AvatarPMLive',
@provider = 'MSDASQL',
@provstr = 'DRIVER={InterSystems
ODBC};SERVER='myCacheServer';PORT=1972;
DATABASE=MYCACHEDB;UID=LIVE:MYUID;PWD=MYPWD'

This is not well documented so any suggestions or examples would be
appreciated. Thanks.

[Sukesh Hoogan]

1) Which Cache version?

2) Not sure if this would help
Change the provider to
Provider=MSDASQL.1;
and try

HTH
Regards
Sukesh Hoogan
Bombay, India
- Enterprise Resource Planning
- Business Intelligence]
- Offshore Development

Provider=MSDASQL.1;Extended
Properties='DRIVER={InterSystemsODBC};SERVER=127.0.0.1;PORT=1972;PROTOCOL=
TCP;DATABASE=USER;UID=_system;PWD=sys"

[darcanion]

By the way, I'm trying to connect to a Cache version
5.0.10.5300.0.4856 server. The interesting thing is I created a
connection inside SSIS that works with no problem, but something is
wrong with my linked server definition in Management Studio.

[Jeffrey Williams]

Create a DSN on the server and use that in your linked server definition.
Here is the script I use to connect, with your information inserted:

Execute master.dbo.sp_addlinkedserver
@server = N'AvatarPMLive'
,@srvproduct = N'MSDASQL'
,@provider = N'MSDASQL'
,@datasrc = N'{DSN Name}' -- replace this with the DSN you created
,@provstr = N'DRIVER={Intersystems
ODBC};Server=myCacheServer;Port=1972;Database=myCacheDB';

Execute master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'AvatarPMLive'
,@useself = N'False'
,@locallogin = Null
,@rmtuser = N'{username}' -- replace this with the ODBC user
account
,@rmtpassword='########' -- replace this with the actual password
for the above user account
GO

Jeff

"darcanion" <darc...@gmail.com> wrote in message
news:01172b93-f308-4d42...@f11g2000vbf.googlegroups.com...

[darcanion]

Jeffrey and Sukesh, thank you both for your replies and suggestions. I
got pulled away by other projects for several weeks. My apologies.

After first downloading and installing InterSystems ODBC-5.0.10.5300.0
driver for Win 32, I created a DSN as indicated and get a successful
connection test to Cache from there. Then I substitute correct
parameters and execute Jeffrey's script. However, when I test the
connection or try to expand the catalog folder in the new linked
server object, I get the error:
"Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "AvatarPMLive". (Microsoft SQL Server, Error: 7303)"

Sukesh, I also tried substituting @provider = N'MSDASQL.1', but this
has no effect.