exception when connecting to Azure DW about mssql-scripter HOT 19 CLOSED

@sequentialogic - we recently fixed issue #76 that failed with the same stack trace. Can you confirm you are using the latest version 1.0.0a12 using the command:

mssql-scripter --version

To upgrade to the latest version, run command:

pip install --upgrade mssql-scripter

If the issue still repro's with the latest version, we'll have to further investigate as it seems our fix was not complete.

from mssql-scripter.

--version shows 1.0.0a12.

Was only installed yesterday after being alerted of DWH support in the azure portal :)

from mssql-scripter.

@sequentialogic - it seems to script DWH, you must specify the argument:

--target-server-version AzureDW

Sorry I didn't catch that before. Having to specify this argument is cumbersome, so we're looking into auto-detecting the target server version so this argument wouldn't be necessary in the future. We're tracking this in issue #44.

from mssql-scripter.

@sequentialogic , just following up. Did enabling the flag fix your issue?

from mssql-scripter.

Using the flag results in a different error:

Scripting request: 2b0b903d-582c-422f-a0bd-cb10e40c6ebd 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 85

from mssql-scripter.

@sequentialogic does the database name you used exist on the server name you provided?

from mssql-scripter.

Sorry, that was a typo on my end.

even with the AzureDW switch, I still get the below exception:

An error occurred while scripting the objects.
Error details: Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.MissingMethodException: Constructor on type 'Microsoft.SqlServer.Management.Smo.PostProcessUser' not found.
at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)
at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at System.Reflection.Assembly.CreateInstance(String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)
at Microsoft.SqlServer.Management.Smo.Util.CreateObjectInstance(Assembly assembly, String objectType)
at Microsoft.SqlServer.Management.Smo.SqlPostProcess.GetPostProcessInstance()

from mssql-scripter.

@sequentialogic - Can you try updating to the latest release? We found and fixed the PostProcessUser issue in version 1.0.0a13. The command to upgrade:

$ pip install --upgrade mssql-scripter

from mssql-scripter.

@sequentialogic just following up, are you able to connect successfully with the latest version?

FYI, we did release again, so our latest version is now 1.0.0a14

from mssql-scripter.

Upgraded to 1.0.0a15 and this now works :)

from mssql-scripter.

I'm an idiot and closed this too early, looks like there's a new issue, when writing to files (-f, --file-per-object), I get the below (although many files are produced in the destination folder):

encountered error: An error occurred while scripting the objects.
Error details: Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: Either the object or one of its properties is not supported on the target server version.
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ThrowIfBelowVersionLimit(SqlServerVersionInternal targetVersion, SqlServerVersionInternal upperLimit, String exceptionText)
at Microsoft.SqlServer.Management.Smo.Index.ClusteredColumnstoreIndexScripter.Validate()
at Microsoft.SqlServer.Management.Smo.Index.IndexScripter.GetCreateScript()

from mssql-scripter.

@sequentialogic did you use the --target-server-version AzureDW?

from mssql-scripter.

Sorry, closed by accident.

from mssql-scripter.

@MrMeemus --target-server-version is set to AzureDW. some files are generated correctly (including DWH specific options such as distribution).

Looking at one of the files, the comment header looks wrong. It claims the source is Azure Sql, however I cannot see the switch to set this.

/* ==Scripting Parameters==

Source Database Engine Edition : SqlDatabase
Source Database Engine Type : SqlAzureDatabase

Target Database Engine Edition : SqlDataWarehouse
Target Database Engine Type : SqlAzureDatabase

from mssql-scripter.

still getting this stacktrace: Error details: Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> System.MissingMethodException: Constructor on type 'Mic
oft.SqlServer.Management.Smo.PostProcessUser' not found.

version is 1.0.0a16

from mssql-scripter.

I too now get
---> System.MissingMethodException: Constructor on type 'Microsoft.SqlServer.Management.Smo.PostProcessUser' not found.

with a16

from mssql-scripter.

It looks like mssqltoolsservice was not built with the correct dependencies even though the project.json file was updated correctly. Will investigate and release new version once it's fixed.

Thanks @BadLambdaJamma and @sequentialogic

from mssql-scripter.

@sequentialogic and @BadLambdaJamma the following issues will be fixed in our latest release:

• Constructor on type 'Micoft.SqlServer.Management.Smo.PostProcessUser' not found.
• Microsoft.SqlServer.Management.Smo.UnsupportedVersionException:

As for the header:

Source Database Engine Edition : SqlDatabase
Source Database Engine Type : SqlAzureDatabase

This is a issue within one of our dependencies and will be fixed, but should not affect the script.

Thanks all so much for the feedback!

from mssql-scripter.

@MrMeemus I used mssql-scripter to backup scripts from AzureDW, but I got the following exception:

Scripting request submitted with request id: b31cadd8-bc61-4f6b-be86-c6370eb44ce7
Scripting request: b31cadd8-bc61-4f6b-be86-c6370eb44ce7 encountered error: File writing exception.
Error details: Microsoft.SqlServer.Management.Smo.ScriptWriterException: File writing exception. ---> System.IO.DirectoryNotFoundException: Could not find a part of the path 'C:\path\'.
   at System.IO.FileStream.ValidateFileHandle(SafeFileHandle fileHandle)
   at System.IO.FileStream.CreateFileOpenHandle(FileMode mode, FileShare share, FileOptions options)
   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
   at Microsoft.SqlServer.Management.Smo.NetCoreHelpers.CreateFileStream(String path, Boolean appendToFile)
   at Microsoft.SqlServer.Management.Smo.SingleFileWriter.Init(String path, Boolean appendToFile, Encoding encoding)
   --- End of inner exception stack trace ---
   at Microsoft.SqlServer.Management.Smo.SingleFileWriter.Init(String path, Boolean appendToFile, Encoding encoding)
   at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.GetScriptWriter(ScriptOutputOptions outputOptions)
   at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions)
   at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.Execute() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.ServiceLayer\Scripting\ScriptingScriptOperation.cs:line 64

Path is exist. What does it mean by System.IO.DirectoryNotFoundException?

Comment used:

mssql-scripter --connection-string "Server=tcp:,1433;Initial Catalog=;Persist Security Info=False;User ID=;Password=;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;" -f C:\path\ --script-drop-create --target-server-version "AzureDW" --continue-on-error --statistics --display-progress --enable-toolsservice-logging --owner

from mssql-scripter.