Comments stripped from new view

I've just discovered a difference between SQL Server Management Studio and DBVisualiser that essentially strips all comments out of any database views or procedures we create. I noticed that comments have been stripped from the DDL of several of our database objects when compared to the original source code. As a test, I ran the attached SQL to create a new view with a few comments in it (sorry, I can't put it inline as the forum mangles the code) Running the query in Management Studio then examining the new view in DBVisualizer's DDL tab results in exactly what I expected. Running the query in DBVisualizer then examing the new view in DBVisualizer's DDL tab results all the comments vanishing. Both the SSMS and DBVisualizer results are attached. Many of our views are quite complex and require comments to understand what's going on, who the author is, what the view's purpose is, etc. Though these all still exist in our source SQL files, people often browse the database directly and might not realise that there should be extra comments in a view. Worse than this though - when I changed the CREATE VIEW command to a CREATE PROCEDURE and retested, the comments were still stripped out by DBVisualizer. It's even more important that stored procedures retain comments than views. I'm accessing a SQL Server 2008 database via jTDS. I've not tried the native driver so do not know if that has the same issues. I'm using DBVisualizer 8.0.10. Is there some way to turn this behaviour off and send the raw query to the DBMS, comments and all?

I've found the solution to this now. For others experiencing this odd behaviour, DBVisualizer's Tool Properties (or options/preferences, as the rest of the world refers to them) contain a SQL Editor>Comments section. In here you define what DBVisualizer thinks is a comment and these are stripped out of all queries. We had these set to the SQL Server comment characters of --, /* and */. Setting all these fields to an obscure value such as ¬¬ allows SQL queries to be sent direct to the server with comments intact. SQL Server understands comments anyway so removing them may save a tiny bit of network bandwidth but won't achieve any great performance gains. I'll be suggesting the rest of our developers also prevent DBVisualizer from pre-filtering them.

Hi, When you use the Execute action in the SQL Commander, DbVisualizer strips off all comments as you have seen. This is because many databases do not accept comments for statements sent via JDBC. If your database accepts them, a better way to prevent them from being stripped than changing the delimiters in Tool Properties is to use Execute Buffer, or enclose the statement in SQL Block delimiters (--/ and / by default): --/ CREATE VIEW ? / For procedures, functions etc, we recommend that you use the Procedure/Function Editor instead of the SQL Commander. The Procedure Editor does not strip off comments or try to split the code into separate statements based on statement delimiters. You can read more about this in the Users Guide: Execute Buffer http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId958847 SQL Blocks http://www.dbvis.com/doc/main/doc/ug/sqlCommander/sqlCommander.html#mozTocId652495 Procedure Editor http://www.dbvis.com/doc/main/doc/ug/procEditor/procEditor.html Best Regards, Hans

Hi Hans, Sadly the option of using SQL block delimiters is no use to us because not all our developers use DBVisualizer. We can't put non-standard SQL syntax in our SQL script files or SQL Server Management Studio would fail to execute it. The execute buffer option is better but relies on all our developers remembering it and also no longer gives us the option of highlighting a chunk of code from a script then executing that with comments included. Executing via the Procedure/Function Editor is again only useful in limited circumstances. In this case where the only thing the SQL does is recreate the stored procedure. We often need to execute SQL files that alter a table structure, repopulate its data and rebuild stored procedures or views that rely on the altered columns. We want to store these scripts as a single SQL file but be able to highlight and execute parts of the script separately to make sure the database looks correct at each stage. Unfortunately, changing the comment delimiters in Tool Properties also broke the syntax highlighting so I've had to restore it back to the default settings. Could I put in a feature request, allowing us the ability to indicate via a check box in the connection properties whether comments should be stripped before execution? For now, I'm just advising our developers not to use DBVisualizer to execute any change scripts that rebuild views, functions or procedures with comments. A look through our database revealed quite a number of our stored procedures that have had comments vanish, presumably due to this issue. Thanks

Hi, Okay, I agree that what we currently offer is not an ideal solution for cases like the one you present. We will look into making stripping of comments configurable in the next feature release. Thanks for clarifying the your scenario. Best Regards, Hans

Hi Roger, Re: new feature in BETA release SQL Commander Make stripping of comments configureable [http://www.dbvis.com/forum/thread.jspa?messageID=14485 ] Thanks very much for this feature. The problem I have is that I can't find where to set it! I just downloaded the latest beta version. Product: DbVisualizer Pro 90.1860 Build: #1860 (2012/11/12 11:50) Thanks, Gary

Hi Vince, I asked for this feature(sending comments to the database) a while ago. Thanks for raising this. It appears to be included in the latest release. Gary

Hi, You enable/disable this in the SQL Commander top level menu. Best Regards, Hans