DatabaseMetaData getTypeInfo() failure

Question

Cannot call getTypeInfo() (from DatabaseMetaData object) due to error:

EXECUTE statement is not supported

Sample Java code:

connection = DriverManager.getConnection( url , username, password ); 
DatabaseMetaData dmd = connection.getMetaData(); 
ResultSet resultSet = dmd.getTypeInfo();

Detailed log shows failure for

SQLServerStatement:1 Executing (not server cursor) sp_datatype_info_100 @ODBCVer=3

From SQL:

exec sp_datatype_info_100 @ODBCVer=3

Msg 40000, Level 16, State 1, Line 1 "'EXECUTE' statement is not supported. Line:1, Position:1

The connection is against Azure cloud service, cannot grant rights:

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master

and cannot change current database into master (Azure cloud service has access only to user db)

Accepted Answer

The EXECUTE statement being restricted due to the nature of the Azure SQL Database, which doesn't allow certain operations that would typically be permissible in an on-premises SQL Server environment.

The root cause here seems to be that the getTypeInfo() method internally calls the sp_datatype_info_100 stored procedure, which requires specific permissions that are restricted in Azure SQL Database. Azure SQL Database operates with limited access to system procedures and master databases for security and scalability reasons.

You can manually query system views that provide similar information to what getTypeInfo() returns. You can query the INFORMATION_SCHEMA views such as INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.DOMAINS, which can give you type information available within your database.


SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE 

FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_SCHEMA = 'your_schema_name';

This won't give you all the details that getTypeInfo() provides (especially for user-defined types), but it can serve as a workaround for common data types.

You can also execute queries against Azure-specific system views, such as sys.types, sys.columns, and sys.objects, which might provide more details on the data types. For example:


SELECT name AS TypeName, system_type_id, user_type_id, max_length, precision, scale 

FROM sys.types;

Depending on your use case, some JDBC drivers offer alternative ways to fetch type information without relying on restricted stored procedures. You may want to check the documentation of the specific JDBC driver you're using for Azure SQL Database to see if they offer any alternative methods.

If the lack of access to these stored procedures is a critical limitation for your project, you might consider switching to Azure SQL Managed Instance, which offers broader compatibility with on-premises SQL Server features. This service provides more permissions, allowing for features like those invoked by getTypeInfo().

If none of these options work for your particular case, you might need to contact Azure Support to see if there is a service-level workaround or an upcoming feature that might address this limitation.

Share via

DatabaseMetaData getTypeInfo() failure

0 additional answers

Your answer