Options
Unhandled Exception
moff
Posts: 34
One of our developers gets this error when caching a DB with SQLPrompt on a box on which they have restricted access.
Interestingly if the cache db entry is deleted first the error is handled better and returned in a SQL Prompt error box, but the gist is still the same.
This is the error:
Using SQL Profiler I think this is the statement, and it ties in with the error message
when I run it, it pulls back all the data. When the developer runs it the Text column is NULLs.
I'm guessing this is to do with permissions, since I know SQL 2005 locks down a lot more of the view privileges.
The developer is in the db_datareader role.
I'm not sure how SQL Prompt should handle this, since a user not having access to some of the system data is valid, but certainly barfing isn't the best
Versions:
- Microsoft SQL Server Management Studio 9.00.3042.00
- SQL Prompt 3.5.0.333
Cheers, Robin.
Interestingly if the cache db entry is deleted first the error is handled better and returned in a SQL Prompt error box, but the gist is still the same.
This is the error:
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
************** Exception Text **************
System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
at _12._21(SqlConnection )
at _12._1(_11 )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects)
at _130._2(IAuthenticationTokenProvider )
at _130.Refresh(IAuthenticationTokenProvider provider)
at _35.DoTask()
at RedGate.SQLPrompt.CommonUI.TaskExecuter.Executer._1()
************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
AppIDPackage
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/AppIDPackage.DLL
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
Microsoft.SqlServer.SqlTools.VSIntegration
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/Microsoft.SqlServer.SqlTools.VSIntegration.DLL
----------------------------------------
Microsoft.VisualStudio.Shell.Interop
Assembly Version: 7.1.40304.0
Win32 Version: 7.0.4054
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.Shell.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.Interop.dll
----------------------------------------
Microsoft.VisualStudio.OLE.Interop
Assembly Version: 7.1.40304.0
Win32 Version: 7.0.4054
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.OLE.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.OLE.Interop.dll
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
Microsoft.SqlServer.SqlTDiagM
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1399.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.SqlTDiagM/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.SqlTDiagM.dll
----------------------------------------
SqlWorkbench.Interfaces
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/SqlWorkbench.Interfaces.DLL
----------------------------------------
Microsoft.DataWarehouse.SQM
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/Microsoft.DataWarehouse.SQM.DLL
----------------------------------------
Microsoft.SqlServer.Instapi
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1399.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.Instapi/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.Instapi.dll
----------------------------------------
RedGate.SQLPrompt.SSMSUI
Assembly Version: 3.5.0.333
Win32 Version: 3.5.0.333
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SQLPrompt.SSMSUI.dll
----------------------------------------
RedGate.SQLPrompt.CommonVS
Assembly Version: 3.5.0.333
Win32 Version: 3.5.0.333
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SQLPrompt.CommonVS.DLL
----------------------------------------
Extensibility
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///C:/WINDOWS/assembly/GAC/Extensibility/7.0.3300.0__b03f5f7f11d50a3a/Extensibility.dll
----------------------------------------
EnvDTE
Assembly Version: 8.0.0.0
Win32 Version: 8.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC/EnvDTE/8.0.0.0__b03f5f7f11d50a3a/EnvDTE.dll
----------------------------------------
Microsoft.VisualStudio.TextManager.Interop
Assembly Version: 7.1.40304.0
Win32 Version: 7.0.4054
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.TextManager.Interop/7.1.40304.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.TextManager.Interop.dll
----------------------------------------
RedGate.SqlPrompt.Engine
Assembly Version: 3.5.0.333
Win32 Version: 3.5.0.333
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SqlPrompt.Engine.DLL
----------------------------------------
RedGate.SQLPrompt.CommonUI
Assembly Version: 3.5.0.333
Win32 Version: 3.5.0.333
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SQLPrompt.CommonUI.DLL
----------------------------------------
log4net
Assembly Version: 1.2.10.0
Win32 Version: 1.2.10.0
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/log4net.DLL
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
RedGate.Licensing.Client
Assembly Version: 1.3.3.2
Win32 Version: 1.3.3.2
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.Licensing.Client.DLL
----------------------------------------
RedGate.SQL.Shared
Assembly Version: 6.0.0.95
Win32 Version: 6.0.0.95
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SQL.Shared.DLL
----------------------------------------
RedGate.Shared.Utils
Assembly Version: 6.0.0.95
Win32 Version: 6.0.0.95
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.Shared.Utils.DLL
----------------------------------------
Microsoft.SqlServer.Management.Reports
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/Microsoft.SqlServer.Management.Reports.DLL
----------------------------------------
EnvDTE80
Assembly Version: 8.0.0.0
Win32 Version: 8.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC/EnvDTE80/8.0.0.0__b03f5f7f11d50a3a/EnvDTE80.dll
----------------------------------------
RedGate.SQLPrompt.vsmso
Assembly Version: 8.0.0.0
Win32 Version: 8.0.0.0
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SQLPrompt.vsmso.DLL
----------------------------------------
CustomMarshalers
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/CustomMarshalers/2.0.0.0__b03f5f7f11d50a3a/CustomMarshalers.dll
----------------------------------------
RedGate.SqlPrompt.Engine.Gatekeeper
Assembly Version: 3.5.0.333
Win32 Version: 3.5.0.333
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SqlPrompt.Engine.Gatekeeper.DLL
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
RedGate.SQLCompare.Engine
Assembly Version: 5.8.1.31
Win32 Version: 5.8.1.31
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SQLCompare.Engine.DLL
----------------------------------------
ObjectExplorer
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/ObjectExplorer.DLL
----------------------------------------
SqlMgmt
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/SqlMgmt.DLL
----------------------------------------
ConnectionDlg
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/ConnectionDlg.DLL
----------------------------------------
Microsoft.VisualStudio
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 built by: RTM
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualStudio/2.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.dll
----------------------------------------
Microsoft.VisualStudio.Shell
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualStudio.Shell/2.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.dll
----------------------------------------
Microsoft.VisualStudio.Shell.Interop.8.0
Assembly Version: 8.0.0.0
Win32 Version: 8.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.Shell.Interop.8.0/8.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Shell.Interop.8.0.dll
----------------------------------------
System.Drawing.Design
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing.Design/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.Design.dll
----------------------------------------
Microsoft.VisualStudio.Designer.Interfaces
Assembly Version: 1.0.5000.0
Win32 Version: 1.1.4322.573
CodeBase: file:///C:/WINDOWS/assembly/GAC/Microsoft.VisualStudio.Designer.Interfaces/1.0.5000.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Designer.Interfaces.dll
----------------------------------------
Microsoft.SqlServer.CustomControls
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.CustomControls/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.CustomControls.dll
----------------------------------------
Microsoft.SqlServer.RegSvrEnum
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.RegSvrEnum/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.RegSvrEnum.dll
----------------------------------------
Accessibility
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
----------------------------------------
Microsoft.NetEnterpriseServers.ExceptionMessageBox
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.NetEnterpriseServers.ExceptionMessageBox/9.0.242.0__89845dcd8080cc91/Microsoft.NetEnterpriseServers.ExceptionMessageBox.dll
----------------------------------------
Microsoft.SqlServer.ConnectionInfo
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.ConnectionInfo/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.ConnectionInfo.dll
----------------------------------------
Microsoft.SqlServer.SmoEnum
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.SmoEnum/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.SmoEnum.dll
----------------------------------------
System.Transactions
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------
Microsoft.SqlServer.Smo
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.Smo/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.Smo.dll
----------------------------------------
Microsoft.SqlServer.SqlEnum
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.SqlEnum/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.SqlEnum.dll
----------------------------------------
Microsoft.SqlServer.BatchParser
Assembly Version: 9.0.242.0
Win32 Version: 2005.090.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/Microsoft.SqlServer.BatchParser/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.BatchParser.dll
----------------------------------------
msvcm80
Assembly Version: 8.0.50608.0
Win32 Version: 8.00.50727.42
CodeBase: file:///C:/WINDOWS/WinSxS/x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_0DE06ACD/msvcm80.dll
----------------------------------------
ObjectExplorerReplication
Assembly Version: 9.0.242.0
Win32 Version: 9.00.1399.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/ObjectExplorerReplication.DLL
----------------------------------------
System.Management
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Management/2.0.0.0__b03f5f7f11d50a3a/System.Management.dll
----------------------------------------
SQLEditors
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/SQLEditors.DLL
----------------------------------------
RedGate.SQLCompare.ASTParser
Assembly Version: 5.8.1.31
Win32 Version: 5.8.1.31
CodeBase: file:///C:/Program%20Files/Red%20Gate/SQL%20Prompt%203/RedGate.SQLCompare.ASTParser.DLL
----------------------------------------
Microsoft.VisualStudio.Package.LanguageService
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.42 (RTM.050727-4200)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.VisualStudio.Package.LanguageService/2.0.0.0__b03f5f7f11d50a3a/Microsoft.VisualStudio.Package.LanguageService.dll
----------------------------------------
Microsoft.SqlServer.GridControl
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.GridControl/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.GridControl.dll
----------------------------------------
Microsoft.SqlServerCe.Client
Assembly Version: 9.0.242.0
Win32 Version: 3.0.5300.0
CodeBase: file:///C:/Program%20Files/Microsoft%20SQL%20Server/90/Tools/binn/VSShell/Common7/IDE/Microsoft.SqlServerCe.Client.DLL
----------------------------------------
Microsoft.SqlServer.DataStorage
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DataStorage/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DataStorage.dll
----------------------------------------
Microsoft.SqlServer.DlgGrid
Assembly Version: 9.0.242.0
Win32 Version: 9.00.3042.00
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Microsoft.SqlServer.DlgGrid/9.0.242.0__89845dcd8080cc91/Microsoft.SqlServer.DlgGrid.dll
----------------------------------------
System.Web
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.210 (QFE.050727-2100)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Web/2.0.0.0__b03f5f7f11d50a3a/System.Web.dll
----------------------------------------
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
Using SQL Profiler I think this is the statement, and it ties in with the error message
SELECT so.name AS TableName, sys.schemas.name AS TableOwner, cc.name AS ConstraintName, cc.object_id AS Constraint_id, cc.is_not_for_replication AS NotForReplication, cc.is_not_trusted AS WithNoCheck, cc.is_disabled AS Disabled, sc.name AS ColumnName, cc.parent_column_id AS colid, cc.definition AS Text FROM sys.check_constraints cc WITH (NOLOCK) INNER JOIN sys.objects so WITH (NOLOCK) ON so.object_id=cc.parent_object_id LEFT JOIN sys.schemas WITH (NOLOCK) ON sys.schemas.schema_id=so.schema_id LEFT JOIN sys.columns sc ON sc.column_id=cc.parent_column_id AND sc.object_id=cc.parent_object_id ORDER BY so.name, cc.name
when I run it, it pulls back all the data. When the developer runs it the Text column is NULLs.
I'm guessing this is to do with permissions, since I know SQL 2005 locks down a lot more of the view privileges.
The developer is in the db_datareader role.
I'm not sure how SQL Prompt should handle this, since a user not having access to some of the system data is valid, but certainly barfing isn't the best
Versions:
- Microsoft SQL Server Management Studio 9.00.3042.00
- SQL Prompt 3.5.0.333
Cheers, Robin.
Comments
Thanks for reporting this. It's been filed as a bug and fixed this morning. It'll be available in the 3.6 final release, which we're now thinking will be out early-mid next week. We've made a couple of other fixes as well so we need some additional test time.
Cheers,
Bart
Principal Consultant
bartread.com Ltd