Problem with connection to MSSQL database

Former Member — Tue, 10 Jun 2008 11:59:46 GMT

Sorry if there is an answer already but i've searched for the solution half a day and hasn't found any.

I want to export the data from SAP HR system to MSSQL database using ADODB. So I have a form for opening the connection and for running sql statements like select and insert. Everything works great when I run this program in SAP GUI via SA38. It makes all the changes it should do like:

- running select statement and returning the results

- delete records from destination table

- insert records into destination table

The problem is when I try to run it in background which in fact is what I need since this application should provide the data to MSSQL on regular dayly basis.

It just does not make any changes on the destination database. Strange thing is that all of the statements like

CREATE OBJECT or CALL METHOD return with subrc = 0. But for instance the select statement which should return the results does not change the initial value of statement. So if you look at the code below, the statement

"select count(*) from addSAPHRIMPORT" normally returns the number and the pipe but when it runs in background the value doesn't change so after running this command I still have this select value in sql variable.

Therefore I cannot find where the problem is. The program runs without errors but has no effect on destination database.

Below is the code I use (many writes were added only for testing reasons so do not be scared 😞

*opening connection to MSSQL

FORM openconnection.

sql = 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xxxxxx;Password=yyyyyy;Initial Catalog=schema_name;Data Source=db_server_dns_name'.

IF con-header IS INITIAL OR con-handle = -1.

CREATE OBJECT con 'ADODB.Connection'.

write : / 'The result: ', sy-subrc.

IF NOT sy-subrc = 0.

EXIT.

ENDIF.

CREATE OBJECT rec 'ADODB.Recordset'.

write : / 'The result : ', sy-subrc.

IF NOT sy-subrc = 0.

EXIT.

ENDIF.

CALL METHOD OF con 'Open'

EXPORTING

#1 = sql.

if sy-subrc <> 0.

write: / 'Connection error', sy-subrc.

else.

write : / sql.

endif.

ENDFORM. "openconnection

operations on database

FORM get_rec.

sql = 'select count(*) from addSAPHRIMPORT;'.

CALL METHOD OF rec 'OPEN'

EXPORTING

#1 = sql

#2 = con

#3 = '1'.

IF sy-subrc = 0.

WRITE: / 'OK'.

ELSE.

WRITE: / 'Error: ', sy-subrc.

EXIT.

ENDIF.

CALL METHOD OF rec 'getstring' = sql

EXPORTING

#1 = '2'

#2 = 1

#3 = '|'

#4 = '|'.

IF sy-subrc = 0.

REFRESH spl. CLEAR spl.

WRITE: / sql.

SPLIT sql AT '|' INTO noOfRecs xxx.

WRITE: / 'Number of records: ', noOfRecs.

ELSE.

WRITE: / 'Błąd: ', sy-subrc.

noOfRecs = 0.

EXIT.

ENDIF.

CALL METHOD of rec 'Close'.

sql = 'delete from [addSAPHRIMPORT]; '.

CALL METHOD OF rec 'Open'

EXPORTING

#1 = sql

#2 = con

#3 = '1'.

IF sy-subrc = 0.

WRITE : / 'Records deleted'.

ELSE.

WRITE : / 'ERROR'.

EXIT.

ENDIF.

ENDIF.

CALL METHOD of rec 'Close'.

counter = 0.

refresh spl.

clear spl.

LOOP with created inserts earlier in the program

LOOP AT dbComm.

write : / dbComm-sqlStr.

CALL METHOD OF rec 'OPEN'

EXPORTING

#1 = dbComm-sqlStr

#2 = con

#3 = '1'.

IF sy-subrc = 0.

add 1 to counter.

ELSE.

write : 'ERROR: '.

ENDIF.

CALL METHOD of rec 'Close'.

ENDLOOP.

write : / 'Inserted ', counter, ' records'.

PERFORM close_connection.

ENDFORM. "Get_Rec

closing connection

FORM close_connection.

FREE rec.

FREE con.

ENDFORM. "closeconnection

Edited by: Pawel Knapik on Jun 10, 2008 2:00 PM

Re: Problem with connection to MSSQL database

Former Member — Mon, 16 Jun 2008 09:37:16 GMT

Anyone ?

Re: Problem with connection to MSSQL database

Former Member — Wed, 03 Sep 2008 05:56:31 GMT

Hi,

I also want to connect from ABAP to MSSQL. CAn you send the code

Thanks

Santhikumar

topic Re: Problem with connection to MSSQL database in Application Development and Automation Discussions

Problem with connection to MSSQL database

Re: Problem with connection to MSSQL database

Re: Problem with connection to MSSQL database