Solved

Connection failure

Posted on 2006-10-24
16
2,147 Views
Last Modified: 2008-01-09
I developed an MS Access front end to an MS SQL 2000 database.  The server is now in production on a remote site.
I am attempting to continue developing the system linking to a development version on my PC.
The VB coding has connection to the devl database hard coded, and the drop down combos use the linked tables to the DEVL database.
But when I call a query that in turn passes parameters to stored procedure the system hangs for 30 secs then returns error:
Connection failed
SQL State: '01000'
SQL server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Connection Open (Connect())
Connection failed
SQL State: '01000'
SQL server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets] Specified SQL Server not found

It then allows me to choose between IMEX-SERVER ( the PROD server which is not connected) and PHACT-PC (the devl database server). PHACT-PC brings back the correct selection from the devl database.

I have done a reinstall of the WIN XP operating system, lest there was data in the registry, and reinstalled the developers SQL SERVER 2000 with SP4.  The problem still persists.

I cannot see any alias when I use CLICONFG.

How can I continue to develop the system whilst pointing to the DEVL database, yet be certain that it will work when I put it on the users client machines which point to IMEX-SERVER?

Any ideas?  
0
Comment
Question by:Nigel Keith-Walker
  • 6
  • 5
  • 5
16 Comments
 
LVL 5

Expert Comment

by:Netstore
Comment Utility
Thats looks like this is unable to connect to the SQL database. From your home PC try the following:

telnet <SQL Host> 1433

If this connects then it is physically possible to connect to the database.

If not then try using the IP address of the host, it may be something simple like adding a entry to your host file on your deveopment PC.
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
Firewall ports are also in play here...both hardware and Windows firewall in XP sp2.  Neetstore's telnet suggestion should show that.

make sure the SQL Server is actually listening on TCP, instead of just named pipes/shared memory
0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
Telnet IMEX-SERVER 1433 returns connecting to IMEX-SERVER could not open connection to the host on port 1433.  Which is as expected as it is not on the same network.  However when I try telnet PHACT-PC which is the SQL sever sitting on the same PC it hangs for more than three minutes.

The CLICONFG is set for two protocols firstly TCPIP with port 1433, secondly named pipes \\.\sql\query

Thanks for the suggestion about firewalls, but this is connecting to a SQL server on the same box.  I am using ESET/NOD antivirus and MS windows firewall.
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
Check the SQL Server error log to see if its actually listening on TCP.  Also try to connect using

tcp:192.168.0.1,1433          (use your own IP)

np:servername

np:IPAddress

Any Server aliases set in CLICONFG?
0
 
LVL 5

Expert Comment

by:Netstore
Comment Utility
Are there any errors in the event viewer:

Start -> Run -> eventver

Is the service up and running as the correct user:

Start -> Run -> Services.msc - Check MSSQLSERVER and SQLSERVERAGENT are up and running correctly.

Can you open Enterprise Manager and navigate around the database?
0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
Server Error log : thsi had a couple of interesting messages : see below - could this help

tcp:192.168.0.150 (own ip address) - err msg - filename, directory name or volume name syntax is incorrect
np:PHACT-PC (local sql server) - err msg - filename, directory name or volume name syntax is incorrect
np:192.168.0.150 (own ip address) - err msg - filename, directory name or volume name syntax is incorrect
No aliases set in CLICONFG
start>run>eventver - err msg cannot find 'eventver'
No problems with opening up the Enterprise manager and navigating around.  I can query a database table, create a new datbase and use data transformation to rebuild a database.

SQL Error log
Date      Source      Message
2006-10-26 22:15:38.32       server      Microsoft SQL Server  2000 - 8.00.194 (Intel X86) ...
2006-10-26 22:15:38.59       server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQ
2006-10-26 22:15:38.59       server      Server Process ID is 528.
2006-10-26 22:15:38.59       server      All rights reserved.
2006-10-26 22:15:38.59       server      Copyright (C) 1988-2000 Microsoft Corporation.
2006-10-26 22:15:38.75       server      SQL Server is starting at priority class 'normal'(1 CPU detected).
2006-10-26 22:15:40.26       server      SQL Server configured for thread mode processing.
2006-10-26 22:15:40.34       server      Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2006-10-26 22:15:40.71       server      Attempting to initialize Distributed Transaction Coordinator.
2006-10-26 22:15:43.46       server      Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b
2006-10-26 22:15:43.79       spid3      Starting up database 'master'.
2006-10-26 22:15:44.50       server      Using 'SSNETLIB.DLL' version '8.0.311'.
2006-10-26 22:15:44.51       spid3      Server name is 'PHACT-PC'.
2006-10-26 22:15:44.51       spid5      Starting up database 'model'.
2006-10-26 22:15:44.53       spid11      Starting up database 'IMEX_HQ'.
2006-10-26 22:15:44.53       spid10      Starting up database 'Northwind'.
2006-10-26 22:15:44.53       spid9      Starting up database 'pubs'.
2006-10-26 22:15:44.53       spid8      Starting up database 'msdb'.
2006-10-26 22:15:44.71       server      SuperSocket Info: Bind failed on TCP port 1433.
2006-10-26 22:15:44.71       server      SuperSocket Info: Bind failed on TCP port 1433.
2006-10-26 22:15:44.71       server      SuperSocket Info: Bind failed on TCP port 1433.
2006-10-26 22:15:44.71       server      SuperSocket Info: Bind failed on TCP port 1433.
2006-10-26 22:15:44.71       server      SQL server listening on 127.0.0.1: 1433.
2006-10-26 22:15:44.71       server      SQL server listening on 192.168.0.150: 1433.
2006-10-26 22:15:44.78       server      SQL Server is ready for client connections
2006-10-26 22:15:44.78       server      SQL server listening on TCP, Shared Memory, Named Pipes.
2006-10-26 22:15:47.01       spid5      Clearing tempdb database.
2006-10-26 22:15:49.98       spid5      Starting up database 'tempdb'.
2006-10-26 22:15:51.49       spid3      Recovery complete.
2006-10-26 22:29:44.70       spid51      Using 'xpstar.dll' version '2000.80.194' to execute extended stored


0
 
LVL 5

Assisted Solution

by:Netstore
Netstore earned 150 total points
Comment Utility
0
 
LVL 5

Expert Comment

by:Netstore
Comment Utility
This is a total stab in the dark but your using VPN may be relevant:

http://www.thescripts.com/forum/thread504532.html

Alot of the stuff on this recommends trying to run SQL server on a different port and connect to it on that i.e. port 1433 is still in use or has an issue
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:Netstore
Comment Utility
Last one and I'll leave you in peace, but this seems fairly relevant 8)

http://thedotnet.com/nntp/430380/showpost.aspx
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 200 total points
Comment Utility
Dude...upgrade that thing to AT LEAST SP3a....you are at RTM (8.00.194)

If the host O/S is Windows 2003, tcp will not work until you are at sp3 or higher
0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
I skipped the upgrade SP3a and went directly to SQL2000.AS-KB884525-SP4-x86-ENU.
Should I apply SP3a then SP4?
Should I uninstal SQL server and start again?
It will be 24 hours before I can do it, but will keep you informed.
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
that is the Analysis Services sp4...not the SQL engine

Download the correct file (there are 4 on the page) and go from there.   SQL2000-KB884525-SP4-x86-ENU.EXE

Uninstalling/reinstalling is useless

0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
I have just upgraded to sp4 but still get the same problem after re-booting the PC.  Thanks for pointing out that the upgrade had not been effected.  

I checked all of the above including CLICONFG, ALIAS and Telnet - no symptoms had changed. It still hangs when a query calls a stored procedure.

I have printed out the new SQL server log:
Date      Source      Message
2006-10-28 00:11:30.65       server      Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) ...
2006-10-28 00:11:31.37       server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQ
2006-10-28 00:11:31.37       server      Server Process ID is 1600.
2006-10-28 00:11:31.37       server      All rights reserved.
2006-10-28 00:11:31.37       server      Copyright (C) 1988-2002 Microsoft Corporation.
2006-10-28 00:11:31.67       server      SQL Server is starting at priority class 'normal'(1 CPU detected).
2006-10-28 00:11:32.49       server      SQL Server configured for thread mode processing.
2006-10-28 00:11:32.54       server      Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2006-10-28 00:11:32.81       server      Attempting to initialize Distributed Transaction Coordinator.
2006-10-28 00:11:32.98       server      Failed to obtain TransactionDispenserInterface: XACT_E_TMNOTAVAILABLE
2006-10-28 00:11:33.14       spid3      Starting up database 'master'.
2006-10-28 00:11:33.73       server      Using 'SSNETLIB.DLL' version '8.0.2039'.
2006-10-28 00:11:33.75       spid5      Starting up database 'model'.
2006-10-28 00:11:33.78       server      SQL server listening on 192.168.0.150: 1433.
2006-10-28 00:11:33.78       spid3      Server name is 'PHACT-PC'.
2006-10-28 00:11:33.79       spid11      Starting up database 'IMEX_HQ'.
2006-10-28 00:11:33.79       spid10      Starting up database 'Northwind'.
2006-10-28 00:11:33.79       spid9      Starting up database 'pubs'.
2006-10-28 00:11:33.79       spid8      Starting up database 'msdb'.
2006-10-28 00:11:33.81       server      SuperSocket Info: Bind failed on TCP port 1433.
2006-10-28 00:11:33.81       server      SQL server listening on 127.0.0.1: 1433.
2006-10-28 00:11:33.89       server      SuperSocket Info: Bind failed on TCP port 1433.
2006-10-28 00:11:34.60       server      SQL Server is ready for client connections
2006-10-28 00:11:34.60       server      SQL server listening on TCP, Shared Memory, Named Pipes.
2006-10-28 00:11:34.95       spid5      Clearing tempdb database.
2006-10-28 00:11:37.14       spid5      Starting up database 'tempdb'.
2006-10-28 00:11:38.04       spid3      SQL global counter collection task is created.
2006-10-28 00:11:38.04       spid3      Recovery complete.
2006-10-28 00:22:43.32       spid51      Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure

I note that the  SuperSocket Info: Bind failed on TCP port 1433.  I checked on netdiag /test:winsock, it passed but warned that "RAS Async adapter" may not be working because it had not received any packets, but that is just a remote area connection.  

I checked the network connections for TCP/Ip.  It is set to automatically to obtain IP address and automatically obtain DNS server address and is DHCP enabled.

There was some reference to winsock corruption seizing port 1433.
There is a Microsoft registry workaround in Q307197 to change the DWORD to TcpAbortiveClose on HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetlib\Tcp which I have not tried.

The other alternative is to use LSP-FIX a utility that repairs damage done to WINSOCK2 registry.  But Netdiag seems to think this is OK.

Any suggestions gratefully received.



0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
I hace found the problem.

The queries are pass-through queries.  These are attached to the data-source when they are saved originally.  This seems to be the only occassion when the data source is set, as it cannot be edited or enven displayed again.  I have eight pass-through queries. I will rename them and re-create eight pass-through queries with the same name pointing to the TEST database.  I understand that I could create a generic pass through query.

Thanks for the tip about the upgrade version.  I still do not know why I have a supersocket bind error   SuperSocket Info: Bind failed on TCP port 1433.
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
Probably some other process grabbing that port before SQL does...sometime a legitimate application, sometimes a SQL aware virus or rootkit.

Set the SQL instance to a different port and restart it to see if it takes that.

I believe a netstat command will show you what has 1433.

Nice catch on the pass-through queries....that's good info for the EE archives
0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
Just to round this off

I have found how to change the passed through query.  It was not obvious, but a posting on EE helped.

MS Access > objects > queries
select pass through query
right click > properties
  NO GOOD - too intuitive and obvious

select pass through queries
Design View
View > Properties
ODBC Connection string : ODBC;Description=IMEX_HQ on PHACT-PC Devl box;DRIVER=SQL Server;SERVER=PHACT-PC;UID=Phact;DATABASE=IMEX_HQ;Network=DBMSSOCN;Trusted_Connection=Yes

Kevin3NF thanks for the netstat hint - it showed the connections for tcp-ip but nothing for port 1433

However all is working, and I have been able to assure the client that I was working on their application
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now