SQL Server Performance Forum – Threads Archive

Error [DBNETLIB][ConnectionOpen in a DTS Package

Hi On a DTS Package that was running perfectly since now, we now get the error : <pre id="code"> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.</pre id="code"> The error occurs when the DTS Package is executing an ActiveX script. This script executes some ‘SELECT’ and ‘UPDATE’ statements on the database. The connection statements to the database are :<pre id="code"> Set MyConnection = CreateObject("ADODB.Connection") MyConnection.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=my_database;user id = ‘sa'<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />assword=’mypassword’" </pre id="code"> When I connect to the database throught the query Analyser using the same user/password everything is OK. Any idea ?

Hi Yuan, Hope the DB Server does exist and connectible[ping] from the Appln Server. Since you have said that the scripts worked fine..it would be better if you find any changes made in the recent past to the SQL Server/Connectivity tools, incl the "sa" pwd. Also, check the SQL Server logs for any messages depicting the current state of the Server. Is the SQL Server fine?? The consistency of my_database?? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Honey.

Hi,
i think the issue here is connection to local sql server from activex script.
You can try connection with windows login instead of sa user.
Else you can try the same script after installation of mdac 2.81 if it is not installed

This is not a connection issue : everything is running on a single machine ! In fact, for another reason, we had to restart SQL Server, and now, the DTS package is working fine again (without modifying anything !). Thank’s all the same…

We just solved this one in our installation. The problem is not related to using an ActiveX control. The problem is encountered when a script is accessing SQL server very quickly, opening and closing connections over and over, using a TCP/IP connection. What happens is windows (All versions, I think) defaults to a limited numbers of anonymous ports (1024-5000, based on the BSD standard) for cycling connections, and the timeout on these ports is set fairly high (240 seconds), so after you close your connection, the assigned port remains in the TIME_WAIT state for the rest of the 240 seconds. When you overrun your pool of ports, you get that error; ergo, you need to configure the ports to release faster, and you need to enlarge your initial pool of ports. To do this, on the *client* machine, you need to make a couple fo registry changes. First, usie regedit to navigate to: HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpipParameters …then modify (or create if you have to) this key: MaxUserPort …it’s a REG_DWORD, and the default value is 5000 decimal. Make it 65534 decimal. Then, in the same registry folder, make or modify the following key: TcpTimedWaitDelay this is also a REG_DWORD, and the default is 240 decimal. MAke it 30 (Which is a low as you can go). This solved the problem for us. Good luck, and contact me directly if you have any questions. –Sean Harrison
[email protected]

Hi Scatterblak, I’ll look this side next time the issue occur. Thank’s

]]>