microsoft / mssql-scripter Goto Github PK
View Code? Open in Web Editor NEWRepository for the new SQL cross-platform command line tools
License: Other
Repository for the new SQL cross-platform command line tools
License: Other
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.
When we encounter a scripting error, we need to define:
On a clean machine the current install will fail because there does not exist a module for sqltoolsservicesetup.
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.
Key things to track:
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?
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
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.
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.
[ ] 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
This request contains following function requirements.
Update command line parameter to handle:
Add recipes for common usage scenarios:
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, TaskCompletionSource
1 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 nSource
1 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
i.e Given a directory, script all procedures to a procedures folder, all tables to a tables folder etc.
$ pip install --user -U mssql-scripter
Requirement already up-to-date: mssql-scripter in /home/chaz/.local/lib64/python3.4/site-packages
Requirement already up-to-date: mssqltoolsservice-CentOS-7==1.0.0a12 in /home/chaz/.local/lib64/python3.4/site-packages (from mssql-scripter)
Requirement already up-to-date: wheel>=0.29.0 in /home/chaz/.local/lib64/python3.4/site-packages (from mssql-scripter)
Requirement already up-to-date: future>=0.16.0 in /home/chaz/.local/lib64/python3.4/site-packages (from mssql-scripter)
$ mssql-scripter -S localhost -d AdventureWorks2014
Scripting request: c2bec063-6034-4e06-989f-36dd3628219c encountered error: Invalid directory specified by the ScriptingParams.FilePath property.
Error details: System.ArgumentException: Invalid directory specified by the ScriptingParams.FilePath property.
at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.ValidateScriptDatabaseParams()
at Microsoft.SqlTools.ServiceLayer.Scripting.ScriptingScriptOperation.Execute()
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.
Scripting support in the SqlToolsService is tracked by pull request #316
Update SMO to use Environment.NewLine
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)
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:
This way, we let pip manage everything.
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.
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!
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
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 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.
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.
Answers for common questions:
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.
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. "
Update help page if it doesn't look convoluted.
@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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.