I have a program coded for reading a record from a MS SQL Server database Table.  When I step through the code in debug mode it lands on the EXEC SQL statement and when I step again it lands outside of the EXEC SQL/END-EXEC structure. It looks as though none of the statements inside this structure are executing.  Is this normal?  If it is, is there a way to step through this structure to observe the back end process?  I'm not returning the record from my SQL  query and need to figure out why.

My database and table are able to display in the 'Server Explorer' of Visual Studio.

The database object is not displayed in the Solution Explorer.

I'm using a ADO.NET DSN and it's connected to my MS SQL Server database successfully.

I'm using GEN-SQLCA directive in the ESQL Preprocessor-OPENESQL.

Here is my code:

IDENTIFICATION DIVISION.
PROGRAM-ID. FirstDBProg.

ENVIRONMENT DIVISION.

CONFIGURATION SECTION.

DATA DIVISION.
*
WORKING-STORAGE SECTION.
*
EXEC SQL
INCLUDE SQLCA
END-EXEC
*
COPY CustContacts.
*
PROCEDURE DIVISION.

*Test error handling
EXEC SQL
CONNECT TO ‘SQLNOEXIST’
END-EXEC
*Connect to DSN that uses Windows authentication

EXEC SQL
CONNECT TO ‘MSSQLSADONET’
END-EXEC
*Retrieve record(s)
PERFORM Execute-Select
*
EXEC SQL
DISCONNECT CURRENT
END-EXEC

STOP RUN.
*
Execute-Select.
EXEC SQL
SELECT A.CustFirstName INTO :WS-CustFirstName
FROM TblCustContacts A
WHERE (A.CustNum = 1)
END-EXEC
*
DISPLAY WS-CustFirstName.
*
OpenESQL-ERROR.
DISPLAY "Error = " SQLERRM
DISPLAY "SQLCode = " SQLCODE.
*
END PROGRAM FirstDBProg.

******************************************************

Hi Kevin.

You have an error checking paragraph in your program but I dont see any code that actually performs the check of SQLCODE after each exec sql statement. 

If you perform this paragraph after each statement, what is the value of SQLCODE after the select statement?

I had to code a perform of the paragraph "OpenESQL-ERROR" to force it to execute and got the following error after the statement line EXEC SQL CONNECT TO ‘MSSQLSADONET’ END-EXEC:

Error = ; Data source name not found and no default driver specified
SQLCode = -0000019703

I show the following properties of the data source in the ADO.NET Connection Editor:

I'm not sure where to verify the driver so if you can help with that i'd appreciate it.

If you code an EXEC SQL WHENEVER statement at the top of your procedure division in your program a perform of the specified error paragraph will be done when an error occurs.

EXEC SQL WHENEVER sqlerror PERFORM OpenESQL-ERROR END-EXEC

It looks like your connection string is not complete. You need to fill in the data source name which is your SQL Server database name and perhaps the initial catalog field with a value such as NORTHWIND.

You can click the test button in the ADO connection editor to make sure it works before trying it in your program.

I tried unsuccessfully to test the connection in the ADO connection editor.  Here is the error:

Set the Context Connection option to false.

After setting context connection to false, Retrying and entering my user name i get the following error:

Try turning off the MultiSubnetFailover option.

Is your database on the same machine on which you are creating the connection or on a remote machine? If it is on a different machine then you have to make sure it allows remote connections.

See here:

If you open up SQL Server Management Studio is the Server name that you connect to the same as the name that you are using in the ADO connection?

My database is on the same machine that I’m attempting to make a connection and the MS SQL Server setting is already set to “allow remote connections to this server.”

The Server name I connect to is the same as the name that I’m using in the ADO connection.

After setting MultiSubnetFailover option to False and attempting to login using my userid (there is no password set in MSQL Server account), I get a different error: 

Can you please show me what the complete contents of the Connection String field is?

Connection string in ADO Connection Editor is:  Data Source=<device name here>;Initial Catalog=MiscCustomer;Integrated Security=True;MultipleActiveResultSets=True;Context Connection=True

You have the Context Connection set to true again. Please set it back to false.

That worked.  I now have a successful connection from the ADO.NET connection editor.  When I include that connection string in my cobol program it throws the error "Data source name not found and no default driver specified   SQLCode = -0000019703"

Here is a extract from my cobol code for the connection:

EXEC SQL
CONNECT DSN DBConn
END-EXEC

Where DBConn is coded as:

01 DBConn PIC X(111) VALUE "Data Source=<device name here>;"      &
"Initial Catalog=MiscCustomer;Integrated Security"                                   &
"=True;MultipleActiveResultSets=True".

You just created a DSN using the ADO connection editor. You should use the DSN name.

EXEC SQL CONNECT TO "MSSQLSADONET" END-EXEC

If you want to use a connection string directly then you need to use a format 6 connect statement as documented here:

It would look something like:

01  connString    string. 
    set connString    to "Data Source=DESKTOP-USJT69L;Initial Catalog=MiscCustomer;" &
    "Integrated Security=True;MultipleActiveResultSets=True;factory=System.Data.SqlClient;".
   

 procedure division.

     EXEC SQL 
         CONNECT USING :connString  
     END-EXEC 

I tried both methods-using the DSN only and using the connection string in your example and got the same error ("Data source name not found and no default driver specified SQLCode = -0000019703")

Can you show me the two connect statements that you are trying?

Are you creating the DSN as a 64-bit DSN and the project is also set to 64-bit
It should show the bitism at the bottom of the connection editor. This can be changed using the settings icon at the top of the window.

also what are the SQL directives that you are setting in your program?

method 1

EXEC SQL CONNECT TO "MSSQLSADONET" END-EXEC

method 2

EXEC SQL
CONNECT USING :connString
END-EXEC

Where connString is coded as:

01 connString PIC X(142) VALUE
"Data Source=<device name here>;Initial Catalog=MiscCustomer;"
&
"Integrated Security=True;MultipleActiveResultSets=True;" &
"factory=System.Data.SqlClient;".

Yes the DSN was created as 64 bit and the project is 64 bit.

Here are the SQL directives:

You are not setting the DBMAN directive to ADO. Please add this directive to your SQL settings.

There is only one value "ODBC" available for DBMAN in the application properties:

ADO can only be used with managed .NET Framework projects. The project type that you selected is a native.COBOL project.  Native projects use ODBC connections and not ADO. Everything that we have done so far would be required if you selected .NET Framework as a project type.

ODBC DSN's are not created with the ADO Connection Editor. Instead you use the ODBC Data Source Adminstration tool. For a 64-bit project you use the 64-bit version of this tool. For 32-bit projects you use the 32-bit version of this tool.

Please see the documentation here:

I have a program coded for reading a record from a MS SQL Server database Table.  When I step through the code in debug mode it lands on the EXEC SQL statement and when I step again it lands outside of the EXEC SQL/END-EXEC structure. It looks as though none of the statements inside this structure are executing.  Is this normal?  If it is, is there a way to step through this structure to observe the back end process?  I'm not returning the record from my SQL  query and need to figure out why.

My database and table are able to display in the 'Server Explorer' of Visual Studio.

The database object is not displayed in the Solution Explorer.

I'm using a ADO.NET DSN and it's connected to my MS SQL Server database successfully.

I'm using GEN-SQLCA directive in the ESQL Preprocessor-OPENESQL.

Here is my code:

IDENTIFICATION DIVISION.
PROGRAM-ID. FirstDBProg.

ENVIRONMENT DIVISION.

CONFIGURATION SECTION.

DATA DIVISION.
*
WORKING-STORAGE SECTION.
*
EXEC SQL
INCLUDE SQLCA
END-EXEC
*
COPY CustContacts.
*
PROCEDURE DIVISION.

*Test error handling
EXEC SQL
CONNECT TO ‘SQLNOEXIST’
END-EXEC
*Connect to DSN that uses Windows authentication

EXEC SQL
CONNECT TO ‘MSSQLSADONET’
END-EXEC
*Retrieve record(s)
PERFORM Execute-Select
*
EXEC SQL
DISCONNECT CURRENT
END-EXEC

STOP RUN.
*
Execute-Select.
EXEC SQL
SELECT A.CustFirstName INTO :WS-CustFirstName
FROM TblCustContacts A
WHERE (A.CustNum = 1)
END-EXEC
*
DISPLAY WS-CustFirstName.
*
OpenESQL-ERROR.
DISPLAY "Error = " SQLERRM
DISPLAY "SQLCode = " SQLCODE.
*
END PROGRAM FirstDBProg.

******************************************************

Need solution. 

This code was working in Visual Cobol 3.1 in Visual Studio 2012 environment with SLQ SERVER database.

We are now migrating to Visual Cobol 9.1+ with SQL SERVER database 2012 and Visual Studio 2022.

Here are the existing code.

INITIALIZE PREP
STRING
'"C:\\SQL\\SCRIPTS\\REFU\\NETCMD.CMD G: ' DELIMITED BY SIZE
DUMP-SERVER DELIMITED BY ' '
' COPY C:\\SQL\\DATA\\' DELIMITED BY SIZE
WS-DATA-FILE-NAME DELIMITED BY SIZE
' G:' DELIMITED BY SIZE
DUMP-PATH DELIMITED BY ' '
'"' DELIMITED BY SIZE
INTO PREP
END-STRING.
EXEC SQL
EXEC master.dbo.xp_cmdshell :PREP, NO_OUTPUT
END-EXEC.

Compilation is failed.
Error => COBES0503 Found MASTER.DBO.XP_CMDSHELL, expected SQL in nested EXEC statement.

From what I see, what you are trying to do is not supported. You are trying to execute Transact-SQL statements directly within an EXEC SQL block. In order to run Transact-SQL statements, dynamic SQL should be used.

The following compiles cleanly:

INITIALIZE PREP
STRING
'exec master.dbo.xp_cmdshell ' delimited by size
'"C:\\SQL\\SCRIPTS\\REFU\\NETCMD.CMD G: ' DELIMITED BY SIZE
DUMP-SERVER DELIMITED BY ' '
' COPY C:\\SQL\\DATA\\' DELIMITED BY SIZE
WS-DATA-FILE-NAME DELIMITED BY SIZE
' G:' DELIMITED BY SIZE
DUMP-PATH DELIMITED BY ' '
'"' DELIMITED BY SIZE
', NO_OUTPUT' delimited by size
INTO PREP
END-STRING.

EXEC SQL
     execute immediate :PREP
END-EXEC.