Architecting, installing and maintaining your SAS environment

Registring Defferent data base from the same server

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Registring Defferent data base from the same server

Hi,

I have three different data bases in one SQL - server and I have one service accont and password for this serve which user can connect to all three data bases using same service account.

I need to register all these data bases into Metadata through Managemenet console(9.4).

I have created one new library and regaisterd first data base.

My question are here:

Assume server Name: SQL1234

1. How do I register second and third data bases which are on same SQL server and which is havig same service account and same password.

2. HOw can I Include second database under the same server name in management console.

3. how can I create authoriztion access to all thress data base which are having same service account and password.

Appreciate you support in this matter


Accepted Solutions
Solution
‎11-18-2014 01:51 PM
Super User
Posts: 3,256

Re: Registring Defferent data base from the same server

SQL Server tables are defined like this:

[ServerName].[DatabaseName].[SchemaName].[Table]

We have found that defining only the ServerName level in SMC Server Manager works best for us. You can do this either through a connection string like this:

"server=ServerName;DRIVER=SQL Server;user=UserName;password=MyPassword" or through a datasrc definition. We prefer the connection string as then you don't need to define ODBC sources in the ODBC Administrator.

Then in your Data Library definition you add the options QUALIFIER = DatabaseName and SCHEMA = SchemaName to have your data library list all of the tables for a particular schema.

View solution in original post


All Replies
Super User
Posts: 3,256

Re: Registring Defferent data base from the same server

In SMC servers are defined using the Server Manager plugin, and databases are defined under Data Library Manager. Multiple data libraries can point to the same server definition.

The server definition can have a single server account and password to connect to any database on that server.

A data library that connects to an external database contains a server tab under properties for identifying the server name.

How do you connect to SQL Server? If you use ODBC / OLEDB you just create a data library of this type in the new data library wizard.

Occasional Contributor
Posts: 14

Re: Registring Defferent data base from the same server

Hi Kiwi,

I got it what you saying but How can I defined both the data base names together when I adding new server on to a server manager plugin since

do i need to give name of the data base like this format: database1;database2 in datasrc: field ?

Solution
‎11-18-2014 01:51 PM
Super User
Posts: 3,256

Re: Registring Defferent data base from the same server

SQL Server tables are defined like this:

[ServerName].[DatabaseName].[SchemaName].[Table]

We have found that defining only the ServerName level in SMC Server Manager works best for us. You can do this either through a connection string like this:

"server=ServerName;DRIVER=SQL Server;user=UserName;password=MyPassword" or through a datasrc definition. We prefer the connection string as then you don't need to define ODBC sources in the ODBC Administrator.

Then in your Data Library definition you add the options QUALIFIER = DatabaseName and SCHEMA = SchemaName to have your data library list all of the tables for a particular schema.

Occasional Contributor
Posts: 14

Re: Registring Defferent data base from the same server

Hi Kiwi,

It works now, thank you very much for you valuable time

Super User
Posts: 7,832

Re: Registring Defferent data base from the same server

Disclaimer: I can only speak from my experience with DB/2.

There you have what is called an "instance" to connect to a database/set of databases. Every instance is defined in UNIX with a separate userid that has the configuration in its home directory; environment variables are used to hand off the instance to SAS. Since there is only one set of environment variables, a single SAS session/server definition can use only one instance.

So _may_ be forced to define several logical workspace servers to have access to all databases.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 974 views
  • 0 likes
  • 3 in conversation