|
| [WD19] DB connection error ? |
| Iniciado por guest, 05,nov. 2014 14:25 - 5 respuestas |
| |
| | | |
|
| |
| Publicado el 05,noviembre 2014 - 14: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() HCloseConnection(gclSettings.DAServerConnection) gclSettings.DAServerConnection..Server = gclSettings.sDAServer gclSettings.DAServerConnection..User = gclSettings.sDAUser gclSettings.DAServerConnection..Password = gclSettings.sDAPw gclSettings.DAServerConnection..Database = gclSettings.sDADB gclSettings.DAServerConnection..Provider = hOledbSQLServer 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 HCloseConnection(gclSettings.NAVServerConnection) 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 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) 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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 05,noviembre 2014 - 15:14 |
I have the same problem when there are erros 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) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,noviembre 2014 - 04:57 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,noviembre 2014 - 09:54 |
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 <img src="/NG2013_WEB/ui/smiley/5.gif" align=absmiddle border=0 alt=":D">.
Thanks for the reply !
Danny |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,noviembre 2014 - 10:13 |
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 |
| |
| |
| | | |
|
| | |
| |
| Publicado el 06,noviembre 2014 - 14:51 |
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 |
| |
| |
| | | |
|
| | | | |
| | |
|