Connect SQL Server 2008 to SAS with ODBC

[AnnBlakeslee]

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?  

[SASKiwi]

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.

[AnnBlakeslee]

Sounds logical. Let me give it a shot! Do I need to restart the SAS
Connect Server to make this option kick in?

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Ann Blakeslee
Institute for Health Research (IHR)
(303) 614-1359


NOTICE TO RECIPIENT: If you are not the intended recipient of this
e-mail, you are prohibited from sharing, copying, or otherwise using or
disclosing its contents. If you have received this e-mail in error,
please notify the sender immediately by reply e-mail and permanently
delete this e-mail and any attachments without reading, forwarding or
saving them. Thank you.

[SASKiwi]

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. 

[AnnBlakeslee]

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.