Re: Using SQL CONNECT problem to MS SQL database

Darryl,
let us try to analyze your programm snippet:

Exec Sql CONNECT TO AXTRN01
USER :SQL_USER
Using :SqlPwd;

this should work, if ArdGate is configured well (verifyed by strsql) and user annd password are correct (verifyed by debug) checking in the programm would be better.

Exec Sql Set Connection AXTRN01;

you won't need this, the last connect is current connection untill changed by set connection, but this will do no harm to your programm.

zSqlCmd = 'Select count(*) into ? from AXTRN01..UNITOFMEASURE'
+ ' where Symbol = '
+ Q + %trim(pUOM) + Q;

Parameter markers are replaced later by vars and are only allowed where a literal could be (in other words: the vars are read only)
=> here we have your problem!

Exec Sql Prepare SQLRMTCMD from :zSqlCmd;

DB2 can't check this, it doesn't see the current value of the prepareString at compiletime. At runtime this is checked by the remote database and diffrennt databases could have very diffrent behaviour. Important is the SQLSTATE in the joblog and for additional information the javalog. The SQLCODE might be misleading, because ArdGate has to derive it from a SQLSTATE of a remote database - it's only guruanteed that success is 0, warning is > 0 and error is < 0.
This might crash for one database and pass for another database. You should check SQLCODE or SQLSTATE for success after every SQL Operation.

Exec Sql Execute SQLRMTCMD using :Count ;

now is the latest point where it will crash.

As I stated over and over again: the easiest way to running programms is, to take an example and go on. If you are not familar with SQL and dealing with remote databases, it might be betterto pay for an expert (BTW: I've done coaching and/or writing the needed programms - it's my business and mostly cheaper than tumbling around for days).

Dieter