Multiple databases on one server instance

[Wouter Pronk]

Hi,

just dived into Jam and really like it! I have a question though: Would it be possible to have multiple databases in 1 project? Like multiple tasks? Our current SQL set-up has multiple databases and they interact with each other f.e. to get a lookup-value.

Kr Wouter Pronk

[Dean D. Babic]

Hi, 

I think this might be possible only with linked server databases. Not something done in Jam.

Regards

D.

[Wouter Pronk]

Hi Dean,

that's not really what I mean. Within MSSQL server (and probably also other variants) there can be multiple databases. MSSQL has by default Master, MSDB, Model and tempdb as their System databases. Besides that you can create multiple databases that are not linked via linked server.

Kr Wouter

Op di 28 okt 2025 om 10:19 schreef Wouter Pronk <wjbp...@gmail.com>:

Hi,

just dived into Jam and really like it! I have a question though: Would it be possible to have multiple databases in 1 project? Like multiple tasks? Our current SQL set-up has multiple databases and they interact with each other f.e. to get a lookup-value.

Kr Wouter Pronk

--
You received this message because you are subscribed to a topic in the Google Groups "Jam.py Users Mailing List" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jam-py/a-EKfgqMkZg/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jam-py+un...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/jam-py/539cb918-36d4-4b2c-a310-6d9aece12606n%40googlegroups.com.

[Dean D. Babic]

Jam is connecting to one single database with one single user:
https://jampy-docs-v7.readthedocs.io/en/latest/intro/new_project.html

For sure you could connect to any number of databases, but you do not get CRUD on them automatically:
https://jampy-docs-v7.readthedocs.io/en/latest/how_to/how_to_data_from_other_database_tables.html

The above is needed, but was not tested yet.

D.

[Dean D. Babic]

Hi,

if you are still after a lookup to a different database from the one you are connecting, 

this is the solution:

As seen, Jam is connecting to jam8 database, which is this on PA:
https://elgar.pythonanywhere.com/

 jam7 database is Demo, with Albums table. What is needed is a synonym to jam7.dbo.ALBUMS.

Then you can import it as usual, and do the Lookup as seen on Voiceinsttable.

I've spent quite some time on this but it is really simple. 

This concludes using multiple MSSQL databases with Jam. It is all DBA work.

Hope this helps. 

D.

[Wouter Pronk]

Hi D,

thanks for your reply. Interesting idea. I'm not sure it will work out of the box because when retrieving tables during import the information_schema is used in which synonyms are not retrieved. Also later when retrieving the columns you encounter the same issue. I have resolved this by using a union select on sysobjects for the table (xtype = 'SN') and a union select for retrieving the columns (SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM table_name', NULL, 1);) in mssql_db.py. Did you not encounter the same issue?

Op dinsdag 2 december 2025 om 10:57:44 UTC+1 schreef Dean D. Babic:

[Dean D. Babic]

Hi W,

this is true for indexes and constraints as well. Some ppl are also using views, which is the same.
I guess some work will be always with the DBA. Re synonyms, this is quite normal for some Apps, 

no user can connect directly to a DB schama.

This worked well for me, I can even create records for the lookup.

Hope this helps.