how to access SSMS table using JDBC with Python

Question

how to access SSMS table using JDBC with Python

Miroslav Muras 25

Service:

Using Azure Synapse Python Spark.

Scenario:

While Reading data from SSMS using Azure Synapse Spark, I'm getting an “Invalid object name” error.

Result:

Trying to access /SELECT existing table in the SSMS. When executed, I get an "Invalid object name" error. The table is in the DB as External Table and a View table. Can you please direct me to troubleshoot/Debug? Thank you.

User's image

2 answers

Your answer

Answer 1

Erland Sommarskog 120.2K MVP

I don't work much with Synapse, so I cannot be that much of help. But I notice one this which is entirely wrong. You talk about an "SSMS table". There are no tables in SSMS. SSMS is only a tool talk to database servers like SQL Server or Azure Synapse.

The table Table_FaultCodeRelatedRepairs_Vw may be visible in SSMS, but that does not mean that you access it from your serverless pool. For all we know, it could be located in an SQL Server instance elsewhere. You will need investigate this a little more closely.

Miroslav Muras 25 Reputation points

2023-06-27T13:45:30.39+00:00
@Erland Sommarskog thank you very much for your quick response. The answer you provided is correct. There are no Tables in SSMS. SSMS is a tool that allows you to connect to and manage database servers. That was the wrong wording on my part, but why can I manage to connect/access/return data from another DB located on the same SQL server instance using the same connection script query is the core of my question. Please guide me to troubleshoot/debug if you can. Again, thank you!!

I am using JDBC driver

jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2}".format(HOSTNAME,PORT,DBNAME)
Erland Sommarskog 120.2K Reputation points MVP

2023-06-27T21:04:18.63+00:00

This is out of my league, since I don't use Synapse Serverless pools. But I'm trying to piece together what you are saying.

While Reading data from SSMS using Azure Synapse Spark

I didn't know you can do that. Maybe you can, but the screenshot you have seems more like Synapse Studio to me. Certainly not like SSMS. (But since I don't work with serverless pools, I might be wrong.)

That was the wrong wording on my part, but why can I manage to connect/access/return data from another DB located on the same SQL server instance using the same connection script query is the core of my question.

What SQL Server instance? When you say "SQL Server instance" that makes me think on an instance on-prem. But why would you use PySpark from Azure Synapse to query an SQL Server on-prem.

Sorry, that I am picking on terminology again, but if your post is inaccurate, it is more difficult to understand what you want to do.
Miroslav Muras 25 Reputation points

2023-06-28T13:57:39.8733333+00:00

@Erland Sommarskog thank you! I appreciate your time and help.

The Python script in Azure Synapse defines the connection parameters for the SQL Server database. Then, with a SparkSession and JDBC SQL server driver, I create a DataFrame from the SQL Server table. The issue is that I can connect/access/read one of the tables on the server but not the others.
Erland Sommarskog 120.2K Reputation points MVP

2023-06-28T20:44:27.7966667+00:00

But in this screenshot you are pointing to databases, but at the same time you talk about tables?

It could be as simple as a permission issue. Have you talked with whomever is responsible for the security on the Synapse service?
Miroslav Muras 25 Reputation points

2023-06-28T21:22:32.47+00:00

@Erland Sommarskog, that's correct! Because I'm trying to connect/access/read from DB Table. Something is not resonating in this communication, and I think we are going in the loop here. Perhaps my description is not clear. I truly appreciate your time and help. Anyway, I found a way to go around this issue. Instead of using the JDBC protocol to access data/tables from the database, I used the ABFSS protocol for accessing the data/table from Azure Blob Storage. I was using the Azure Synapse Spark to connect and create a dataframe to do the required analysis.

Answer 2

Ensure that the table name is correct and that it exists in the database. The "Invalid object name" error usually occurs when the table name is incorrect or the table does not exist in the database.
Ensure that the table name is correct and that it exists in the database. The "Invalid object name" error usually occurs when the table name is incorrect or the table does not exist in the database.
Check if the user has the necessary permissions to access the table. The user must have the SELECT permission on the table to read data from it. You can grant the SELECT permission to the user by executing the following command in SSMS:

GRANT SELECT ON <table_name> TO <user_name>

Check if the table is an External Table or a View table. If the table is an External Table, you need to ensure that the data source is configured correctly. You can refer to the following documentation for more information on how to create and configure External Tables in Azure Synapse Analytics:
Check if the table is an External Table or a View table. If the table is an External Table, you need to ensure that the data source is configured correctly. You can refer to the following documentation for more information on how to create and configure External Tables in Azure Synapse Analytics:

Use external tables with Synapse SQL

I hope this helps! Kindly accept the answer if it's helpful to you. Thankyou

Share via

how to access SSMS table using JDBC with Python

2 answers

Your answer