[ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()) er

[c676228]

Hi all,

It seems that very frequently that we receive an error message on our
ecommerce site. See below. But when I click refresh button in the browser,
the page will be displayed correctly. I just don't know if it is database
connection problem or network problem or it is just memory corruption
problem. Don't have any clue where to start to correct the problem.

* COM Error #: -2147467259 (80004005)
* Category: Microsoft OLE DB Provider for ODBC Drivers
* File: ///receipt.asp
* Line, Column:49, -1
* Description: [Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionWrite (send()).

line 48: sQry = "select * from tablename where order_id='" &
Request("order_id") & "'"
Line 49: set txn_info = conn.Execute(sQry)

This kind of error always occur at line conn.Execute(sQry) from different
programs. while conn is database connection and sQry is a very simple select
statement.
Does that mean we have database connection problem?
Its'quite a while, I have been searched the resolution. But never got any
idea how to avoid this annoying thing.
I guess our customer is quite upset with us.
Betty

[William Vaughn [MVP]]

Well, it does not look like a "Connection" issue but since you're still
working with a fairly old program using outdated technology (ASP and ODBC
instead of ADO.NET and SqlClient or at least SNAC) I can see why you would
have problems trying to debug it.
If it were me, I would add an exception handler to trap the error and retry
the operation. Next, I would look at the SQL Server Profiler to see if the
operation made it to the server. I would also see if the Connection pool was
full. If so, you might just be overloading the server (IIS or SQL Server).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"c676228" <be...@newsgroup.nospam> wrote in message
news:354F36E7-C5C7-4071...@microsoft.com...

[c676228]

Hi William,
Thank you so much for your instruction. I believe this is a good lead.
Speaking of trapping the error, I am not experienced at all.
will this a way to do it?
<%If Err.Number <> 0 Then
'do this again
set txn_info = conn.Execute(sQry)
<%End If%>
How can I check if the Connection pool was full or not. I always think maybe
we have many connection there and how can I check in SQL Server Profiler if
the operation made ti to the server or not.

if you can give me initial instruction or direct to the right resouces,
articles and anything might be helpful. I will be thrilled.

Sincerely

--
Betty

[William Vaughn [MVP]]

Ah, I haven't written about this dead technology for a decade or more.
Sorry, since the new techniques make these problems trivially simple to
correct, it's hard to say how to fix them. I would look in some of the very
early ASP books for exception handling. Not, I don't know that you can
monitor the Connection Pool very easily without .NET performance counters to
help.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"c676228" <be...@newsgroup.nospam> wrote in message

news:305E4E3D-49FA-41B4...@microsoft.com...

[Charles Wang[MSFT]]

Hi Betty,
I understand that you encountered the ODBC error message on your ecommerce
site intermittently. The error always occurred at the line
conn.Execute(sQry) from different programs.
If I have misunderstood, please let me know.

From my research, this issue may happen if you are using TCP/IP protocol to
connect to your SQL Server instance and if there are intermittent network
disconnections in your LAN. When you have established the connection with
TCP/IP protocol to your SQL Server instance by calling conn.Open, something
happens and causes a momentary loss of connectivity to your SQL Server
instance (You can use SQL Profiler on your SQL Server side to monitor your
SQL Server instance and see if there is a connection reset when this issue
happens), then the call conn.Execute would fail. You may try configuring
your ODBC source using Named Pipes protocol for your client connections. On
your Web server, open Administrative Tools from Control Panel, double click
Data Sources (ODBC), select your DSN and click Configure..., click Next,
click Client Configuration..., check Named Pipes and then click OK. Please
also ensure that Named Pipes protocol is enabled at your SQL Server side.

Regarding ODBC Connection Pooling, you may use Performance Monitor to
monitor it on your Web server side. You may refer to this KB article:
How to Enable ODBC Connection Pooling Performance Counters
http://support.microsoft.com/kb/216950/EN-US/

Also if this issue persists, I recommend that you try using ADO to connect
to your SQL Server to see if it helps. For example:
<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim strConnection, conn, rs, strSQL

strConnection = "Provider=sqloledb;Data Source=SQLServerName;" & _
"Initial Catalog=DBaseName;User Id=sa;Password=password;"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM myTable"
rs.open strSQL, conn, 3,3

rs.MoveFirst
WHILE NOT rs.EOF
Response.Write(rs("myField") & "<br/>")
rs.MoveNext
WEND

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing
%>

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msd...@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

[c676228]

Charles,
thanks for your research and I will first take the suggestion of configure
the ODBC source using named pipe, but I don't know how to enable named pipes
protocol on our sql server?
If it is not working I am going to use ado to connect to sql server. The
only problem is I probably need to change code in different programs.

This is our production server. I want to make sure that I need to change
both ODBC data source using named pipes protocal and the add named pipes
protocol to our sql server and my program is just working as usual, right?

Sincerely

--
Betty

[c676228]

Hi Charles,
I think I got it. I went to configuration tool-->surface area configuration-->
Database Engine -->remote connection -->on the right-hand side local and
remote connection. should I just check named pipes only or both TCP/IP and
named pipes

thanks
--
Betty

[Charles Wang[MSFT]]

Hi Betty,
That is a server side setting. Of course, for this issue, you can only
enable Named Pipes protocol to see if it helps.
Actually at your client side, if you have set named pipes protocol for your
DSN in ODBC Administrator from Control Panel, it may not be necessary to
configure the remote connection in Surface Area Configuration (SAC).

Please feel free to let me know if you have any further questions or
concerns. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================

Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msd...@microsoft.com.
=========================================================