how to run azure sql select query in onpremise SSMS

Question

how can i run azure sql "select query" in on premise sql server? or how can i see the azure sql tables data in on-premise sql server.

ex: select * from table_name; --azure sql query

i want to run the above select query in on-premise sql server?

Answer 1

Hey,

You can create a linked server on your on prem server connecting to the Azure SQL database.

And via linked server you can query the Azure SQL database tables.

Sample : Step #1 in below blog

https://datasharkx.wordpress.com/2022/10/01/event-trigger-data-sync-from-sql-server-to-synapse-via-azure-data-factory-synapse-pipeline/

Answer 2

Bommisetty, Rakesh (Hudson IT Consultant) Thank you for reaching out.

To see the azure SQL tables data in on-premises SQL server.

1. Open SSMS
2. Connect by selecting in the drop-down azure server name. You can use the Azure SQL Server Management Studio to connect to your Azure SQL Server and view the table data directly. You will need to have the necessary permissions to access the Azure SQL Server from your on-premises SQL Server.
3. Select the authentication type.
4. Then connect

Alternatively, you can also do this:

To query the Azure SQL tables, you can use the four-part naming convention. For example:

SELECT * FROM AzureSQL.database_name.schema_name.table_name by creating a linked server in SSMS.

• In SSMS, create a new Linked Server by right-clicking on "Linked Servers" and selecting "New Linked Server".
• In the "New Linked Server" dialog box, provide the following information:
• Linked Server:
• Server type: Select "SQL Server".
• Provider: Select "Microsoft OLE DB Provider for SQL Server" from the drop-down list
• Product name: Enter "AzureSQL"
• Data source: Enter the Azure SQL Server name and instance.
• Provider string: Enter the connection string for the Azure SQL Server.
• Click OK to create the Linked Server.

Please let us know if that works for you.

Regards,

Oury