Error while Registering tables from a library in SAS SMC

[Karthikk]

Hi,

I created an ODBC Server using DSN in my server and i have my EG installed on Desktop.

As mentioned in the document i followed the steps to connect with ODBC from SMC.

I created a user gave  him all the rights, I defined ODBC Server in SMC and also defined a LIbrary.

Now After closing SMc and reopened it i was trying to register tables by right click on library which i have defined and register

Here i get and "Error: No tables were retrieved from this querry. Your Connection Information May be Incoorect. do you want to view the log?" 

Now i opened the log so this is what i got:

NOTE: SAS Initialization used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

NOTE: The autoexec file, C:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1 LIBNAME OBLIB ODBC DATAsrc=xxxx SCHEMA=xxxx USER=xxx PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
NOTE: Libref OBLIB was successfully assigned as follows:
Engine: ODBC
Physical Name: OBLIB
2

Neither i am able to see the tables in the library nor i am able to register them.

Can anyone Please help me with this. Please share the steps without skipping anything if you have. This is quiet Urgent and impoortant

Thanks and Regards,

Karthik

[Kurt_Bremser]

The workspace server that is used by the SMC has its configuration in

 C:\SAS\Config\Lev1\SASMeta\WorkspaceServer

not in SASApp

[Karthikk]

Hi KurtBremser,

Thank you so much for replying my querry. Kindly request you to elaborate on the soultion. Since i am new to this i am unable to understand the mentioned solution properly.

Thanks and Regards,

Karthik

[Kurt_Bremser]

That was a mistake on my side. The server that is used for access depends on the library definition, so using the SASApp workspace server is perfectly OK.

Since the library assignment went through, I can only make a guess that the userid used for the database connection lacks permissions to really see tables in the database. So I'd recommend to connect to the DB outside of SAS with those credentials (use the native DB client) and see what happens.

You also might have problems with the "schema" definition.

[Karthikk]

First of all thank you for actively and patiently replying for my querry.  I appreciate your interest towards my querry

Yes i have tried accessing the data with those credentials using Sql Sever Management studio there is no problem with the credentials and also the schema definition.

All i am afraid of is "can we register an Sql View not a table in SAS SMC?". Since i was able to extract the data using proc Sql program but as I have many views to be extracted and registered in SMC I thought of using this ODBC connection from SAS SMC.

Kindly let me know if we can register a view in SAS SMC. If yes please tell me the work around steps for it.

Thanks & Regards,

Karthik

[TomKari]

I don't know what your specific problem is, but here are two issues that I've encountered when connecting to databases:

1. Make sure you're connecting to the correct schema. If your not, of course you'll see no tables.

2. Many databases are very fussy about the case of the table names. Make sure your duplicating the upper and lower case details for your table names.

Good luck!

   Tom

[SASKiwi]

Rather than registering tables in SMC I find it a lot easier to set the AssignMode option in the library definition (see screenshot). This should automatically show all tables in the library or in your case tables in the ODBC server DSN.

If that doesn't help then test your LIBNAME in EG by submitting the statement:

LIBNAME OBLIB ODBC DATAsrc=xxxx SCHEMA=xxxx USER=xxx PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;

Open OBLIB in your Server List - it should show a list of tables. If it doesn't then your DATASRC or SCHEMA options are most likely not correct. What database are you connecting to?

[Karthikk]

Hi Tom/kiwi,

Thank you for your suggestions.

@kiwi: i am trying to connect to Ms SQL server.

When I am trying to get the details from proc SQL program I am able to extract the view from it but the same view I am unable to see using connection odbc.

However I tried libname also. Libname gets successfully executed but I don't find these views in smc library.

Can you please tell me " can we register SQL views from smc using the method which I followed? "

I will try with assign mode. Thank you

Regrds,
Karthik

[TomKari]

A question that should have been asked earlier; is your SAS server running Windows or Linux? Connecting to an ODBC database from Linux is a totally different process.

If your SAS server is Windows, you should absolutely be able to register your views using SMC. There's some kind of issue preventing it right now, but you should definitely be able to do it in the end. All that your SAS server is doing is issuing a libname statement, the same as you are.

When you say you can see your data from PROC SQL, are you running the PROC SQL locally, or on your SAS server using EG. If it's locally, try doing it on your SAS server, and see if you can access any data in your database.

Tom

[Karthikk]

Hi Tom,

I am working on Windows platform. If you can please tell me the full process to connect to ODBC from SAS SMC without skipping any step, that will be a great help from your side. I am trying using SQL Server Authentication while creating DSN. I Kindly request you for the steps so that i can validate where i made mistake including Authentication Domains in SAS SMC.

Thanks & Regards,

Karthik

[TomKari · Accepted solution]

Unfortunately, the environment I'm working in right now doesn't have any ODBC data sources. But here's some steps that I hope will advance your process.

1. First, make sure you're getting access to your ODBC data FROM YOUR SAS SERVER. This means creating some sas code with a libname statement, and then successfully accessing the odbc data. You must run this program successfully on your sas server, using (for example) enterprise guide. This establishes that you can actually access the data from your sas server.

2. Once this is working, use SMC to set up your library definition. Follow the steps as seem appropriate. Once you've created it, if you can access it, that's great. If not, right-click the library and select "Display LIBNAME Statement". Hopefully, you'll be able to see a difference from the one you created that will show why it doesn't work.

3. An additional possibility is to copy the libname statement that you see in step 2, and just try running it in SAS code. Again, tinker with it until it works. Then "retrofit" that change to your definition in SMC.

Good luck! The good news is that these are frequently a pain to set up, but once they work they tend to keep working.

Tom

[SASKiwi]

I suggest you start by getting a LIBNAME working - if this doesn't work then there is no point defining the same source in SMC as that isn't going to work either.

The good news is because you using SAS on Windows you can do away with DSNs and do everything in a database connection string. I suggest you try testing this until it works. You are using Windows Authentication so you can use the "Trusted Connection" option. Please note do NOT put extra blanks in the NOPROMPT string as it will cause errors. Just replace what I have posted with your correct SQL Server name, database name and schema name,

If the LIBNAME assigns without error, then open the library in the EG server list and check if it is now listing the expected tables/views.  

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

[Karthikk]

Thank you Kiwi. I am now successfully able to connect.