Solved

odbc not talk to SQL

Posted on 2007-03-30
10
407 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 70
Abstract Express Replacement Software 12 27
Live Storage Vmotion VMs with shared VMDK 10 58
MS SQL Server COnditional Where statement 7 59
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

777 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