search

microsoft / mssql-scripter

Repository for the new SQL cross-platform command line tools
Other
742 stars 148 forks source link

Workaround for scripting Azure DB fails with Set Database failed exception: Value cannot be null. Parameter name: database #250

Closed bbct closed 3 years ago

bbct commented 3 years ago

When scripting AzureDB, I used the -S, -d, -U, and -P parameters to specify the server, database, user and password.
like this: mssql-scripter -S myserver.database.windows.net -d myDBName -U myUserID -P MyPassword I get an exception like this: Scripting request submitted with request id: a5ee9260-c15e-4294-b0b8-3b9b168d7670 Scripting request: a5ee9260-c15e-4294-b0b8-3b9b168d7670 encountered error: Set Database failed. Error details: Microsoft.SqlServer.Management.Smo.FailedOperationException: Set Database failed. ---> System.ArgumentNullException: Value cannot be null. Parameter name: database --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.TransferBase.SetDatabase(Database database) at Microsoft.SqlServer.Management.Smo.Transfer..ctor(Database database) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.GetTransfer() at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.GetUrnList() at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishModel.GenerateScript(ScriptOutputOptions outputOptions) at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.Execute() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.ServiceLayer\Scripting\ScriptingScriptOperation.cs:line 64

There is an easy work around though! Simply use the --connection-string parameter instead of specifying the -S, -d, -U, and -P parameters separately. When I made this change, the tool worked perfectly!

mssql-scripter --connection-string "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=MyDBName;Persist Security Info=False;User ID=MyUserID;Password=MyPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

bbct commented 3 years ago

See the workaround in the initial issue message. Hopefully this helps someone else who ran into the same issue I did. No need to leave this issue open, as there is a work around. Would be nice if the error message led the user to this conclusion though, instead of just leaving them scratching their heads...