December 2, 2002 at 11:02 am
Can anybody help with this:
I've recently modifed a set of functions, the changes of which included adding a parameter to the existing param list. The changes work fine on my local db, but after they have been scripted via Power Designer, and added into our main db, they don't work.
Without going into all gory details, if I simply edit the functions in Enterprise Manager (i.e. add a space and delete it so the apply button lights up), and apply the change, they work again.
This, I find somewhat worrying. Mysteriously, half the functions work, the other half need this manual edit. In so far as I can see, there is nothing to differentiate the working functions from the lame ones!
If it helps, the functions are all recursive, and return cursors. I've looked at issues with the sysdepends table but havn't really found an answer I'm happy with.
December 2, 2002 at 7:37 pm
Check the scripts created by Power Designer and see if anything is unusally in how they are copied. Could be moving to the table directly and not issuing an alter (not sure how they could do this). May be that they are bypassing the process that refreshes it in memory, to test this theory you try stopping and starting SQL Server and see if corrects, if does then refer issue to vendor as a bug.
December 3, 2002 at 3:24 am
Thanks, but no luck as yet. Shutting down SQL Server doesn't make any difference. If I issue an alter function command, that doesn't make any difference either. The scripts from Power Designer seem pretty standard - all they do is drop the existing function, then recreate it. Whether or not it could do with some sort of process to force SQL to realise that they have been droppped and recreated, I know not.
To give some more detail: The new functions (or amendments to the initial functions) are being executed - just not correctly. So SQL Server is doing something with them - but for some reason it isn't evaluating/executing the logic behind them correctly. I'm thinking this has something to do with the fact that they are recursive.
I get the incorrect evaluation the first time the function recurses. I know that the new functions have been compiled[incorrectly] because the output given could only have come from my new functions - it just should'nt be happening. The only way I can see to sort it out is by replicating whatever the 'apply button' does in the function editor. Any ideas on how to go about this(sp_recompile doesn't seem to like function names - I've looked at this stored proc and forced it to recompile but with no success).
Edited by - cbj on 12/03/2002 03:26:57 AM
December 3, 2002 at 4:28 am
Dropping and creating should cause SQL to use correctly. This could be a bug. What Service Pack is installed and if not the latest have you tested to see if it corrects the issue. If so then can you provide maybe a few examples for us to test to verify if is a bug?
December 3, 2002 at 8:51 am
I didn't have any service packs installed, but have since applied sp1 and sp2.... No change though!!!
Below is a copy of the errant function:
CREATE FUNCTION DBO.GET_PARENTORGANISATIONS(@ORGID INT, @RECLEVEL INT = 0, @CUSTIDLIST VARCHAR(220))
RETURNS @ORGTAB TABLE
(ID INT,
CLASSID INT,
PARENTID INT,
LEVEL INT,
debug varchar(220),
id2 int)
AS
BEGIN
IF @RECLEVEL < 31 -- Preven SS2000's recursive limit of 32 levels from being breached
BEGIN
DECLARE
@PARENTORGID INT,
@PARENTPARENTORGID INT,
@ORGCLASSID INT
--for the first iteration, insert the organisation specified by @ORGID
IF @RECLEVEL = 0
BEGIN
SET @CUSTIDLIST = ','
INSERT @ORGTAB
SELECT ORGID ID, CLASSID, PARENTID, @RECLEVEL, @custidlist,0 FROM ORGANISATION WHERE ORGID=@ORGID
END
--get organisation parent info
DECLARE ORG_CUR CURSOR FOR
SELECT ORGID, CLASSID, PARENTID FROM ORGANISATION WHERE ORGID= (SELECT PARENTID FROM ORGANISATION WHERE ORGID = @ORGID)
OPEN ORG_CUR
FETCH NEXT FROM ORG_CUR INTO @PARENTORGID, @ORGCLASSID, @PARENTPARENTORGID
IF (@@FETCH_STATUS = 0) AND (@PARENTORGID IS NOT NULL)
BEGIN
declare @temp int
--Make sure we don't have a cyclic relantionship
SET @CUSTIDLIST = @CUSTIDLIST+LTRIM(STR(@PARENTPARENTORGID))+','
set @temp = patindex('%,'+LTRIM(STR(@ORGID))+',%',@CUSTIDLIST)
*******IF patindex('%,'+LTRIM(STR(@ORGID))+',%',@CUSTIDLIST) = null or patindex('%,'+LTRIM(STR(@ORGID))+',%',@CUSTIDLIST)=0
BEGIN
SET @RECLEVEL = @RECLEVEL - 1
--insert the parentid into the result, along with the parentid's parents
INSERT @ORGTAB
SELECT ID, CLASSID, PARENTID, LEVEL, debug,@temp id2 FROM DBO.GET_PARENTORGANISATIONS(@PARENTORGID, @RECLEVEL, @CUSTIDLIST)
UNION
SELECT @PARENTORGID ID, @ORGCLASSID CLASSID, @temp PARENTID, @RECLEVEL, @custidlist debug,@temp id2
END
ELSE -- We have a cyclic relationship
BEGIN
INSERT @ORGTAB VALUES (-1, @ORGCLASSID, @PARENTPARENTORGID, @RECLEVEL,@custidlist,@temp)
END
END
CLOSE ORG_CUR
DEALLOCATE ORG_CUR
END
ELSE -- Hierarchy deeper than SS2000's limit of 32 recursive calls so exit gracefully
BEGIN
INSERT @ORGTAB VALUES (-2, @ORGCLASSID, @PARENTPARENTORGID, @RECLEVEL, @custidlist,@PARENTPARENTORGID)
END
RETURN
END
... apoloiges if it looks a mess!! Probably not the best place to display it. However, the line marked '*******' seems to be the one where things go hoopy. This function also returns two debug columns; @custidlist and the second part of the if ... expression on the highlighted line. Basically, it evaluates to true,(the debug values prove this), but for some reason SS2000 evaluates this to false. As mentioned, if I edit the function in Enterprise Manager, it starts working properly!!
As an aside, I think we can rule out Power Designer causing any problems; if I copy the function onto the clipboard, then drop it and paste the function into the query analyser and execute it [to create it again], I get the same problem.
Also checking dependencies before and after the Enterprise manger 'edit' doesn't show any differences.
Edited by - cbj on 12/03/2002 08:54:32 AM
December 3, 2002 at 9:29 am
Got it!!
And its relatively obvious.
A lesson to us all, and especially me. In my last post, I highlighted the line that I thought was causing the problem. This is the case, and why?
Whenever comparing a value to null, I'm sure that using '=' is dodgey business. Use 'is' instead. Hence the line should read:
IF patindex('%,'+LTRIM(STR(@ORGID))+',%',@CUSTIDLIST) is null or patindex('%,'+LTRIM(STR(@ORGID))+',%',@CUSTIDLIST)=0
Although this is probably more syntactically correct, it still doesn't explain all the funny stuff with editing it through Enterprise Manager. That is unless Enterprise Manager, in some way, automatically caters for the above gaffe.
Thanks for the help anyhow.
December 3, 2002 at 10:49 am
Acutally, there is another thread on this very subject which I was involved in but I cannot find right now.
However, the reason = NULL does not behave the same as IS NULL is this. When a value is defined but not set the value is "unknown" or does not evaluate to anything. NULL is an actual value it means does nothing. So when you do a check for = NULL it is looking for a value that has been set explicitly to NULL. When you do IS NULL it checks both the unknown condition and the value NULL. You have to be very carefull not to mix. But explicitly set a value to NULL then = NULL will work.
December 4, 2002 at 5:05 pm
on a slightly different note, can you explain the differences (or what appear to be differences) in the way a function is compiled through Enterprise Manager's function editor, as opposed to creating it through the query analyser; i.e. it would appear that both treat 'x=null' differenty - I'd say that Enterprise Manager has got it wrong. While its very noble of it to correct my programming mistakes, its also very dangerous feature - especially when third party tools are used to maintain db schemas etc.
December 5, 2002 at 5:28 pm
I didn't think with regards to that, that anything was different. Might watch both with profiler to see if anything happens on the backend under the hood. But considering they have multiple developers there could be a difference I just haven't come across. WHat specifically are you seeing is happening differently (sorry maybe I missed it in your post)? I am pretty sure thou that if you open it an already created one in EM and apply or OK it will change the create to an alter and run against the DB, where QA requires you to think about that.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply