DS 3.1 Server Group Error

[Former Member]

Hi

I am trying to setup a Server Group for 2 job server in separate m/c's. I have done the preliminary steps of adding the repostiory to the job servers and testing a job in that repository for both the job servers and they work fine. I am connecting to the Repository using SQL server connection

When I setup the Server group in the admin console, the status page throws an error that both the job servers are not part of the server group with the following sql statement

SELECT "AL_MACHINE_INFO"."SERVER_NAME" , "AL_MACHINE_INFO"."MACHINE_NAME" , "AL_MACHINE_INFO"."PORT" ,

"AL_MACHINE_INFO"."SERVERGROUP_NAME"

FROM "bfcnonprod"."AL_MACHINE_INFO" "AL_MACHINE_INFO_1",

"bfcnonprod"."AL_MACHINE_INFO" "AL_MACHINE_INFO"

WHERE ( "AL_MACHINE_INFO_1"."SERVERGROUP_NAME" = "AL_MACHINE_INFO"."SERVERGROUP_NAME" )

and ( "AL_MACHINE_INFO_1"."SERVER_NAME" = 'DS_JS_scatlbbw') and

( "AL_MACHINE_INFO_1"."MACHINE_NAME" = 'SCATLBBW') and

( "AL_MACHINE_INFO_1"."PORT" = '3500')

DS is using the sqluser as the schema name in the select statement whereas the table is located within the 'dbo' schema. If I run the above query replacing "bfcnonprod" to "dbo", I can see 2 records with the server group name in the repository.

The DBA is saying that the client standard is to have all the tables with dbo schema as the owner. Has anyone faced this issue before ? Is there any place to force DS to use a default schema when reading the repository.

All DS servers are on Windows 2003 R2, repository - SQL Server 2005 EE/SP3

Thanks

Dinesh

[Former Member]

Hi Manoj

The DBA created a synonym for bfcnonprod that would point to dbo. objects and I restarted the 2 job servers and I can see the green check mark status on the admin page. So, do we have a requirement to create a synonym when we setup a server group or can DS resolve the sql statement to use the default schema owner. Is this a bug ?

See my responses below for u'r questions

what is schema that is dispalyed for the DI repository tables ? is it dbo or bfcnonprod

Ans: dbo

what is the user that is used during Repository Creation ? did you login as sa or some other user

Ans: The database was created using the DBA's login ID. bfcnonprod was used within the Repository Manager to create the repository

what is the error you get on executing the SQL statement ? does it throw error about table not found or returns zero rows

Ans: Invalid object name

use the same login that was used to create repository and login to SQL Server Query Analyzer and run the following query, check the output that youget, is it dbo or bfcnonprod ?

SELECT SCHEMA_NAME(SCHEMA_ID())

Ans: bfcnonprod

thanks

Dinesh

[Former Member]

no, there is no requirement to create synonyms for server group

since the query SELECT SCHEMA_NAME(SCHEMA_ID()) returned bfcnonprod, your DI table should be created for schema bfcnonprod during repository creation, and the same will be used in the DI Internal datastore object, using which this query is executed

did you change the owner for repo tables from SQL Server or any other tool

I didn't get the following point, you used "bfcnonprod" is login in repository manager or sa ?

"The database was created using the DBA's login ID. bfcnonprod was used within the Repository Manager to create the repository"

[Former Member]

what is schema that is dispalyed for the DI repository tables ? is it dbo or bfcnonprod

what is the user that is used during Repository Creation ? did you login as sa or some other user

what is the error you get on executing the SQL statement ? does it throw error about table not found or returns zero rows

use the same login that was used to create repository and login to SQL Server Query Analyzer and run the following query, check the output that youget, is it dbo or bfcnonprod ?

SELECT SCHEMA_NAME(SCHEMA_ID())