Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


odbc not talk to SQL

Posted on 2007-03-30
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

LVL 30

Accepted Solution

nmcdermaid earned 2000 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

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

688 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