BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BenConner
Pyrite | Level 9

Hi,

 

Was recently granted access to tables in a MS SQL Server database.  Selects on tables work fine, but when I use a libname in odbc datasrc='...'; statement and run a proc contents data=in._all_; I get nothing back.

 

What permissions does proc contents need that I wasn't given?

 

Thanks!

 

--Ben

1 ACCEPTED SOLUTION

Accepted Solutions
snoopy369
Barite | Level 11

They should know what to do, if you specify that the ODBC connection should include the specific schema you're looking for.  (and, they should be able to figure out what schema you mean if they know what tables you're accessing, so long as those table names aren't used in mulitple schemas)

 

You might look at https://support.sas.com/techsup/technote/ts765.pdf for more information about schemas (about halfway down the doc).  This also discusses the code KSharp included.

View solution in original post

8 REPLIES 8
snoopy369
Barite | Level 11
I just ran that on a libname I have relatively little permission on (just read), so I don't think it's necessarily a permissions issue.

Did you properly set the schema? If your database uses schemas other than DBO, it may work if you run selects but won't necessarily work in conjunction with the metadata-based SAS pieces (such as this) on datasets not in the DBO schema, unless you set which schema you're accessing.
BenConner
Pyrite | Level 9

This is on a SAS server and the ODBC entry was pre-defined.  So I don't know what the schema was, unfortunately, much less if it was set properly.  I don't have access directly to the DB server.  So that's why I had to phrase the question as I did.

 

--Ben

Ksharp
Super User
You can use the following to get schema. but firstly you could try schema=dbo ,
maybe could solve your problem.

proc sql;
connect to odbc(.)
 select * from connection to odbc(ODBC::SQLTables);
quit; 


BenConner
Pyrite | Level 9

Wow.  Didn't even know what this did.  That returned a large amount of information.  Will have the DBA compare it to what we should have assigned to us for a Table schema.  Thanks!

 

--Ben

snoopy369
Barite | Level 11

You may have to talk to your SAS admin then who set up the ODBC connection, as I am fairly sure that's where the problem lies.

BenConner
Pyrite | Level 9

Thanks!  Anything I should tell them to look for?

 

--Ben

snoopy369
Barite | Level 11

They should know what to do, if you specify that the ODBC connection should include the specific schema you're looking for.  (and, they should be able to figure out what schema you mean if they know what tables you're accessing, so long as those table names aren't used in mulitple schemas)

 

You might look at https://support.sas.com/techsup/technote/ts765.pdf for more information about schemas (about halfway down the doc).  This also discusses the code KSharp included.

BenConner
Pyrite | Level 9
Looks like the schema wasn't included in the default definition we use to access the DB; I have to include it explicitly. So the code KSharp provided told me what schema they had given to it.

Thanks to all with this!

--Ben

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4856 views
  • 1 like
  • 3 in conversation