I have vendors asking for verification that messages for locations are coming through to them.  I can get generic ADT_A01 type of numbers in Activity.  I'd really like to get some good SQL queries that can give me a count of MSH.4s (for example) for a day for X Operation.  I'm not sure which table to look at for that information.

0 1 63

Following instructions on this page https://docs.intersystems.com/latest/csp/docbook/Doc.View.cls?KEY=D2IMP_... I am trying to create a Data Connector to be used as a base class for a cube with update support.
That page suggests that putting an SQL query inside XData block is not suitable for a Data Connector that supports updates.
Later it also suggests that to enable updates your SQL query must include 

0 0 45

Does anybody know if there is an easy way to know if the execution of the command $System.SQL.PurgeForTable went well/wrong?

The documentation describes that a string is returned, but in fact there is a nice Quit "" in the code.

Maybe inside PurgeForTable^%apiSQL there is any process variable set when it goes well/wrong?


Thank you very much!

0 5 80

Hi All, I am a Data Architect and am trying a method of deploying alter statements onto Cache DB.

My Data Modeling tool generates alters as this.. So question is if the Cache has a way to rename table? If so what is it?


This is giving error.

0 4 266
Mark Sharman · Sep 19, 2019
Credentials - Domain Account


I've a Service utilising the Adapter EnsLib.SQL.InboundAdapter, which uses a Credentials item set with the details of a local SQL account. This currently works, however, we're looking to use the credentials of an AD domain account.

The domain account is a member of an AD security group, which has the required permissions on the source SQL database. I've checked that access is possible with this account via SQL studio.

0 2 116
Scott Beeson · Feb 12, 2016
Get columns dynamically?

In MSSQL I think you can do something like this:

select *
from HS_IHE_ATNA_Repository.COLUMNS
where TABLE_NAME=&#39;Aggregation&#39;


How can I do this in Cache SQL?

[%msg: < Table 'HS_IHE_ATNA_REPOSITORY.COLUMNS' not found>]

0 11 1,637

We use ExecureProcedure() to execute a stored procedure which returns a result set. But we see lot of "Invalid cursor state" errors when the result set is empty.  The connection to SQL server database is made through ODBC. 

ERROR #6022: Gateway failed: Fetch. + ERROR <Ens>ErrGeneral: SQLState: (24000) NativeError: [0] Message: [Microsoft][ODBC Driver 11 for SQL Server]Invalid cursor state 

Here is the code snippet from the business operation class which uses EnsLib.SQL.OutboundAdapter  

0 2 703

In MS SQL Server i can do this:

Category, CrewNumber, MedicalCertificationDate, Seat, SeatbeltUsed, Sex, ShoulderHarnessUsed, ToxicologyTestPerformed, childsub
INTO #tempfemale
FROM Aviation.Crew
WHERE Sex = 'F'

0 8 305

I am trying to create a Procedure in Caché, but this message is showing:

<UNDEFINED>frmit+118^%qaqpsq *mt("v",1)

This is the procedure:

CREATE PROCEDURE testebi.sp_cargainicial()
    INSERT INTO testebi.Fato_Atendimentos (
    PK_OsProcedimento )
     from dado.TblOsProcedimento    ;    

0 2 107


I have an sql outbound adapter. Sometimes we have trouble connecting to the database. 

The timeout in develpment is set to 15 seconds in live it is 150 seconds as it is an always connected Buisness operation. 

I thought adding E=S or/and X=S would suspend the message. Why does it not? 

Is the only way around then adding something to the buisness operation itself/ creating a customised SQL buisness operation rather than EnsLib.SQL.OutboundAdapter?




0 5 141

Hello, I am running into a little problem.

I am trying to create a Cache Trigger on a FACS table using the SQL CREATE TRIGGER command but I am getting the message [SQLCODE: <-300>:<DDL not allowed on this table definition>].

I am not creating a persistent class but using the SQL CREATE TRIGGER command.

Please advise.


0 8 141
Jeff Burdette · Nov 20, 2017
Cache ODBC - Access Denied


I'm working on integrating a Cache database into an existing .NET project of mine.  I'm trying to execute certain queries utilizing the Cache ODBC driver.  The first error I ran into was "[S1000] [Cache ODBC][State : S1000][Native Code 417] Access Denied".  The part that's throwing me for a loop though is that the application ran ~200 successful queries before I started getting that.  After some of those Access Denied errors, they turned into "[Cache ODBC][State : S1T00][Native Code 450] Request timed out due to user timeout".  This was all with the regular (2.5?) driver.

0 8 4,641

During runtime I build an object which is essentially a wrapper over in-memory table:

col1 ... colN
val11 ... valN1
val12 ... valN2


I want to use this object as a part of INSERT or UPDATE queries, based on a value of some column (the main use case one of the columns is an ID value)

What's the best way to expose the object to SQL?

0 13 210

I am experimenting with Relationships, both Parent to Child and One to Many.

I have done some SQL look-ups and have searched through the documentation, however not in a lot detail, but wonder if there are more and better ways to access both sides of Relationships through SQL?

Thank you in advance for any help provided.

0 25 1,333
Ken Wenze · Jan 6, 2020
ODBC Providers for SQL Server

Where can i get the latest odbc providers from SQL Server 2012/2016?


I see some posts on line to  an ftp site out there but I am unsuccessful at getting access to it.  I have a version loaded on my server but get errors when querying cache'.

"[Cache ODBC][State : 22005][Native Code 22005]" 

Any help would be appreciated.

the one we have might be version  2014.01.05851  64 bit (would that make sense?)



0 4 550
Yakov Berger · Mar 1, 2020
SQL Inbound Adapter Errors


I created a BS with a SQL Inbound Adapter.

Query = "SELECT rowid, fname ,lname,datesent  
                   FROM labs
                   WHERE datesent IS NOT  NULL"

DeleteQuery = " update labs set datesent  =  Getdate() where rowid = ?"

KeyFieldName = "rowid"

i am getting the following errors.

"NOT assuming network disconnect error based on: Gateway failed: DropStatement."

"Skipping previously errored row '235'"

0 1 112

HI I am using below code to retrieve the last inserted ID of the table. It works when we do not set any custom column as IDKey. If we define any column as 

IDKey below code does not return any IDKey. I know when i am defining any column as IDKey its not auto generated , but whats the best way to get the ID column value

0 1 287