|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procedure Results change after saveHi All
I am experiencing a strange happening in a Stored Procedure. The sp is built by a program (vb6) and when it is executed it returns no results. However if I go to the server and save without making any changes whatever it starts to produce rows. I have created two versions of the sp, one I have saved without changing extracted the two files and compared then and they are the same (apart from the different sp name). Does sql store any hints related to the sp rather than the sql it contains that might cause it to change the way it procesess? TIA Paul It could be a users' issue (the user responsible for the execution of that
vb6 app is not the same than user responsible from Enterprise) Show quote "Paul" wrote: > Hi All > > I am experiencing a strange happening in a Stored Procedure. The sp is built > by a program (vb6) and when it is executed it returns no results. However if > I go to the server and save without making any changes whatever it starts to > produce rows. > > I have created two versions of the sp, one I have saved without changing > extracted the two files and compared then and they are the same (apart from > the different sp name). > > Does sql store any hints related to the sp rather than the sql it contains > that might cause it to change the way it procesess? > > TIA > > Paul Hi Enric
Thanks for your post - I have experimenting and it seems to to something to do with set ansi_nulls option if i set it to off it works, set it to on and it does not??? I dont understand why yet.. Thanks again Show quote "Enric" wrote: > It could be a users' issue (the user responsible for the execution of that > vb6 app is not the same than user responsible from Enterprise) > > "Paul" wrote: > > > Hi All > > > > I am experiencing a strange happening in a Stored Procedure. The sp is built > > by a program (vb6) and when it is executed it returns no results. However if > > I go to the server and save without making any changes whatever it starts to > > produce rows. > > > > I have created two versions of the sp, one I have saved without changing > > extracted the two files and compared then and they are the same (apart from > > the different sp name). > > > > Does sql store any hints related to the sp rather than the sql it contains > > that might cause it to change the way it procesess? > > > > TIA > > > > Paul > Thanks for your post - I have experimenting and it seems to to something That would indicate that you are using logic that is dependent on the > to > do with set ansi_nulls option if i set it to off it works, set it to on > and > it does not??? setting - which should be unncessary and entirely avoidable. At a minimum, you should read the sections in BOL for "set ansi_nulls" and "create procedure" to make sure you understand how this setting works and the interplay between this setting and the creation of a stored procedure. Most likely, you have an equality condition in your logic and you intended to equate NULL with NULL - which, by definition, evaluates to unknown (not true). I would also question the need to dynamically create a stored procedure from an application. Hi Scott
Yeah since I found out about the ansi_nulls setting I spoken to the people around me and discovered that this is a problem introduced by legacy code. Know that I know this I can sort it out so that the problem goes away. As for emitting a dynamically generated sp we do this to support user reporting - again a legacy situation. When I have time I will rip it out in favour of a olap/dw solution. Regards Paul Show quote "Scott Morris" wrote: > > Thanks for your post - I have experimenting and it seems to to something > > to > > do with set ansi_nulls option if i set it to off it works, set it to on > > and > > it does not??? > > That would indicate that you are using logic that is dependent on the > setting - which should be unncessary and entirely avoidable. At a minimum, > you should read the sections in BOL for "set ansi_nulls" and "create > procedure" to make sure you understand how this setting works and the > interplay between this setting and the creation of a stored procedure. > Most likely, you have an equality condition in your logic and you intended > to equate NULL with NULL - which, by definition, evaluates to unknown (not > true). > > I would also question the need to dynamically create a stored procedure from > an application. > > >  |
|||||||||||||||||||||||
Other interesting topics