2

Following this doc View or Change the Default Locations for Data and Log Files for SQL Server 2016. I am attempting a simple change of location for data/log/backup files in MSSM.

  • Connect in Object Explorer
  • Login to localdb
  • Right-click on localdb instance and choose Properties
  • Choose Database Settings
  • Attempt to change file paths (single/multiple instances) to a subfolder (with rights) of default folder

Logged into database using Windows Authentication using the following credentials:

  • (localdb)\MSSQLLocalDB
  • Laptop\user

I am using the following version of Microsoft SQL Server Management SQL (Have tried this both in standard/(Administrator) modes)

Server Management Studio                        15.0.18424.0
SQL Server Management Objects (SMO)                     16.100.47021.0+7eef34a564af48c5b0cf0d617a65fd77f06c3eb1
Microsoft Analysis Services Client Tools                        15.0.19750.0
Microsoft Data Access Components (MDAC)                     10.0.19041.2604
Microsoft MSXML                     3.0 6.0 
Microsoft .NET Framework                        4.0.30319.42000
Operating System                        10.0.19044

Error message from here:

===================================

Alter failed for Settings 'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Settings&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
   at Microsoft.SqlServer.Management.SqlManagerUI.ServerPropDBSettings.SendDataToServer()
   at Microsoft.SqlServer.Management.SqlManagerUI.ServerPropDBSettings.OnRunNow(Object sender)
   at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
   at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext, Boolean executeForAlter)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

===================================

RegCreateKeyEx() returned error 5, 'Access is denied.' (.Net SqlClient Data Provider)

------------------------------
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-22002-database-engine-error

------------------------------
Server Name: (localdb)\MSSQLLocalDB
Error Number: 22002
Severity: 16
State: 1


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

Results from error message help suggestions

  • first "For help" link leads to advertising
  • second database errors results in 404 error
  • follow links of 404 page to database errors listed by number
  • using quoted error number Error Number: 22002 for SQL Server 2016 I find there isn't one?

Even using the provided script for doc Database Engine events and errors (22000 to 22999) does not produce an explanation for Error Number: 22002

Does anyone have an idea for what more I could do? Any would be appreciated.

5
  • Your first link in your post seems to be broken if you are saying this is something you are trying because I see this when I click on it: i.imgur.com/qpAnmon.png. Seems to be related to a service account not having sufficient permissions to start or set something though. Commented Mar 5, 2023 at 6:19
  • Have you tried on the latest version of SSMS? Commented Mar 5, 2023 at 12:42
  • I'm now thinking this is more a Windows registry issue. Windows NOT allowing SQL Server permissions on the Windows registry keys. In my use-case I was just trying to change three file paths. But that means SQL Server is required to issue a command to the Windows registry. The error message says: An exception occurred while executing a Transact-SQL statement or batch. So, I am trying to track down the issue using Process Monitor v3.92 I appreciate the suggestions above. It's just I don't think they are helpful.
    – Dave
    Commented Mar 5, 2023 at 18:44
  • What exactly are you trying to do? Please explain, show the logic, and/or explain the steps and confirm with which tool you are using to perform the operation generating the error. Note: Unhelpful comments deleted, no worries! Commented Mar 18, 2023 at 2:07
  • I do have suggestions, explain what you are doing and how you are doing the "operation" in which you get the error. Running some code, clicking some UI option, etc. Trying to change 3 files paths to what, a database file? If you want to change an OS level path, use the OS and not SQL, that's a security concern/common sense to have your SQL Server security configured without more than it needs for its purpose, and that's not to modify OS level stuff. If you are trying to change a file path do a DB, you are likely doing it wrong is my assumption though based on lack of necessary detail. Commented Mar 18, 2023 at 2:10

0