Connection failure

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?  
Nigel Keith-WalkerContractorAsked:
Who is Participating?
 
Kevin HillSr. SQL Server DBACommented:
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
 
NetstoreCommented:
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
 
Kevin HillSr. SQL Server DBACommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nigel Keith-WalkerContractorAuthor Commented:
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
 
Kevin HillSr. SQL Server DBACommented:
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
 
NetstoreCommented:
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
 
Nigel Keith-WalkerContractorAuthor Commented:
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
 
NetstoreCommented:
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
 
NetstoreCommented:
Last one and I'll leave you in peace, but this seems fairly relevant 8)

http://thedotnet.com/nntp/430380/showpost.aspx
0
 
Nigel Keith-WalkerContractorAuthor Commented:
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
 
Kevin HillSr. SQL Server DBACommented:
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
 
Nigel Keith-WalkerContractorAuthor Commented:
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
 
Nigel Keith-WalkerContractorAuthor Commented:
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
 
Kevin HillSr. SQL Server DBACommented:
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
 
Nigel Keith-WalkerContractorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.