Write and run SAS programs in your web browser

Connect SQL Server 2008 to SAS with ODBC

Reply
New Contributor
Posts: 3

Connect SQL Server 2008 to SAS with ODBC

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?  

Super User
Posts: 3,260

Re: Connect SQL Server 2008 to SAS with ODBC

Posted in reply to AnnBlakeslee

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.

New Contributor
Posts: 3

Re: Connect SQL Server 2008 to SAS with ODBC

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.
Super User
Posts: 3,260

Re: Connect SQL Server 2008 to SAS with ODBC

Posted in reply to AnnBlakeslee

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. 

New Contributor
Posts: 3

Re: Connect SQL Server 2008 to SAS with ODBC

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.

Ask a Question
Discussion stats
  • 4 replies
  • 320 views
  • 1 like
  • 2 in conversation