web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Dexterity status = SQL...
Microsoft Dynamics GP (Archived)

Dexterity status = SQL_Execute(SQL_connection, SQL_Statements); is returning 18.

(0) ShareShare
ReportReport
Posted on by Shreenath Gandhi 4

tSQL = "select a.AssetNo , a.Model, m.ProductCode " + sCR;
tSQL = tSQL + "from Assets a " + sCR;
tSQL = tSQL + "inner join Models m on a.Model = m.Model " + sCR;
tSQL = tSQL + "where a.AssetNo IN (" + sCR;
for i = 1 to 200 do
if i = 200 then
tSQL = tSQL + str(AssetNo[i]);
else
tSQL = tSQL + str(AssetNo[i]) + ", ";
end if;
end for;
tSQL = tSQL + ") and a.AssetNo <> 0" + sCR;
tSQL = tSQL + "ORDER BY m.ProductCode DESC, a.Model ASC, a.AssetNo ASC";

status = SQL_Execute(SQL_connection, tSQL);

Till here it works fine.

But when it reaches code

status = SQL_Fetchnext(SQL_connection, tSQL);

It give return code 18?

I am not able to find what error code 18 means.

Can anyone hepl?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    The only thing I can think of is an incorrectly formatted SQL statement. Try this:

    local text tsql;

local long status, conn;
local integer i;
local string strAsset;
local integer AssetNo[200];
local long GPS_error_number, SQL_error_number;
local string SQL_error_string, ODBC_error_string;



clear tsql;

pragma(disable warning LiteralStringUsed);

tsql = tsql + "select a.AssetNo , a.Model, m.ProductCode" + CRLF;
tsql = tsql + "from Assets a inner join Models m" + CRLF;
tsql = tsql + " on a.Model = m.Model" + CRLF;
tsql = tsql + "where a.AssetNo in " + CH_LEFTPAREN + CRLF;

for i = 1 to 200 do
	strAsset = str(AssetNo[i]);
	if not empty(strAsset) and (strAsset <> ZERO_STR) then
		tsql = tsql + strAsset + CH_SPACE + CH_COMMA;
	end if;
end for;

tsql = tsql + ZERO_STR + CH_RIGHTPAREN + CH_SPACE + "and a.AssetNo <> 0 order by m.ProductCode desc, a.Model, a.AssetNo";

status = SQL_Execute(conn, tsql);
if status = OKAY then
	status = SQL_FetchNext(conn);
    if status <> 31 then
		// Do your thing here 
		// status = SQL_GetData(conn, ...)
	else
		warning "No information available for this asset number";
	end if;
else
    error "An error occurred executing SQL statements.";
    {Retrieve the specific error information.}
    status = SQL_GetError(conn, GPS_error_number, SQL_error_number, SQL_error_string, ODBC_error_string);
    if status = 0 then
        warning "GPS Error: " + str(GPS_error_number) + CH_SPACE + "SQL Error: " + str(SQL_error_number) + CH_SPACE + SQL_error_string + CH_SPACE + "ODBC Error: " + ODBC_error_string;
    else
        error "Unable to retrieve SQL error information.";
    end if;
end if;

pragma(enable warning LiteralStringUsed);


    You may or may not need the variables, so adjust accordingly.

  • Shreenath Gandhi Profile Picture
    Shreenath Gandhi 4 on at

    Hi Mariano Gomez

    Thanks for the reply.

    But this did not really work. I am getting the same error that I was getting earlier.

    May be error description will help.

    Following is the error I am getting after 'status = SQL_FetchNext(SQL_connection);'

    GPS Error: 18

    SQL Error: 0 [Microsoft] [ODBC Driver Manager] Function sequence error

    ODBC Error: S1010

    Also can you tell me what is ZERO_STR?

  • Suggested answer
    MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    Hi,

    I noticed you are getting an ODBC error S1010. In doing some research, it seems your GP ODBC connection does not have Multiple Active Results Set (MARS) enabled. You might run into issues like this when working with SQL Server prepared statements, particularly if you are expecting a large data set back.

    Try this:

    1. Open your registry editor and go to HKLM\Software\WOW6432Node\ODBC\ODBC.INI\Dynamics GP 2015\

    2. Right-click and select New\String Value

       String: MARS_Connection

       Value: Yes

    3. Try to run your code once more.

    As for ZERO_STR, that's a constant in the Dynamics.dic database, along with CH_RIGHTPAREN and CH_LEFTPAREN

  • Shreenath Gandhi Profile Picture
    Shreenath Gandhi 4 on at

    Hi @Mariano Gomez,

    I found out what the issue was.

    The variable that I was using to get data from table had wrong datatype which was causing the issue.

    Thanks for your help.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans