Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Data Tools doesn't accept FORCESEEK parameter in CHANGETABLE function #434

Closed
dzsquared opened this issue Mar 23, 2024 · 0 comments
Closed
Assignees
Labels
bug Something isn't working fixed-pending-release Fix in upcoming release
Milestone

Comments

@dzsquared
Copy link
Contributor

copied from https://developercommunity.visualstudio.com/t/Data-Tools-doesnt-accept-FORCESEEK-para/10602301
for visibility and tracking

documented forceseek parameter - https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql?view=sql-server-ver16
core scriptdom issue - microsoft/SqlScriptDOM#78

When using the Change Tracking function CHANGETABLE in either a view or stored procedure, Visual Studio Data Tools doesn’t support the use of the third optional parameter FORCESEEK.

Example:

CREATE VIEW [dbo].[View1]
AS
SELECT * FROM CHANGETABLE(CHANGES dbo.MyTable, 0, FORCESEEK) a
GO

This project cannot build when the FORCESEEK is used - and returns this error:
SQL46010: Incorrect syntax near ,.

This prevents us from creating a database project of our production database, since we do use this FORCESEEK parameter. Without it, we too often see execution plans scanning the system change tracking tables, which kills performance for us.

I’m was using Visual Studio 2022 17.8.3 and just updated to 17.9.1 - both have this issue.
The extension used is SQL Server Data Tools - Sql Editor - version 1.4

I can see that this has previously been reported, but closed with low priority.

@dzsquared dzsquared added the bug Something isn't working label Mar 23, 2024
@dzsquared dzsquared added this to the 162.4 milestone Mar 23, 2024
@llali llali added the fixed-pending-release Fix in upcoming release label Aug 14, 2024
llali added a commit to microsoft/SqlScriptDOM that referenced this issue Aug 16, 2024
Adding forceseek to changetable. fixing bug microsoft/DacFx#434

----
#### AI description  (iteration 1)
#### PR Classification
New feature

#### PR Summary
This pull request adds support for the `FORCESEEK` hint in `CHANGETABLE` queries.
- `/SqlScriptDom/Parser/TSql/TSql140.g`, `/SqlScriptDom/Parser/TSql/TSql130.g`, `/SqlScriptDom/Parser/TSql/TSql150.g`, `/SqlScriptDom/Parser/TSql/TSql160.g`: Updated grammar to support `FORCESEEK` in `CHANGETABLE` queries.
- `/SqlScriptDom/Parser/TSql/Ast.xml`: Added `ForceSeek` property to `ChangeTableChangesTableReference` and `ChangeTableVersionTableReference` classes.
- `/SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.ChangeTableTableSource.cs`: Implemented code generation for `FORCESEEK` hint.
- `/Test/SqlDom/Baselines150/FromClauseTests150.sql`, `/Test/SqlDom/TestScripts/FromClauseTests150.sql`: Added test cases for `FORCESEEK` in `CHANGETABLE` queries.
- `/Test/SqlDom/Only150SyntaxTests.cs`: Updated test expectations to include new `FORCESEEK` test cases.
llali added a commit to microsoft/SqlScriptDOM that referenced this issue Aug 22, 2024
* [Security] Update .NET SDK to latest patch version

This change updates to the latest patch version for the .NET SDK which contains the latest security fixes.
Note that your global.json does configure `rollForward`, however roll forward will allow for the build to pass when using a different SDK. So, if locally you have a newer patch version installed it will pass, without the `rollforward` it will fail.

CI in many cases installs exactly what is in the global.json, so making sure that your build requires the latest patch version ensures that CI is compliant with those vulnerabilities.

---

For feedback or questions about this PR, please contact the [Gardener team](mailto:gardener@microsoft.com).

---

This change was automatically generated by [1ES Gardener](https://eng.ms/docs/cloud-ai-platform/devdiv/one-engineering-system-1es/1es-docs/gardener/1es-gardener) (a [MerlinBot](https://aka.ms/MerlinBot) extension) which is an initiative by the 1ES team to help repos stay up-to-date with latest tools, features, and best practices.

* [Security] Update .NET SDK to latest patch version

This change updates to the latest patch version for the .NET SDK which contains the latest security fixes.
Note that your global.json does configure `rollForward`, however roll forward will allow for the build to pass when using a different SDK. So, if locally you have a newer patch version installed it will pass, without the `rollforward` it will fail.

CI in many cases installs exactly what is in the global.json, so making sure that your build requires the latest patch version ensures that CI is compliant with those vulnerabilities.

Release notes:
* [6.0.32](https://github.com/dotnet/core/blob/main/release-notes/6.0/6.0.32/6.0.32.md)

The following CVEs will be addressed by this update:
* [CVE-2024-38081](https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2024-38081)
* [CVE-2024-38095](https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2024-38095)

---

For feedback or questions about this PR, please contact the [Gardener team](mailto:gardener@microsoft.com).

---

This change was automatically generated by [1ES Gardener](https://eng.ms/docs/cloud-ai-platform/devdiv/one-engineering-system-1es/1es-docs/gardener/1es-gardener) (a [MerlinBot](https://aka.ms/MerlinBot) extension) which is an initiative by the 1ES team to help repos stay up-to-date with latest tools, features, and best practices.

* Merged PR 1417651: Add tests for Json data type in scriptdom

This PR adds tests for Json data type in ScriptDom.
##Test added
- ALTER TABLE T ADD jsonCol JSON;
- ALTER TABLE T ALTER COLUMN col JSON;
- Create function with Json data type as parameter;
- Create stored procedure with Json data type as parameter;
- Convert Char to Json;
- Convert NChar to Json;
- Convert Varchar to Json;
- Convert NVarchar to Json;
- Convert Json to Char;
- Convert Json to NChar;
- Convert Json to Varchar;
- Convert Json to NVarchar;
- Cast Char to Json;
- Cast NChar to Json;
- Cast Varchar to Json;
- Cast NVarchar to Json;
- Cast Json to Char;
- Cast Json to NChar;
- Cast Json to Varchar;
- Cast Json to NVarchar;

----
#### AI description  (iteration 1)
#### PR Classification
New feature: Adding tests for JSON data type in ScriptDOM.

#### PR Summary
This pull request introduces tests for the JSON data type in ScriptDOM, ensuring proper handling and conversion of JSON data.
- Added JSON conversion and casting tests in `Test/SqlDom/TestScripts/ExpressionTests160.sql`.
- Created baseline for JSON conversion and casting tests in `Test/SqlDom/Baselines160/ExpressionTests160.sql`.
- Added tests for JSON data type in function and procedure creation in `Test/SqlDom/TestScripts/CreateFunctionStatementTests160.sql` and `Test/SqlDom/TestScripts/CreateProcedureStatementTests160.sql`.
- Included tests for altering tables to add or modify JSON columns in `Test/SqlDom/TestScripts/AlterTableStatementTests160.sql`.
- Updated `Only160SyntaxTests.cs` to include new test scripts for JSON data type.

Related work items: #2949474

* Merged PR 1415210: Adding forceseek to changetable

Adding forceseek to changetable. fixing bug microsoft/DacFx#434

----
#### AI description  (iteration 1)
#### PR Classification
New feature

#### PR Summary
This pull request adds support for the `FORCESEEK` hint in `CHANGETABLE` queries.
- `/SqlScriptDom/Parser/TSql/TSql140.g`, `/SqlScriptDom/Parser/TSql/TSql130.g`, `/SqlScriptDom/Parser/TSql/TSql150.g`, `/SqlScriptDom/Parser/TSql/TSql160.g`: Updated grammar to support `FORCESEEK` in `CHANGETABLE` queries.
- `/SqlScriptDom/Parser/TSql/Ast.xml`: Added `ForceSeek` property to `ChangeTableChangesTableReference` and `ChangeTableVersionTableReference` classes.
- `/SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.ChangeTableTableSource.cs`: Implemented code generation for `FORCESEEK` hint.
- `/Test/SqlDom/Baselines150/FromClauseTests150.sql`, `/Test/SqlDom/TestScripts/FromClauseTests150.sql`: Added test cases for `FORCESEEK` in `CHANGETABLE` queries.
- `/Test/SqlDom/Only150SyntaxTests.cs`: Updated test expectations to include new `FORCESEEK` test cases.

* Merged PR 1420062: Added test script for vector native data type

Added test script for expression, ereate table, stored procedure, functions tests with vector native type

----
#### AI description  (iteration 1)
#### PR Classification
New feature: Added test script for vector native data type.

#### PR Summary
This pull request introduces test scripts and updates to support the vector native data type in SQL.
- Added test cases for casting and converting between `vector` and other data types in `/Test/SqlDom/TestScripts/ExpressionTests160.sql` and `/Test/SqlDom/Baselines160/ExpressionTests160.sql`.
- Created stored procedures and functions utilizing the `vector` data type in `/Test/SqlDom/TestScripts/CreateProcedureStatementTests160.sql`, `/Test/SqlDom/Baselines160/CreateProcedureStatementTests160.sql`, `/Test/SqlDom/TestScripts/CreateFunctionStatementTests160.sql`, and `/Test/SqlDom/Baselines160/CreateFunctionStatementTests160.sql`.
- Updated `SqlDataTypeOption` and related parser files to include the `vector` data type in `/SqlScriptDom/Parser/TSql/SqlDataTypeOption.cs`, `/SqlScriptDom/Parser/TSql/TSql80ParserBaseInternal.cs`, `/SqlScriptDom/Parser/TSql/TSql160ParserBaseInternal.cs`, `/SqlScriptDom/Parser/TSql/CodeGenerationSupporter.cs`, and `/SqlScriptDom/ScriptDom/SqlServer/ScriptGenerator/SqlScriptGeneratorVisitor.SqlDataType.cs`.
- Added a new table with a `vector` column in `/Test/SqlDom/TestScripts/CreateTableTests160.sql` and `/Test/SqlDom/Baselines160/CreateTableTests160.sql`.

* Merged PR 1433433: Adding release notes for 161.9135

Adding release notes for 161.9135

----
#### AI description  (iteration 1)
#### PR Classification
Documentation update for release notes.

#### PR Summary
This pull request adds release notes for version 161.9135.0 of `Microsoft.SqlServer.TransactSql.ScriptDom`, detailing new features, fixed issues, and target platform support.
- `release-notes/161.91/161.9135.0.md`: Added release notes including new support for native Vector type and FORCESEEK hint in CHANGETABLE function.

---------

Co-authored-by: MerlinBot <MerlinBot>
Co-authored-by: Lijun Ji <lijji@microsoft.com>
Co-authored-by: Abhishek Kumar (MDD) <Kumar.Abhishek@microsoft.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working fixed-pending-release Fix in upcoming release
Projects
None yet
Development

No branches or pull requests

3 participants