Several SSRS reports with an Oracle data source show empty result until reboot

Question

In November 2020 we upgraded our SSRS Server to Version 2019 (Version 15.0.1102.861). This morning several users called and complained that multiple reports, which they use every morning, showed no data, only the headlines. These reports use an Oracle data source but other reports, using the same data source, worked as normal. When I opened the affected reports in Visual Studio I saw the expected data, so the problem wasn't in our data warehouse. I rebooted the server and after this everything was fine. Probably restarting the SSRS service only would have solved the problem as well. Is this a known problem? Will it happen periodically? Is there a solution to this?

Answer 1

I found an error message in the log file but I'm not sure if it is related to our problem as the error message occured only once and several users had the problem yesterday morning, that their reports didn't show a result. The message in the logfile is:

library!ReportServer_0-2!10c8!01/20/2021-07:57:16:: i INFO: Call to GetItemTypeAction(<path_to_report>). User: <domain>\<username>.
webserver!ReportServer_0-2!1774!01/20/2021-07:57:16:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnhandledHttpApplicationException: , Microsoft.ReportingServices.Diagnostics.Utilities.UnhandledHttpApplicationException: Der Berichtsserver hat eine unbehandelte Ausnahme in HttpApplication erkannt. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerHttpRuntimeClientDisconnectionException: Die Verbindung eines Clients mit der Anwendungsdomäne "ReportServer_SSRS_0-2-132555585706304304" des Berichtsserver-Webdiensts wurde getrennt. Es sind keine Korrekturmaßnahmen erforderlich. Es wurde ein Fehlercode an ASP.NET gesendet, um die Verbindung freizugeben. Der Fehlercode lautet 800708CA. ---> System.Runtime.InteropServices.COMException: Diese Netzwerkverbindung ist nicht vorhanden. (Ausnahme von HRESULT: 0x800708CA)
bei Microsoft.ReportingServices.HostingInterfaces.IRsHttpPipeline.GetAuthType()
bei ReportingServicesHttpRuntime.RsWorkerRequest.GetUserToken()
--- Ende der internen Ausnahmestapelüberwachung ---
bei ReportingServicesHttpRuntime.BaseWorkerRequest.FilterAndThrowException(Exception e, String message)
bei ReportingServicesHttpRuntime.RsWorkerRequest.GetUserToken()
bei System.Web.HttpContext.get_ImpersonationToken()
bei System.Web.ClientImpersonationContext.Start(HttpContext context, Boolean throwOnError)
bei System.Web.ThreadContext.AssociateWithCurrentThread(Boolean setImpersonationContext)
bei System.Web.HttpApplication.OnThreadEnterPrivate(Boolean setImpersonationContext)
bei System.Web.HttpApplication.ApplicationStepManager.ResumeSteps(Exception error)
--- Ende der internen Ausnahmestapelüberwachung ---;

I found someone describing a similar problem like ours but without a solution:

https://stackoverflow.com/questions/62159553/ssrs-oracle-connection-provides-no-data-but-does-not-error

Answer 2

Hi @Ulrich M. Weber ,
Is there any error message when the situation occurs? Could you check the error log of SSRS and Oracle? You could pay attention to whether a similar situation will occur in the future. For now, I don't seem to have heard of the problem that the report cannot be presented due to the upgrade of the server. I cannot rule out whether it is the Oracle data source.
Try to provide an error log.
Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

This seems to be the reason for the timeout, but I am not sure. I will make follow-up improvements.
At first, check the execution log 3.
And please check the report execution timeout: to do so, Go to report manager-> Site settings-> Report Timeout and select "Do not timeout report" or change the value of "Limit report processing to the following number of seconds"

In the web config set the http run time as：
<httpRuntime maxRequestLength="2147483647" executionTimeout = "10800"/>

Usually we can check the Event Viewer together when checking the error log, it may be easier to find out the reason.

Answer 4

My answer is a little late, because I was busy with other topics, but our problem still persists. Yesterday, shortly after noon the trouble occurred the second time. Two users called me shortly after each other and told me that different reports, that normally shows data every day, are empty. Only the headlines of the columns were shown. I don’t think, that the problem is related to execution timeout, which is set to 6000 seconds in our environment, as the empty results appear very quickly.
The time between the two occurrences of our problem is quite similar to what is described in
https://stackoverflow.com/questions/62159553/ssrs-oracle-connection-provides-no-data-but-does-not-error

Here are some rows from the execution log, showing both times when the problem occurred. The RowCount of several reports is 0 until the SSRS Service was restarted. There are some Reports with RowCount > 0 in between, but these Reports use a different DataSource.

select 
  ReportID,
  Obfuscate(UserName) UserName,
  ExecutionID,
  Format,
  ReportAction,
  TimeStart,
  TimeEnd,
  TimeDataRetrieval,
  TimeProcessing,
  TimeRendering,
  Status,
  ByteCount,
  "RowCount"
from ReportServer.dbo.ExecutionLogStorage
where (   TimeStart between CONVERT(DATETIME, '2021-01-19 08:20', 120) 
                        and CONVERT(DATETIME, '2021-01-19 08:33', 120)
       or TimeStart between CONVERT(DATETIME, '2021-01-25 13:35', 120) 
                        and CONVERT(DATETIME, '2021-01-25 13:55', 120)
      )              
  and Source = 1                    
order by TimeStart desc;


ReportID    UserName    ExecutionID Format  ReportAction    TimeStart   TimeEnd TimeDataRetrieval   TimeProcessing  TimeRendering   Status  ByteCount   RowCount
D53A5529-EEB2-4574-9CAC-C510047497F7    <...>\add1    afjkbe45frhwqc2pvgfaix55    RPL 1   25.01.2021 13:54    25.01.2021 13:55    9842    483 180 rsSuccess   36026   27
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\bc2 yzmcleyxgsvmww45wf0ebyi3    RPL 1   25.01.2021 13:54    25.01.2021 13:54    9   15  24  rsSuccess   44483   20
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\bc2 yzmcleyxgsvmww45wf0ebyi3    RPL 1   25.01.2021 13:53    25.01.2021 13:53    68  13  21  rsSuccess   29111   10
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\aa1 idvso145p5czzrq3ets5f145    RPL 1   25.01.2021 13:52    25.01.2021 13:52    2615    1212    1066    rsSuccess   187067  112
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\aa1 idvso145p5czzrq3ets5f145    RPL 1   25.01.2021 13:50    25.01.2021 13:50    459 8   10  rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\aa1 idvso145p5czzrq3ets5f145    RPL 1   25.01.2021 13:50    25.01.2021 13:50    425 8   11  rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\bc2 yzmcleyxgsvmww45wf0ebyi3    RPL 1   25.01.2021 13:50    25.01.2021 13:50    460 7   7   rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\aa1 idvso145p5czzrq3ets5f145    RPL 1   25.01.2021 13:50    25.01.2021 13:50    428 8   11  rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\aa1 idvso145p5czzrq3ets5f145    RPL 1   25.01.2021 13:50    25.01.2021 13:50    464 8   11  rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\bc2 15pyqq55awleknf1mpjx1wzp    RPL 1   25.01.2021 13:48    25.01.2021 13:48    418 9   11  rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\bc2 15pyqq55awleknf1mpjx1wzp    RPL 1   25.01.2021 13:48    25.01.2021 13:48    459 7   11  rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\bc2 15pyqq55awleknf1mpjx1wzp    RPL 1   25.01.2021 13:48    25.01.2021 13:48    456 7   11  rsSuccess   10692   0
B9748EE9-26FD-479D-824A-4671A775BB38    <...>\bc2 15pyqq55awleknf1mpjx1wzp    RPL 1   25.01.2021 13:48    25.01.2021 13:48    442 15  12  rsSuccess   10692   0
D53A5529-EEB2-4574-9CAC-C510047497F7    <...>\add1    ecevz4vutewlbm45if1rll45    RPL 1   25.01.2021 13:47    25.01.2021 13:47    10268   12  15  rsSuccess   14974   8
D53A5529-EEB2-4574-9CAC-C510047497F7    <...>\add1    vhzf3245kbkm1pvfmfearm45    RPL 1   25.01.2021 13:46    25.01.2021 13:46    9550    28  28  rsSuccess   14974   8
5D63B676-B9FC-4C07-9F29-B842D2DB26CE    <...>\ba10    3qw1yvvvlpcvpa45u5jogc55    RPL 1   25.01.2021 13:45    25.01.2021 13:45    11641   187 29  rsSuccess   30060   402
A783806C-D51C-478F-A860-ECEDAA55C783    <...>\add1    3d5bgrix1va5un45uqp0fg45    RPL 1   25.01.2021 13:45    25.01.2021 13:45    316 34  57  rsSuccess   91411   29
F70B7ED8-3FA9-441C-80AC-C23AFB72E81C    <...>\ee4 dsxpoemtajw0vkbv3y0q2c55    RPL 1   25.01.2021 13:43    25.01.2021 13:43    1939    333 312 rsSuccess   1232164 393
1E429DAF-1A02-4A65-8C09-BB50A602861C    <...>\add1    4vglj355vxuv42ardsz0hgey    RPL 1   25.01.2021 13:42    25.01.2021 13:42    1197    56  91  rsSuccess   403288  145
F00D7958-E11F-45C8-8349-95D86B6C176C    <...>\bd42    hqqd15et1zmohm3c5gltpzqh    RPL 1   25.01.2021 13:42    25.01.2021 13:42    4451    51  104 rsSuccess   486260  207
6D31E446-3EED-4146-BDD9-F1ADA697F80C    NT SERVICE\SQLServerReportingServices   t3lm4k55riqcp5uk3lsio5zx    EXCELOPENXML    1   25.01.2021 13:38    25.01.2021 13:38    844 1184    2118    rsSuccess   305066  3093
5D63B676-B9FC-4C07-9F29-B842D2DB26CE    <...>\ba16    lyb2tinp5h12fg55nhsa0q55    RPL 1   25.01.2021 13:36    25.01.2021 13:37    10850   88  18  rsSuccess   29837   228
F00D7958-E11F-45C8-8349-95D86B6C176C    <...>\bd42    hqqd15et1zmohm3c5gltpzqh    RPL 1   25.01.2021 13:35    25.01.2021 13:36    3310    91  25  rsSuccess   23815   404
F00D7958-E11F-45C8-8349-95D86B6C176C    <...>\bd42    hqqd15et1zmohm3c5gltpzqh    RPL 1   25.01.2021 13:35    25.01.2021 13:35    3951    3009    342 rsSuccess   1349212 15312

F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bl3 hhpga52enzh4r245lori4x55    RPL 1   19.01.2021 08:31    19.01.2021 08:31    3723    2635    299 rsSuccess   1142230 46523
8CF0F2FE-1501-46B3-8444-32F825EC44D7    NT SERVICE\SQLServerReportingServices   oxqmc535pmnwmu45tz3xt055    XML 1   19.01.2021 08:31    19.01.2021 08:31    347 274 145 rsSuccess   481 1
82D50161-7514-466C-8113-021754A92080    <...>\aa1 kc5ggriuogb035ybnx1hoqrl    RPL 1   19.01.2021 08:31    19.01.2021 08:31    734 381 2675    rsSuccess   106030  359
8BE04BC3-F5EE-468A-8FE7-8BB563A3DE5D    NT SERVICE\SQLServerReportingServices           9   19.01.2021 08:31    19.01.2021 08:31    3084    253 0   rsSuccess   698 1
8F752661-DB1E-40DF-A322-8A9374D8ABFE    <...>\aa1 vdq3sd55zhw53vn42pdaxby4    RPL 1   19.01.2021 08:31    19.01.2021 08:31    2148    158 243 rsSuccess   48171   29
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 amhj0145qnpigz45virvp1mw    RPL 1   19.01.2021 08:30    19.01.2021 08:30    3858    1088    1040    rsSuccess   115869  4358
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 amhj0145qnpigz45virvp1mw    RPL 1   19.01.2021 08:29    19.01.2021 08:29    3   12  12  rsSuccess   11688   0
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 amhj0145qnpigz45virvp1mw    RPL 1   19.01.2021 08:29    19.01.2021 08:29    4   11  11  rsSuccess   11688   0
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 clhbguefpcnqqbuhjxdujevm    RPL 1   19.01.2021 08:28    19.01.2021 08:28    4   12  13  rsSuccess   11688   0
91E237E3-D8C6-49F6-BCB0-91B6FA879153    <...>\bd44    b4c111551intmsjvxb5uzl23    PDF 1   19.01.2021 08:27    19.01.2021 08:27    129 13  98  rsSuccess   92376   28
DA80363D-BB4B-47DC-A31B-ECF6CD11FBD9    <...>\ad12    k5cuz445cxdrbgn0ou0wyi55    PDF 1   19.01.2021 08:27    19.01.2021 08:27    2412    217 155 rsSuccess   102265  406
DA80363D-BB4B-47DC-A31B-ECF6CD11FBD9    <...>\ad12    vjq2pd552bslso455vc3q1mz    PDF 1   19.01.2021 08:27    19.01.2021 08:27    2262    219 192 rsSuccess   104046  406
8F752661-DB1E-40DF-A322-8A9374D8ABFE    <...>\aa1 51wr5qymugulwi55dpz5wy45    RPL 1   19.01.2021 08:27    19.01.2021 08:27    2102    10  8   rsSuccess   7941    0
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 hlxrnk45yf33fpyve2izaebl    RPL 1   19.01.2021 08:27    19.01.2021 08:27    4   17  12  rsSuccess   11688   0
C1F4995F-8FD6-4749-9EF2-A1365463BE6B    <...>\bc7 1tkqa345a4h0ivyo1gvt34ql    RPL 1   19.01.2021 08:27    19.01.2021 08:27    16  13  59  rsSuccess   12519   0
455D770E-6FCB-476A-84B9-DB55BE3499EE    <...>\bd44    4om5o33gu25k2n45vqnjqtq4    RPL 1   19.01.2021 08:27    19.01.2021 08:27    15  8   7   rsSuccess   5671    0
91A7690A-D194-453D-916A-D4FC6774E7D3    <...>\bw18    zijbpp55sxnq5cmwm3qzde55    RPL 1   19.01.2021 08:27    19.01.2021 08:27    4643    11  10  rsSuccess   9704    1
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 gwefps55oqjeuh55viunqoip    RPL 1   19.01.2021 08:26    19.01.2021 08:26    3   12  13  rsSuccess   11688   0
C1F4995F-8FD6-4749-9EF2-A1365463BE6B    <...>\bc6 lut1kj55qp1kif55ps5ef345    RPL 1   19.01.2021 08:26    19.01.2021 08:26    17  14  17  rsSuccess   12519   0
DA80363D-BB4B-47DC-A31B-ECF6CD11FBD9    <...>\ad12    r2y4x4bihopz4kv2zr52it55    PDF 1   19.01.2021 08:26    19.01.2021 08:26    3390    309 198 rsSuccess   100472  706
DA80363D-BB4B-47DC-A31B-ECF6CD11FBD9    <...>\ad12    ou021k55uxe0gp45px2rdb45    PDF 1   19.01.2021 08:26    19.01.2021 08:26    3492    419 291 rsSuccess   103718  706
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 opam1m45pmb2pw55v2mx2wbq    RPL 1   19.01.2021 08:26    19.01.2021 08:26    4   10  13  rsSuccess   11688   0
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 pydicvikdv0lcc45znmk3vji    RPL 1   19.01.2021 08:26    19.01.2021 08:26    3   12  20  rsSuccess   11688   0
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 3chrnq55l0hl3pb0efkwpv45    RPL 1   19.01.2021 08:26    19.01.2021 08:26    3   11  12  rsSuccess   11688   0
C1F4995F-8FD6-4749-9EF2-A1365463BE6B    <...>\bc7 pmz2c4v5pru0i445icxutz55    RPL 1   19.01.2021 08:25    19.01.2021 08:25    17  12  17  rsSuccess   12519   0
F02BA0DF-24ED-494E-BCDA-2EF3247C70B4    <...>\bc9 dx0fbh45neuv1sya1vvigr45    RPL 1   19.01.2021 08:25    19.01.2021 08:25    55  18  15  rsSuccess   11688   0
1406F416-DC1B-4030-BE3D-9923129ADAB4    <...>\bc9 fmyo2i45pkzojiu00sa41k45    RPL 1   19.01.2021 08:25    19.01.2021 08:25    1332    75  48  rsSuccess   107693  155
91A7690A-D194-453D-916A-D4FC6774E7D3    <...>\bc9 4nskid455giucobnerur0m45    RPL 1   19.01.2021 08:24    19.01.2021 08:24    5976    25  10  rsSuccess   9704    1
91E237E3-D8C6-49F6-BCB0-91B6FA879153    <...>\bd44    fdytn155s4u41zifbk1f1m45    PDF 1   19.01.2021 08:22    19.01.2021 08:22    357 38  126 rsSuccess   85520   18
320D8640-E159-46F5-8ABE-B393F5AF8684    <...>\eb2 pv2eqifdomqhmdiobvinhq55    RPL 1   19.01.2021 08:22    19.01.2021 08:22    23173   42  128 rsSuccess   402219  135
82D50161-7514-466C-8113-021754A92080    <...>\aa1 ls1tt255cleq3t55mtv5qn55    RPL 1   19.01.2021 08:21    19.01.2021 08:21    525 21  66  rsSuccess   1675    10

Answer 5

This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.
One solution is to install both x86 (32-bit) and x64 Oracle Clients on your machine, then it does not matter on which architecture your application is running.

Here an instruction to install x86 and x64 Oracle client on one machine:

Assumptions: Oracle Home is called OraClient11g_home1, Client Version is 11gR2

• Optionally remove any installed Oracle client (see How to uninstall / completely remove Oracle 11g (client)? if you face problems)
• Download and install Oracle x86 Client, for example into C:\Oracle\11.2\Client_x86
• Download and install Oracle x64 Client into different folder, for example to C:\Oracle\11.2\Client_x64
• Open command line tool, go to folder %WINDIR%\System32, typically C:\Windows\System32 and create a symbolic link ora112 to folder C:\Oracle\11.2\Client_x64 (see commands section below)
• Change to folder %WINDIR%\SysWOW64, typically C:\Windows\SysWOW64 and create a symbolic link ora112 to folder C:\Oracle\11.2\Client_x86, (see below)
• Modify the PATH environment variable, replace all entries like C:\Oracle\11.2\Client_x86 and C:\Oracle\11.2\Client_x64 by C:\Windows\System32\ora112, respective their \bin subfolder. Note: C:\Windows\SysWOW64\ora112 must not be in PATH environment.
• If needed set your ORACLE_HOME environment variable to C:\Windows\System32\ora112
• Open your Registry Editor. Set Registry value HKLM\Software\ORACLE\KEY_OraClient11g_home1\ORACLE_HOME to C:\Windows\System32\ora112
• Set Registry value HKLM\Software\Wow6432Node\ORACLE\KEY_OraClient11g_home1\ORACLE_HOME to C:\Windows\System32\ora112 (not C:\Windows\SysWOW64\ora112)
• You are done! Now you can use x86 and x64 Oracle client seamless together, i.e. an x86 application will load the x86 libraries, an x64 application loads the x64 libraries without any further modification on your system.
• Probably it is a wise option to set your TNS_ADMIN environment variable (resp. TNS_ADMIN entries in Registry) to a common location, for example TNS_ADMIN=C:\Oracle\Common\network.

Commands to create symbolic links:

cd C:\Windows\System32  
mklink /d ora112 C:\Oracle\11.2\Client_x64  
cd C:\Windows\SysWOW64  
mklink /d ora112 C:\Oracle\11.2\Client_x86  

Notes:

Both symbolic links must have the same name, e.g. ora112.

Despite of their names folder C:\Windows\System32 contains the x64 libraries, whereas C:\Windows\SysWOW64 contains the x86 (32-bit) libraries. Don't be confused.

Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.