microsoft / sqltoolsservice Goto Github PK
View Code? Open in Web Editor NEWSQL Tools API service that provides SQL Server data management capabilities.
License: Other
SQL Tools API service that provides SQL Server data management capabilities.
License: Other
I'm looking for a modern equivalent of the old Smo TraceServer API and can see in #456 that @kburtram worked on providing an API to launch XEvents.
This project seems to be the sweet spot I am looking for, but I don't see a nuget package I can use.
https://www.nuget.org/packages?q=sqltoolsservice currently returns no results
I understand that this is a work in progress. So, I just want to point out a discrepancies I found when building data object when executing T-SQL queries with SMO .ExecuteWithResults() method.
I'm comparing using the Windows SQL Server SMO vs the '1.0.0-alpha.38 - .Net Core 2.0 build'. In Windows SMO, I can use the columns in the result dataset. But, in the '.NET Core 2.0 Build', it excluding the columns and instead giving the data in the .ItemArray property (which is looking like a Python list).
This could be a major breaking change for people that have invested time building SMO scripts.
we are handling it as byte[] data type and as a result, when using Edit Data for a table with this type of columns in Azure Data Studio, we will get an error indicating cannot convert string to byte[].
this issue is found while editing the OrganizationNode column of HumanResources.Employee table in adventureWorks database.
BTW, SSMS handles this data type properly.
Hi,
I would like to allow users of my web app to write custom sql query in their browser.
The query occur against a few well known tables and should be readonly (no update/delete/create).
Ideally, the queries should be validated and I came across this project.
It's quite overwhelming though and I don't know where to start to provide basic intellisense and error reporting to the user.
I was thinking that the query being written by the user could be sent to the server (.net) periodically and the GUI could check the results and provide feedback to the users.
Any basic samples out there? Not sure where to even look.
I had a look at https://github.com/Microsoft/vscode-mssql/tree/master/src/languageservice but there doesn't seem to be much there.
Looks like Microsoft.SqlServer.XEvent.Linq.dll
has been removed. It was found in previous versions, including microsoft.sqlserver.sqlmanagementobjects.150.18040.0-preview
Either way, I'm looking for QueryableXEventData which is awesome and useful. When I try to load the DLL from old versions including microsoft.sqlserver.sqlmanagementobjects.150.18040.0-preview
, I get
Add-Type : Could not load file or assembly 'Microsoft.SqlServer.Xe.Core.dll' or one of its dependencies. The specified module could not be found.
How can I get this class back? I'm using this version of SMO
Thanks much,
Chrissy
I just install "v1.0.0-alpha.34 - .Net Core 2.0 build" ServiceLayer in Ubuntu 16.04.2 and I'm getting the error:
Exception calling "getSqlEdition" with "1" argument(s): "Property Login was not set."
At line:1 char:1
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : PropertyNotSetException
The following code below works in Window 1o Build 161199 using PowerShell Beta.2
PowerShell Beta requirements:
I have copied all ServiceLayer dll's into the PowerShell Beta folder.
OS: Ubuntu 16.04.2
Shell: PS /home/maxt> $PSVersionTable
Name Value
PSVersion 6.0.0-beta
PSEdition Core
BuildVersion 3.0.0.0
CLRVersion
GitCommitId v6.0.0-beta.2
OS Linux 4.4.0-43-Microsoft #1-Microsoft Wed Dec 31 14:42:53 PST 2014
Platform Unix
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0
.NET Core Version installed: 2.0 Preview 1
$Source = @"
public class MyMSSql
{
public static string getEdition(string sqlName)
{
string sqlEdition;
Microsoft.SqlServer.Management.Smo.Server sname = new Microsoft.SqlServer.Management.Smo.Server(sqlName);
sqlEdition = sname.Information.Edition;
return sqlEdition;
}
public string getSqlEdition(string sqlName)
{
string sqlEdition;
Microsoft.SqlServer.Management.Smo.Server sname = new Microsoft.SqlServer.Management.Smo.Server(sqlName);
sqlEdition = sname.Information.Edition;
return sqlEdition;
}
}
"@;
$Assem = (
"Microsoft.SqlServer.Management.Sdk.Sfc",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.ConnectionInfo"
);
Add-Type -ReferencedAssemblies $Assem -TypeDefinition $Source
$MySQLobj = New-Object MyMSSql
$MySQLobj.getSqlEdition('mtrinidadlt2,1437')
It appears that the Sql Parser is not parsing the OVER clause.
For example with an expression like LAG(o.[col1]) OVER(ORDER BY o.[col2]
, the OVER clause is tokenized but is not even part of the AST.
This is causing issues for us as we can't validate the OVER clause with our visitor (since the over clause is not in the AST, it is not visited).
I'm guessing this means that formatting and auto-completion won't work either inside the over clause (although I couldn't test this).
Some breaking changes in the big data cluster query are coming to move this to a DMV. Our implementation will fall back to existing CTP logic for at least 1 release, but then we should remove the fallback logic for simplicity / maintainability.
Hover works correctly and shows UDF details.
But peek definition and go to definition show create statement for the schema instead.
While testing ADS against a test database with object names like "Table'quote]bracket" I found that the script menu items failed for those objects. I am submitting a fix and a unit test for the scripting component, but we should add broader test coverage for objects with these characters in their names.
I recently installed to latest PowerShell Beta 3 and now SMO component won't work.
But, it's only "[Microsoft.SqlServer.Management.Smo.Server]".
I can use it with PowerShell Beta 2 but not with Beta 3.
There's a newer SQLToolsService-Servicelayer for .Net 2.0 but I get the same error in Beta 3.
So, I think it has to be with PowerShell Beta 3.
Beta 3 error:
PS C:\Program Files\PowerShell\6.0.0-beta.3> $SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvCo
nn);
new-object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this
type is loaded.
At line:1 char:14
I have use both .NET 2.0 version of the SMO in the SQLToolsService-Servicelayer version(s):
'v1.0.0-alpha38 - .NET Core 2.0 Build' and 'v1.0.0-alpha.43 - .NET Core 2.0 Build'.
Both of these versions works with PowerShell Core Beta 2 in both Windows and Linux. But, it will not work in Beta 3.
So, something happened with the Microsoft.SqlServer.Management.Smo.Server DLL.
Windows 10 Build 16215
Linux: Ubuntu 16.04.2
SqlToolsService - ServiceLayer: v6.0.0-alpha43 - .NET 2.0 Core Build
.NET Core: 2.0 Preview 1
PS C:\Program Files\PowerShell\6.0.0-beta.3> $PSVersionTable
Name Value
---- -----
PSVersion 6.0.0-beta
PSEdition Core
GitCommitId v6.0.0-beta.3
OS Microsoft Windows 10.0.16215
Platform Win32NT
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0
PS /home/maxt/sqltoolsservice> $PSVersionTable
Name Value
---- -----
PSVersion 6.0.0-beta
PSEdition Core
GitCommitId v6.0.0-beta.3
OS Linux 4.4.0-43-Microsoft #1-Microsoft Wed Dec 31 14:42:53 PST 2014
Platform Unix
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1
WSManStackVersion 3.0
Apologies if this is the wrong place to ask, but is it possible to also open source Microsoft.SqlServer.Management.SqlParser.dll ?
It would seem like the right repo for it?
Hello,
I am trying to link to sqltoolsservice libraries directly in .NET 4.7.2 application (WinForms or WPF). It is possible to add reference to sqltoolsservice dll's (as NET Core reference) in WinForms application and compile, but during running I got error - Could not load file System.Runtime 4.2.1.0 or one of its dependencies. How properly should be such embedding done?
Best regards,
JK
Dotnet core has a built in linker that can be used to decrease the overall package size of our application, by passing in the -o linked
argument into the publish command.
In my testing it reduced the size of the binaries from 435 files totalling 119 MB to 15 files totalling 4.3 MB. This is quite the savings.
After the upgrade to Ubuntu 17.04 from 16.10, the service as shipped in in the mssql extension is failing to start.
The resultsset tests ReadToEndForXmlJson and ReadToEndSuccess fail randomly when all tests are run but pass when the run alone. Temporarily disabling the tests.
The service as used by vscode-mssql and mssql-scripter is broken on Ubuntu 17.04. This also includes the alpha build that uses .NET Core 2.0 preview as used in the alpha builds of vscode-mssql.
There seems to be some problems with the batch selection data from the batch parser that is sending back incorrect selection data.
This impacts the client ability to correctly handle problems that occur.
The app setup I have
Web App (.net472)
Class Library (.netstandard2.0)
The Web App references Microsoft.SqlServer.SqlManagementObjects and the Class library also references Microsoft.SqlServer.SqlManagementObjects
The problem lies in the versions that get loaded, .net472 references a version 15.0. and the class library a version 15.2.0
When kicking the web app up, we end up with the error:
"Could not load file or assembly 'Microsoft.SqlServer.SqlEnum, Version=15.2.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)":"Microsoft.SqlServer.SqlEnum, Version=15.2.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"} System.Exception {System.IO.FileLoadException
Thoughts?
It seems the docs are somewhat lacking.
We'd love to use the sql formatting functionality to format some sql scripts.
Our app is a .net app (full framework, not core), but we can't find any nuget package to pull this library.
Is this possible to do?
Linux file system on the server allows names that vary only by case
Currently, the baseline files for BatchParser tests are being compared without any whitespace/newline characters. A better logic to compare baseline files should be implemented.
I successfully added AccessToken support to dbatools but it seems to only work partially. Here's some code for a repro:
$params = @{
Type = 'ManagedIdentity'
Subtype = 'AzureSqlDb'
EnableException = $true
}
$token = New-DbaAzAccessToken @params
$params = @{
SqlInstance = 'psdbatools.database.windows.net'
Database = 'abc'
AccessToken = $token
}
# This will connect successfully. You can even do $azSqlCn.Databases.Name and get the db names
$azSqlCn = Connect-DbaInstance @params
# This will break
Get-DbatoolsLog | Select -First 1 | Write-DbaDataTable -SqlInstance $azSqlCn -Database abc -Table ThisisErrorLog -AutoCreateTable
The last command should write to the $azSqlCn
instance. But instead I get System.Data.SqlClient.SqlException: Login failed for user ''.
This unanswered question had similar things and I wondered if it was because it changed the db context or something? Either way, I tried for like a week and could not get it to work - kept getting that Login failed for user ''.
Any advice would be appreciated.
Just for clarification! What's the difference between the microsoft.sqltools.credentials and microsoft.sqltools.servicelayer? Should I install both of them?
Thanks,
:)
TestStartProfilingRequest has been failing on and off in AppVeyor builds. See https://ci.appveyor.com/project/kburtram/sqltoolsservice/build/1.0.1572 or https://ci.appveyor.com/project/kburtram/sqltoolsservice/build/1.0.1570 for examples.
Stack trace:
Error Message:
Assert.Equal() Failure
Expected: (null)
Actual: 4d7e4874-682f-406c-a24a-a38311d78b6a
Stack Trace:
at Microsoft.SqlTools.ServiceLayer.UnitTests.Profiler.ProfilerServiceTests.<TestStartProfilingRequest>d__0.MoveNext() in C:\projects\sqltoolsservice\test\Microsoft.SqlTools.ServiceLayer.UnitTests\Profiler\ProfilerServiceTests.cs:line 64
Not sure if this is the right place to report this issue, but I can't find a repo specifically for SMO.
Using a NetCore 2.1 app, I'm trying to script my database using code like the following:
private static void ScriptDatabaseTables(string outputPath, Func<(string schemaName, string
tableName), bool> shouldIncludeTable, bool includeDependencies)
{
using (var dbContext = TestUtilities.GetDbContext())
{
var builder = new SqlConnectionStringBuilder(dbContext.Database.GetDbConnection().ConnectionString)
{
MultipleActiveResultSets = false
};
using (var sqlConnection = new SqlConnection(builder.ToString()))
{
var sqlServer = new Server(new ServerConnection(sqlConnection));
var database = sqlServer.Databases["MyDatabase"];
var scripter = new Scripter(sqlServer);
scripter.Options.ScriptData = true;
scripter.Options.ScriptSchema = false;
scripter.Options.WithDependencies = includeDependencies;
using (var writer = File.CreateText(outputPath))
{
var tableList = database.Tables.Cast<Table>()
.Where(table => !table.IsSystemObject && shouldIncludeTable((table.Schema, table.Name)))
.Select(table => table.Urn).ToArray();
var scriptLines = scripter.EnumScript(tableList);
foreach (var scriptLine in scriptLines)
{
writer.WriteLine(scriptLine);
}
}
}
}
}
With NuGet package Microsoft.SqlServer.Smo, Version=140.17279.0-xplat, and also with Microsoft.SqlServer.SqlManagementObjects, Version=140.17279 this code fails the error "Discover dependenceis failed". It works with version 140.17199
The innermost exception is
--ArgumentNullException
at System.IO.StreamReader..ctor(Stream stream, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean leaveOpen)
at System.IO.StreamReader..ctor(Stream stream)
at Microsoft.SqlServer.Management.Smo.SqlEnumDependencies.EnumDependencies(Object ci, DependencyRequest rd)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.EnumDependencies(Object connectionInfo, DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetDependencies(DependencyRequest dependencyRequest)
at Microsoft.SqlServer.Management.Smo.DependencyWalker.DiscoverDependencies(Urn[] urns, Boolean parents)
The code that is failing is in SqlEnumDependencies.EnumDependencies and looks like this:
string str1 = string.Empty;
if (this.IsDbCloud)
str1 = "CloudDependency.sql";
else if (this.m_targetVersion.Major <= 8)
str1 = "ShilohDependency.sql";
else if (this.m_targetVersion.Major == 9)
str1 = "YukonDependency.sql";
else if (this.m_targetVersion.Major == 10)
str1 = "KatmaiDependency.sql";
else if (this.m_targetVersion.Major >= 11)
str1 = "SQL11Dependency.sql";
string str2 = string.Format((IFormatProvider) CultureInfo.InvariantCulture, "declare @find_referencing_objects int\nset @find_referencing_objects = {0}\n", (object) (rd.ParentDependencies ? 0 : 1));
Assembly assembly = SqlEnumNetCoreExtension.GetAssembly(typeof (SqlTypeConvert));
StreamReader streamReader = new StreamReader(assembly.GetManifestResourceStream(assembly.GetName().Name + "." + str1));
Looking at the differences between the two versions, I can see that in the most recent versions of the Microsoft.SqlServer.SqlEnum the prefix "Microsoft.SqlServer.SqlEnum" was removed from all the resource names, yet the code calling GetManifestResourceStream still seems to be expecting it to be there.
Since Azure can support other SQL sever types like MySQL or PostgreSQL are there plans to support them or allow extensions for different flavors of SQL/or even NoSQL. I could envision this as a platform for managing data connections similar to how VSCode allows for any language to be used through extensions. Even if Microsoft doesn't support the plugins directly it would be nice to only have one platform to be able to query from.
Hi,
I apologize if this tracker is the wrong one, but maybe you know how to to contact the owners of the following package, as it is used by your package and both are apparently owned by Microsoft SQL Tools. I tried contacting the owners via Nuget contact information but never received any reply:
https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/
The above nuget package targets netfx (4.0) and netcoreapp2.0. Is there any reason why it does not target netstandard2.0? With netcoreapp2.0 I need to multi-target my internal libraries for my products for both netfx, netcoreapp, as I have products sharing the same libraries but targeting different platforms.
The package does not copy the correct native runtime libs for the new PackageReference system and the new sdk style csproject, as the targets file does not properly work . I can provide a repro if necessary
So currently the above mentioned package is kinda broken.
Thanks
TSQL Debugger have been deprecated in SSMS 18.0.
I`m sure that many persons think it is a usefull tool, why don't bring it back in Azure Database Studio?
No "Application Name" Property in New Connection - Advanced Properties.
Azure Data Studio Version:
Version 1.3.1-insider
Commit bd53e685d0091f9fe09c2662c30e0e506c402561
Date 2018-11-09T19:34:35.108Z
VS Code 1.26.1
Shell 2.0.9
Renderer 61.0.3163.100
Node 8.9.3
V8: 6.1.534.41
Architecture x64
I excluded the code for these features from code coverage to reflect the actual coverage rate for the features that are going to be GA. PR: #688
This issue is to track the task to add test coverage for these features and eventually remove the filters I added with the above PR.
Hello,
I think that static code analysis will be a great feature for the extension. Is it possible to make analyzer like eslint using the current SQL parser?
This code is common to the 3 backend services: resourceprovider, credential service and tools service layer. We should probably refactor it out into a programbase class, so we do not leave behind .
I've had some tests fail in the appveyor run as part of a pull request, but there was zero information in the log to identify the issue.
Eg, this assert on not null provides no information on what is null:
Location[] locations = scripter.GetSqlObjectDefinition(objectName, schemaName, objectType);
if (shouldReturnValidResult)
{
Assert.NotNull(locations);
Cleanup(locations);
}
else
{
Assert.Null(locations);
}
Also, GetSqlObjectDefinition has zero tracing. Since the failure doesn't reproduce readily on a dev machine figuring out how to make these tests reliable in the cloud will be difficult without appropriate tracing.
In sqltoolsservice/src/Microsoft.SqlTools.ServiceLayer/LanguageServices/AutoCompleteHelper.cs,
the EXCEPT
keyword is not included in DefaultCompletionText
.
I ran into the problem that the mssql extension's intelisense didn't work when typing EXCEPT
in vscode. I traced down the root cause to here (hope i'm right 🙂) .
Hope this helps.
Installed extensions don't appear as tabs on your management dashboard
Version 1.0.0 connecting to SQL 2016, installed SQL Server Agent 0.33.0, does not appear on connection to SQL server.
Also tried Server Reports 0.1.3 it doesn't appear either
The query execution won't cancel a request until after the query executes. If a query takes a long time to execute, this can result in a long delay until the query is cancelled. Instead, we should invoke SqlCommand.Cancel() on any outstanding query to speed up the cancellation.
Currently when reading data, the query execution services reads the entire row regardless if it only intends to display the first 8K.
.NET 4.5 introduced streaming APIs. We should investigate using the streaming api to obtain a stream for the row, only read what we need, and then move on to the next column.
Update runtime id's for MacOsx and Windows.
Remove linux distro runtime id's and start using the linx-x64 runtime id.
As I finally got Linux SMO piece working in Linux, I failed to mention that it was on Windows 10 Insider (16215) Bash. During my preparation to the South Florida SQLSaturday session, I discover that the behavior between Windows 10 Bash and Ubuntu is different.
I've created the 'sqltoolsservice' folder on my home user location in both Win10 Bash and Ubuntu. Now, in Win10 Bash I haven't add the sqltoolsservice folder to the PATH and I can run my PowerShell SMO script without any error.
Now, in Ubuntu, I have to change folder to sqltoolsservice in order to run my PowerShell SMO script. Also, I added the both path: "/home/maxt/sqltoolsservice: .../refs:$PATH" in the ~/.bashrc with no result.
So, what I'm doing wrong?? Any hint!
Greatly Appreciated,
:)
Hi,
I apologize if this tracker is the wrong one, but maybe you can help me with this package
https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects/
When I try to get the collection of Service Broker queues, i get the the following excpetion. Is there a package I'm missing?
Could not load file or assembly 'Microsoft.SqlServer.ServiceBrokerEnum, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. The system cannot find the file specified.
I'm trying to get a list of the Queues already in Service Broker.
Write more tests for the BatchParserWrapper class as well as the ExecutionEngine class. These classes have low code coverage in coveralls right now, and can be definitely be increased.
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.