Hi all,

I am trying to join the master file located at _EDACONF/CATALOG/RM/RMLDB to one of the tables located in our data warehouse. Unfortunately, I am getting this error:

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
: Microsoft OLE DB Provider for SQL Server: [] Cannot create new connectio
: n because in manual or distributed transaction mode.
L (FOC1406) SQL OPEN CURSOR ERROR. : SMRPCS


The code in my procedure:

JOIN SMUSERID IN _EDACONF/CATALOG/RM/RMLDB TO THIRD_PARTY_ID IN DIM_PERSON TAG J1 AS J1

TABLE FILE _EDACONF/CATALOG/RM/RMLDB
SUM
CNT.DST.SMFEXNAME AS 'Number of Reports'
BY SMUSERID AS 'Username'
BY LAST_NAME AS 'Last Name'
WHERE SESSDATE GE '20160701' AND SESSDATE LE '20160726'
END


I've also tried the reverse join (from DIM_PERSON to _EDACONF/CATALOG/RM/RMLDB) with no success.

Does anyone know how I can join off of the RMLDB master file?

Thanks in advance,

Matt

This message has been edited. Last edited by: <Emily McAllister>,

Saw a tech support case similar to this where they recommended the following code in edasprof:
ENGINE SQLMSS SET CURSORS SERVER

you might try adding it to the top of the focexec instead of your edasprof and see if it makes a difference.

https://techsupport.informatio...ttrack/92821014.html

Thanks for the reply BabakNYC.

I can't access that link?

Also - I think that line of code worked, as I am no longer getting the error. However, the fex is timing out. Any suggestions?

This message has been edited. Last edited by: mlhapner,

I can get the join to work if I use a small RECORDLIMIT (around 1000 records), but anything much larger than that will time out with no results

TABLE FILE both tables and hold them, then join the hold files. Maybe you'll have better luck doing it that way.

I am getting the same result as before. It times out if I don't have a RECORDLIMIT

This message has been edited. Last edited by: mlhapner,