odbc not talk to SQL

Posted on 2007-03-30
Last Modified: 2008-02-20
Hi there we have citrix using ODBC to connect to our ERP system.
Today all of a sudden 10 people got kicked out i investigated and on one of our citrix servers i could not even do a trace on the odbc so we rebooted and i could then do a trace on here.

Does anyone know what the limiitation of odbc is i found this thread but could not see the limitation

When ODBC tries to connect to SQL ---how does it handshake ....does it just keep on sending the commands to SQL and then SQL sits and waits to process these in a queue or by threads.
Do you think increasing threads would be benefical and is there a way i could confirm all used before chaining configrations

Is there a flush command on ODBC ?
Question by:TRACEYMARY
  • 5
  • 3
  • 2

Expert Comment

ID: 18831977
The company I work work use an ERP system that is based on ODBC connection for years and I never heard about a draw back or something else ???

It worth considering though I will investigate the subject

But what kind of error msgs are u getting or how did u know its not handshaking

whats the ERPP solution you are using and what the backend Database


Author Comment

ID: 18833273
I never experienced it either, i have had IIS not talk to SQL due to IIS using all the process threads..and had to recycle IIS.

The way i determined it was we have 3 main citirx servers 4, 6, 7 and all of sudden about 10-15 people could not connect and got disconnected message and network errors....

But we could ping all machines and do net perf test which tested data volume between citrix to server this worked.

Users were stuck in the the ERP system and they would press say add po or update something so i went to the citrix machine they were on to put a trace on the ODBC and i couldn't i could not write to a trace file by doing ODBC tracing it just did not allow me...the administrator also tried nothing.
So we rebooted the citrix 7 machine and every one was ok.

During the time i had local users connected to the ERP direct to SQL who were happily plugging away.

We use SQL.

If it was a blipe in network all users would have been out.
I thinking perhaps ODBC is sending to TCP/IP and then TCP/IP can't communicate to SQL  but im not fully knowledge of this...or perhaps its windows timeout?

Kind of in the dark right now.

This has happened a few times in the past and after rebooting Citirx eveything is fine but now its time to figure out as it causes a lot of time to reboot

ERP is costpoint

Author Comment

ID: 18833430
I have found this too

Perhaps The SQL has 5 WinsockListenBacklog setting of 5. and there are more than 5 coming in
Not sure how i add this as a error log to SQL to confirm. 17832
Also i probrably could do the netstat - n to see if i have 400 in wait stat.

If you run netstat -n and you see that close to 4000 connections to the IP address of the target computer that is running SQL Server are in a TIME_WAIT state, you can both increase the default MaxUserPort setting and reduce the TcpTimedWaitDelay setting so that you do not run out of client anonymous ports. For example, you can set the MaxUserPort setting to 20000 and set the TcpTimedWaitDelay setting to 30. A lower TcpTimedWaitDelay setting means that the sockets wait in the TIME_WAIT state for less time. A higher MaxUserPort setting means that you can have more sockets in the TIME_WAIT state.

If i have 400 waiting and sql only deals with 5 at a time...i don't understand the logic here.

LVL 30

Accepted Solution

nmcdermaid earned 500 total points
ID: 18833687
I don't think it has anything to do with  threads, processes, timeouts etc. You said that the issue was on one of the Citrix servers so it has to do with that Citrix Server.

I think the best thing to do is wait and monitor. See if the problem only manifests itself on a single Citrix server at a time, and see if it is always the same one.

Don't complicate yourself with threads and stuff. Someone probably tripped over the network cable to that box.

Next time it drops on that Citrix server you should PING the SQL Server and also TELNET to verify connectivity. If that fails the next thing to do is try pinging some of your file servers from there.

ALso what was the exact error that ODBC gave you when you tried to trace?

Author Comment

ID: 18835261
I got no error could not even save the ODBC would not write to the ODBC log, but after that it was ok when rebooted.

Probrably i stay in this area for a while and see whats going on first.  Its not the first time its happened its happening a lot  once every other week or so which is strange.
Perhaps its the application not closing connections when they use sql
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery


Author Comment

ID: 18840395
Today was just as crazy timeouts sql errors disconnects from ODBC.
I have just found out network people change the DNS to a new one today ...
Perhaps that has something to do with it so i shutdown sql
LVL 30

Expert Comment

ID: 18842315
Is it isolated to one Citrix Server?

Messing with the DNS will certainly cause that issue. DNS maps server names to IP adresses.

Expert Comment

ID: 18842692
Please also check the <B>hosts</B>  under C:\WINDOWS\system32\drivers\etc

Expert Comment

ID: 18842698
Please also check the hosts file under C:\WINDOWS\system32\drivers\etc

Author Comment

ID: 18846059
Check all on the SQL today it seem better i think that must have been the problem

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 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

21 Experts available now in Live!

Get 1:1 Help Now