Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
6
Erster Beitrag:
vor 3 Jahren, 3 Monaten
Letzter Beitrag:
vor 3 Jahren, 3 Monaten
Beteiligte Autoren:
Danny Lauwers, Kingdr, Paulo Oliveira, kingdr

[WD19] DB connection error ?

Startbeitrag von Danny Lauwers am 05.11.2014 13:25

Hi,

I have a program (Windows Service) that runs 24/7 to sync/convert some data between 2 MS SQL 2008R2 servers. Program is running fine most of the time, but sometimes I get the error:

Error:OLE DB access error.
Error Number = 170129

Failure opening file.

System Error Details:
Description = Connection failure
Source = Microsoft OLE DB Provider for SQL Server
SQL State = 08S01
Error Number = -2147467259 (0x80004005)


I think it's triggerred by a network/Internet glitch (VPN connection to datacenter) or when there is a problem with the remote or local SQL. Sometimes the remote SQL is rebooted for updates etc... but wil always be back online after that.

Most of the time once this error has occurred, the program does not recover by its self. When I am restarting my Windows service, everything works again without further actions. So it seems that the database connection somehow hangs ?

In my code I use the following to make or reinit my connection. At every start of a new sync cycle this is (for the database) the first call "InitDBConnection".


PROCEDURE InitDBConnection()
Result1,Result2 are strings

IF NOT ValidSettings THEN
// gclLogger("Invalid settings, do nothing")
RESULT "Invalid settings, do nothing"
END

WHEN EXCEPTION IN
SQLDisconnect()

gclSettings.DAServerConnection..Server = gclSettings.sDAServer
gclSettings.DAServerConnection..User = gclSettings.sDAUser
gclSettings.DAServerConnection..Password = gclSettings.sDAPw
gclSettings.DAServerConnection..Database = gclSettings.sDADB
gclSettings.DAServerConnection..Provider = hOledbSQLServer
HCloseConnection(gclSettings.DAServerConnection)

IF NOT HOpenConnection(gclSettings.DAServerConnection) THEN
IF InServiceMode() THEN
gclLogger.Add("Error connecting to DB: "+ ErrorInfo())
ELSE
ErrorWithTimeout(1000,ErrorInfo())
END

RESULT "Geen verbinding"
ELSE
HChangeConnection("*",gclSettings.DAServerConnection)
Result1 = gclSettings.sDAUser + "@" + gclSettings.sDAServer + "/" + gclSettings.sDADB
END

gclSettings.NAVServerConnection..Server = gclSettings.sNAVServer
gclSettings.NAVServerConnection..User = gclSettings.sNAVUser
gclSettings.NAVServerConnection..Password = gclSettings.sNAVPw
gclSettings.NAVServerConnection..Database = gclSettings.sNAVDB
gclSettings.NAVServerConnection..ExtendedInfo = "WD Connection Timeout=60; WD Command Timeout=60" // 60 seconds connection timeout, 60 seconds Command timeout, default this is 30 sec.
gclSettings.NAVServerConnection..Provider = hOledbSQLServer
HCloseConnection(gclSettings.NAVServerConnection)

IF NOT HOpenConnection(gclSettings.NAVServerConnection) THEN
IF InServiceMode() THEN
gclLogger.Add("Error connecting to DB: "+ ErrorInfo())
ELSE
ErrorWithTimeout(1000,ErrorInfo())
END

RESULT "Geen verbinding"
ELSE
//HChangeConnection("*",gclSettings.NAVServerConnection)
// Using pure SQL commands, no HChangeConnection needed, gives errors when used on NAV DB ?!!
//
Result2 = gclSettings.sNAVUser + "@" + gclSettings.sNAVServer + "/" + "NAV"
END
DO
gclLogger.Add("Exception connecting to DB: "+ ExceptionInfo())

IF NOT InServiceMode() THEN
ErrorWithTimeout(1000,ExceptionInfo())
END

RESULT "Geen verbinding"
END

RESULT Result1 + " | " + Result2


So before the synchronisation I (re)connect to the DB and after the sync I close the connection.

The error occurres after the init on the first HExecuteSQLQuery that is run on the remote server.

Is there something I can add or change to my code to be able to automatically recover from this error ?
This error occurres only once or twice each month, but there is no patern in when it fails. I do have some E-mail alerts to notify me of the problem, but this thing should be running 24/7 without this kind of error, it should auto recover from it !

Thanks
Danny

Antworten:

I have the same problem when there are errors in the connections, the only way i can recover is to restart the service.

In my service program when i get one of these errors i end the service and use the os AT command to start it again in 5 minutes:

wprox is Time=TimeSys()
wprox..Minute+=5
IF NOT ExeRun("AT "+TimeToString(wprox,"HH:MM")+ " NET START my_service",exeIconize,exeDontWait) THEN
G_TXT_ERRO="Error in AT command for my_service - "+ErrorInfo()
ServiceWriteEventLog(G_TXT_ERRO,elWarning,52)
END
EndService(esStop)

von Paulo Oliveira - am 05.11.2014 14:14
Hi

Why using SqlDisconnect() and HOpenConnection()?
I use codes as below with sqlNative Client in 2012

sDS is Data Source
nCnx is int=HDescribeConnection("sqlConn","userName","userPassword","ipAddress","dbName", hNativeAccessSQLServer, hOReadWrite)
trace(HOpenConnection("sqlConn"))
sSql is String= "select count(*) as [ttlRows] from test"
if not HExecuteSqlQuery(sDS, "sqlConn", hQueryWithoutCorrection,sSql) then//
error(HErrorInfo())
else
hreadfirst(sDS)
info(sDS.ttlRows)
end
HCloseConnection("sqlConn")

I always have problem when using OLE stuff, not only slow but some weird connect/disconnect behaviour.

I guess sqlClient 11 is backward comp. to sqlServer 2008.

I've been using such for almost 3yrs and connection is rock solid.

HTH

Cheers
King

von kingdr - am 06.11.2014 03:57
Hi King,

You are using the optional MS SQL Native Access ? I don't have the native access, so I guess I cannot use the hNativeAccessSQLServer type of provider you are using.

Is there anyother way without using Native stuff, else I have to buy a lot of native clients (for every server the software runs on).

It would be better if PCSoft would make that a "developer license" where the developer buy's this native DB access ones and can compile and distribute unlimited times. Even better would be free native access :D.

Thanks for the reply !
Danny

von Danny Lauwers - am 06.11.2014 08:54
Paulo,

If no other solutions are available I will indeed need to auto restart the service.

Have you ever tried if the function ServiceRestart(SERVICE_NAME) can be used to restart the service by itself ?

Bye
Danny

von Danny Lauwers - am 06.11.2014 09:13
Danny

Whooze you're right I do have native from pcSoft but
can you download Sql Server Native Client 11.0 / 10.0 from m/s and
define it as in odbcad32.exe and test out connection with wdSql.exe (ie 32bit) but
you can test connect with c:\windows\system32\odbcad32.exe (64-bit if yr
win7 is running 64-bit) and just use SqlConnect() with parameter hODBC or
not OLEDB.

Pls let me know.

Cheers

King

von Kingdr - am 06.11.2014 13:51
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.