An existing connection was forcibly closed by the remote host

Hi,

I am using ASP.NET 2.0 application to fetch data from a SQL server 2005 using stroed proc.

Stored proc is also using one table from Linked Server (SQL 2000). Linked Server is configured to use login's current security context.

I am getting the following error message

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 6/15/2006 4:26:30 PM
Event time (UTC): 6/15/2006 8:26:30 PM
Event ID: 988964e6fa5249e38b1bc6c9a5ecd1e4
Event sequence: 850
Event occurrence: 7
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/MyApp-1-127948732130920720
Trust level: Full
Application Virtual Path: /MyApp
Application Path: C:\Inetpub\wwwroot\MyApp\
Machine name: MyServer
Process information:
Process ID: 352
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: SqlException
Exception message: TCP Provider: An existing connection was forcibly closed by the remote host.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Request information:
Request URL: http://MyServer/MyApp/Page1.aspx
Request path: /MyServer/MyApp/Page1.aspx
User host address: XX.XX.XXX.XX
User: DomainName/UserName

Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

Please Guide

Thanks

Is it intermittent or consistent?

1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

This looks like a consistent issue. It normally means that you need to configuration IIS account to access SQL Server. You can take a look at this link, http://support.microsoft.com/kb/247931/en-us.

2. TCP Provider: An existing connection was forcibly closed by the remote host.

This normally is an intermittent issue. It happens when you enable connection pooling and server closes the connection for reason like connection clean up, restart, network failure, the ASP.NET( sqlclient ) use dead connection from the pool to write data and thus get the failure you see. Re-retry connection should solve the issue.

|||

Hi Nan,

Re: 1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

IIS is configured properly to access data from SQL Server and it is working fine when IIS & SQL are on the same box. But the above message comes only when the stored proc tries to access data from a 2nd SQL Server (linked server which is not on the same box as IIS). Somehow it is not passing the credential to the 2nd SQL Server. 2nd SQL server is linked using login's current security context. Also ASP.NET 2 web.config file says <identity impersonate="true" />. I am using NT domain right now. Is this issue of "Double hop"?

Re: 2. TCP Provider: An existing connection was forcibly closed by the remote host.

Is connection pooling is by default? Can we change so that it should not use it?

Thanks

Shafiq

An Amazing Errorrrrrrrrrrrrrrrr

Hi All Friends

i have an amazing error in my project

i wrote a project in ASP.NET 2

a part of this project change a value in a row in a table in sql 2005 to another value

i used it in my computer (With Local host and my own iis) and it works properly and without any problem

but when i upload it in internet it have this error:

"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. "

can anyone help me?

i think the error is from my table in sql 2005 but i dont know what is wrong in it

thanks for your cares friends and im waiting for your usefull answers

It means that someone changed the row between the time you initially retrieved it, and the time you did the update. Or you are pointing at the wrong database for the update.

|||

No it cannot happen

because this form has a password that no one have it and no one know about this form

it cant be

but it's a Notice here :When i delete my table and recreate it again it work probably but sometimes it has this error again

|||

Well, if you're absolutely sure that there is no way that the row could have been changed--there could be other forms or other apps that are updating the table--then there is something wrong in the concurrency code. Did a wizard generate this for you, or did you write some custom code?

Don

Am I missing something when trying to connect via ASP.NET 2.0?

I am at my wit's end trying to set up a simple test to retrieve some records from with ASP.NET and C#. I'm using the Express Edition on my development laptop. Whenever I try to run my application, I get the following error:

ERROR: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have researched this problem, and done everything I can find online to fix it but no avail. I have gone to SQL Server Configuration Manager and enabled Named Pipes and TCP/IP. I have stopped and restarted the server. Local and Remote connections (Both TCP/IP and Named Pipes) are enabled in the Surface Area Configuration. I added it to the Exceptions list of the Windows firewall. And yet nothing works. I can connect to it fine with Management Studio Express, but I am totally unable to connect to the database from within my code. I've spent probably about two weeks wracking my brain trying to figure this out; does anybody know what might be causing the problem and what could fix it?what does your connection string look like.|||"Server=ORPHEUS;Database=TestDB;User ID=sa;Password=mypass;Trusted_connection=False"

I've also tried specifyinig the instance of SQL Server (e.g. Server=ORPHEUS\SQLExpress) but that does not work either.
|||

If you're trying to connect to an instance name, then i'm pretty sure you have to specify it in your connection string.

Have you tried using the examples in the docs, like this one? http://msdn2.microsoft.com/en-us/library/ms247257.aspx

|||Well what do you know... turns out I wasn't specifying it in the right format.. I should have had it was "Database=ORPHEUS\\SQLEXPRESS".

Thanks a ton! Sometimes its the most obvious things that you end up missing...

Am completely lost

Hello all let me first start out by saying I suck at SQL. I can do quite a bit with ASP.NET but SQL Server is on area that has haunted me and I have finally decided to bite the bullet and figure this behamouth out.

Well here is my first problem, which is the biggest reason I can't seem to understand SQL Server or dataases in general.

To have a realtional database you set up tables with foriegn keys and primary keys referenceing the id's like product table has a product name and one of the columns is a user_id displaying the id number of the user in the user table. From what I can tell you are supposed to set up the user_id in the products table a foriegn key to the Primary Key in the user table. I may be totally wrong on this though.

Now my biggest question is how do you retrive this information the proper way to get it ready to be displayed in a gridveiw or a datalist?

I have been reading SQL Server 2005 for developers and reading online tutorials and it seems like they say you need to set up a relationship and because of the realtionship you don't need to do a join, but I never hear how to use the relationship at all.

Please someone help I am so totally lost that i feel like i will never understand. I have spent the last week trying to figure this out and I guess I am just googleing this wrong completely or i am incompentent one of the two.

Thank you.

try this

select a.column1, a.column2 ... b.column1, b.column2

from user a inner join product b on a.userID = b.userID

Hope this help.

Alternative to T-SQL OpenXML

Hi,

Our application makes extensive use of XML to move data between ASP.NET and the backend SQL Server 2000 database. Lately we have found that the OpenXML statement is becoming a major performance bottleneck.

Could someone suggest a more efficient way of 1) passing data between the web and data tier or 2) alternatives to OpenXml/sp_prepare_document.

Regards
Arjun

Dear Arjun

First, how does your OpenXML code look like? There are some best practises that can have a large performance impact.

Secondly, are you using Service pack 4? That also has some sometimes substatial performance improvements.

Otherwise, I would recommend at looking at the SQLXML 3.0 XML Bulkload object, or a ADO.Net DataSet based approach.

Best regards

Michael

|||

Dear Michael,

Here's a code snippet. This is contained in a stored procedure which accepts the XML data as input. Any suggestions to tune this.

exec sp_xml_preparedocument @.outputDoc OUTPUT, @.strAddXML

Insert into @.Table(txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev )
Select txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev FROM OPENXML(@.outputDoc, 'DocXML/intRow',1)
with
(
txtCategory varchar(50),
intFinYearEnd int,
fltTotalRevenue float,
fltCost float,
fltCostPer float,
fltCommPer float,
fltAprRev float,
fltMayRev float,
fltJunRev float,
fltJulRev float,
fltAugRev float,
fltSepRev float,
fltOctRev float,
fltNovRev float,
fltDecRev float,
fltJanRev float,
fltFebRev float,
fltMarRev float
)

Regards
Arjun

|||

Hi Arjun

Sorry for the late reply but I was travelling.

The OpenXML statement that you show looks ok to me. Have you done some performance testing to see where the time is being spent? During parsing of the sp_xml_preparedocument or the actual OpenXML call?

How large is the XML that you are passing in? Note that SQL Server 2000 versions prior to SP4 used a parser that had some non-linear scaling problems during parsing of larger documents (which has been fixed with SP4). What service pack version are you using?

Best regards

Michael

Alternative to T-SQL OpenXML

Hi,

Our application makes extensive use of XML to move data between ASP.NET and the backend SQL Server 2000 database. Lately we have found that the OpenXML statement is becoming a major performance bottleneck.

Could someone suggest a more efficient way of 1) passing data between the web and data tier or 2) alternatives to OpenXml/sp_prepare_document.

Regards
Arjun

Dear Arjun

First, how does your OpenXML code look like? There are some best practises that can have a large performance impact.

Secondly, are you using Service pack 4? That also has some sometimes substatial performance improvements.

Otherwise, I would recommend at looking at the SQLXML 3.0 XML Bulkload object, or a ADO.Net DataSet based approach.

Best regards

Michael

|||

Dear Michael,

Here's a code snippet. This is contained in a stored procedure which accepts the XML data as input. Any suggestions to tune this.

exec sp_xml_preparedocument @.outputDoc OUTPUT, @.strAddXML

Insert into @.Table(txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev )
Select txtCategory,intFinYearEnd,fltTotalRevenue,fltCost,fltCostPer,fltCommPer,fltAprRev,fltMayRev , fltJunRev, fltJulRev,fltAugRev ,fltSepRev ,fltOctRev ,
fltNovRev ,fltDecRev ,fltJanRev ,fltFebRev ,fltMarRev FROM OPENXML(@.outputDoc, 'DocXML/intRow',1)
with
(
txtCategory varchar(50),
intFinYearEnd int,
fltTotalRevenue float,
fltCost float,
fltCostPer float,
fltCommPer float,
fltAprRev float,
fltMayRev float,
fltJunRev float,
fltJulRev float,
fltAugRev float,
fltSepRev float,
fltOctRev float,
fltNovRev float,
fltDecRev float,
fltJanRev float,
fltFebRev float,
fltMarRev float
)

Regards
Arjun

|||

Hi Arjun

Sorry for the late reply but I was travelling.

The OpenXML statement that you show looks ok to me. Have you done some performance testing to see where the time is being spent? During parsing of the sp_xml_preparedocument or the actual OpenXML call?

How large is the XML that you are passing in? Note that SQL Server 2000 versions prior to SP4 used a parser that had some non-linear scaling problems during parsing of larger documents (which has been fixed with SP4). What service pack version are you using?

Best regards

Michael

Alternative Color

Does anyone know how to do alternative color row dynamically on reports? I
can do that in ASP.NET DataGrid.. but can't seem to find a property on
reporting server..Hi Zean,
You can alternate the color row of your tables putting the following
expression in the BackgroundColor property of the table or part of the table
you want :
=iif(RowNumber(Nothing) Mod 2 , "#F9F9F9","#EBEBEB" )
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:BfSdnRTl74eizsPenZ2dnUVZ_tCdnZ2d@.rogers.com...
> Does anyone know how to do alternative color row dynamically on reports?
I
> can do that in ASP.NET DataGrid.. but can't seem to find a property on
> reporting server..
>|||And in case, you don't have a simple table report item, but a matrix report
item you should check the following blog article:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Maria Isabel Henao" <dev@.hotmail.com> wrote in message
news:eVUEN%23Y2FHA.2444@.TK2MSFTNGP10.phx.gbl...
> Hi Zean,
> You can alternate the color row of your tables putting the following
> expression in the BackgroundColor property of the table or part of the
> table
> you want :
>
> =iif(RowNumber(Nothing) Mod 2 , "#F9F9F9","#EBEBEB" )
> "Zean Smith" <nospam@.nospamaaamail.com> wrote in message
> news:BfSdnRTl74eizsPenZ2dnUVZ_tCdnZ2d@.rogers.com...
>> Does anyone know how to do alternative color row dynamically on reports?
> I
>> can do that in ASP.NET DataGrid.. but can't seem to find a property on
>> reporting server..
>>
>