>>>We are storing a sql statement and passing it between to prg files. It is bombing out and we think that we might be trying to pass a string that is too large...or it could be something else altogether. Has anyone else run into such a limit?
>>
>>Ed, what's the error message? On what line of code?
>
>I working with my partner on this...just got the code. getting a syntax error..right now he is reversing the quotes and double quotes in case that might be the culprit...any ideas?
>
>Here is the code.
>
>mSQLFldList = 'Select lastname, firstname, ssn, classcode, adrep, farep, '
>mSQLFldList2 = 'sttrans, sddate, sdamount, sdledate, sdleamt '
>mSQLFrom = 'From students, studtran, studdisb '
>mSQLJoin = 'Where stssn = ssn and sdtrannum = stnum '
>mSQLWhere = 'and status = "A" and sdstatus = "P" and between(sddate,msdate,medate) '
>mSQLOrder = 'Order By sttrans, lastname, firstname'
>MySQLComm = '&mSQLFldList &mSQLFldList2 &mSQLFrom &mSQLJoin &mSQLWhere &mSQLOrder'
>if MyCommProc.ExecuteRemoteSQL('SELECT', MyFinanceConn, @MySQLComm, 'SQLResults')
>*************************
>
>It works if you try to run the string MySQLComm from the command line, but if you pass it as a parameter in the
>MyCommProc.ExecuteRemoteSQL - it doesn't.
>
>
>Ed

First, I would try to cleanup the SQL a bit... make explicit reference to the fields

mSQLFldList = 'Select students.lastname, students.firstname, .... '
mSQLJoin = 'Where TBL1.field = TBL2.field (or variable) and ... '

Then change MySQLComm to eliminate macro substitution since they are all strings

MySqlComm = mSQLFldList + mSQLFldList2 + ...

If you want to check the string limit, you may want to create a bogus function that just prints what was passed into it. Then call it with each segment plus the previous

=MyFunc( mSqlFldList )
=MyFunc( mSqlFldList + mSqlFldList2 )
=MyFunc( mSqlFldList + mSqlFldList2 + ... )

If you see a breakdown you'll adjust as needed...