You’re offline. This is a read only version of the page.
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
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.
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
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?
The only thing I can think of is an incorrectly formatted SQL statement. Try this:
Share
Report
All responses (
Answers (
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156