SQL Session Remains Open After ReportViewerControl Form is Closed.
Hi experts!
I'm working on a Windows Forms application in VB, using Visual Studio 2010. The application has an option to open a form that contains a ReportViewerControl which displays an SSRS report housed on our MS SQL Server 2008 R2 server. When the form is displayed the report shows up as expected and can be closed without any errors that I can see.
However, I've noticed that the session ID assigned when that form is launched remains open well after the form has been closed. In fact, it stays open even after the application itself is shut down. It does eventually go away - about 10 minutes later, which is (I believe) the default timeout value for the ServerReport class.
For reference, I'm seeing the session id hanging out there when I run the following from SSMS:
I'd like to avoid shorting the timeout value, in the hopes of preventing timeout errors for the users. However, I'd also like to close that session safely when the form is closed, as the session is no longer needed and seems like a waste of resources to have it left open.
My question is, what is the best way to safely close the SQL session when the report form is closed?
I'm working on a Windows Forms application in VB, using Visual Studio 2010. The application has an option to open a form that contains a ReportViewerControl which displays an SSRS report housed on our MS SQL Server 2008 R2 server. When the form is displayed the report shows up as expected and can be closed without any errors that I can see.
However, I've noticed that the session ID assigned when that form is launched remains open well after the form has been closed. In fact, it stays open even after the application itself is shut down. It does eventually go away - about 10 minutes later, which is (I believe) the default timeout value for the ServerReport class.
For reference, I'm seeing the session id hanging out there when I run the following from SSMS:
SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'MyLoginName';I'd like to avoid shorting the timeout value, in the hopes of preventing timeout errors for the users. However, I'd also like to close that session safely when the form is closed, as the session is no longer needed and seems like a waste of resources to have it left open.
My question is, what is the best way to safely close the SQL session when the report form is closed?
ASKER
The report uses a datasource stored on the report server. The dataset executes a SQL stored procedure on the data server. There is no VB code in the RDL - at least, not that I'm aware of... maybe something system generated, but as far as I know that's just xml.
However, to display the report in the ReportViewer control on the VB form, it uses an instance of the ServerReport class.
However, to display the report in the ReportViewer control on the VB form, it uses an instance of the ServerReport class.
OK, that is the normal case. So where are those database sessions coming from? ReportViewer (via the ServerReport class) connects to the execution web service endpoint of the report server service, not to a database server directly.
Is the report server service running under your identity? On my dev system it runs as a virtual service account, NT Service\ReportServer, and that is what I would expect to see as the login for any database connections.
The report server might hang on to its database connections for a while, perhaps to do connection pooling. I haven't paid a lot of attention to what goes on there.
Is the report server service running under your identity? On my dev system it runs as a virtual service account, NT Service\ReportServer, and that is what I would expect to see as the login for any database connections.
The report server might hang on to its database connections for a while, perhaps to do connection pooling. I haven't paid a lot of attention to what goes on there.
ASKER
The session id is tied to a login that I created just for this application. The 'MyLoginName' in the query I posted above is that, not the service account. (Names changed to protect the innocent and all that, but point being I'm specifying my application account in that WHERE clause.)
I actually stumbled across the session hanging out there by trying to drop and recreate that login and getting an error stating that user (the application, not a person) was still logged in. The program is not in production yet so the only 'user' is me while I'm working on it and I had completely closed the program and Visual Studio.
Now it is possible that I used that custom account when I setup the datasource, using SQL Server Authentication (very likely, although I don't recall for sure...will have to double check when I'm back in the office). However, I'm accessing that report and the datasource it uses via that ServerReport class in the VB front end.
I actually stumbled across the session hanging out there by trying to drop and recreate that login and getting an error stating that user (the application, not a person) was still logged in. The program is not in production yet so the only 'user' is me while I'm working on it and I had completely closed the program and Visual Studio.
Now it is possible that I used that custom account when I setup the datasource, using SQL Server Authentication (very likely, although I don't recall for sure...will have to double check when I'm back in the office). However, I'm accessing that report and the datasource it uses via that ServerReport class in the VB front end.
The datasets are going to be using the datasource credentials, so that's what you will see there for the login, even though the session is connected to the report server, not the client. (It's still hard for me to visualize after all these years -- I don't usually need think about it.)
The session is not with your client, though. For it to close when the client terminates, the client would have to notify the report server that it was shutting down, and the report server would have to close the connection. I don't think that happens.
Here is a blog post about report server connection pooling. It might be a little hard to read.
The session is not with your client, though. For it to close when the client terminates, the client would have to notify the report server that it was shutting down, and the report server would have to close the connection. I don't think that happens.
Here is a blog post about report server connection pooling. It might be a little hard to read.
ASKER
I've verified that I did, in fact, use the SQL user in the shared data source that I created on the reporting server to connect to that database. If I understand correctly, you don't believe there is a good way to signal the report server from the client to request that it close that connection?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fair enough. Since it does eventually close itself, it isn't a major issue. It hasn't really caused any problems, other than while trying to drop and recreate that login during development. It just felt like something I should be cleaning up once the application has closed. However, I can now see the advantage to leaving it open. Thanks for your help!
Which are you querying for connections, a data source database or the ReportServer database?