Reference one column from a different database; reference one column from this database

Mij

Hi, I'm hoping there is some kind of answer to this.  For my Red Gate project, my current table I tried to reference from a table in a different database.  There are only two columns in this current table.

The first column I wish to reference the employee number from the other database, however when I select FK (manual), it won't let me pick a table and column from that other database table that my current table references.  It only tries to let me pick a table in this database.  My second column I wish to reference from a table within this database (an access level for the application).  This one appears to reference the correct FK defined by the database.

Is there something I can do with the first column though to get it to correctly be a manual FK to the other database values?

Jessica R

Hi @Mij!

I'm afraid the Foreign Key (manual) generator will only allow you to choose from a table within the same database as you've seen. However, you should be able to reference a column from the different database by using the Generic>SQL Statement column. You can then use something like a "SELECT employeeNumber" query to get the values from the table in the other database.

I hope that helps but please let us know if not!

(Note that there was a bug in SQL Data Generator versions 4.3.0 and 4.3.1 that would make Generic>SQL Statement fail with an "Object reference is not set to an instance of an object" error, but this is resolved in today's release of 4.3.2, available from Help>Check for updates.)

Mij

This does seem to work for me.  I have to click on the table again to have it successfully generate.  If I click on the column, it tells me that my database credentials aren't working.  But pulling data and shuffling which is what I need.  Thank you!