ASP.NET application with intermittent returns of 503 errors with the
following message.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. SQL Server does not exist or access denied.
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Servers running Windows 2003 SQL 2000.
This has been a problem for a long time (less than .3% of the time). No
code changes have been made. The only changes was Windows SP1 and post
SP patches on all servers. The patching may have made this issue more
pronounced.
Any suggestions from the experts?
Thanks,
RoxanneWhen they are intermittent they are difficult to track down.
Often those related to network connectivity issues. Have you
checked the event logs on the IIS box - particularly looking
for any network related issues?
-Sue
On 2 Nov 2005 10:32:52 -0800, roxy636@.yahoo.com wrote:
>ASP.NET application with intermittent returns of 503 errors with the
>following message.
>System.Data.SqlClient.SqlException: SQL Server does not exist or access
>denied. SQL Server does not exist or access denied.
>An unhandled exception was generated during the execution of the
>current web request. Information regarding the origin and location of
>the exception can be identified using the exception stack trace below.
>Servers running Windows 2003 SQL 2000.
>This has been a problem for a long time (less than .3% of the time). No
>code changes have been made. The only changes was Windows SP1 and post
>SP patches on all servers. The patching may have made this issue more
>pronounced.
>Any suggestions from the experts?
>Thanks,
>Roxanne|||Search no further...
http://support.microsoft.com/defaul...kb;en-us;328476
Note, this answer took forever to find so I'm posting everywhere...|||So was your solution to enable connection pooling? Or was it simply a
tcp/ip configuration issue. Unless you explicitly disable connection
pooling, then connections should be pooled, were you disabling pooling?
Thanks for any info, we are seeing the exact same symptoms at a client
of ours.|||Connection pooling was disabled for security reasons, so you need to
add registry keys specified in article here:
path:
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\services\Tcpip\Parameters
type: DWORD
name: TcpTimedWaitDelay
value: 30 (decimal)
type: DWORD
name: MaxUserPort
value: 10000 (decimal)
Ultimately is connection ooliing is enabled this shouldn't happen, but
try it anyway. Check netsats from command prompt...if you see around
4000 connections in TIME_WAIT, then that is the problem.|||Thanks, since connection pooling is enabled and this is occuring even
soon after a reboot under low load. I do not suspect we are creating
too many connections to sql, but I do suspect some network
communication problems. I am going to further diagnose thier network
configuration relative to the front and back planes of the web server.
But I will not overlook this as a possibility, so I will also get a
netstat -n Thanks so much for the info..|||Turned out to be Bandwidth Throttling, disabling that option for the
Application Pool alleviated the HTTP 503 errors.
Showing posts with label system. Show all posts
Showing posts with label system. Show all posts
Friday, March 30, 2012
Intermittent SQL Server does not exist or access denied.
ASP.NET application with intermittent returns of 503 errors with the
following message.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. SQL Server does not exist or access denied.
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Servers running Windows 2003 SQL 2000.
This has been a problem for a long time (less than .3% of the time). No
code changes have been made. The only changes was Windows SP1 and post
SP patches on all servers. The patching may have made this issue more
pronounced.
Any suggestions from the experts?
Thanks,
Roxanne
When they are intermittent they are difficult to track down.
Often those related to network connectivity issues. Have you
checked the event logs on the IIS box - particularly looking
for any network related issues?
-Sue
On 2 Nov 2005 10:32:52 -0800, roxy636@.yahoo.com wrote:
>ASP.NET application with intermittent returns of 503 errors with the
>following message.
>System.Data.SqlClient.SqlException: SQL Server does not exist or access
>denied. SQL Server does not exist or access denied.
>An unhandled exception was generated during the execution of the
>current web request. Information regarding the origin and location of
>the exception can be identified using the exception stack trace below.
>Servers running Windows 2003 SQL 2000.
>This has been a problem for a long time (less than .3% of the time). No
>code changes have been made. The only changes was Windows SP1 and post
>SP patches on all servers. The patching may have made this issue more
>pronounced.
>Any suggestions from the experts?
>Thanks,
>Roxanne
|||Search no further...
http://support.microsoft.com/default...b;en-us;328476
Note, this answer took forever to find so I'm posting everywhere...
|||So was your solution to enable connection pooling? Or was it simply a
tcp/ip configuration issue. Unless you explicitly disable connection
pooling, then connections should be pooled, were you disabling pooling?
Thanks for any info, we are seeing the exact same symptoms at a client
of ours.
|||Connection pooling was disabled for security reasons, so you need to
add registry keys specified in article here:
path:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\servic es\Tcpip\Parameters
type: DWORD
name: TcpTimedWaitDelay
value: 30 (decimal)
type: DWORD
name: MaxUserPort
value: 10000 (decimal)
Ultimately is connection ooliing is enabled this shouldn't happen, but
try it anyway. Check netsats from command prompt...if you see around
4000 connections in TIME_WAIT, then that is the problem.
|||Thanks, since connection pooling is enabled and this is occuring even
soon after a reboot under low load. I do not suspect we are creating
too many connections to sql, but I do suspect some network
communication problems. I am going to further diagnose thier network
configuration relative to the front and back planes of the web server.
But I will not overlook this as a possibility, so I will also get a
netstat -n Thanks so much for the info..
|||Turned out to be Bandwidth Throttling, disabling that option for the
Application Pool alleviated the HTTP 503 errors.
following message.
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied. SQL Server does not exist or access denied.
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Servers running Windows 2003 SQL 2000.
This has been a problem for a long time (less than .3% of the time). No
code changes have been made. The only changes was Windows SP1 and post
SP patches on all servers. The patching may have made this issue more
pronounced.
Any suggestions from the experts?
Thanks,
Roxanne
When they are intermittent they are difficult to track down.
Often those related to network connectivity issues. Have you
checked the event logs on the IIS box - particularly looking
for any network related issues?
-Sue
On 2 Nov 2005 10:32:52 -0800, roxy636@.yahoo.com wrote:
>ASP.NET application with intermittent returns of 503 errors with the
>following message.
>System.Data.SqlClient.SqlException: SQL Server does not exist or access
>denied. SQL Server does not exist or access denied.
>An unhandled exception was generated during the execution of the
>current web request. Information regarding the origin and location of
>the exception can be identified using the exception stack trace below.
>Servers running Windows 2003 SQL 2000.
>This has been a problem for a long time (less than .3% of the time). No
>code changes have been made. The only changes was Windows SP1 and post
>SP patches on all servers. The patching may have made this issue more
>pronounced.
>Any suggestions from the experts?
>Thanks,
>Roxanne
|||Search no further...
http://support.microsoft.com/default...b;en-us;328476
Note, this answer took forever to find so I'm posting everywhere...
|||So was your solution to enable connection pooling? Or was it simply a
tcp/ip configuration issue. Unless you explicitly disable connection
pooling, then connections should be pooled, were you disabling pooling?
Thanks for any info, we are seeing the exact same symptoms at a client
of ours.
|||Connection pooling was disabled for security reasons, so you need to
add registry keys specified in article here:
path:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\servic es\Tcpip\Parameters
type: DWORD
name: TcpTimedWaitDelay
value: 30 (decimal)
type: DWORD
name: MaxUserPort
value: 10000 (decimal)
Ultimately is connection ooliing is enabled this shouldn't happen, but
try it anyway. Check netsats from command prompt...if you see around
4000 connections in TIME_WAIT, then that is the problem.
|||Thanks, since connection pooling is enabled and this is occuring even
soon after a reboot under low load. I do not suspect we are creating
too many connections to sql, but I do suspect some network
communication problems. I am going to further diagnose thier network
configuration relative to the front and back planes of the web server.
But I will not overlook this as a possibility, so I will also get a
netstat -n Thanks so much for the info..
|||Turned out to be Bandwidth Throttling, disabling that option for the
Application Pool alleviated the HTTP 503 errors.
Monday, March 26, 2012
Intermittent 401: Not Authorized
The first time I try to access a RS report after an IISreset, I get a
"System.Net.WebException: The request failed with HTTP status 401:
Unauthorized." error. If I try to access it again, I get in without a
problem.
In addition to the "first attempt after the iisreset", which happens
consistently, we also intermittently experience the same error, infrequently.
Any idea what is going on here? Caching of Credentials?
Michael DorfmanHello Michael,
To understand the issue better, I'd like to know the following information:
1. Does the issue only occur with a specific RS report or any RS report
such as sample reports including in RS installation?
2. Do you have anonymous access enabled on Report Manager or Report Server
virtual directories?
Also, this issue might be related to a known issue in .Net framework 1.1
sp1, I suggest that you contact CSS to get 887563 installed hotfix 887563
to see if it helps:
884537 FIX: You may receive a "System.Net.WebException" error message when
you
http://support.microsoft.com/?id=884537
887563 Availability of the .NET Framework 1.1 Post-Service Pack 1 rollup
http://support.microsoft.com/?id=887563
Also, please search if there are multiple copies of security.dll on your
SQL Reporting Services machine. If there are security.dll other than
%windir%\system32 folder, please tempoarily rename them to test the
situation.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Intermittent 401: Not Authorized
| thread-index: AcVTuOW2QlsDZxZ1SwW4LqLFvzorxw==| X-WBNR-Posting-Host: 62.97.217.178
| From: =?Utf-8?B?TWljaGFlbCBEb3JmbWFu?= <mercatus2004@.online.nospam>
| Subject: Intermittent 401: Not Authorized
| Date: Sun, 8 May 2005 03:30:04 -0700
| Lines: 11
| Message-ID: <E81A662F-4F37-45EB-8174-9C17787D5295@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43126
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| The first time I try to access a RS report after an IISreset, I get a
| "System.Net.WebException: The request failed with HTTP status 401:
| Unauthorized." error. If I try to access it again, I get in without a
| problem.
|
| In addition to the "first attempt after the iisreset", which happens
| consistently, we also intermittently experience the same error,
infrequently.
|
| Any idea what is going on here? Caching of Credentials?
|
| Michael Dorfman
||||Are you using 2003 server and applied SP1? If so, I did the following and
solved my same problem:
"create a DWORD entry called DisableLoopbackCheck to
HKLM\System\CurrentControlSet\Control\Lsa. Set this key to a value of 1"
This is an issue which occures after appling SP1 to 2003 server.
I hope, this helps.
Barbaros Saglamtimur
MCDBA, MCAD
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:wrpXrMFVFHA.2476@.TK2MSFTNGXA01.phx.gbl...
> Hello Michael,
> To understand the issue better, I'd like to know the following
> information:
> 1. Does the issue only occur with a specific RS report or any RS report
> such as sample reports including in RS installation?
> 2. Do you have anonymous access enabled on Report Manager or Report Server
> virtual directories?
> Also, this issue might be related to a known issue in .Net framework 1.1
> sp1, I suggest that you contact CSS to get 887563 installed hotfix 887563
> to see if it helps:
> 884537 FIX: You may receive a "System.Net.WebException" error message when
> you
> http://support.microsoft.com/?id=884537
> 887563 Availability of the .NET Framework 1.1 Post-Service Pack 1 rollup
> http://support.microsoft.com/?id=887563
> Also, please search if there are multiple copies of security.dll on your
> SQL Reporting Services machine. If there are security.dll other than
> %windir%\system32 folder, please tempoarily rename them to test the
> situation.
>
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | Thread-Topic: Intermittent 401: Not Authorized
> | thread-index: AcVTuOW2QlsDZxZ1SwW4LqLFvzorxw==> | X-WBNR-Posting-Host: 62.97.217.178
> | From: =?Utf-8?B?TWljaGFlbCBEb3JmbWFu?= <mercatus2004@.online.nospam>
> | Subject: Intermittent 401: Not Authorized
> | Date: Sun, 8 May 2005 03:30:04 -0700
> | Lines: 11
> | Message-ID: <E81A662F-4F37-45EB-8174-9C17787D5295@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:43126
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | The first time I try to access a RS report after an IISreset, I get a
> | "System.Net.WebException: The request failed with HTTP status 401:
> | Unauthorized." error. If I try to access it again, I get in without a
> | problem.
> |
> | In addition to the "first attempt after the iisreset", which happens
> | consistently, we also intermittently experience the same error,
> infrequently.
> |
> | Any idea what is going on here? Caching of Credentials?
> |
> | Michael Dorfman
> |
>|||I have 4 different security.dll files...
C:\WINDOWS\system32 - version 5.2.3790.0
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET
Files\reportserver\28ed0657\f12b7b77\assembly\dl2\d011044e\8fb1f7af_6dc3c401
C:\WINDOWS\system32\dllcache
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin - version 1.0.1770.24267
should I keep the Reporting Services version or the Windows version?
"Peter Yang [MSFT]" wrote:
> Hello Michael,
> To understand the issue better, I'd like to know the following information:
> 1. Does the issue only occur with a specific RS report or any RS report
> such as sample reports including in RS installation?
> 2. Do you have anonymous access enabled on Report Manager or Report Server
> virtual directories?
> Also, this issue might be related to a known issue in .Net framework 1.1
> sp1, I suggest that you contact CSS to get 887563 installed hotfix 887563
> to see if it helps:
> 884537 FIX: You may receive a "System.Net.WebException" error message when
> you
> http://support.microsoft.com/?id=884537
> 887563 Availability of the .NET Framework 1.1 Post-Service Pack 1 rollup
> http://support.microsoft.com/?id=887563
> Also, please search if there are multiple copies of security.dll on your
> SQL Reporting Services machine. If there are security.dll other than
> %windir%\system32 folder, please tempoarily rename them to test the
> situation.
>
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Intermittent 401: Not Authorized
> | thread-index: AcVTuOW2QlsDZxZ1SwW4LqLFvzorxw==> | X-WBNR-Posting-Host: 62.97.217.178
> | From: =?Utf-8?B?TWljaGFlbCBEb3JmbWFu?= <mercatus2004@.online.nospam>
> | Subject: Intermittent 401: Not Authorized
> | Date: Sun, 8 May 2005 03:30:04 -0700
> | Lines: 11
> | Message-ID: <E81A662F-4F37-45EB-8174-9C17787D5295@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43126
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | The first time I try to access a RS report after an IISreset, I get a
> | "System.Net.WebException: The request failed with HTTP status 401:
> | Unauthorized." error. If I try to access it again, I get in without a
> | problem.
> |
> | In addition to the "first attempt after the iisreset", which happens
> | consistently, we also intermittently experience the same error,
> infrequently.
> |
> | Any idea what is going on here? Caching of Credentials?
> |
> | Michael Dorfman
> |
>
"System.Net.WebException: The request failed with HTTP status 401:
Unauthorized." error. If I try to access it again, I get in without a
problem.
In addition to the "first attempt after the iisreset", which happens
consistently, we also intermittently experience the same error, infrequently.
Any idea what is going on here? Caching of Credentials?
Michael DorfmanHello Michael,
To understand the issue better, I'd like to know the following information:
1. Does the issue only occur with a specific RS report or any RS report
such as sample reports including in RS installation?
2. Do you have anonymous access enabled on Report Manager or Report Server
virtual directories?
Also, this issue might be related to a known issue in .Net framework 1.1
sp1, I suggest that you contact CSS to get 887563 installed hotfix 887563
to see if it helps:
884537 FIX: You may receive a "System.Net.WebException" error message when
you
http://support.microsoft.com/?id=884537
887563 Availability of the .NET Framework 1.1 Post-Service Pack 1 rollup
http://support.microsoft.com/?id=887563
Also, please search if there are multiple copies of security.dll on your
SQL Reporting Services machine. If there are security.dll other than
%windir%\system32 folder, please tempoarily rename them to test the
situation.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Intermittent 401: Not Authorized
| thread-index: AcVTuOW2QlsDZxZ1SwW4LqLFvzorxw==| X-WBNR-Posting-Host: 62.97.217.178
| From: =?Utf-8?B?TWljaGFlbCBEb3JmbWFu?= <mercatus2004@.online.nospam>
| Subject: Intermittent 401: Not Authorized
| Date: Sun, 8 May 2005 03:30:04 -0700
| Lines: 11
| Message-ID: <E81A662F-4F37-45EB-8174-9C17787D5295@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43126
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| The first time I try to access a RS report after an IISreset, I get a
| "System.Net.WebException: The request failed with HTTP status 401:
| Unauthorized." error. If I try to access it again, I get in without a
| problem.
|
| In addition to the "first attempt after the iisreset", which happens
| consistently, we also intermittently experience the same error,
infrequently.
|
| Any idea what is going on here? Caching of Credentials?
|
| Michael Dorfman
||||Are you using 2003 server and applied SP1? If so, I did the following and
solved my same problem:
"create a DWORD entry called DisableLoopbackCheck to
HKLM\System\CurrentControlSet\Control\Lsa. Set this key to a value of 1"
This is an issue which occures after appling SP1 to 2003 server.
I hope, this helps.
Barbaros Saglamtimur
MCDBA, MCAD
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:wrpXrMFVFHA.2476@.TK2MSFTNGXA01.phx.gbl...
> Hello Michael,
> To understand the issue better, I'd like to know the following
> information:
> 1. Does the issue only occur with a specific RS report or any RS report
> such as sample reports including in RS installation?
> 2. Do you have anonymous access enabled on Report Manager or Report Server
> virtual directories?
> Also, this issue might be related to a known issue in .Net framework 1.1
> sp1, I suggest that you contact CSS to get 887563 installed hotfix 887563
> to see if it helps:
> 884537 FIX: You may receive a "System.Net.WebException" error message when
> you
> http://support.microsoft.com/?id=884537
> 887563 Availability of the .NET Framework 1.1 Post-Service Pack 1 rollup
> http://support.microsoft.com/?id=887563
> Also, please search if there are multiple copies of security.dll on your
> SQL Reporting Services machine. If there are security.dll other than
> %windir%\system32 folder, please tempoarily rename them to test the
> situation.
>
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | Thread-Topic: Intermittent 401: Not Authorized
> | thread-index: AcVTuOW2QlsDZxZ1SwW4LqLFvzorxw==> | X-WBNR-Posting-Host: 62.97.217.178
> | From: =?Utf-8?B?TWljaGFlbCBEb3JmbWFu?= <mercatus2004@.online.nospam>
> | Subject: Intermittent 401: Not Authorized
> | Date: Sun, 8 May 2005 03:30:04 -0700
> | Lines: 11
> | Message-ID: <E81A662F-4F37-45EB-8174-9C17787D5295@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:43126
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | The first time I try to access a RS report after an IISreset, I get a
> | "System.Net.WebException: The request failed with HTTP status 401:
> | Unauthorized." error. If I try to access it again, I get in without a
> | problem.
> |
> | In addition to the "first attempt after the iisreset", which happens
> | consistently, we also intermittently experience the same error,
> infrequently.
> |
> | Any idea what is going on here? Caching of Credentials?
> |
> | Michael Dorfman
> |
>|||I have 4 different security.dll files...
C:\WINDOWS\system32 - version 5.2.3790.0
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET
Files\reportserver\28ed0657\f12b7b77\assembly\dl2\d011044e\8fb1f7af_6dc3c401
C:\WINDOWS\system32\dllcache
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin - version 1.0.1770.24267
should I keep the Reporting Services version or the Windows version?
"Peter Yang [MSFT]" wrote:
> Hello Michael,
> To understand the issue better, I'd like to know the following information:
> 1. Does the issue only occur with a specific RS report or any RS report
> such as sample reports including in RS installation?
> 2. Do you have anonymous access enabled on Report Manager or Report Server
> virtual directories?
> Also, this issue might be related to a known issue in .Net framework 1.1
> sp1, I suggest that you contact CSS to get 887563 installed hotfix 887563
> to see if it helps:
> 884537 FIX: You may receive a "System.Net.WebException" error message when
> you
> http://support.microsoft.com/?id=884537
> 887563 Availability of the .NET Framework 1.1 Post-Service Pack 1 rollup
> http://support.microsoft.com/?id=887563
> Also, please search if there are multiple copies of security.dll on your
> SQL Reporting Services machine. If there are security.dll other than
> %windir%\system32 folder, please tempoarily rename them to test the
> situation.
>
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
> --
> | Thread-Topic: Intermittent 401: Not Authorized
> | thread-index: AcVTuOW2QlsDZxZ1SwW4LqLFvzorxw==> | X-WBNR-Posting-Host: 62.97.217.178
> | From: =?Utf-8?B?TWljaGFlbCBEb3JmbWFu?= <mercatus2004@.online.nospam>
> | Subject: Intermittent 401: Not Authorized
> | Date: Sun, 8 May 2005 03:30:04 -0700
> | Lines: 11
> | Message-ID: <E81A662F-4F37-45EB-8174-9C17787D5295@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43126
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | The first time I try to access a RS report after an IISreset, I get a
> | "System.Net.WebException: The request failed with HTTP status 401:
> | Unauthorized." error. If I try to access it again, I get in without a
> | problem.
> |
> | In addition to the "first attempt after the iisreset", which happens
> | consistently, we also intermittently experience the same error,
> infrequently.
> |
> | Any idea what is going on here? Caching of Credentials?
> |
> | Michael Dorfman
> |
>
Intermittant SQL Connection Issue
We currently have a system in production that raises the following error
intermittantly;
"A transport-level error has occurred when sending the request to the
server. (provider: TCP Provider, error: 0 - An existing connection was
forcibly closed by the remote host.)"
The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
SP1.
What could be causing this error, and more importantly how do we resolve it?
Regards
Andrew Teece
Technical Architect, eNateHi Andrew,
Per my understanding, the error was intermittantly thrown by your system in
production environment when it was trying to perform a database operation.
If I have misunderstood, please let me know.
From my research, the issue might be caused by various reasons, such as
broken connections in connection pool, hardware failure, network problems
etc. Also, it might be the known issue which is described in this article:
4.1.2 Connections May Be Forcibly Closed When Running on Windows Server
2003 SP1
http://support.microsoft.com/defaul...kb;EN-US;910228
As it mentioned, to resolve this issue, you may use the regedit.exe utility
to add a new DWORD value named SynAttackProtect to the registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\Tcpip\Parameters\with
value data of 00000000.
If that is not the problem, I recommend that you check if there is a
connection pool in your system and if the connections in the pool have a
timeout value. If so, please set the active connections in the pool with
infinite timeout value.
Since your environment is complex and the issue occurs intermittantly in
production environment, if this issue persists, immediately and effectively
I recommend that you contact Microsoft Customer Support Services ( CSS )
via telephone so that a dedicated Support Professional can assist you
recover the server in a more efficient manner. Please be advised that
contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> We currently have a system in production that raises the following error
> intermittantly;
> "A transport-level error has occurred when sending the request to the
> server. (provider: TCP Provider, error: 0 - An existing connection was
> forcibly closed by the remote host.)"
> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
> SP1.
> What could be causing this error, and more importantly how do we resolve i
t?
> Regards
> Andrew Teece
> Technical Architect, eNate
Check the following
1. Router log for any errors
Routers may support IP Virtual Re-Assembly on all interfaces. It is
turned on by default may cause numerous errors .Disable the option
on all interfaces of routers and try
2. Synattack Registry setting
3. Anti Virus software may be closing the connection
Report back after you solve the problem . It may help others
Thanks
Srinivas|||Thanks for the ideas M A Srinivas
At the moment, I think the most likly is the "2. Synattack Registry setting"
that was also mentioned by Charles Wang.
I think this mostly because we are running on a cluster, and the application
raising the error is running on the same pair of cluster servers. As the
application is running concurrently on both, and is raising the error from
both I doubt a network issue because on at least one of the servers my
understanding is that the connection will go through the tcp/ip layer, but
not onto the physical network.
Obviously I will post back any findings.
Regards
Andrew Teece
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1179827956.238191.166180@.u36g2000prd.googlegroups.com...
> On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> Check the following
> 1. Router log for any errors
> Routers may support IP Virtual Re-Assembly on all interfaces. It is
> turned on by default may cause numerous errors .Disable the option
> on all interfaces of routers and try
> 2. Synattack Registry setting
> 3. Anti Virus software may be closing the connection
> Report back after you solve the problem . It may help others
> Thanks
> Srinivas
>
>
>
intermittantly;
"A transport-level error has occurred when sending the request to the
server. (provider: TCP Provider, error: 0 - An existing connection was
forcibly closed by the remote host.)"
The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
SP1.
What could be causing this error, and more importantly how do we resolve it?
Regards
Andrew Teece
Technical Architect, eNateHi Andrew,
Per my understanding, the error was intermittantly thrown by your system in
production environment when it was trying to perform a database operation.
If I have misunderstood, please let me know.
From my research, the issue might be caused by various reasons, such as
broken connections in connection pool, hardware failure, network problems
etc. Also, it might be the known issue which is described in this article:
4.1.2 Connections May Be Forcibly Closed When Running on Windows Server
2003 SP1
http://support.microsoft.com/defaul...kb;EN-US;910228
As it mentioned, to resolve this issue, you may use the regedit.exe utility
to add a new DWORD value named SynAttackProtect to the registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Services\Tcpip\Parameters\with
value data of 00000000.
If that is not the problem, I recommend that you check if there is a
connection pool in your system and if the connections in the pool have a
timeout value. If so, please set the active connections in the pool with
infinite timeout value.
Since your environment is complex and the issue occurs intermittantly in
production environment, if this issue persists, immediately and effectively
I recommend that you contact Microsoft Customer Support Services ( CSS )
via telephone so that a dedicated Support Professional can assist you
recover the server in a more efficient manner. Please be advised that
contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> We currently have a system in production that raises the following error
> intermittantly;
> "A transport-level error has occurred when sending the request to the
> server. (provider: TCP Provider, error: 0 - An existing connection was
> forcibly closed by the remote host.)"
> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
> SP1.
> What could be causing this error, and more importantly how do we resolve i
t?
> Regards
> Andrew Teece
> Technical Architect, eNate
Check the following
1. Router log for any errors
Routers may support IP Virtual Re-Assembly on all interfaces. It is
turned on by default may cause numerous errors .Disable the option
on all interfaces of routers and try
2. Synattack Registry setting
3. Anti Virus software may be closing the connection
Report back after you solve the problem . It may help others
Thanks
Srinivas|||Thanks for the ideas M A Srinivas
At the moment, I think the most likly is the "2. Synattack Registry setting"
that was also mentioned by Charles Wang.
I think this mostly because we are running on a cluster, and the application
raising the error is running on the same pair of cluster servers. As the
application is running concurrently on both, and is raising the error from
both I doubt a network issue because on at least one of the servers my
understanding is that the connection will go through the tcp/ip layer, but
not onto the physical network.
Obviously I will post back any findings.
Regards
Andrew Teece
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1179827956.238191.166180@.u36g2000prd.googlegroups.com...
> On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> Check the following
> 1. Router log for any errors
> Routers may support IP Virtual Re-Assembly on all interfaces. It is
> turned on by default may cause numerous errors .Disable the option
> on all interfaces of routers and try
> 2. Synattack Registry setting
> 3. Anti Virus software may be closing the connection
> Report back after you solve the problem . It may help others
> Thanks
> Srinivas
>
>
>
Intermittant SQL Connection Issue
We currently have a system in production that raises the following error
intermittantly;
"A transport-level error has occurred when sending the request to the
server. (provider: TCP Provider, error: 0 - An existing connection was
forcibly closed by the remote host.)"
The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
SP1.
What could be causing this error, and more importantly how do we resolve it?
Regards
Andrew Teece
Technical Architect, eNateHi Andrew,
Per my understanding, the error was intermittantly thrown by your system in
production environment when it was trying to perform a database operation.
If I have misunderstood, please let me know.
From my research, the issue might be caused by various reasons, such as
broken connections in connection pool, hardware failure, network problems
etc. Also, it might be the known issue which is described in this article:
4.1.2 Connections May Be Forcibly Closed When Running on Windows Server
2003 SP1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;910228
As it mentioned, to resolve this issue, you may use the regedit.exe utility
to add a new DWORD value named SynAttackProtect to the registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\with
value data of 00000000.
If that is not the problem, I recommend that you check if there is a
connection pool in your system and if the connections in the pool have a
timeout value. If so, please set the active connections in the pool with
infinite timeout value.
Since your environment is complex and the issue occurs intermittantly in
production environment, if this issue persists, immediately and effectively
I recommend that you contact Microsoft Customer Support Services ( CSS )
via telephone so that a dedicated Support Professional can assist you
recover the server in a more efficient manner. Please be advised that
contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> We currently have a system in production that raises the following error
> intermittantly;
> "A transport-level error has occurred when sending the request to the
> server. (provider: TCP Provider, error: 0 - An existing connection was
> forcibly closed by the remote host.)"
> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
> SP1.
> What could be causing this error, and more importantly how do we resolve it?
> Regards
> Andrew Teece
> Technical Architect, eNate
Check the following
1. Router log for any errors
Routers may support IP Virtual Re-Assembly on all interfaces. It is
turned on by default may cause numerous errors .Disable the option
on all interfaces of routers and try
2. Synattack Registry setting
3. Anti Virus software may be closing the connection
Report back after you solve the problem . It may help others
Thanks
Srinivas|||Thanks for the ideas M A Srinivas
At the moment, I think the most likly is the "2. Synattack Registry setting"
that was also mentioned by Charles Wang.
I think this mostly because we are running on a cluster, and the application
raising the error is running on the same pair of cluster servers. As the
application is running concurrently on both, and is raising the error from
both I doubt a network issue because on at least one of the servers my
understanding is that the connection will go through the tcp/ip layer, but
not onto the physical network.
Obviously I will post back any findings.
Regards
Andrew Teece
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1179827956.238191.166180@.u36g2000prd.googlegroups.com...
> On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
>> We currently have a system in production that raises the following error
>> intermittantly;
>> "A transport-level error has occurred when sending the request to the
>> server. (provider: TCP Provider, error: 0 - An existing connection was
>> forcibly closed by the remote host.)"
>> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows
>> 2003
>> SP1.
>> What could be causing this error, and more importantly how do we resolve
>> it?
>> Regards
>> Andrew Teece
>> Technical Architect, eNate
> Check the following
> 1. Router log for any errors
> Routers may support IP Virtual Re-Assembly on all interfaces. It is
> turned on by default may cause numerous errors .Disable the option
> on all interfaces of routers and try
> 2. Synattack Registry setting
> 3. Anti Virus software may be closing the connection
> Report back after you solve the problem . It may help others
> Thanks
> Srinivas
>
>
>
intermittantly;
"A transport-level error has occurred when sending the request to the
server. (provider: TCP Provider, error: 0 - An existing connection was
forcibly closed by the remote host.)"
The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
SP1.
What could be causing this error, and more importantly how do we resolve it?
Regards
Andrew Teece
Technical Architect, eNateHi Andrew,
Per my understanding, the error was intermittantly thrown by your system in
production environment when it was trying to perform a database operation.
If I have misunderstood, please let me know.
From my research, the issue might be caused by various reasons, such as
broken connections in connection pool, hardware failure, network problems
etc. Also, it might be the known issue which is described in this article:
4.1.2 Connections May Be Forcibly Closed When Running on Windows Server
2003 SP1
http://support.microsoft.com/default.aspx?scid=kb;EN-US;910228
As it mentioned, to resolve this issue, you may use the regedit.exe utility
to add a new DWORD value named SynAttackProtect to the registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\with
value data of 00000000.
If that is not the problem, I recommend that you check if there is a
connection pool in your system and if the connections in the pool have a
timeout value. If so, please set the active connections in the pool with
infinite timeout value.
Since your environment is complex and the issue occurs intermittantly in
production environment, if this issue persists, immediately and effectively
I recommend that you contact Microsoft Customer Support Services ( CSS )
via telephone so that a dedicated Support Professional can assist you
recover the server in a more efficient manner. Please be advised that
contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
If you have any other questions or concerns, please feel free to let me
know. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
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.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
> We currently have a system in production that raises the following error
> intermittantly;
> "A transport-level error has occurred when sending the request to the
> server. (provider: TCP Provider, error: 0 - An existing connection was
> forcibly closed by the remote host.)"
> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows 2003
> SP1.
> What could be causing this error, and more importantly how do we resolve it?
> Regards
> Andrew Teece
> Technical Architect, eNate
Check the following
1. Router log for any errors
Routers may support IP Virtual Re-Assembly on all interfaces. It is
turned on by default may cause numerous errors .Disable the option
on all interfaces of routers and try
2. Synattack Registry setting
3. Anti Virus software may be closing the connection
Report back after you solve the problem . It may help others
Thanks
Srinivas|||Thanks for the ideas M A Srinivas
At the moment, I think the most likly is the "2. Synattack Registry setting"
that was also mentioned by Charles Wang.
I think this mostly because we are running on a cluster, and the application
raising the error is running on the same pair of cluster servers. As the
application is running concurrently on both, and is raising the error from
both I doubt a network issue because on at least one of the servers my
understanding is that the connection will go through the tcp/ip layer, but
not onto the physical network.
Obviously I will post back any findings.
Regards
Andrew Teece
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1179827956.238191.166180@.u36g2000prd.googlegroups.com...
> On May 21, 9:31 pm, "Andrew Teece" <e...@.nospam.nospam> wrote:
>> We currently have a system in production that raises the following error
>> intermittantly;
>> "A transport-level error has occurred when sending the request to the
>> server. (provider: TCP Provider, error: 0 - An existing connection was
>> forcibly closed by the remote host.)"
>> The SQL Server is a clustered SQL Server 2005 SP1, running on Windows
>> 2003
>> SP1.
>> What could be causing this error, and more importantly how do we resolve
>> it?
>> Regards
>> Andrew Teece
>> Technical Architect, eNate
> Check the following
> 1. Router log for any errors
> Routers may support IP Virtual Re-Assembly on all interfaces. It is
> turned on by default may cause numerous errors .Disable the option
> on all interfaces of routers and try
> 2. Synattack Registry setting
> 3. Anti Virus software may be closing the connection
> Report back after you solve the problem . It may help others
> Thanks
> Srinivas
>
>
>
Interim Summing every x number of rows
Using SQL Server 2005 Standard & SQL Server Reporting Services.
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.
AMB
|||OK, I'm a newbie at all of this so I will try and give you what you asked for:
In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.
Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.
Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes
Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)
Expected result Data for Query ran with Interval Parameter set to 30 Mins
Expected Result Set for Query Ran with Interval Parameter set to 60 Mins
Hope this helps explain it a little more, if not let me know how I can clarify.
Thanks for your time.
|||This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.
Code Snippet
CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime
)
GO
INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO
I will see if I can come up with a procedure to show you how to get what you want.
|||Try:
Code Snippet
createtable #t (
RecordID intnotnulluniqueclustered,
Conv1 smallintnotnull,
Conv2 smallintnotnull,
Conv3 smallintnotnull,
TimeStampVal datetimenotnull
)
go
setnocounton
insertinto #t values(1, 2, 2, 2,'2007-06-29 07:00:00.000')
insertinto #t values(2, 2, 2, 2,'2007-06-29 07:15:00.000')
insertinto #t values(3, 2, 2, 2,'2007-06-29 07:30:00.000')
insertinto #t values(4, 2, 2, 2,'2007-06-29 07:45:00.000')
insertinto #t values(5, 2, 2, 2,'2007-06-29 08:00:00.000')
insertinto #t values(6, 2, 2, 2,'2007-06-29 08:15:00.000')
insertinto #t values(7, 2, 2, 2,'2007-06-29 08:30:00.000')
insertinto #t values(8, 2, 2, 2,'2007-06-29 08:45:00.000')
insertinto #t values(9, 2, 2, 2,'2007-06-29 09:00:00.000')
insertinto #t values(10, 2, 2, 2,'2007-06-29 09:15:00.000')
insertinto #t values(11, 2, 2, 2,'2007-06-29 09:30:00.000')
insertinto #t values(12, 2, 2, 2,'2007-06-29 09:45:00.000')
setnocountoff
go
declare @.sd datetime
declare @.interval int
set @.sd = '2007-06-29 07:00:00.000'
set @.interval = 30
;with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal)/ @.interval as grp
from
#t
)
select
sum(Conv1)as Conv1,
sum(Conv2)as Conv2,
sum(Conv3)as Conv3,
min(TimeStampVal)as TimeStampVal
from
cte_1
group by
grp
order by
min(TimeStampVal)
go
droptable #t
go
AMB
|||Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).
I do have a quick question for clarification though if you would indulge me:
In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.
Code Snippet
with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal) / @.interval as grp
from
#t
)
I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.
Thanks again
Colin
|||Hi Colin,
Glad it helped.
> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view
> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.
select 1 / 2, 1 / 2.
go
I will suggest, if you want to learn more T-SQL, to get the books:
- Inside SQL server 2005: T-SQL Querying
- Inside SQL server 2005: T-SQL Programming
you will not regret having those books.
AMB
Friday, March 23, 2012
Interim Summing every x number of rows
Using SQL Server 2005 Standard & SQL Server Reporting Services.
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors. Bag counts are logged every 15 minutes. There is a count for each conveyor. Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date End Date Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
For this kind of question / problem, it is very very very helpful to post the estructure of the tables, including constraints and indexes, sample data (insert statements) and expected result.
AMB
|||OK, I'm a newbie at all of this so I will try and give you what you asked for:
In the Tables below, RecordID is my Key, Identity field, Increment 1, seed 1 - Data Type Int.
Conv columns are all SmallInt and TimeStampVal is DateTime with DefaultValue of (getdate()) to log the time when the record is inserted. 1 Record will be insertedevery 15 mins via an OPC Server.
Example of table including sample data - for easy explanation of Math I have presumed that each conveyor will have a throughput of 2 bags every 15 minutes
Expected result Data for Query ran with Interval parameter set to 15 mins will be identical to the table above (without the RecordID column.)
Expected result Data for Query ran with Interval Parameter set to 30 Mins
Expected Result Set for Query Ran with Interval Parameter set to 60 Mins
Hope this helps explain it a little more, if not let me know how I can clarify.
Thanks for your time.
|||This is a good start, however, given the information in this format, anyone wanting to help would need to spend some time creating the create table statements and insert statements before they can start helping you. You will get a lot more help if you provide the create table and insert statements like this.
Code Snippet
CREATE TABLE BagCounts
(
RecordID INT IDENTITY (1,1) PRIMARY KEY,
Conv1 smallint,
Conv2 smallint,
Conv3 smallint,
TimeStampVal DateTime
)
GO
INSERT INTO BagCounts (Conv1, Conv2, Conv3, TimeStampVal)
SELECT 2, 2, 2, '2007-06-29 07:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 07:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 08:45:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:00:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:15:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:30:00.000'
UNION SELECT 2, 2, 2, '2007-06-29 09:45:00.000'
GO
I will see if I can come up with a procedure to show you how to get what you want.
|||Try:
Code Snippet
create table #t (
RecordID int not null unique clustered,
Conv1 smallint not null,
Conv2 smallint not null,
Conv3 smallint not null,
TimeStampVal datetime not null
)
go
set nocount on
insert into #t values(1, 2, 2, 2, '2007-06-29 07:00:00.000')
insert into #t values(2, 2, 2, 2, '2007-06-29 07:15:00.000')
insert into #t values(3, 2, 2, 2, '2007-06-29 07:30:00.000')
insert into #t values(4, 2, 2, 2, '2007-06-29 07:45:00.000')
insert into #t values(5, 2, 2, 2, '2007-06-29 08:00:00.000')
insert into #t values(6, 2, 2, 2, '2007-06-29 08:15:00.000')
insert into #t values(7, 2, 2, 2, '2007-06-29 08:30:00.000')
insert into #t values(8, 2, 2, 2, '2007-06-29 08:45:00.000')
insert into #t values(9, 2, 2, 2, '2007-06-29 09:00:00.000')
insert into #t values(10, 2, 2, 2, '2007-06-29 09:15:00.000')
insert into #t values(11, 2, 2, 2, '2007-06-29 09:30:00.000')
insert into #t values(12, 2, 2, 2, '2007-06-29 09:45:00.000')
set nocount off
go
declare @.sd datetime
declare @.interval int
set @.sd = '2007-06-29 07:00:00.000'
set @.interval = 30
;with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal) / @.interval as grp
from
#t
)
select
sum(Conv1) as Conv1,
sum(Conv2) as Conv2,
sum(Conv3) as Conv3,
min(TimeStampVal) as TimeStampVal
from
cte_1
group by
grp
order by
min(TimeStampVal)
go
drop table #t
go
AMB
|||Hunchback, Thanks for the solution, it was just what I was looking for. It took me a while to look some stuff up to figure out what you were doing (I've only been using SQL Server for around 2 months now).
I do have a quick question for clarification though if you would indulge me:
In this part of the code i think you are creating a Common Table Expression (cte_1) which basically replicates the original table and adds a new column onto it called grp.
Code Snippet
with cte_1
as
(
select
*,
datediff(minute, @.sd, TimeStampVal) / @.interval as grp
from
#t
)
I had never heard of CTE's until now so please forgive any dumb questions but why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
By the way your solution is pure genious - I am still trying to get my brain thinking in t-sql, some of the problems and solutions I am reading on this forum are really opening my eyes as to what is possible.
Thanks again
Colin
|||Hi Colin,
Glad it helped.
> why do you not have to specify a datatype for the grp column? I know that it somehow defaults to an int from playing with your codesnippet to view
> the entire cte_1 table but why does it do this when the values returned from the datediff/@.interval function are floating point?
The function DATEDIFF returns int, the variable @.interval is int and the division of integers yields integer.
select 1 / 2, 1 / 2.
go
I will suggest, if you want to learn more T-SQL, to get the books:
- Inside SQL server 2005: T-SQL Querying
- Inside SQL server 2005: T-SQL Programming
you will not regret having those books.
AMB
Interesting scenario - Multiple files updating one table
Hi,
i have a scenario where I have to read 2 files that update the same table (a temp staging table)...this comes from the source system's limitation on the amount of columns that it can export. What we have done as a workaround is we split the data into 2 files where the 2nd file would contain the first file's primary key so we can know on which record to do an update...
Here is my problem...
The table that needs to be updated contains 9 columns. File one contains 5 of them and file2 contains 4 of them.
File 1 inserts 100 rows and leaves the other 4 columns as nulls and ready for file 2 to do an update into them.
File 2 inserts 10 rows but fails on 90 rows due to incorrect data.
Thus only 10 rows are successfully updated and ready to be processed but 90 are incorrect. I want to still do processing on the existing 10 but cant affort to try and do processing on the broken ones...
The easy solution would be to remove the incorrect rows from the temp table when ever an error occurs on the 2nd file's package by running a sql query on the table using the primary keys that exist in both files but when the error occurs on the Flat File source, I can't get the primary key.
What would be the best suggestion? Should i rather fail the whole package if 1 row bombs out? I cant put any logic in the following package that does the master file update/insert from the temp table because of the nature of the date. I
Regards
Mike
You have more than one way to accomplish that, I think.
You could add an extra column to the staging table that will act as a flag to indicate whether a row was properly updated by the 2 file or not; then further steps should filter the rows based on the value of that column.
Or...
Why you don't create 2 staging tables; one for each file. Then you can use SQL statements to join them, perform some data quality checks and decide which rows are going to be processed and which ones would be rejected.
|||You could also use a Merge Join transform with an inner join prior to loading the table - you would take the good record pipelines from the two flat file sources into the Merge Join and set up an inner join on the key from each file. The pipeline output from the Merge Join would then have only the ten records that had a key match and all 9 columns, which you can then load to the SQL table desitination.|||Thanks, I used the flag method and its doing fine.
Much Appreciated Rafael
Mike
Interesting scenario - Multiple files updating one table
Hi,
i have a scenario where I have to read 2 files that update the same table (a temp staging table)...this comes from the source system's limitation on the amount of columns that it can export. What we have done as a workaround is we split the data into 2 files where the 2nd file would contain the first file's primary key so we can know on which record to do an update...
Here is my problem...
The table that needs to be updated contains 9 columns. File one contains 5 of them and file2 contains 4 of them.
File 1 inserts 100 rows and leaves the other 4 columns as nulls and ready for file 2 to do an update into them.
File 2 inserts 10 rows but fails on 90 rows due to incorrect data.
Thus only 10 rows are successfully updated and ready to be processed but 90 are incorrect. I want to still do processing on the existing 10 but cant affort to try and do processing on the broken ones...
The easy solution would be to remove the incorrect rows from the temp table when ever an error occurs on the 2nd file's package by running a sql query on the table using the primary keys that exist in both files but when the error occurs on the Flat File source, I can't get the primary key.
What would be the best suggestion? Should i rather fail the whole package if 1 row bombs out? I cant put any logic in the following package that does the master file update/insert from the temp table because of the nature of the date. I
Regards
Mike
You have more than one way to accomplish that, I think.
You could add an extra column to the staging table that will act as a flag to indicate whether a row was properly updated by the 2 file or not; then further steps should filter the rows based on the value of that column.
Or...
Why you don't create 2 staging tables; one for each file. Then you can use SQL statements to join them, perform some data quality checks and decide which rows are going to be processed and which ones would be rejected.
|||You could also use a Merge Join transform with an inner join prior to loading the table - you would take the good record pipelines from the two flat file sources into the Merge Join and set up an inner join on the key from each file. The pipeline output from the Merge Join would then have only the ten records that had a key match and all 9 columns, which you can then load to the SQL table desitination.|||Thanks, I used the flag method and its doing fine.
Much Appreciated Rafael
Mike
Wednesday, March 21, 2012
Interesting ID issues...
Hi all,
I have a system in place currently that both a local and a live database, currently the sales database is kept in line with a home grown "syncronise" script that controls keeping the two tables (live and local) in sync with the use of Local and Remote ID's
For example;
Table "Sales"
ID - Int
RemoteID - Int
Various Data fields
The 'Live' System writes new records using the RemoteID field, whilst the local inserts new records using the ID field. The Sync script copies the data up/down and assigns the corresponding RemoteID/ID's to the records as they are copied accordingly.
Its been decided that this process must change, in favor of Two Way Replication. I have been tasked with finding the solution to the safe handling of ID's as theres a danger that two people can be writing a record (one live, one local) and possibly create the same ID's - i know that replication has its own ID's and could probably manage this well on its own, but these 'Internal' ID's have to be unique due to business rules.
So i have been considering two possibilities to address this problem,
1. Staggering the ID's - for instance Live ID's begin at 1,000,001 and local ID's begin at 1.
2. Having a controlling ID table which provides the next ID
Both options have their advantages and disadvantages, with option 1 we will run into a problem later when the local ID's reach the 1 million mark (a few years away, i admit) and option 2 will need careful locking/transactional code to ensure the same ID isnt given twice.
Does anyone have experience with this kind of issue? or could suggest an alternative approach to this problem?
Thanks in advance!
Replication ( of any type) by itself does not genrerate any Id's for you. Replication is just a process of copying the data from A to B and/or B to A. You can however, set up 2 range's of Id's for the 2 columns but like you mentioned you could run into issues in future and it could be sooner than later depending on how your data changes. And option 2 does create locking issues.
Now my question is since the 2 columns are different what is the need to keep them in the same table? Are local Id's from your local testing or do they hold any significance? I am trying to understand your design. Looks like you have 2 tables with same data (in 2 db's over 2 servers)?
There is a Peer-Peer Replication option available in SQL 2005 where you can insert data in either of the peer's and replication will take care of sync'ing them.
|||I work for a Dotcom and we have two databases, one on our 'hosted' live servers and one in the local office.
Historically we have used two seperate ID fields, one live (RemoteID) and one local (ID), to allow staff to create sales records locally and customers to create sales records on the live systems. We have a script that then pulls down/copies up the new records and manages the ID's. Due to our sales increasing over the last 12 months this script can no longer handle the work in a timely fashion so another solution is required.
The ID numbers need to be the same, locally and live as this is part of the reference number given to the customer, its been communicated to me that the company wants to keep only a single ID record for sales now and not have to manage multiple ID fields, the company also wants to use replication to manage the data movement. So i need to find a solution for this.
The database can get quite busy under peak load, so i need a solution thats fast, efficient and capable of handling a lot of new records whilst still allowing staff to create local sales and customers to create live sales simultaneously without getting the ID's mixed up.
Using a control table for ID's was a thought, but my concern is speed and locking under heavy load
Monday, March 19, 2012
Intercept Result
Hi,
I am trying to write a system (Stored Proc, View, CLR Proc, ?), where if you query a specific table and it returns data from only 1 column for the submitted SELECT statement then substitute data obtained from an external web service.
Does anyone have any suggestions about how best to implement this? I have searched through the BOL but nothing directly deals with what I want to do.
Thanks,
Blair
P.S. I am using SQL SERVER 2005 ENT Edition with everything available.
Hi,
I'm not very clear with your question.
For substituting a result, you can use CASE within your SELECT statement.
Assuming Table1 table with Col1 as column, and a scalar valued function CLRFunction() which will call a web service,
SELECT CASE Col1
WHEN 'abc' THEN CLRFunction(1)
WHEN 'def' THEN CLRFunction(2)
END 'Col1'
FROM Table1
Here 'abc' and 'def' are the values stored in Col1 of Table1
Hope this answers for your query, else please explain your requirement with some more detail.
Regards
Babu
|||Thanks that is exactly what I was looking for. I now know what to do.
blair
|||Hello...
I think calling a webservie inside a SP is not a very good behavior. You should write your SPs so that they finish as fast as possible. If you try to access an outside resource you never know how long it will take(and a webservice is even worse). You should consider moving this logic into your application. Also you need to access external resources in that function, so you have to declare it as "unsafe" which will also have some implications on security...
Also it will be more complicated to make your app responsive while your code is waiting on the SQL Server. Calling a webservice async is very easy... Calling SQL is a little more work (but still no big deal)
Friday, February 24, 2012
Integration Services extraction/loading throughput/performance
I'm new to integration services.
I want to create a centralized reporting system for our customers. Some customers have up to 1,000 sites and some are expected to grow past 5,000 sites. The sites are running POS applications and I want to extract the POS sales data from these sites. Is it practical to expect that SSIS can handle the extraction of data from this many sites and load the data into a central
SQL database? The POS sales data at the sites is stored in SqlExpress databases but the data is also available in XML format.
If it's practical for Integration Services to do this, what frequency is it possible to pull this data?
I realize that the amout of data is relative but just wondering if anyone is attempting to do this with integration services.
If not with integration services, then what method(s) are available and used to extract data from this many remote sites?
I want to create a centralized reporting system for our customers. Some customers have up to 1,000 sites and some are expected to grow past 5,000 sites. The sites are running POS applications and I want to extract the POS sales data from these sites. Is it practical to expect that SSIS can handle the extraction of data from this many sites and load the data into a central
SQL database? The POS sales data at the sites is stored in SqlExpress databases but the data is also available in XML format.
If it's practical for Integration Services to do this, what frequency is it possible to pull this data?
I realize that the amout of data is relative but just wondering if anyone is attempting to do this with integration services.
If not with integration services, then what method(s) are available and used to extract data from this many remote sites?
SSIS is a very good fit for this type of problem. Loading data from flat files/XML files/databases and inserting to a RDBMS is an every day task for SSIS and something it does very efficiently.
SSIS does not have any restriction on how often you pull the data. This decison depends on many many factors such as:
latency of data|||Thanks for the reply!Do you know where I may find a case study or know of anyone else extracting data from this many sites? I really do expect some customers to grow to 5,000 sites. So before investing into this strategy I want to see if it's being done.
Thanks!
|||
I don't know of any case studies, no. Fundamentally, SSIS will be able to do this. Whether it will work in your situation is down to the factors that I mentioned, not the lack of functionality.
-Jamie
Subscribe to:
Posts (Atom)