Solved

odbc not talk to SQL

Posted on 2007-03-30
10
404 Views
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
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
0
Comment
Question by:TRACEYMARY
  • 5
  • 3
  • 2
10 Comments
 
LVL 8

Expert Comment

by:MOA81
ID: 18831977
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
 
LVL 7

Author Comment

by:TRACEYMARY
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
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 18833430
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
 
LVL 30

Accepted Solution

by:
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?
0
 
LVL 7

Author Comment

by:TRACEYMARY
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 7

Author Comment

by:TRACEYMARY
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
0
 
LVL 30

Expert Comment

by:nmcdermaid
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.
0
 
LVL 8

Expert Comment

by:MOA81
ID: 18842692
Please also check the <B>hosts</B>  under C:\WINDOWS\system32\drivers\etc
0
 
LVL 8

Expert Comment

by:MOA81
ID: 18842698
Please also check the hosts file under C:\WINDOWS\system32\drivers\etc
0
 
LVL 7

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

17 Experts available now in Live!

Get 1:1 Help Now