Trying to connect to SQL SERVER

[Former Member]

We have SAP on Oracle database but as we are implementing our HR system on SQL Server so we need to connect to SQL SERVER through ABAP in order to retrieve some information. We created the Connection successfully but when trying to retrieve the data it is giving us following dump :

Runtime Errors         DBIF_DSQL2_INVALID_CURSOR

Except.                   CX_SY_NATIVE_SQL_ERROR

Invalid interruption of a database selection

The code through which i am trying to retrieve the data is as follows :

DATA: c1  TYPE cursor.

DATA : BEGIN OF EMP_DATA OCCURS 0,

        FNAME(5000) TYPE C,

        END OF EMP_DATA.

DATA : fnm TYPE STRING.

EXEC SQL.

   CONNECT TO  'HRMS'

ENDEXEC.

if sy-subrc eq 0.

   MESSAGE 'Connected..' TYPE 'I'.

else.

   MESSAGE 'Not Connected..' TYPE 'I'.

endif.

EXEC SQL.

   OPEN c1  for

   SELECT FNAME

   FROM view_EMPDATAPayroll_New

ENDEXEC.

if sy-subrc eq 0.

   MESSAGE 'Access Successful..' TYPE 'I'.

else.

   MESSAGE 'Access Not Successful..' TYPE 'I'.

endif.

DO.

   EXEC SQL.

     FETCH NEXT c1 INTO  :EMP_DATA.FNAME;

   ENDEXEC.

   APPEND EMP_DATA.

   IF sy-subrc <> 0.

     EXIT.

   ENDIF.

ENDDO.

EXEC SQL.

   CLOSE c1

ENDEXEC.

EXEC SQL.

   DISCONNECT 'HRMS'

ENDEXEC.

We have tried a lot to solve the error but every time we are getting the same dump. It is giving dump at the following command :

FETCH NEXT c1 INTO  :EMP_DATA.FNAME;

The connection properties are also as follows :

DB Connection      HRMS

DBMS                 MSS

User Name           sa

Conn. info            MSSQL_SERVER=192.168.0.60\SQLEXPRESS MSSQL_DBNAME=HRAlign_Rubamin

Please help me out.

Tarun.

[Former Member]

We have SAP on Oracle database but as we are implementing our HR system on SQL Server so we need to connect to SQL SERVER through ABAP in order to retrieve some information. We created the Connection successfully but when trying to retrieve the data it is giving us following dump :

Runtime Errors         DBIF_DSQL2_INVALID_CURSOR

Except.                   CX_SY_NATIVE_SQL_ERROR

Invalid interruption of a database selection

The code through which i am trying to retrieve the data is as follows :

DATA: c1  TYPE cursor.

DATA : BEGIN OF EMP_DATA OCCURS 0,

        FNAME(5000) TYPE C,

        END OF EMP_DATA.

DATA : fnm TYPE STRING.

EXEC SQL.

   CONNECT TO  'HRMS'

ENDEXEC.

if sy-subrc eq 0.

   MESSAGE 'Connected..' TYPE 'I'.

else.

   MESSAGE 'Not Connected..' TYPE 'I'.

endif.

EXEC SQL.

   OPEN c1  for

   SELECT FNAME

   FROM view_EMPDATAPayroll_New

ENDEXEC.

if sy-subrc eq 0.

   MESSAGE 'Access Successful..' TYPE 'I'.

else.

   MESSAGE 'Access Not Successful..' TYPE 'I'.

endif.

DO.

   EXEC SQL.

     FETCH NEXT c1 INTO  :EMP_DATA.FNAME;

   ENDEXEC.

   APPEND EMP_DATA.

   IF sy-subrc <> 0.

     EXIT.

   ENDIF.

ENDDO.

EXEC SQL.

   CLOSE c1

ENDEXEC.

EXEC SQL.

   DISCONNECT 'HRMS'

ENDEXEC.

We have tried a lot to solve the error but every time we are getting the same dump. It is giving dump at the following command :

FETCH NEXT c1 INTO  :EMP_DATA.FNAME;

The connection properties are also as follows :

DB Connection      HRMS

DBMS                 MSS

User Name           sa

Conn. info            MSSQL_SERVER=192.168.0.60\SQLEXPRESS MSSQL_DBNAME=HRAlign_Rubamin

Please help me out.

Tarun.

[Arun_Prabhu_K]

Hello Tarun.

     Semicolon should not come at the end of FETCH NEXT c1 INTO  :EMP_DATA.FNAME

Regards.

[Former Member]

Thanks for your timely response Arun. Solved the issue as I was using Message inside the cursor , I removed that and problem is solved.

When I am retrieving only 2 columns in select statement then it is getting executed but when I am giving more than 2 columns then it is giving error "Incorrect Syntax Near".

But now I made following changes in program and getting dump (with different message).

The code is as under.

DATA: c1  TYPE cursor.

DATA : BEGIN OF EMP_DATA OCCURS 0,

        FNAME(100)       TYPE C,

        LNAME(100)       TYPE C,

        JOINING_CTC(16)  TYPE P DECIMALS 2,

        END OF EMP_DATA.

DATA : fnm(100)    TYPE c,

        lnm(100)    TYPE c,

        jng_ctc(16) TYPE P DECIMALS 2.

EXEC SQL.

   CONNECT TO  'HRMS'

ENDEXEC.

EXEC SQL.

   OPEN c1  for

   SELECT FNAME LNAME JOINING_CTC  FROM view_EMPDATAPayroll_New.

ENDEXEC.

DO.

   EXEC SQL.

     FETCH NEXT c1 INTO :EMP_DATA

   ENDEXEC.

   APPEND EMP_DATA.

   IF sy-subrc <> 0.

     EXIT.

   ENDIF.

ENDDO.

EXEC SQL.

   CLOSE c1

ENDEXEC.

EXEC SQL.

   DISCONNECT 'HRMS'

ENDEXEC.

LOOP AT EMP_DATA.

   WRITE: EMP_DATA-FNAME , EMP_DATA-LNAME , EMP_DATA-JOINING_CTC.

ENDLOOP.

At execution I am getting dump with the following message:

Short text

    SQL error 102 occurred when executing Native SQL.

What happened?

    The error 102 occurred in the current database connection "HRMS".

    Error Text of Database: "Incorrect syntax near 'JOINING_CTC'."

    SQL Statement Responsible:"SELECT FNAME LNAME JOINING_CTC FROM

     view_EMPDATAPayroll_New."

Error analysis

    An exception occurred that is explained in detail below.

    The exception, which is assigned to class 'CX_SY_NATIVE_SQL_ERROR', was not

     caught and

    therefore caused a runtime error.

    The reason for the exception is:

    Error Text of the Database: "Incorrect syntax near 'JOINING_CTC'."

Regards.

Tarun.

[Former Member]

Hi,

When using native sql , dont use  any full stop (.) at the end of syntax. Also comma missed after each field. In native sql it should be with comma

Change below syntax and try:

OPEN c1  for
SELECT FNAME,
                 LNAME , 
                 JOINING_CTC  FROM view_EMPDATAPayroll_New  ". Remove full stop from this syntax

Regards,

[Arun_Prabhu_K]

Tarun,

          Chandra is right.

          Better have a look at basic native SQL syntax.

Regards.

[Former Member]

Thanks for your help Chandra & Arun. Its solved as Comma was missing between the fields and I removed fullstop at the end.