search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.58k stars 905 forks source link

Azure Data Studio: no longer able to 'select top 1000' from table #25321

Open caobhin opened 9 months ago

caobhin commented 9 months ago

when using Azure Data Studio, with a specific DB connection, I get a consistent error now when trying to view the contents of a table from the database view. See attachments for context.

Azure data studio error

ADE - top 1000 error ADE select top 1000

ADSlogs.txt 20240213T120321.zip

This was working correctly for several months, but started failing a month or two ago. Thanks in advance

Steps to Reproduce:

  1. open ADS, choose specific azure DB
  2. right-click a table and choose 'show 10000 records'
  3. observe error

Does this issue occur when all extensions are disabled?: Yes/No yes

cheenamalhotra commented 9 months ago

Pasting logs for investigation:

24-02-13 12:04:47.4036703 pid:7132 tid:21 sqltools Verbose: 0 : eventTID:10576 SqlCommand.RunExecuteReaderTds | Info | Object Id 60, Activity Id dff6c417-c8bd-405b-900f-7fd78e6cb133:4, Client Connection Id f0016cf9-95b8-4eb5-9eed-fb473e7836e5, Command executed as SQLBATCH, Command Text 'DECLARE @sid varbinary(max) 
                DECLARE @name varchar(max)
                SET @sid = 0x0106000000000164000000000000000039265E897765E544ADA778F0880590C6
                set @name = CAST(@sid as UNIQUEIDENTIFIER)
                select @name = name from sys.database_principals WITH (NOLOCK) where sid=@sid
                IF @@ROWCOUNT = 0
                BEGIN
                  select @name = name from sys.sql_logins WITH (NOLOCK) where sid=@sid
                END
                select @name' 
24-02-13 12:04:47.5744370 pid:7132 tid:21 sqltools Verbose: 0 : eventTID:10576 SqlError.ctor | ERR | Info Number 229, Error State 5, Error Class 14, Error Message 'The SELECT permission was denied on the object 'sql_logins', database 'master', schema 'sys'.', Procedure '', Line Number 8
24-02-13 12:12:40.5271020 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptOptions could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5273910 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptAnsiPadding could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5276622 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.AppendToFile to value False
24-02-13 12:12:40.5279611 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.ContinueScriptingOnError to value True
24-02-13 12:12:40.5281906 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ConvertUDDTToBaseType could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5283509 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name GenerateScriptForDependentObjects could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5284962 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name IncludeDescriptiveHeaders could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5287075 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.IncludeIfNotExists to value False
24-02-13 12:12:40.5289284 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name IncludeVarDecimal could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5290879 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptDriIncludeSystemNames could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5292330 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name IncludeUnsupportedStatements could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5294428 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.SchemaQualify to value True
24-02-13 12:12:40.5296664 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.Bindings to value False
24-02-13 12:12:40.5298775 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name Collation could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5300765 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.Default to value True
24-02-13 12:12:40.5302358 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptCreateDrop could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5303860 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptExtendedProperties could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5305132 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptCompatibilityOption could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5456738 pid:7132 tid:45 MicrosoftSqlToolsServiceLayer Warning: 0 : Missing regular property Value property bag state Lazy for type DatabaseScopedConfiguration
24-02-13 12:12:40.5457670 pid:7132 tid:45 MicrosoftSqlToolsServiceLayer Information: 0 : 2024-02-13T12:12:40.5457616-05:00 - get data for urn: Server[@Name='sql-dp-qas']/Database[@Name='sql-dp-qas']/DatabaseScopedConfiguration[@Name='Legacy_Cardinality_Estimation']
24-02-13 12:12:40.5462689 pid:7132 tid:45 sqltools Verbose: 0 : eventTID:10688 SqlCommand.Set_CommandTimeout | API | ObjectId 162, Command Timeout value 600, Client Connection Id 201ef4db-4fda-41d8-bc5a-77699b500375
24-02-13 12:12:40.5465951 pid:7132 tid:45 sqltools Verbose: 0 : eventTID:10688 SqlCommand.RunExecuteReaderTds | Info | Object Id 162, Activity Id d5264091-dd19-48f9-a5d8-d30194bf43b1:5, Client Connection Id 201ef4db-4fda-41d8-bc5a-77699b500375, Command executed as RPC, RPC Name 'sp_executesql' 
24-02-13 12:12:40.5490546 pid:7132 tid:38 sqltools Warning: 0 : An exception occurred setting option TargetDatabaseEngineType to value SqlAzure: System.ArgumentException: Requested value 'SqlAzure' was not found.
   at System.Enum.TryParseByName(RuntimeType enumType, ReadOnlySpan`1 value, Boolean ignoreCase, Boolean throwOnFailure, UInt64& result)
   at System.Enum.TryParseInt32Enum(RuntimeType enumType, ReadOnlySpan`1 value, Int32 minInclusive, Int32 maxInclusive, Boolean ignoreCase, Boolean throwOnFailure, TypeCode type, Int32& result)
   at System.Enum.TryParse(Type enumType, ReadOnlySpan`1 value, Boolean ignoreCase, Boolean throwOnFailure, Object& result)
   at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)
   at Microsoft.SqlTools.SqlCore.Scripting.SmoScriptingOperation.PopulateAdvancedScriptOptions(ScriptOptions scriptOptionsParameters, Object advancedOptions) in /_/src/Microsoft.SqlTools.SqlCore/Scripting/SmoScriptingOperation.cs:line 181
24-02-13 12:12:40.5497371 pid:7132 tid:38 sqltools Warning: 0 : An exception occurred setting option TargetDatabaseEngineEdition to value SqlAzureDatabaseEdition: System.ArgumentException: Requested value 'SqlAzureDatabaseEdition' was not found.
   at System.Enum.TryParseByName(RuntimeType enumType, ReadOnlySpan`1 value, Boolean ignoreCase, Boolean throwOnFailure, UInt64& result)
   at System.Enum.TryParseInt32Enum(RuntimeType enumType, ReadOnlySpan`1 value, Int32 minInclusive, Int32 maxInclusive, Boolean ignoreCase, Boolean throwOnFailure, TypeCode type, Int32& result)
   at System.Enum.TryParse(Type enumType, ReadOnlySpan`1 value, Boolean ignoreCase, Boolean throwOnFailure, Object& result)
   at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)
   at Microsoft.SqlTools.SqlCore.Scripting.SmoScriptingOperation.PopulateAdvancedScriptOptions(ScriptOptions scriptOptionsParameters, Object advancedOptions) in /_/src/Microsoft.SqlTools.SqlCore/Scripting/SmoScriptingOperation.cs:line 181
24-02-13 12:12:40.5499176 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptLogins could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5500247 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptObjectLevelPermissions could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5502035 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.ScriptOwner to value False
24-02-13 12:12:40.5503314 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptStatistics could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5504323 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptUseDatabase could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5505378 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name TypeOfDataToScript could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5506470 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptChangeTracking could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5507458 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptCheckConstraints could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5508451 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptDataCompressionOptions could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5509524 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptForeignKeys could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5510510 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptFullTextIndexes could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5511575 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptIndexes could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5512582 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptPrimaryKeys could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5513659 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name ScriptTriggers could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5514640 pid:7132 tid:38 sqltools Warning: 0 : Invalid property info name UniqueKeys could not be mapped to a property on SqlScriptOptions.
24-02-13 12:12:40.5516112 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.AnsiPadding to value False
24-02-13 12:12:40.5517627 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.ConvertUserDefinedDataTypesToBaseType to value False
24-02-13 12:12:40.5519825 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.IncludeHeaders to value True
24-02-13 12:12:40.5522693 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.DriIncludeSystemNames to value False
24-02-13 12:12:40.5524195 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.SchemaQualifyForeignKeysReferences to value True
24-02-13 12:12:40.5526404 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.NoCollation to value True
24-02-13 12:12:40.5528107 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.DriDefaults to value True
24-02-13 12:12:40.5529973 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.ExtendedProperties to value True
24-02-13 12:12:40.5531661 pid:7132 tid:38 sqltools Verbose: 0 : Setting ScriptOptions.Permissions to value False
24-02-13 12:12:40.5534387 pid:7132 tid:38 sqltools Warning: 0 : An exception occurred setting option Statistics to value ScriptStatsNone: System.ArgumentException: Type provided must be an Enum. (Parameter 'enumType')
   at System.Enum.ValidateRuntimeType(Type enumType)
   at System.Enum.TryParse(Type enumType, ReadOnlySpan`1 value, Boolean ignoreCase, Boolean throwOnFailure, Object& result)
   at System.Enum.Parse(Type enumType, String value, Boolean ignoreCase)
   at Microsoft.SqlTools.SqlCore.Scripting.SmoScriptingOperation.PopulateAdvancedScriptOptions(ScriptOptions scriptOptionsParameters, Object advancedOptions) in /_/src/Microsoft.SqlTools.SqlCore/Scripting/SmoScriptingOperation.cs:line 181

Related code: https://github.com/microsoft/sqltoolsservice/blob/18eff5e2aa9c266d08475a37602121a4da347f02/src/Microsoft.SqlTools.SqlCore/Scripting/SmoScriptingOperation.cs#L159-L161

@shueybubbles anything you recall changed for ScriptOptions recently that wasn't ported over to STS ?

cheenamalhotra commented 9 months ago

@caobhin

What is the engine edition of this Azure database? Please run and share output for: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('edition')