Heidisql: Cannot create VIEW on MSSQL
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.
- CREATE ALGORITHM = UNDEFINED VIEW "V_ZCUSTOM_AGTDATA" AS .......
- /* 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
ionred
All 8 comments
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?
ansgarbecker
on 12 Dec 2017
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.
ionred
on 12 Dec 2017
@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
ghost
on 21 Mar 2018
@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.
magisch
on 14 Jan 2019
Hi @ansgarbecker , is there an update on this bug?
ionred
on 8 Oct 2019
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.
ansgarbecker
on 8 Oct 2019
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 notifications@github.com
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
https://github.com/HeidiSQL/HeidiSQL/issues/39?email_source=notifications&email_token=ABZDR3OEEGRQHQ47UFMKRKLQNRZYDA5CNFSM4EHYG6WKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEAT5PII#issuecomment-539482017,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABZDR3N3F22JFASVTCDYRIDQNRZYDANCNFSM4EHYG6WA
.
ionred
on 8 Oct 2019
Long story short: should work with the next build :)
ansgarbecker
on 3 Dec 2019
Related issues
mpaland
·
5Comments
naoma123
·
3Comments
rkmaier
·
5Comments
Ivan-Perez
·
3Comments
jorisrobijn
·
5Comments