Update SMO to use Environment.NewLine

I'm running this command line:

mssql-scripter -S localhost -d carga_inicial_9 -U scripter -P scripter -f ./objects --schema-and-data --display-progress --file-per-object --script-drop-create

But upon examination of the generated scripts, I can see no drop has been scripted.

Answers for common questions:

• consistency guarantees scripting data
• identity columns
• binary/lob data

@MrMeemus Hello MrMeenus,I have tried the latest version a1.0.0a14 and I find a bug.
when I dump database not include the create database and create user statement, the table-level full-text index will be removed.If I did't remove the create database and create user statement,full-text index will be successfully dumped out。
My executive commands are

mssql-scripter -S localhost -d test -U user1 > test.sql
mssql-scripter -S localhost -d test -U user1 --exclude-types User > test.sql

The second command is missing as follows

CREATE FULLTEXT INDEX ON [dbo].[customer](
[name] LANGUAGE 'Simplified Chinese')
KEY INDEX [PK_customer]ON ([CustomerSearch], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

Thank you for your answer.

Our current approach to manage the SqlToolsService is getting complicated due to all of the ways pip can install: user install, machine install, virtual environments, etc.

To simplify things, we're going to try a different approach to managing the SqlToolsService dependency:

• Package up each SqlToolsService distro archive into it's own pip wheel
• Using a pip sdist install and a custom install script, detect the distro at pip install time, and include the appropriate SqlToolsService wheel as a dependency

This way, we let pip manage everything.

Scripting a database can result in the following exception:

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()
   at Microsoft.SqlServer.Management.Smo.SqlPostProcess.Register(SqlObjectBase obj)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.RegisterPostProcessHits()
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.PrepareGetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting)
   at Microsoft.SqlServer.Management.Smo.Database.PrefetchUsers(ScriptingPreferences options)
   at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.PrefetchAllObjects(String urnType)
   at Microsoft.SqlServer.Management.Smo.DatabasePrefetchBase.<PrefetchObjects>d__14.MoveNext()
   at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.SfcChildrenDiscovery(HashSet`1 discoveredUrns)
   at Microsoft.SqlServer.Management.Smo.SmoDependencyDiscoverer.Discover(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Discover(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions)

On Windows running Python 3.5, I'm seeing the below behavior:

Run the following command:
$ python main.py -S localhost -d AdventureWorks2014 --schema-and-data > AdventureWorks.sql

Result:
Traceback (most recent call last):
File "main.py", line 123, in
main(sys.argv[1:])
File "main.py", line 102, in main
sys.stdout.write(line)
File "C:\Python35\lib\encodings\cp1252.py", line 19, in encode
return codecs.charmap_encode(input,self.errors,encoding_table)[0]
UnicodeEncodeError: 'charmap' codec can't encode characters in position 364-379: character maps to

I would like to be able to perform an npm install, pull down a package, and be able to query and script out objects. With this request, I would the ability to log into the SQL instance using Windows Auth. Having the ability to use Node.Js would be amazing. I am currently using Edge.js to perform MSSQL calls that marshal out to .Net and then use ADO to query SQL, I would prefer something native and support by Microsoft.

When in production we use the site.getsitepackages() method to find the location of the site-packages of the current python interpreter. This will fail in virutal environments. We need to manually search for the sqltoolsservice folder.

Repro
Run with the --file option:

mssql-scripter -S localhost -d AdventureWorks2014 --file AdventureWorks.sql

Actual
The output is going to stdout and the output file.

Expected
The output to go to the output file only.

We're encounter a strange issue in SMO which is causing the SqlScriptPublishModel.AdvancedOptions to get lost, so default values are used. Since default values are used, data is never scripted.

I have a fix, just adding more tests and I'll have a PR out soon.

Hello,
When I use the software in windows7,there was a bug. The error message is as follows.

$ mssql-scripter -S localhost -d db_01 -U user1 --enable-toolsservice-logging
Password:
Scripting request: 1da2a284-987d-4669-8bf2-63be6bebabba encountered error: Inval
id directory specified by the ScriptingParams.FilePath property.
Error details: System.ArgumentException: Invalid directory specified by the Scri
ptingParams.FilePath property.
at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.Validat
eScriptDatabaseParams() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.Servi
ceLayer\Scripting\ScriptingScriptOperation.cs:line 298
at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.Execute
() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.ServiceLayer\Scripting\Scr
iptingScriptOperation.cs:line 70

And the log files found in my user directory under “.mssqlscipyer” is as follows.

2017/6/26 19:10:17 [ERROR] - Method "Execute" at line 115 of D:\repos\sqltoolsservice\src\Microsoft.SqlTools.ServiceLayer\Scripting\ScriptingScriptOperation.cs

Scripting operation 1da2a284-987d-4669-8bf2-63be6bebabba failed with exception System.ArgumentException: Invalid directory specified by the ScriptingParams.FilePath property.
   at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.ValidateScriptDatabaseParams() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.ServiceLayer\Scripting\ScriptingScriptOperation.cs:line 298
   at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.Execute() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.ServiceLayer\Scripting\ScriptingScriptOperation.cs:line 70

Please check out, thank you!

https://docs.microsoft.com/en-us/sql/tools/command-prompt-utility-reference-database-engine

Add mssql-scripter usage examples and getting started guide to official Microsoft docs.

When connecting to azure sql data warehouse, the below exception occurs:

Scripting request: 1c89a309-2656-453d-92e5-9ed451d94f60 encountered error: 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.

command used:

mssql-scripter -S a-sql-server.database.windows.net -d dwh-name -U username

Add recipes for common usage scenarios:

• change database name
• windows->linux paths
• change schema name
• etc

i.e Given a directory, script all procedures to a procedures folder, all tables to a tables folder etc.

Scripting support in the SqlToolsService is tracked by pull request #316

Update help page if it doesn't look convoluted.

Currently we have no way to filter object by type, and filtering by schema is not intuitive with the --include-object schema.* argument.

I'm proposing we add the following command line options:

--include-schema [name]
--exclude-schema [name]
--include-type [name]
--exclude-type [name]

I think this is little more clear and usable. Thoughts?

Currently there are only unit tests. We will need to add support to simulate end to end scenarios via session play backs. i.e simulate the input and output stream with baseline files to verify expected responses match with requests.

https://github.com/Microsoft/sql-xplat-cli/blob/dev/mssqlscripter/jsonrpc/jsonrpcclient.py#L318 adds the number of bytes read to the buffer end offset regardless of the outcome of the call to read from the stream. This could cause issues if EOF is reached when reading b/c adding "" (returned when EOF is reached) to a number will raise a TypeError.

Moving the referenced line after the if not length_read: block would solve the issue easily.

In my server implementation, I've also added a unit test for checking that this condition doesn't occur:

    def test_read_next_chunk_eof(self):
        with io.BytesIO() as stream:
            # If:
            # ... I create a reader with a stream that has no bytes
            reader = JsonRpcReader(stream)

            # ... and I read a chunk from the stream
            # Then: I should get an exception
            with self.assertRaises(EOFError):
                reader._read_next_chunk()

When we encounter a scripting error, we need to define:

• How the scripting api indicates and error
• How to surface errors to the end user

[ ] Option not to produce SET option statements
e.g. --exclude-set-options

SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF etc

Key things to track:

• Connection strings which may hold credentials - these cannot be logged
• Temp .sql files which can contain data from the database being scripting

Please refer to: http://dba-planet.blogspot.com/2017/05/mssql-scripter-new-sql-server-command.html

"I was expecting some sort of warning/error about compression as this feature is not supported in the target version and edition. But in the output it had the syntax for data compression as well. "

• Need logic around feature mismatches for script generation

I am using Python 3.6.1 on Windows, running the command below. I receive the error message (below) when trying to script out my database.

mssql-scripter -S -d airlift -U -P

Thanks!
Kevin

Update command line parameter to handle:

• Prompt for password
• Password from environment variable
• Connection string from environment variable

Scripts can have reference to OS specific file paths. For example, file groups on Windows will have path "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA". We need to figure out how to make the script use OS independent paths.

I'm trying to dump the objects on macOS Sierra and getting the output attached. Let me know if you need more information.

Currently, we have hard coded defaults used for SqlScriptOptions, such as the target server version, database edition, etc. The idea is we can do better using defaults based on the target database.

This request contains following function requirements.

1. Produce one sql file per t-sql object. e.g. table, view, sp, function etc.
2. Option to specify the output directory.
3. Option to specify type of t-sql object in addition to the multiple sql file capability. e.g. script all tables + create a sql file for each table.
4. Option not to include DROP or ALTER statement in the scripted file. e.g. CREATE statement only.

Using correct credential but cannot connect to AzureDB, with error Login failed.

C:\Users\UserName>mssql-scripter --version
1.0.0a12

C:\Users\UserName>mssql-scripter -S ae-az-dw-prod.database.windows.net -d bidw_prod -U etluser
Password:
Scripting request: 484b9114-e793-4760-8743-2a9b8509e96a encountered error: Failed to connect to server ae-az-dw-prod.database.windows.net.
Error details: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server ae-az-dw-prod.database.windows.net. --->
System.Data.SqlClient.SqlException: Login failed for user 'etluser'.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerIn
fo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderIn
fo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions option
s, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConne
ction)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal old
Connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate,
Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOpti ons, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions u
serOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, Tas
kCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletio nSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.GetServerInformation()
at Microsoft.SqlServer.Management.Smo.Server.IsAzureDbScopedConnection(ServerConnection sc)
at Microsoft.SqlServer.Management.Smo.Server.GetExecutionManager()
at Microsoft.SqlServer.Management.Smo.Server.get_ExecutionManager()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.get_ServerVersion()
at Microsoft.SqlServer.Management.Smo.Server.get_Version()
at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishModel.GetAdvancedScriptingOptions()
at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.BuildPublishModel() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.Servic
eLayer\Scripting\ScriptingScriptOperation.cs:line 162
at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.Execute() in D:\repos\sqltoolsservice\src\Microsoft.SqlTools.ServiceLayer\Scr
ipting\ScriptingScriptOperation.cs:line 70

On a clean machine the current install will fail because there does not exist a module for sqltoolsservicesetup.