We had a serious issue to address. We had
identical data in two different
SQL Servers that were yielding different results with the same code (my local
development system). It worked if I pointed SQL to the CrystalTech
server but not to the database on my local development server.
Even after I deleted the local dataset and copied it from the
remote server I still had the issue. There was no logical explanation so
I was forced to isolate the problem down to the line of code (no small task since
this process deals with thousands of lines of code in the program - and stored
procedures on the SQL side; all being
identical.
Before I continue I’ll
point out that the culprit was the version of SQL Server that I am using,
and
more importantly, that I found a
workaround. Why this is significant is because when we have
a large client base we cannot always be assured that they will have the most
recent version of SQL Server – no more than we can ensure they all will
run
Windows XP. We have to make sure the program works on the lowest
common denominator. Why did this happen? I altered the
environment when I added the retainage column but this shouldn’t have been
an
issue – I was just adding a column to a SQL statement.
Note that the third
grid has the values 0, 1, and 9. These are the values that are being
searched for by the statement AND
ISNULL(a.Cod_Category,0) IN which is immediately above the code I
removed to fix the bug. Object 402 is a line item and this section
checks to ensure that the user is either an owner of the line item – OR – the
owner of the project, either case will provide them access to the lineitem (a
1). Placing a SELECT 0
with a
This
was very difficult to isolate - I had to go line by line through a process
of
elimination (through hundreds of lines of code) because this should not have
resulted in a 0 value. Since
it does, and there was no logical explanation, I could only assume that it was
a
version
issue (which I run into a lot in my line of work). I compared the
two versions and sure enough the version that it does work on is a newer
release. I trust if I did the research I would find that
this was a bug fixed in some release between my version and the newer
version.
Following my executable/documented version of the stored
procedure code (in blue below) I will present the actual stored procedure – it
will give you a clue to what I had to work with – no documentation, some
redundancy, some useless statements, coded as a “string” versus a
SQL Statement (lose internal error checking capabilities and the ability to
execute/debug)
however it doesn’t hurt the process so it is beyond the scope of this phase
of
the project to rewrite it. The red code that follows will be
saved as part of the architectural documentation (which we have quite a bit
of
now). It permits me to execute/debug the code while providing a
necessary “proof of concept”.
SELECT
dbo.DRW_Draws.Cod_Draw,
dbo.DRW_Draws.Draw_Status,
dbo.DRW_Status.Status_Name,
CONVERT(VARCHAR(30),
dbo.DRW_Draws.Draw_Closed, 107) as Draw_Closed ,
CONVERT(VARCHAR(30),
dbo.DRW_Draws.Draw_Created, 107) AS
Draw_Created,
(SELECT
ISNULL(SUM(DISTINCT A.Type_Approved), 0)+ISNULL(SUM(DISTINCT A.Type_Denied),
0) AS PERMISSION
FROM dbo.SCR_Security A
WHERE (A.Cod_Object = 402)
AND
ISNULL(A.Cod_Category,0) IN (
--
Add in a default of 0 so we have a default value if nothing is returned on
other unions
SELECT
0
--
Add the users category to the list of applicable
categories
select
b.Cod_Category from USR_Users_Categories B where
B.Cod_user=@COD_USER
--
OBJECT 402 is a line item - if the user is an owner of the project or draw
then we'll
--
return a 9 (object owner) permitting them to view line
item.
SELECT
Cod_Category = CASE @COD_USER
--
Set Cod_Category to 9 (object owner) if there is a user record for instance
@COD_PROJECT
WHEN ( SELECT
C.Cod_User
FROM dbo.OBJ_Objects_Owners C
WHERE (C.Cod_Instance =@COD_PROJECT AND (C.Cod_Object = 402) AND (C.Cod_User =
@COD_USER))
)
THEN
9
ELSE
0
END
--
Set Cod_Category to 9 (object owner) if there is a user record for
DRW_DRAWS.Cod_Draw
WHEN ( SELECT
C.Cod_User
FROM
dbo.OBJ_Objects_Owners C
WHERE
( C.Cod_Instance =
DRW_Draws.Cod_Draw) AND (C.Cod_Object = 402) AND (C.Cod_User = @COD_USER))
THEN
9
ELSE
0
END
)
AND
ISNULL(a.cod_user,0) in (0,@cod_user)
AND
ISNULL(A.Project_Instance,0) IN
(0,@COD_PROJECT)
AND
ISNULL(A.Instance_Level1,0) IN
(0,DRW_Draws.Cod_Draw)
AND
ISNULL(A.Project_status,0) IN
(SELECT
D.Project_Status FROM dbo.PRJ_Projects D WHERE (D.Cod_Project
=@COD_PROJECT)
SELECT 0)
AND
ISNULL(A.Draw_Status,0) IN
(SELECT
E.Draw_Status FROM dbo.DRW_Draws E WHERE (E.Cod_Draw =
dbo.DRW_Draws.Cod_Draw)
SELECT 0)
AND
ISNULL(A.User_status,0) IN
(SELECT
T.User_Status FROM dbo.USR_Users T WHERE (T.Cod_User IN
(@COD_USER))
ACTUAL
STORED PROCEDURE FOLLOWS
@COD_PROJECT
INT,
@COD_USER
INT,
@WHERE
VARCHAR(100),
@ORDERBY
VARCHAR(100)
AS
DECLARE @SQL
VARCHAR(7000)
-----------------------------------------------------------------------------------------------------------------------
--DETERMINAR
WHERE
-----------------------------------------------------------------------------------------------------------------------
SET @SQL
= 'SELECT dbo.DRW_Draws.Cod_Draw,
dbo.DRW_Draws.Draw_Number, dbo.DRW_Draws.Draw_Status,
dbo.DRW_Status.Status_Name,
CONVERT(VARCHAR(30), dbo.DRW_Draws.Draw_Closed, 107) as Draw_Closed ,
CONVERT(VARCHAR(30), dbo.DRW_Draws.Draw_Created, 107) AS
Draw_Created,
(SELECT ISNULL(SUM(DISTINCT A.Type_Approved), 0)
+ ISNULL(SUM(DISTINCT A.Type_Denied), 0) AS
Permission
FROM dbo.SCR_Security
A
WHERE (A.Cod_Object = 402) AND (ISNULL(A.Cod_Category,
0) IN
(SELECT B.Cod_Category FROM USR_Users_Categories B WHERE
B.Cod_User = ' + CAST(@COD_USER AS VARCHAR(10))
+
'
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10)) + '
WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance =' + CAST(@COD_PROJECT AS
VARCHAR(10)) + ') AND (C.Cod_Object = 402) AND (C.Cod_User =' + CAST(@COD_USER
AS VARCHAR(10)) + ')) THEN 9 ELSE 0 END
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10))
+ ' WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance = dbo.DRW_Draws.Cod_Draw) AND
(C.Cod_Object = 402) AND (C.Cod_User = ' + CAST(@COD_USER AS
VARCHAR(10)) + ' )) THEN 9 ELSE 0 END
))
AND ISNULL(A.Cod_User,0) IN (0, ' + CAST(@COD_USER AS VARCHAR(10)) + '
)
AND
ISNULL(A.Project_Instance,0) IN (0,' + CAST(@COD_PROJECT AS VARCHAR(10))
+ ')
AND
ISNULL(A.Instance_Level1,0) IN
(0,dbo.DRW_Draws.Cod_Draw)
AND
ISNULL(A.Project_Status,0) IN
(SELECT
D.Project_Status
FROM
dbo.PRJ_Projects D
WHERE
(D.Cod_Project =' + CAST(@COD_PROJECT AS VARCHAR(10)) +
')
SELECT
0)
AND
ISNULL(A.Draw_Status,0) IN
(SELECT
E.Draw_Status
FROM
dbo.DRW_Draws E
WHERE
(E.Cod_Draw = dbo.DRW_Draws.Cod_Draw)
SELECT
0)
AND
ISNULL(A.User_Status,0) IN
(SELECT
T.User_Status
FROM
dbo.USR_Users T
WHERE
(T.Cod_User IN (' + CAST(@COD_USER AS VARCHAR(10)) +
'))
SELECT 0)) AS ''402'',
(SELECT ISNULL(SUM(DISTINCT
A.Type_Approved), 0) + ISNULL(SUM(DISTINCT A.Type_Denied),
0) AS Permission
FROM dbo.SCR_Security
A
WHERE (A.Cod_Object = 410) AND (ISNULL(A.Cod_Category,
0) IN
(SELECT B.Cod_Category FROM USR_Users_Categories B WHERE
B.Cod_User = ' + CAST(@COD_USER AS VARCHAR(10))
+
'
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10)) + '
WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance =' + CAST(@COD_PROJECT AS
VARCHAR(10)) + ') AND (C.Cod_Object = 410) AND (C.Cod_User =' + CAST(@COD_USER
AS VARCHAR(10)) + ')) THEN 9 ELSE 0 END
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10))
+ ' WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance = dbo.DRW_Draws.Cod_Draw) AND
(C.Cod_Object = 410) AND (C.Cod_User = ' + CAST(@COD_USER AS
VARCHAR(10)) + ' )) THEN 9 ELSE 0 END
SELECT
0))
AND ISNULL(A.Cod_User,0) IN (0, ' + CAST(@COD_USER AS VARCHAR(10)) + '
)
AND
ISNULL(A.Project_Instance,0) IN (0,' + CAST(@COD_PROJECT AS VARCHAR(10))
+ ')
AND
ISNULL(A.Instance_Level1,0) IN
(0,dbo.DRW_Draws.Cod_Draw)
AND
ISNULL(A.Project_Status,0) IN
(SELECT
D.Project_Status
FROM
dbo.PRJ_Projects D
WHERE
(D.Cod_Project =' + CAST(@COD_PROJECT AS VARCHAR(10)) +
')
SELECT
0)
AND
ISNULL(A.Draw_Status,0) IN
(SELECT
E.Draw_Status
FROM
dbo.DRW_Draws E
WHERE
(E.Cod_Draw = dbo.DRW_Draws.Cod_Draw)
SELECT
0)
AND
ISNULL(A.User_Status,0) IN
(SELECT
T.User_Status
FROM
dbo.USR_Users T
WHERE
(T.Cod_User IN (' + CAST(@COD_USER AS VARCHAR(10)) +
'))
SELECT 0)) AS ''410'',
(SELECT ISNULL(SUM(DISTINCT
A.Type_Approved), 0) + ISNULL(SUM(DISTINCT A.Type_Denied),
0) AS Permission
FROM dbo.SCR_Security
A
WHERE (A.Cod_Object = 420) AND (ISNULL(A.Cod_Category,
0) IN
(SELECT B.Cod_Category FROM USR_Users_Categories B WHERE
B.Cod_User = ' + CAST(@COD_USER AS VARCHAR(10))
+
'
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10)) + '
WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance =' + CAST(@COD_PROJECT AS
VARCHAR(10)) + ') AND (C.Cod_Object = 420) AND (C.Cod_User =' + CAST(@COD_USER
AS VARCHAR(10)) + ')) THEN 9 ELSE 0 END
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10))
+ ' WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance = dbo.DRW_Draws.Cod_Draw) AND
(C.Cod_Object = 420) AND (C.Cod_User = ' + CAST(@COD_USER AS
VARCHAR(10)) + ' )) THEN 9 ELSE 0 END
SELECT
0))
AND ISNULL(A.Cod_User,0) IN (0, ' + CAST(@COD_USER AS VARCHAR(10)) + '
)
AND
ISNULL(A.Project_Instance,0) IN (0,' + CAST(@COD_PROJECT AS VARCHAR(10))
+ ')
AND
ISNULL(A.Instance_Level1,0) IN
(0,dbo.DRW_Draws.Cod_Draw)
AND
ISNULL(A.Project_Status,0) IN
(SELECT
D.Project_Status
FROM
dbo.PRJ_Projects D
WHERE
(D.Cod_Project =' + CAST(@COD_PROJECT AS VARCHAR(10)) +
')
SELECT
0)
AND
ISNULL(A.Draw_Status,0) IN
(SELECT
E.Draw_Status
FROM
dbo.DRW_Draws E
WHERE
(E.Cod_Draw = dbo.DRW_Draws.Cod_Draw)
SELECT
0)
AND
ISNULL(A.User_Status,0) IN
(SELECT
T.User_Status
FROM
dbo.USR_Users T
WHERE
(T.Cod_User IN (' + CAST(@COD_USER AS VARCHAR(10)) +
'))
SELECT 0)) AS ''420'',
(SELECT ISNULL(SUM(DISTINCT
A.Type_Approved), 0) + ISNULL(SUM(DISTINCT A.Type_Denied),
0) AS Permission
FROM dbo.SCR_Security
A
WHERE (A.Cod_Object = 430) AND (ISNULL(A.Cod_Category,
0) IN
(SELECT B.Cod_Category FROM USR_Users_Categories B WHERE
B.Cod_User = ' + CAST(@COD_USER AS VARCHAR(10))
+
'
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10)) + '
WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance =' + CAST(@COD_PROJECT AS
VARCHAR(10)) + ') AND (C.Cod_Object = 430) AND (C.Cod_User =' + CAST(@COD_USER
AS VARCHAR(10)) + ')) THEN 9 ELSE 0 END
SELECT Cod_Category = CASE ' + CAST(@COD_USER AS VARCHAR(10))
+ ' WHEN (SELECT ISNULL(C.Cod_User,0)
FROM dbo.OBJ_Objects_Owners
C
WHERE (C.Cod_Instance = dbo.DRW_Draws.Cod_Draw) AND
(C.Cod_Object = 430) AND (C.Cod_User = ' + CAST(@COD_USER AS
VARCHAR(10)) + ' )) THEN 9 ELSE 0 END
SELECT
0))
AND ISNULL(A.Cod_User,0) IN (0, ' + CAST(@COD_USER AS VARCHAR(10)) + '
)
AND
ISNULL(A.Project_Instance,0) IN (0,' + CAST(@COD_PROJECT AS VARCHAR(10))
+ ')
AND
ISNULL(A.Instance_Level1,0) IN
(0,dbo.DRW_Draws.Cod_Draw)
AND
ISNULL(A.Project_Status,0) IN
(SELECT
D.Project_Status
FROM
dbo.PRJ_Projects D
WHERE
(D.Cod_Project =' + CAST(@COD_PROJECT AS VARCHAR(10)) +
')
SELECT
0)
AND
ISNULL(A.Draw_Status,0) IN
(SELECT
E.Draw_Status
FROM
dbo.DRW_Draws E
WHERE
(E.Cod_Draw = dbo.DRW_Draws.Cod_Draw)
SELECT
0)
AND
ISNULL(A.User_Status,0) IN
(SELECT
T.User_Status
FROM
dbo.USR_Users T
WHERE
(T.Cod_User IN (' + CAST(@COD_USER AS VARCHAR(10)) +
'))
SELECT 0)) AS
''430'',
(SELECT isnull(
dbo.udfDrawRetainage(dbo.DRW_Draws.Cod_Draw)
,0)
)
as
Retainage_Draws
FROM dbo.DRW_Draws INNER
JOIN
dbo.DRW_Status
ON dbo.DRW_Draws.Draw_Status =
dbo.DRW_Status.Cod_Status
WHERE
dbo.DRW_Draws.Draw_Project = ' + CAST(@COD_PROJECT AS VARCHAR(10)) +
''
IF @ORDERBY IS NOT
NULL AND @ORDERBY <> ''
BEGIN
SET
@SQL = @SQL + 'ORDER BY ' + @ORDERBY
END
ELSE
BEGIN
SET
@SQL = @SQL + ' ORDER BY CONVERT(VARCHAR(30), dbo.DRW_Draws.Draw_Created,
107) DESC'
END
EXECUTE
(@SQL)
RETURN
GO