SqlDbx Forum

I felt something annoying for a long time when using SQLDBX but never stopped to
think about it.

But today when using highlight in SQLDBX 3.45, I paid attention: what was missing that, some time in past (somewhere between this version and the 3.23 i found lost on a pendrive) I remember that SQLDBX draw an red square around block/parenthesis block delimiters when highlighting... Or is my memory that's failing me?

And the annoying part is that - on my SQLServer job - the monitor almost doesn't show the actual color of the highlight ( there's actually an highlight but I can see only with BEGIN-END, it's almost invisible to me regarding parenthesis - at least on the default  white background ).

I tried to augment contrast to almost no effect.

Since I use FirebirdSQL for things on my personal laptop, I don't use SQLDBX there; so I don't know if this problem happen there.
(As I'm begging for the question: I remember that SQLDBX can use ODBC - but for FB I use another tool ).

So what's the question? Allow change the color of the highlighted text (to a more contrasting color) or draw the red square again....

Thank you very much for your attention,
Fabricio

I found block match highlight very useful on longish stored procedures, since I can detect missed ends on nested blocks.

Today, I discovered the folowing: when I write a block (BEGIN..END) and in the middle of that block exists an BEGIN TRANSACTION  instruction - the highlighter simply get nuts and link my block begin not with the correct END but with next one.

if (blablabla)
begin
    select 1 from sys.objects
   rollback
    -- logging  code
    begin transaction
        ... logging error ...
    commit transaction
end
end --- procedure

SQLDbx 3.42 Personal Test version.
Put the cursor on the begin of the if and see what end it will take.

Some weeks ago, when I was submitting a stored procedure to be reviewed by my boss,
he immediatly asked 'the procedure code is completely screwed'. I imediatly re-scripted it on SQL Server Management Studio and SQLDbx, seeing nothing strange.  So, he said: use sp_helptext.

Now I saw what happened! The procedure code appear completely screwed, to the point of being unreadable (no, the code was still there but the formating of the text was a mess). If you script from SSMS or QueryAnalyser, all appear normal 90% of the time - there are situations where code come messes a little (typically, there were wrong line breaks where it mustn't be).

Most of times, it's not completely screwed but just enough to someone not being able to compile the procedure (linebreaks inside field names, for example).

The workaround is to test with SQLDBX Personal, but only deploying with Management Studio or Query Analyser (I have 2 jobs, one with MSSQL 2005 and 2000, the other only with MSSQL2000) by copy and paste. It does not work another way - even if SQLDbx writes a *.sql and I run it from SSMS ou QueryAnalyser it gets screwed. So, I have to copy-and-paste.

How to reproduce (worked all the times):
1 - Script it with SQLDBX (using Script CREATE from popup menu)
2 - Modify the script (example, reformat CustOrderHist from database NorthWind)
3 - Press F5 to drop and recompile the procedure

Using NorthWind.dbo.CustOrderHist as guinea pig, let's start with original code
(scripted with Query Analyser, just to not have to close SQLDbx personal to
reconnect) :

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
GO

Now the code I've submitted to SQL Server:

IF OBJECT_ID ('dbo.CustOrderHist') IS NOT NULL
    DROP PROCEDURE dbo.CustOrderHist
GO

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
  SELECT 
    ProductName, Total=SUM(Quantity)
  FROM Products P, [Order Details] OD, Orders O, Customers C
  WHERE    C.CustomerID = @CustomerID
       AND C.CustomerID = O.CustomerID 
       AND O.OrderID = OD.OrderID 
       AND OD.ProductID = P.ProductID
  GROUP BY ProductName
go

If I order the SQLDBX to script, it's all ok. With SSMS or Query Analyser, it's
also ok 90% of the times, 10% some messing happens even scripting.

Now the code got from sp_HelpText:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
  SELECT 
    ProductName, Total=SUM(Quantity)
  FROM Products P, [Order Details] OD, Orders O, Customers C
  WHERE    C.CustomerID = @CustomerID
       AND C.CustomerID = O.CustomerID 
       AND O.O
rderID = OD.OrderID 
       AND OD.ProductID = P.ProductID
  GROUP BY ProductName       

This is light, I had an procedure of 200+ lines with all code messed in 10 lines!
I have an image of what I got on Notepad if I paste the proc from SSMS using the sp_helptext result directly - it gets completely screwed with some squares (on this computer they are solid black, on other the squares have just the border - maybe this is caused by font settings).
Say where I can upload/send it and I'll send.

If I remove the SQLDbx out of the equation, nothing of this happens.

If is just a question of settings, say me what setting I have to check (is not line break format setting, as it have no influence on this scenario, I tried).

As for the environment:
Servers - happens in all these server versions:
SQL Server 2000 (RTM)
SQL Server 2000 (I don't have the version of MSSQL 2000 of the other job)
SQL Server 2005 (9.0 SP2)

Version of SQLDBX:
All up to 3.41

These test were made with 3.40, but I've updated to 3.41 on the other job.
No influence AT ALL

I'd like to advocate SQLDBX to the other developers and the chief (which is also
developer) but they use sp_helptext extensively on production and I have no power to change that culture.

What I can do beside this?