SqlDbx Forum

FabricioAraujo

Member

SQLDBX and sp_HelpText

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?

sqldbxhelp

Administrator

Re: SQLDBX and sp_HelpText

Hi, Fabricio
Thank you very much for such detailed explanation of the issue.
This bug is fixed and download files have been updated.
Let us know if new version is working for you.

FabricioAraujo

Member

Re: SQLDBX and sp_HelpText

Are ya kidding me ????
So fast? Guys, you just rocks!!!

FabricioAraujo

Member

Re: SQLDBX and sp_HelpText

Wooooooooooooooooooooooooooooooooooooooooooooooowwww!!!!
Really impressive!!!!!!
Completely fixed!!!! (Tested on SQLServer 2000 RTM)

Last edited by FabricioAraujo (2010-01-13 22:17:38)