ca.portal.admin

Processing IDMS User-Defined Setname in SQL Server Linked

Discussion created by ca.portal.admin on Nov 16, 2008
Latest reply on Nov 16, 2008 by ca.portal.admin
Server

The following SQL statement works in OCF where ""CSC-CMCSEV"" is a
user-defined setname. The owner of the set is a Calc record with Calckey
""CSC_CASE_ID"".

SELECT *
FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID =3D 16

OCF 16.0 IDMS PAGE 1 LINE 1 DICT=3DQALDICT 1/86
SYST0005
SELECT *
FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID =3D 16
*+
*+  CSC_CASE_ID  CMCSEV_CASE_EVENT_SEQ_NO  CMCSEV_CASE_EVENT_VERSION_NO
*+  -----------  ------------------------  ----------------------------
*+           16                         1                             1
*+
*+ CMCSEV_OCOME_SORT_SEQ_ID  CMCSEV_SCHED_HEARING_DTE
*+ ------------------------  ------------------------
*+                        1                  20080618


I have defined an SQL Server Linked Server using the ODBC provider:
Microsoft OLE DB Provider For ODBC Drivers and
accessing a Data Source created by the CA-IDMS ODBC Administration tool
as a ""System""-type ODBC Data Source using CA-IDMS ODBC driver. =20


The following SQL statement works in SQL Server Linked Server but
returns too many record:

SELECT *
FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C,
[IDMS_DEV]..[NSQLCOURT].[CRIM-CASE-EVENT] B
WHERE C.CSC_CASE_ID =3D 16;

The following SQL statement gives me an error as expected since the
setname ""CSC-CMCSEV"" is not recognized by SQL Server Linked Server.

SELECT *
FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C,
[IDMS_DEV]..[NSQLCOURT].[CRIM-CASE-EVENT] B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID =3D 16;

Msg 4145, Level 15, State 1, Line 4
An expression of non-boolean type specified in a context where a
condition is expected, near 'AND'.

I am trying to investigate if SQL Server support SQL PASSTHRU option
that does not edit the SQL Syntax and hence allow the setname to
passthru.
I can't find any SQL Server Linked Server option that touches on SQL
PASSTHRU

Does anyone know how to process setname in SQL Server Linked Serve?=20

Regards,
Paul Mak=20
Database Administrator - IDMS

EDS, an HP company

Applications Services, Data Engineering Capability - Sydney
Level 3, 36-46 George Street,=20
Burwood, NSW 2134, AUSTRALIA

Tel: +61 2 90125434
Fax: +61 2 90126612
Mobile: +61 419 398 116
E-mail: paul.mak@eds.com <mailTo:alex.leeflang@eds.com>=20

We deliver on our commitments so you can deliver on yours.
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Processing IDMS User-Defined Setname in SQL Server Linked Server
"Paul,

With SQL Server you can use OPENQUERY to send your IDMS SQL via the
Linked Server. The syntax is something like this;


SELECT * FROM OPENQUERY
(IDMS_DEV,
'SELECT *
FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID = 16')


Joe S Cates, Database Analyst II
Systems Architecture and Operations
Database Management/Unix Administration
Montgomery County Public Schools
Rockville, MD 20850
301-279-3697
joe_cates@mcpsmd.org

Outcomes