Processing IDMS User-Defined Setname in SQL Server Linked

[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: [phone]
Fax: [phone]
Mobile: [phone]
E-mail: [e-mail] <mailTo:[e-mail]>=20

We deliver on our commitments so you can deliver on yours.
"
IDMS 3rd-party providers forum
[e-mail]
SMTP
[e-mail]
[e-mail]
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
[e-mail]

[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 = 16

OCF 16.0 IDMS PAGE 1 LINE 1 DICT=QALDICT 1/86
SYST0005
SELECT *
FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
WHERE ""CSC-CMCSEV""
AND C.CSC_CASE_ID = 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.


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 = 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 = 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?

Regards,
Paul Mak
Database Administrator - IDMS

EDS, an HP company

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

Tel: [phone]
Fax: [phone]
Mobile: [phone]
E-mail: [e-mail] <mailTo:[e-mail]>

We deliver on our commitments so you can deliver on yours.
"
IDMS Public Discussion Forum
[e-mail]
SMTP
[e-mail]
[e-mail]
SMTP








Normal

Normal
Re: 0518 on Obtain First in Set
"ADS does do a bind under the covers, even though the dialog does not
code one.
My initial response would be that the dialog and its maps are referring
to a record that has been removed from the subschema. A more wicked
variation on the problem is, that the loadlib/area contains a likenamed
subschema, but without the diaglogs records.

A regeneration of the dialog would prove/disprove that, but if it has
errors, then the app would be disabled until the errors are fixed. So,
regeneration in production should not be taken lightly.

Lutz Petzold
TDM UDB/IDMS Support
401-782-2265
Page 860 366 0865 or Telalert



This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna
"
IDMS Public Discussion Forum
[e-mail]
SMTP
[e-mail]
[e-mail]
SMTP








Normal

Normal
Re: 0518 on Obtain First in Set
"ADS does do a bind under the covers, even though the dialog does not=0D=0Ac=
ode one=2E=0D=0AMy initial response would be that the dialog and its maps a=
re referring=0D=0Ato a record that has been removed from the subschema=2E =
A more wicked=0D=0Avariation on the problem is, that the loadlib/area conta=
ins a likenamed=0D=0Asubschema, but without the diaglogs records=2E=0D=0A=
=0D=0AA regeneration of the dialog would prove/disprove that, but if it has=
=0D=0Aerrors, then the app would be disabled until the errors are fixed=2E =
So,=0D=0Aregeneration in production should not be taken lightly=2E=0D=0A=
=0D=0ALutz Petzold=0D=0ATDM UDB/IDMS Support=0D=0A401-782-2265=0D=0APage 86=
0 366 0865 or Telalert=0D=0A =0D=0A =0D=0A=0D=0AThis e-mail may contain con=
fidential or privileged information=2E If=0Ayou think you have received thi=
s e-mail in error, please advise the=0Asender by reply e-mail and then dele=
te this e-mail immediately=2E=0AThank you=2E Aetna
"
IDMS 3rd-party providers forum
[e-mail]
SMTP
[e-mail]
[e-mail]
SMTP








Normal

Normal
Re: 0518 on Obtain First in Set
"ADS does not BIND a record unless it's named in the dialog using DML.