odbc not talk to SQL

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
http://msdn2.microsoft.com/en-us/library/ms810829.aspx

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 ?
Thanks
LVL 7
TRACEYMARYAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MOA81Commented:
Dear TRACEYMARY
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

thanks
0
TRACEYMARYAuthor Commented:
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
0
TRACEYMARYAuthor Commented:
I have found this too
http://support.microsoft.com/kb/328476

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.

Phew
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

nmcdermaidCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TRACEYMARYAuthor Commented:
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
0
TRACEYMARYAuthor Commented:
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
0
nmcdermaidCommented:
Is it isolated to one Citrix Server?

Messing with the DNS will certainly cause that issue. DNS maps server names to IP adresses.
0
MOA81Commented:
Please also check the <B>hosts</B>  under C:\WINDOWS\system32\drivers\etc
0
MOA81Commented:
Please also check the hosts file under C:\WINDOWS\system32\drivers\etc
0
TRACEYMARYAuthor Commented:
Check all on the SQL today it seem better i think that must have been the problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.