MetaData and FKCOLUMN_NAME

I've got a SQL Server 2000 database with a table that has two Foreign Keys.
They are declared as such in the database.
When I use the latest JDBC driver (sp3) to get the MetaData info about these
Foreign Keys, I'm runing into what appears to be a bug. Here's a snippet
code:
ResultSet rsForeignKeys = dbmd.getImportedKeys( null, null, "Recipes" );
while( rsForeignKeys.next() ) {
log.debug( "fktablename: " + rsForeignKeys.getString("FKTABLE_NAME") );
log.debug( "fkcolumname: " + rsForeignKeys.getString("FKCOLUMN_NAME") );
log.debug( "pktablename: " + rsForeignKeys.getString("PKTABLE_NAME") );
log.debug( "pkcolumnename: " + rsForeignKeys.getString("PKCOLUMN_NAME") );
}
This yields the following (with Log4J prefix info ommitted):
fktablename: Recipes
fkcolumname: MenuSectionID
pktablename: MenuSections
pkcolumnename: MenuSectionID
fktablename: Recipes
fkcolumname: MenuSectionID***
pktablename: RecipeType
pkcolumnename: RecipeTypeID
Notice the asterisked item above (asterisks are my own, not code generated).
The MenuSectionID is the fkcolumn name from the first FK entry but it is
show for both fk entries returned by the DBMD object. This is not correct,
the name of the second FK column for this table is "RecipeID". I have
double-checked my relationships in the database and all seems well there.
Any idea what might be going on here?
My thanks,
- Gary
Oh duh.
I just triple-checked by db-definition and found the problem. I had a typo
in the fk definition. The DBMD object was giving me exactly the fk's exactly
as I had (incorrectly) defined them.
Sorry about that.
- Gary
"gaffonso" wrote:

> I've got a SQL Server 2000 database with a table that has two Foreign Keys.
> They are declared as such in the database.
> When I use the latest JDBC driver (sp3) to get the MetaData info about these
> Foreign Keys, I'm runing into what appears to be a bug. Here's a snippet
> code:
> ResultSet rsForeignKeys = dbmd.getImportedKeys( null, null, "Recipes" );
> while( rsForeignKeys.next() ) {
> log.debug( "fktablename: " + rsForeignKeys.getString("FKTABLE_NAME") );
> log.debug( "fkcolumname: " + rsForeignKeys.getString("FKCOLUMN_NAME") );
> log.debug( "pktablename: " + rsForeignKeys.getString("PKTABLE_NAME") );
> log.debug( "pkcolumnename: " + rsForeignKeys.getString("PKCOLUMN_NAME") );
> }
> This yields the following (with Log4J prefix info ommitted):
> fktablename: Recipes
> fkcolumname: MenuSectionID
> pktablename: MenuSections
> pkcolumnename: MenuSectionID
> fktablename: Recipes
> fkcolumname: MenuSectionID***
> pktablename: RecipeType
> pkcolumnename: RecipeTypeID
> Notice the asterisked item above (asterisks are my own, not code generated).
> The MenuSectionID is the fkcolumn name from the first FK entry but it is
> show for both fk entries returned by the DBMD object. This is not correct,
> the name of the second FK column for this table is "RecipeID". I have
> double-checked my relationships in the database and all seems well there.
> Any idea what might be going on here?
> My thanks,
> - Gary

messages aren't getting through after backup / restore on a different server...

after restoring a database and regenerating keys, I'm trying to queue messages, and they're all hanging out in sys.transmission_queue with the following message:
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I can't find any documentation or blog info on this error... Help!

thanks!Have you set your database to trustworty?

ALTER DATABASE db_name
SET TRUSTWORTHY ON

Also, maje sure you have a master key in both databases.

CREATE DATABASE MASTER KEY
ENCRYPTION BY PASSWORD = 'somePassW0rd1'

Please let us know if this helps

Niels|||hi Niels.

Nope, the SET TRUSTWORTHY ON on the database did not work.

It would probably be helpful if I mentioned that the backup was taken on the April CTP server, and the restore on the September version...

also - both the initiator and target queues live in the same database...

|||

Which server principal (i.e. login) owns the restored database? Has anything changed with the SQL instance or Windows users since backup was taken? For example you are trying to restore on a machine not connected to a domain and the server principal owning the database was a domain user?

Later,
Rushi

|||Rushi -- a domain administrator owns both databases.
Both servers are connected to the domain, and all services are also run by the domain administrator.|||Probably the dbo of the database is the original login that created the database, on the original server, and that login cannot be impersonated on the new server (e.g. the login is the Windows login corresponding to the original server Administrator account).
Use ALTER AUTHORIZATION ON DATABASE <dbname> TO <loginname> to change the owner of the database to a new login (e.g. [sa]), it will change the dbo to map to a login than is OK to be impersonated.

HTH,
~ Remus|||thanks Remus -- that solved the problem.