MSSQL database introspection failing? Unable to resolve table, unable to resolve column
Followed by 3 people
HI,
I setup new connection to a MSSQL Azure AG database. I am able to connect fine, and when I sync all the schemas, it appears to complete introspection.. however I am finding that none of the code completion works, and I am getting many errors and warnings.
When I try to force refresh the database - I get several of these errors:
[HY010] Preparing the statement failed: Output parameters have not yet been processed. Call getMoreResults().
And in the Editor window, even though I am attached to the database:schema of interest, I am getting errors for every table and column:
Unable to resolve table 'TABLE':line number
Unable to resolve column 'COLUMN':line number
Interestingly the database tree is fully populated on the database explorer window with the :
Server:database:schema:tables:columns
This data is just not available when trying to create a query script.
Any assistance would be most appreciated.
ADDITIONAL SETTINGS
Data Sources Options:
- Introspection: Auto-sync - checked
- Introspection: Load sources for: All excl. system schemas
- Introspection: Use Session template: none
- Introspection:Warn when editing outdated DDL - checked
- Introspection: Track DB /schemas creation and deletion - checked
- Introspection:Use pre-introspected objects for system catalogs that are not introspected - checked
- Schemas: All Databases - Checked
- Schemas: Schema pattern *:*
Please sign in to leave a comment.
"[HY010] Preparing the statement failed" error does not say anything specific. Could you share the logs to check the exact error? You can upload them to https://uploads.jetbrains.com/ and share the upload ID here.
Please share the logs archive (Help | Collect Logs and Diagnostic Data) and SQL logs (Help | Show SQL Logs in Finder) after reproducing the issue.
thank you
Upload id: 2022_08_26_QmDtuvAijGeZPFDPqYQfRn
Upload id: 2022_08_26_HjGKN5WtcUjkMHiKED2bSZ (file: database.0.log)
See 9587 string of the database.0.log file. Seems it is permissions problem:
-- 2022-08-25 16:21:37 [Microsoft SQL AG Project] [@*****] [introspection] [session id: 1270927795] [statement id: 309495721] error - The server principal "UMMS\user" is not able to access the database "database" under the current security context.
I met with our DBAs.. and we figure out how to replicate but not how to fix. Please see upload images.
For some reason, even though MSSQL is setup to specify the correct default schema, in DG it is showing something else (DBO)
If I specify the SCHEMA.TABLE.COLUMN than it works fine.
If I only enter the TABLE.COLUMN than it fails.
Can you advise the cause, and is there a way to force a default schema for a project to alleviate this issue and avoid having to clutter my queries with schemas for every table call?
Upload ID: 2022_08_26_xu7zW8TkeTmfRffJbjuvrP
But the default schema is not about the tool, but rather about a database, isn't it?
https://stackoverflow.com/questions/8208019/how-to-set-the-default-schema-of-a-database-in-sql-server-2005#:~:text=In%20that%20case%2C%20just%20use,change%20the%20default%20schema%20there.