Azure Synapse Function in SSMS

Question

I have create the Function (i.e., XYZ) in Azure Synapse database and when I am connecting Azure Synapse DB via SQL SSMS studio credentials this Function (i.e, XYZ) is not Visible.

Answer 1

Hello

When you create a function in Azure Synapse Database, it should indeed be visible when connecting via SQL Server Management Studio (SSMS).

1.Function Visibility:

If your function (let’s call it XYZ) is not visible, there are a few steps you can take to diagnose the problem.

First, ensure that you are connecting to the correct database within your Synapse workspace.

Verify that you are using the correct credentials (username and password) to connect to the database.

 

2.Querying for Function Definition:

Since sp_helptext is not supported in Synapse, you can use the view sys.sql_modules to retrieve the SQL text for your function.

Run the following query in SSMS:

SELECT * FROM sys.sql_modules WHERE definition LIKE '%XYZ%'

Replace XYZ with the actual name of your function.

This query will return the definition of the function, including the SQL code used to create it.

 

3.Alternative Approach:

Another way to retrieve the function definition is by using the OBJECT_DEFINITION function. For example:

SELECT OBJECT_DEFINITION(object_id), * FROM sys.views WHERE is_ms_shipped = 0;

Again, replace object_id with the actual ID of your function.

 

4.Check Permissions:

Ensure that your user account has the necessary permissions to view functions. You might need to be added to the workspace administrator role or the SQL administrator role in your Azure Synapse workspace.

 

5.Network and Firewall Settings:

Verify that your network allows communication to the Azure Synapse Analytics back-end.

Check if TCP port 1443 is blocked. If so, unblock this port to allow serverless SQL pool to work.

If you’re using a firewall or private endpoints, review those settings as well.

 

6.Serverless SQL Pool Activation:

If you don’t see databases created in the serverless SQL pool, execute any query (e.g., SELECT 1) to activate the pool. This will make the databases appear.

 

If you encounter further issues, consider using Azure Data Studio or SQL Server Management Studio for additional investigation.