martinjw/dbschemareader

'OBJECT_DEFINITION' is not a recognized built-in function name.

Closed this issue · 6 comments

kwkaiwang commented

Got below exception when using with Azure Synapse (Azure Data Warehouse):

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message='OBJECT_DEFINITION' is not a recognized built-in function name.
  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at DatabaseSchemaReader.ProviderSchemaReaders.Databases.SqlExecuter.ExecuteDbReader(IConnectionAdapter connectionAdapter)
   at DatabaseSchemaReader.ProviderSchemaReaders.Databases.SqlServer.Triggers.Execute(IConnectionAdapter connectionAdapter)
   at DatabaseSchemaReader.ProviderSchemaReaders.Adapters.SqlServerAdapter.Triggers(String tableName)
   at DatabaseSchemaReader.ProviderSchemaReaders.Builders.TableBuilder.Execute(CancellationToken ct)
   at DatabaseSchemaReader.DatabaseReader.AllTables(CancellationToken ct)
   at DatabaseSchemaReader.DatabaseReader.ReadAll(CancellationToken ct)
   at DatabaseSchemaReader.DatabaseReader.ReadAll()
   at OData.DynamicModel.SqlDataSource.GetEdmModel() in C:\src\herbalife\hlfdmp\api\DataPlatform.Api\OData\DynamicModel\SqlDataSource.cs:line 72
   at OData.DynamicModel.DataSourceProvider..ctor(IServiceProvider serviceProvider, IOptionsSnapshot`1 options, ApplicationPartManager partManager) in C:\src\herbalife\hlfdmp\api\DataPlatform.Api\OData\DynamicModel\DataSourceProvider.cs:line 39
   at Autofac.Core.Activators.Reflection.BoundConstructor.Instantiate()

  This exception was originally thrown at this call stack:
    Microsoft.Data.SqlClient.SqlConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action<System.Action>)
    Microsoft.Data.SqlClient.SqlInternalConnection.OnError(Microsoft.Data.SqlClient.SqlException, bool, System.Action<System.Action>)
    Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(Microsoft.Data.SqlClient.TdsParserStateObject, bool, bool)
    Microsoft.Data.SqlClient.TdsParser.TryRun(Microsoft.Data.SqlClient.RunBehavior, Microsoft.Data.SqlClient.SqlCommand, Microsoft.Data.SqlClient.SqlDataReader, Microsoft.Data.SqlClient.BulkCopySimpleResultSet, Microsoft.Data.SqlClient.TdsParserStateObject, out bool)
    Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    Microsoft.Data.SqlClient.SqlDataReader.MetaData.get()
    Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(Microsoft.Data.SqlClient.SqlDataReader, Microsoft.Data.SqlClient.RunBehavior, string, bool, bool, bool)
    Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(System.Data.CommandBehavior, Microsoft.Data.SqlClient.RunBehavior, bool, bool, int, out System.Threading.Tasks.Task, bool, bool, Microsoft.Data.SqlClient.SqlDataReader, bool)
    Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior, Microsoft.Data.SqlClient.RunBehavior, bool, System.Threading.Tasks.TaskCompletionSource<object>, int, out System.Threading.Tasks.Task, out bool, bool, bool, string)
    Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(System.Data.CommandBehavior, Microsoft.Data.SqlClient.RunBehavior, bool, string)
    ...
    [Call Stack Truncated]
martinjw commented

OBJECT_DEFINITION is a built in function, even in Azure Sql... perhaps a security/permissions thing in Synapse? I have to get this set up on Azure to try it....

kwkaiwang commented

It's not a permission issue, seems that triggers related logic is not compatible with Synapse.

martinjw commented

There's a test release https://github.com/martinjw/dbschemareader/releases/tag/2.7.11.5

Still not sure of what the actual issue is, but this may help. Drop in the new dll and see if it gets further.

kwkaiwang commented

Still got exception related to Triggers:

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Catalog view 'triggers' is not supported in this version.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at DatabaseSchemaReader.ProviderSchemaReaders.Databases.SqlExecuter.ExecuteDbReader(IConnectionAdapter connectionAdapter) in /work/4/s/DatabaseSchemaReader/ProviderSchemaReaders/Databases/SqlExecuter.cs:line 35
   at DatabaseSchemaReader.ProviderSchemaReaders.Databases.SqlServer.Triggers.Execute(IConnectionAdapter connectionAdapter) in /work/4/s/DatabaseSchemaReader/ProviderSchemaReaders/Databases/SqlServer/Triggers.cs:line 98
   at DatabaseSchemaReader.ProviderSchemaReaders.Adapters.SqlServerAdapter.Triggers(String tableName) in /work/4/s/DatabaseSchemaReader/ProviderSchemaReaders/Adapters/SqlServerAdapter.cs:line 138
   at DatabaseSchemaReader.ProviderSchemaReaders.Builders.TableBuilder.Execute(CancellationToken ct) in /work/4/s/DatabaseSchemaReader/ProviderSchemaReaders/Builders/TableBuilder.cs:line 118
   at DatabaseSchemaReader.DatabaseReader.AllTables(CancellationToken ct) in /work/4/s/DatabaseSchemaReader/DatabaseReader.cs:line 317
   at DatabaseSchemaReader.DatabaseReader.ReadAll(CancellationToken ct) in /work/4/s/DatabaseSchemaReader/DatabaseReader.cs:line 194
martinjw commented

Also affecting other tools- red-gate/SQLSearch-ADS-Issues#65

I think I will just trap the error and carry on with no triggers- not much we can do otherwise...