search

martinjw / dbschemareader

Read database metadata (from SqlServer/Oracle/MySql/SQLite/PostgreSql/DB2 etc) into one simple model
Microsoft Public License
293 stars 128 forks source link

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

Closed kwkaiwang closed 2 years ago

kwkaiwang commented 2 years ago

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 2 years ago

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 2 years ago

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

martinjw commented 2 years ago

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 2 years ago

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 2 years ago

Also affecting other tools- https://github.com/red-gate/SQLSearch-ADS-Issues/issues/65

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

martinjw commented 2 years ago

New release https://github.com/martinjw/dbschemareader/releases/tag/2.7.11.6