topic Re: Registring Defferent data base from the same server in Administration and Deployment

Registring Defferent data base from the same server

Reddy_GS — Tue, 18 Nov 2014 04:06:13 GMT

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

Re: Registring Defferent data base from the same server

SASKiwi — Tue, 18 Nov 2014 04:26:29 GMT

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.

Re: Registring Defferent data base from the same server

Kurt_Bremser — Tue, 18 Nov 2014 07:35:46 GMT

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.

Re: Registring Defferent data base from the same server

Reddy_GS — Tue, 18 Nov 2014 17:45:27 GMT

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 ?

Re: Registring Defferent data base from the same server

SASKiwi — Tue, 18 Nov 2014 18:51:00 GMT

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.

Re: Registring Defferent data base from the same server

Reddy_GS — Thu, 20 Nov 2014 05:00:49 GMT

Hi Kiwi,

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