10-31-2016 07:12 PM
I've got SQL Server tables set up in Metadata and they work fine. The trouble is, my SQL Server DBA is complaining that we have dozens of apparently "orphaned" SQL Server processes that are left by the users of SAS Studio. How do I make SAS Studio clean up the connections?
10-31-2016 08:41 PM
How are your SQL Server data sources set up in metadata - are they pre-assigned or not? If they are pre-assigned you should also add the defer = yes option so database connections are only created if the data source is opened.
11-01-2016 11:26 AM
11-01-2016 03:14 PM
No. Just disconnecting your workspace SAS session and reconnecting should do the trick. You may want to check with your SQL Server DBA as well to see if there is a server connection timeout he can set. I'd suggest something like 24 hours.
Where I work we close all SAS workspace sessions at 2am so we can replicate our SAS environment. This will also drop all database connections.
11-07-2016 11:28 AM
Checked with my DBA this morning and we still have lots of stray connections. The person who signed on has a connection to a test database he never even uses We have large statisticl modeling jobs that run overnight, so closing SAS workspace might be a little dicey for us. I'm wondering about the "Whether to automatically drop utility connections" option? I'll do some more research.