Rick,

We've been running web connect as web service for the past 6-8 months and all has been well. Yesterday, we released an update to our mobile app that makes several more calls to the web service (to get data to enable them to do stuff offline). This was causing problems as our web service primarily brokers SQL connections and then returns data from the SQL calls and we didn't have enough comm servers with all of the connections coming in. We increased to the limit of 16 comm servers (why is 16 the limit?) and that has helped but since we increased the calls to the web service we are getting a SQL error we have never gotten before.

The error is: Connectivity error: [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed [1526:0]

We are using VFP's stringconnect() to connect to SQL (yes we are disconnecting when done). The only thing's I've been able to find on this error is that for 32 bit applications it's possible the odbc driver is hitting the 2GB limit for 32 bit applications and cannot allocate any more memory to establish the connection but I'm not convinced this is what's happening.

So far, either 'restarting' the comm servers through the admin page (unload + load) or recycling the app pool seems to get us back up and going.

Have you ever seen / heard of this error? Do you have any thoughts on what we can do to trap for it if it really is a 2gb limit with a 32bit app to auto recycle the comm server (again, not convinced this is the case as each comm server exe in task manager is never over 12 MB)?

Thanks,

Scott

What are you connecting throught? What data source? FoxPro tables? SQL Server?

If SQL Server (or some other SQL backend) maybe you don't have enough connections available to serve 16 instances? You might just be overrunning your connection pool.

Other than that I don't know - the error is not in Fox but the ODBC connection to the server or the SQL Service directly, but it does look like the issue is related to the driver rather than the actual ODBC backend engine.

+++ Rick ---

Rick,

We are connecting to a SQL server. I know the SQL server(s) we are connecting to can handle the connections.

I can't find anything on your help docs, but is there any way when a certain error is detected to call the 'unload comm servers' from within WebConnect? (i.e. from our admin page the url it hits is: https://mywebsite.com/admin/ReleaseComServers.wc) and the call the load comm servers (https://mywebsite.com/admin/LoadComServers.wc)?

If it's not possible inside of webconnect is there a way to set call it via something like a .bat file? We still haven't been able to track down the error but unloading the comm servers and reloading them fixes it (until it randomly happens again) and I'd like to detect the sql error and reload the comm servers before I have customers calling and saying that we are down.

Thanks,

Scott

You can't unload a COM server from within itself. The only thing you can do is call the HTTP url with wwHttp and force the unload externally.

However I think that's probably a bad idea as this can easily get you into recycling hell. Also you can only really reload the pool as a whole, not individual severs and you have to pass authentication information to the server in order to do it which means the app has to expose that somehow.

I would check SQL connections and see what's actually happening. Perhaps you're not closing your connections or the SQL pool manager has too many pooled instances? If you're not closing your SQL Handles those connections will just stay open and if you have early exits that can be a problem. If you're using wwSql this shouldn't be a problem as it auto-destroys connections when the object goes out of scope, but there are still scnearios where instances can stick around unexpectedly.

Performance Monitor is your friend for these things...

+++ Rick ---

Rick,

Totally agree with tracking down the actual SQL problem. I'm not using wwSQL and I'm about 98% sure we're closing down all SQL connections when they're done but something to check.

Humor me for a minute. What would happen if I did a CLEAR EVENTS in the comm server? I'm assuming the exe / comm server will shut down but does Web Connect monitor how many comm servers are supposed to be running and auto start another comm server for me? What would the implications of issuing a CLEAR EVENTS be?

Thanks,

Scott

You cannot quit a COM server. There is no READ EVENTS loop in the COM server. The COM Server reference is held by the Web Connection .NET module and only the host can release that reference.

Trust me on this - it's not possible I've looked into it. The only things you can do is:

• Make an HTTP call from your app to the release Url
• Use Process.Kill() to kill the process (not a good idea - this will eventually corrupt the COM pool)

First thing you should do is hook up PerfMon and check to see how many SQL Connections you have open. If you have more than the number of servers, you have a leak and that's most likely the cause of your problem. Could be something else, but this is an easy thing to check.

+++ Rick ---

FYI - You can get a list of connections to the Sql Server database by running this query in Sql Server Management Studio:

Select loginame, login_time, last_batch, status, open_tran, hostname, program_name, hostprocess, nt_domain, nt_username, net_address, net_library, lastwaittype
  from sys.sysprocesses
  where loginame not in ('sa', '<<PASSWORD>>')
  order by hostname


SELECT 
    DB_NAME(dbid) as DBName, 
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame;

Rick,

I understand. Thanks.

Matt,

Thanks for the SQL statement. I'll give that a try.

I'm not convinced it's too many connections to any one SQL db. We broker connections to 50+ SQL DBs via our webconnect web service and once I start getting the SQL ODBC Error, I can't connect to any database. It effectively takes us down for all of our customers until I reset the comm servers and then all connections to all databases start working again. Any thoughts?

Thanks,

Scott

Sorry, I only use West Wind Client Tools library to build desktop VFP apps against Sql Server, so I have no experience with the web app side of Web Connection.