DBIF_DSQL2_SQL_ERROR when connect external database

[Former Member]

Hello,

I have a program that does an insert query in an external database using open sql.

The code is something like this:

....

exec sql.

connect to 'MINERVA'

endexec.

exec sql.

insert into MY_TABLE values ( 'OK', 'XPTO' )

commit work

endexec.

....

It works Ok. Althought, when I stop and start the external database, next time I run it happens a run time error 'DBIF_DSQL2_SQL_ERROR':

SQL error 11 occurred when executing EXEC SQL.

The error occurred in the current database connection "MINERVA".

Database error text........: "[DBNETLIB][ConnectionWrite (send()).]General

network error. Check your network documentation."

The connection string is created in table DBCON.

Anyone as any ideia, why is this happening.

Thank you,

Andre Tomada

[Former Member]

Hello André,

Do you close the connection in you're program when you're done with it ?

kind regards

Pieter

[Former Member]

Hello Pieter,

One of the tests I did was to close connection with this code:

-

---

exec sql.

disconnect 'MINERVA'

endexec.

-

---

I don't know if it this correct way, the behaviour was the same.

Regards,

Andre Tomada

[Former Member]

Hello Andre,

This looks good, what DB system is this Minerva ?

Pieter

[Former Member]

Hi,

Recently we updated the External Database in the similar fashion.

Please make note of below points before Testing.

A).Syntax should be as shown below:

1.Open connection

CONSTANTS:c_dbname(3) TYPE c VALUE 'XYZ'.

"Database Connection

*Call Native SQL to connect

EXEC SQL.

CONNECT TO :C_DBNAME

ENDEXEC.

*If Connection successful

IF sy-subrc = 0.

EXEC SQL.

SET CONNECTION :C_DBNAME

ENDEXEC.

ENDIF.

2.Insert

EXEC SQL.

• insert into external database table T_EXTERNAL_TABLE_NAME

insert into T_EXTERNAL_TABLE_NAME

(FIELD1,

FIELD2,

.....

FIELD N)

values(:V_FIELD1,

:V_FIELD2,

.....

:V_FIELDN)

ENDEXEC.

3.Close connection

EXEC SQL.

DISCONNECT :C_DBNAME

ENDEXEC.

B)Make sure the connection has been set in Transaction DBC0

c)Take care of datatypes and lengths while inserting.

Thanks,

Chandu.

[Former Member]

Hello,

This is very strange.

That code is exatly what I have now. The database is MSSQL.

When I stop the database or kill the processes manually, the next time I run it gives the error. No any process was created at this time in database. But if I run again it creates two processes and everthing is ok.

The sy-subrc after the connect is always 0, even in the situations that becomes in error (the dump refers to the line with the "insert").

Regards,

Andre Tomada

[Former Member]

dear Andre Tomada

i am not able to help u out but the thing is that i have also get some requirement like ur to update my external data base in SQL, i have tried my best to do so, but not able to reach the expected results, if so how can u guide me how u have done this, the various steps of doing so, as now i have done thru very complex programs to achieve this, we are using SAP 6.20,

i will be thankful to u if u can send me the steps and necessary setiings involved in doing so at <b>abhiaries@yahoo.com</b>

ur response will be very helpfulf to me

thanx in advance.

abhishek suppal

[Former Member]

Hi,

Can you tell why you are "stopping the database or killing the processes manually?".

If you can paste the code,I guess I can help you out.

Errors can occur in this scenario's also:

1.Check if any date fields are their while inserting?if the value of date fields are initial.Pass space instead.

2.Also with out opening the Connection if you try to close it dumps at Close connection.

3.Try to pass fields in the same order as in External Database.

Thanks ,

Chandu

[Former Member]

Hi Chandu,

I'm stopping the database manually just to test the problem. The issue is that this is a diary process, and sporadically it can be done a restart to external server or database.

The code as I have now, is very simple as you can see:

(I deleted only the comment lines)

-

---

REPORT ZFI_FIM_CADEIA .

data: timestamp(20).

data: date(10),

time(10).

constants: c_dbname(7) type c value 'MINERVA'.

start-of-selection.

exec sql.

connect to :c_dbname

endexec.

if sy-subrc eq 0.

exec sql.

set connection :c_dbname

endexec.

endif.

exec sql.

insert into minerva.gestao.dbo.TB_GS_SAPLOG001

select 'OK' as sap_descritivo,

cast(getdate() as smalldatetime) as sap_timestamp

commit work

endexec.

exec sql.

disconnect :c_dbname

endexec.

-

---

Everything runs Ok, the insert is well done. Except the first time I run after restart the database! Very strange!

Thanks,

Andre Tomada

[Former Member]

Hello Andre,

Have you tried turning on a trace as specified in oss-note 738371 ?

3. If a connection fails or takes a very long time, the first place to

look for errors is in the developer trace files

(SID\DVEBMGS00\work\dev_w??). The error messages will indicate

which network protocol was attempted:

[DBNMPNTW]ConnectionOpen

for example means that named pipes were being used. However the

trace files don't show a lot of detail beyond that. To get a more

detailed trace you can do the following:

a) se38 -> Run program RSMSS_DBSL_PROFILE_SWITCH. Choose to set

profiling ON.

b) Run the program that connects to the remote server and wait for

it to fail.

c) Run program RSMSS_DBSL_PROFILE_SWITCH again and turn profiling

OFF. NEVER leave the profiling on for extended periods.

Now new trace file(s) will exist in SID\DVEBMGS00\work named

dbsl_w??. These files contain much more detail about the server

name, and other connect options being used.