Cannot create VIEW on MSSQL

[ionred]

Current behavior

When creating a view on MS SQL 11.0 DB, algorithm is set to undefined, but instead of ignoring algorithm in the create statement, it still leaves "ALGORITHM = UNDEFINED" at the beginning of the create. The entire Alg section should be unavailable/grayed out as well for a MSSQL connection.

Failure information (for bugs)

/* SQL Error (343): Unknown object type 'ALGORITHM' used in a CREATE, DROP, or ALTER statement. */

Steps to reproduce

Please provide detailed steps for reproducing the issue.

1. CREATE ALGORITHM = UNDEFINED VIEW "V_ZCUSTOM_AGTDATA" AS .......
2. /* SQL Error (343): Unknown object type 'ALGORITHM' used in a CREATE, DROP, or ALTER statement.*/

Context

Please provide any relevant information about your setup. This is important in case the issue is not reproducible except for under certain conditions.

• HeidiSQL version: 9.4
• Database system + version: MS SQL 11

[ansgarbecker]

The view editor in HeidiSQL does currently not care for other systems but MySQL/MariaDB. I guess the controls (definer, sql security, algorithm and check option) are not applicable to a view in MSSQL, or? Is there some good documentation on how to read and write views in MSSQL?

[ionred]

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql
is the most specific information I can find on create view.
There are "view attributes" that I can't speak for their necessity; but they sound like limited need items for implementation. The rest of the options in the create view screen can likely be dithered/disabled on MSSQL connections.

[ghost]

@ansgarbecker Similar issue with editing an existing view:

Hit "Save" and receive an error:

ALTER SQL SECURITY DEFINER VIEW "ExampleView" AS SELECT ...
/* SQL Error (343): Unknown object type 'SQL' used in a CREATE, DROP, or ALTER statement. */

Literally all we need for MS SQL is to ignore the header options so the command sent is simply ALTER VIEW ...
Like @ionred mentioned, the other MS SQL specific options are of limited need.

Server: MS SQL 2008 R2
Client: HeidiSQL 9.5.0. 5196

[magisch]

@ansgarbecker Creating and Editing views in MSSQL is actually really simple and used to work in HeidiSQL before one of the updates (don't remember which exactly.) but the crux is that the algorhitm and definer/invoker parts don't work with mssql. Best would be if you could turn these off so the generated syntax would just be "CREATE VIEW [schema_name.]view_name AS " and "ALTER VIEW [schema_name.]view_name AS".

MSDN has some nice documentation about MSSQL views too: https://docs.microsoft.com/de-de/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017

An option like that would probably be compatible with all other dbms too, as create/alter view instructions are basic sql I think.

[ionred]

Hi @ansgarbecker , is there an update on this bug?

[ansgarbecker]

Not yet, but if it's really just that I have to remove the mentioned clauses, I can try to get this done for the next release.

[ionred]

I would say removing the extra clauses would resolve the bug. If anybody needs any extra functionality related to the MS SQL specific CREATE VIEW statement. Then they can open that up as a feature request or enhancement

…

On Tue, Oct 8, 2019 at 7:07 AM Ansgar Becker ***@***.***> wrote: Not yet, but if it's really just that I have to remove the mentioned clauses, I can try to get this done for the next release. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#39?email_source=notifications&email_token=ABZDR3OEEGRQHQ47UFMKRKLQNRZYDA5CNFSM4EHYG6WKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEAT5PII#issuecomment-539482017>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABZDR3N3F22JFASVTCDYRIDQNRZYDANCNFSM4EHYG6WA> .

[ansgarbecker]

Long story short: should work with the next build :)