Highlighted
Absent Member.
Absent Member.
1197 views

SQL Fetch Statement

Jump to solution

We are migrating from Net Express 3.1 to Visual COBOL and found the following problem in Native code with embedded SQL statements.  When we perform the open cursor for a fetch statement, we receive a sqlcode of -0000000229, sqlstate of 42000, and a mfsqlmessagetext of [Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'Customer', database 'Test', schema 'dbo'. 

In the SQL database, we limit the users to only having connect permissions and the ability to execute a stored procedure that will return an approle username and password.  Then, we execute sp_setapprole using the returned username and password to provide read and write access to the database.  This process works just fine in Visual COBOL.  Further, we can perform a SELECT statement returning a single row and the declare cursor.  However, we get the above error message when we execute the open cursor.

This code works fine in Net Express 3.1.  If we grant the users permission to datareader on the database, then everything runs without a problem.  What do we need to change in order to achieve the same behavior in NE?

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: SQL Fetch Statement

Jump to solution

Try setting the directive SQL(BEHAVIOR=UNOPTIMIZED).

This will cause the behavior of OpenESQL in Visual COBOL to be compatible with Net Express behavior in case the new default is causing the issue you see.

Also, you should be using one of the newer ODBC drivers for SQL Server like the Native Client 10.0 or the new 11.0 driver.

The driver which is just named SQL Server is an old driver and should not be used anymore.

Thanks.

View solution in original post

0 Likes
2 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: SQL Fetch Statement

Jump to solution

Try setting the directive SQL(BEHAVIOR=UNOPTIMIZED).

This will cause the behavior of OpenESQL in Visual COBOL to be compatible with Net Express behavior in case the new default is causing the issue you see.

Also, you should be using one of the newer ODBC drivers for SQL Server like the Native Client 10.0 or the new 11.0 driver.

The driver which is just named SQL Server is an old driver and should not be used anymore.

Thanks.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: SQL Fetch Statement

Jump to solution

Thanks Chris.  The SQL(BEHAVIOR=UNOPTIMIZED) fixed the problem.  I will also look into using the newer ODBC drivers.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.