The error message I am receiving is as follows:

(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. :

I have tried renaming the tags in the joins in case that was an issue but that didn't work. I'm not really sure where to start on this one. Can someone give some insight?

Thanks.

Is your SQL going against 2 different SQL Server databases? I had a similiar issue years ago when doing this.

I had our DBA's create links or synonyms in one of the databases to get around the issue.

This may help, or not.

https://techsupport.informatio...utions/63092535.html

@MattC - I am only using one database to get my information. It is only when I join the one table it seems and I don't have to use that table. Prior to this bit of code, I have this table in a separate JOIN statement. However, I am using JOIN CLEAR * at the beginning of every new JOIN statement. This has never been a problem before, not sure why it is starting all of a sudden.

@Waz - I'm not using DataMigrator or the DBLOOKUP command. Unless, WF uses DBLOOKUP without me knowing, this is very possible.

Like I said above, if I remove the JOIN statement it works without a problem.

Any other suggestions?

Thanks.

Even if it does not seem related, did you try ENGINE SQLMSS SET FASTLOAD OFF

quote:

Originally posted by Waz:
Even if it does not seem related, did you try ENGINE SQLMSS SET FASTLOAD OFF

Yes, I did try it and it doesn't make a difference. I have concluded that it has to do with my JOIN statements.

The error messages give me the error with the second JOIN statement and table QC_RESULTS. This only occurs when I try to print something from QC_RESULTS. If I don't print anything, the report runs.

 
JOIN CLEAR *
JOIN
 INNER LJ1HOLD.LJ1HOLD.FINAL_RUN_ID IN LJ1HOLD TO UNIQUE
 GETRUNANALYSES.INPUT.@RUNID IN GETRUNANALYSES TAG J12 AS J12
 END
JOIN
 INNER LJ1HOLD.LJ1HOLD.FINAL_RUN_ID IN LJ1HOLD TO UNIQUE
 QC_RESULTS.QC_RESULTS.RUN_ID IN QC_RESULTS TAG J4 AS J4
 END
JOIN
 INNER J4.QC_RESULTS.ANALYTESCHEME_ID IN LJ1HOLD TO UNIQUE
 TBL_ANALYTESCHEMES.TBL_ANALYTESCHEMES.ANALYTESCHEME_ID IN TBL_ANALYTESCHEMES
 TAG J7 AS J7
 END
JOIN
 INNER J7.TBL_ANALYTESCHEMES.ANALYTE_ID IN LJ1HOLD TO UNIQUE
 TBL_ANALYTES.TBL_ANALYTES.ANALYTE_ID IN TBL_ANALYTES TAG J13 AS J13
 END

If I take the same tables and JOIN the third table differently, the errors then occur on the TBL_ANALYTESCHEMES table instead of QC_RESULTS table. Again, it only gives the errors when I try to print something from the TBL_ANALYTESCHEMES table.

  
JOIN CLEAR *
JOIN
 INNER LJ1HOLD.LJ1HOLD.FINAL_RUN_ID IN LJ1HOLD TO UNIQUE
 GETRUNANALYSES.INPUT.@RUNID IN GETRUNANALYSES TAG J12 AS J12
 END
JOIN
 INNER LJ1HOLD.LJ1HOLD.FINAL_RUN_ID IN LJ1HOLD TO UNIQUE
 QC_RESULTS.QC_RESULTS.RUN_ID IN QC_RESULTS TAG J4 AS J4
 END
JOIN
 INNER LJ1HOLD.LJ1HOLD.ANALYTESCHEME_ID IN LJ1HOLD TO UNIQUE
 TBL_ANALYTESCHEMES.TBL_ANALYTESCHEMES.ANALYTESCHEME_ID IN TBL_ANALYTESCHEMES
 TAG J7 AS J7
 END
JOIN
 INNER J7.TBL_ANALYTESCHEMES.ANALYTE_ID IN LJ1HOLD TO UNIQUE
 TBL_ANALYTES.TBL_ANALYTES.ANALYTE_ID IN TBL_ANALYTES TAG J13 AS J13
 END

I'm really confused about why this is happening. Any other suggestions?

Thanks.

It may also help to take a look at the sql being generated. And you could comtenplate to let focus do the join iso sql server. There are settings for that, although I can't remember exactly what they are.

quote:

Originally posted by GamP:
It may also help to take a look at the sql being generated. And you could comtenplate to let focus do the join iso sql server. There are settings for that, although I can't remember exactly what they are.

This is the code I have for showing the SQL Trace, however, I can't seem to find the SQL even if I use "View Source".

-SET &ECHO=ALL;
SET TRACEOFF = ALL
SET TRACEON = STMTRACE/CLIENT
SET TRACEON = STMTRACE/CLIENT
SET TRACEON = STMTRACE/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
SET XRETRIEVAL=OFF

Is there anything wrong with the code above?

Thanks.