Skip to Content

Register Login

Industry Online Support

Technical Forum

Navigation

Connect remote SQL server, set ODBC timeout?

Created by: Patbacarde at: 8/28/2008 7:46 PM (3 Replies)

Rating (0)

Thanks 0

4 Entries

8/28/2008 7:46 PM	Rate (0)
Patbacarde Posts: 15 Rating: (0)	HI Expert! What I should do, if I want to connect to a SQL data base on a remote Server with Wincc, but I don’t wan to slow down the Wincc project when the Network is down? With this vbs: 'On ouvre la connexion à la dBase. Set objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = HMIRuntime.Tags("ConnectionString").Read(1) 'RAZ ODBC_Try_Counter. ODBC_Try_Counter = 0 Do Until ODBC_Try_Counter = HMIRuntime.Tags("ODBC_TRY").Read(1) objConnection.ConnectionTimeout = HMIRuntime.Tags("ODBC_Timeout").Read(1) objConnection.CommandTimeout = HMIRuntime.Tags("ODBC_Timeout").Read(1) 'Ouvrir la Connexion. objConnection.Open If Err.Number = 0 Then ODBC_Try_Counter = HMIRuntime.Tags("ODBC_TRY").Read(1) 'Set le Flag que le reseau est disponible (=0) Network_TimeOut = 0 Else 'Incrémenter le ODBC_Try_Counter. ODBC_Try_Counter = ODBC_Try_Counter + 1 'Set le Flag que le reseau n'est pas disponible (=1) Network_TimeOut = 1 'Écriture de l'erreur EventLog(GetAndClearError()) End If 'Appel de la sous-fonction pour valider le temps d'accès réseau. Tracking_Reseau TimeStamp, "BackUp_CN_Ecriture_DB", "objConnection.Open" Loop I try 3 times to connect the remote SQL server when the network is down. But whatever I set the ODBC timeout, when "objConnection.Open" is process Wincc stay stock on this command for 90 seconds when the network is down.I think that the ODBC timeout is set by window but how can I change this setting? Or how can I check if the network is available without slow down Wincc thread?Best RegardsPat
Patbacarde Posts: 15 Rating: (0)	Last edited by: Patbacarde at: 8/28/2008 7:49 PM
Suggestion To thank Quote Answer

9/1/2008 8:25 AM	Rate (0)
aikona Posts: 487 Rating: (36)	Hi We are using a slightly different approach On Error Resume Next Set conn = CreateObject("ADODB.Connection") Set rst = CreateObject("ADODB.Recordset") 'Open data source conn.Open "Provider=SQLOLEDB.1;Password=blablabla;Persist Security Info=True;User ID=moreblabla;Initial Catalog=Test;Data Source=ServerID" 'Error routine If Err.Number <> 0 Then myvar = MsgBox("Error #" & Err.Number & " " & Err.Description, vbOKCancel) ShowSystemAlarm "Error #" & Err.Number & " " & Err.Description Err.Clear Exit Sub End If I am sure you can use the same for ODBC .Our routine is called up via a button and if no network the other statusses on the screen keep on working and it does not stop us from say changing picture. Hope It helps Eric
aikona Posts: 487 Rating: (36)
Suggestion To thank Quote Answer

9/2/2008 3:07 PM	Rate (0)
Patbacarde Posts: 15 Rating: (0)	Thanks Eric for your help. When the: conn.Open "Provider=SQLOLEDB.1;Password=blablabla;Persist Security Info=True;User ID=moreblabla;Initial Catalog=Test;Data Source=ServerID" will be process, if you use Script Debugger you will see that this command is very long to be execute when the network is down or when you can't connect to your DB. What kind of data base do you have? SQL ? Remote server? What kind link to this data base? ODBC? In my project, I connect to remote SQL server via button, via cyclic action and tag trigger action. If the operators push 2 or 3 button with a SQL connection when the network is down, only after 90 sec the operator will see the first msgbox error and only after 180 sec the operator will see the second msgbox. This is very to long. Any other ideas will be estimate!
Patbacarde Posts: 15 Rating: (0)
Suggestion To thank Quote Answer

7/27/2017 11:31 AM	Rate (0)
Jen_Moderator Moderator Joined: 6/19/2017 Last visit: 9/29/2023 Posts: 7306 Rating: (145)	New question published by ZHK is split to a separate thread with the subject ODBC - Problem: too long execution time of ADODBConnection. Best regards Jen_Moderator
	My Forum is the place to personalize your profile, track your topics, and maintain content that interests you. Best regards Jen_Moderator
Suggestion To thank Quote Answer

|

Share this page:

Share this page on...

Follow us on