New Deployment Svr: Call to JDB_SelectKeyed Failed.

Hello, List. I am a newbie following the recipe in JDE document OTI-99-0057 "Changing Out a Deployment Server" and am having a bit of trouble now that I've turned up the new server.

Everything seems to work ok (check in/out, package build/deploy) but I get an annoying "Call to JDB_SelectKeyed Failed" message for EVERY form I access--just like Ryan Hunt had in February 2002. (I've been studying the thread like crazy: confirmed same MDAC version 2.6 SP1, and verified user permissions.)

The last thing shown in the JDELOG is this:

1392/1180 Wed Jul 23 16:44:29 2003 ODBC_U1929
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'PD7333.F98950'. - SQLSTATE: S0002


The F98950 table *IS* there and contains all my user overrides, which work without a hitch on the old server.


I have temporarily set the Central Objects - PD7333 database data source back to the old deployment server, which eliminates the selectkeyed message.

Any ideas?

Log on to your database with the system user, as declared in P98OWSEC and
see if THAT user can see the table F98950. It may be a database permission
issue.
If security is switched off on the new DS log on to your database with same
user you use to logon to OW and check the table.

Gerd



B732 - ERP8, Unix, Windows, Oracle, SQL, WTS, JAS

I would guess that you have a problem with the SQL Server. How did you migrate the databases from the old server to the new server?

The first scenario I would examine would be to look at your ODBC's and datasources to ensure that they are correct for the new server name. Also verify the ODBC's on your workstations.

If you restored a SQL backup, your sysusers table could be orphaned from your syslogins table. You can check this scenario by looking at the results from the following query. If the results do not match, you have orphans:

select a.name as DatabaseSide, b.name as ServerSide
from jde_pd7333..sysusers as A
left join master..syslogins as B
on a.sid=b.sid

Another scenario could simply be security related. If you execute the following query you will remove ALL security from your database:

sp_msforeachtable "grant all on ? to public"

What does your jde.log say? Does it provide the table (maybe this is why you brought up F98950?)? Is there anything helpful in the JDEEDBUG.LOG? I would follow Jeremy's advise on security. My problem turned out to be security. It became evident when I checked object level permissions on two different central objects users and found significant differences.

Good Luck.

Ryan Hunt

Ryan, here is what the JDE.LOG actually says:

1392/1180 Wed Jul 23 16:44:29 2003 ODBC_U1929
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'PD7333.F98950'. - SQLSTATE: S0002

1392/1180 Wed Jul 23 16:44:29 2003 JDBODBC1458
ODB0000055 - Describe table failed for table F98950.


I'm curious about the reference to PD7333.F98950. My actual database in SQL Server is JDE_PD7333. That's true for my old server as well as my new. Could there possibly be a reference problem?

Thank you, Jeremy. I've a SQL Server newbie, too, so bear with me. Here are the results of the query you suggested; does this mean anything?

APPLEAD APPLEAD
CNCADMIN CNCADMIN
CRPCTL CRPCTL
CRPDTA CRPDTA
db_accessadmin NULL
db_backupoperator NULL
db_datareader NULL
db_datawriter NULL
db_ddladmin NULL
db_denydatareader NULL
db_denydatawriter NULL
db_owner NULL
db_securityadmin NULL
dbo NULL
DD7333 DD7333
DEVUSER DEVUSER
DV7333 DV7333
guest NULL
JD7333 JD7333
JDE JDE
JDEDBA JDEDBA
OBJ7333 OBJ7333
PD7333 PD7333
PRISTCTL PRISTCTL
PRISTDTA PRISTDTA
PRODCTL PRODCTL
PRODDTA PRODDTA
PRODUSER PRODUSER
public NULL
PY7333 PY7333
SVM7333 SVM7333
SYS7333 SYS7333
TESTCTL TESTCTL
TESTDTA TESTDTA

Search for a thread started by me in late 2002 on this. The document you referred to from JDE leaves out 4 tables where you need to do some editing. I don't remember all of the details, but I seem to remember somebody supplying the 4 table names to me along with the required edits. It's all in that thread.

The results of the query indicate that you DO NOT have orphans. The issue must be somewhere else.

Jeremy-- To answer your previous question about how I migrated the central objects to the new server: I used the Import & Export Wizard.


You had suggested in this original reply that I strip all security from my database by running sp_msforeachtable "grant all on ? to public". Would I need to do anything after that to restore security settings?

Crazy, sorry my reply has taken so long...I was on vacation.

Did you check your object level security on the F98950? I noticed you said you are new to SQL, if you need help doing that, let me know.

Ryan

No problem, Ryan. I do have some new information.

On my old deployment server, 'PD7333' is listed as the owner of all tables in the JDE_PD7333 database. On the new, 'dbo' is the owner.

I think this may be the problem, but I don't know how to change it. Help!

EXEC sp_changeobjectowner '<tablenamehere>', 'PD7333'

There has to be a way to script this do all the tables, but you don't want to change the owner of the system tables.

Dave

Thank you, Dave. What now?

exec sp_changeobjectowner 'JDE_PD7333.F98950', 'PD7333'

results in this message:

Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object '[JDE_PD7333].[F98950]' does not exist or is not a valid object for this operation.

The current owner is dbo so

exec sp_changeobjectowner 'JDE_PD7333.dbo.F98950', 'PD7333'

should do the trick.

In SQL, you need to qualify the table with the owner if you are also going to specify the database name.

If you are in the JDE_PD7333 data base, then you can drop that part and the owner if you are the owner. (sa = dbo)

Both below should work assuming you are using the database and you are the owner.

exec sp_changeobjectowner 'dbo.F98950', 'PD7333'

exec sp_changeobjectowner 'F98950', 'PD7333'

And all of this assumes the user exists in the database as well.

Dave

THANK YOU, DAVE!!! Because of what you wrote, I logged in as 'sa' and was able to change the owner without a hitch.

You just saved me another week of being on the phone with JDE Global Support.

-Tim