2

I'm trying to create a linked server in MS SQL Server 2005, pointing to an Intersystem Cache database via ODBC.

Below is the query to create the linked server:

/****** Object:  LinkedServer [CC7]    Script Date: 02/22/2011 09:06:39 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'CC7', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc=N'CC7', @provstr=N'DRIVER={Intersystems ODBC};Server=CCMSSRVR;Port=1972;Database=CCMS_STAT', @catalog=N'CCMS_STAT'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CC7',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'CC7', @optname=N'use remote collation', @optvalue=N'true'

I can query the database, for example as below:

SELECT * FROM CC7..dbo.iAgentByApplicationStat

This works fine.

The problem i'm having is when i try and browse the catalog through Microsoft SQL Server Management Studio. Whenever i expand 'Catalogs' under the linked server i get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "SQL Server" for linked server "(null)" reported an error. One or more arguments were reported invalid by the provider.
Cannot obtain the schema rowset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I've googled this for days to no avail, so any help no matter how small is very much appreciated.

George Duckett
  • 31,770
  • 9
  • 95
  • 162
  • This question is for the person who posted the initial Cache link to sql server question. Can you explain how and where you did this link in sql server. I am trying to do a similar thing in order to connect from sql server to a cache db but I don't know where within sql server I do this. Any details on how this is done would be a big help. Thanks –  Mar 29 '11 at 16:56
  • I use SQL Server Management Studio. There are 2 ways i know of to create a linked server. One is using a query (such as in my original question), the other is using the user interface. In either case, for it to work you must first install the ODBC driver (Intersystems ODBC). To use the user interface to create the linked server connect to it, then expand 'Server Objects', then right-click 'linked servers' then choose 'New Linked Server'. – George Duckett Mar 30 '11 at 07:14
  • I ran into the same issue where I was unable to browse the catalog. While i was unable to get the catalog to populate on-screen. I was able to get column definitions without having to login to cache and look at the class definitions by doing the following: SELECT * INTO tempdb.dbo.iAgentByApplicationStat FROM CC7..dbo.iAgentByApplicationStat where 1=0; I can then expand the iAgentByApplicationStat in tempdb to see the schema definition. – john.da.costa Dec 19 '11 at 23:12

1 Answers1

1
set ODBC UserDSN by manul to replace "@provstr=N'DRIVER={InterSystems ODBC};Server=CC7;Port=1972;Database=CCMS_STAT', "

then you try again, 

select data from CCM7 will be succeed.

I am doing for CCM7 reports by SSRS, please let me know if you same to do it. My MSN: zxh-snda@hotmail.com : )

Ed Bangga
  • 12,879
  • 4
  • 16
  • 30
user642363
  • 26
  • 1
  • Fantastic, that's exactly what i needed to know (i scripted the server i'd created then removed the part quoted above and recreated). I'm also planning on using SSRS (added to MSN) – George Duckett Mar 04 '11 at 10:03