on 2009 Apr 30 12:14 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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"
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())
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
66 | |
10 | |
10 | |
10 | |
10 | |
8 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.