Sample Header Ad - 728x90

Can comments in stored procedures cause problems

3 votes
1 answer
672 views
We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state for a long duration. I've ran this sp to track down what query is being ran by the processes and to my surprise, the following is what we discovered: --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', '' --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725', ... Several more executes that are commented out Here's what our SP looks like: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', '' --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725', ALTER PROCEDURE [SomeDB].[SP_POS_SU012_XML] @CRDATE VARCHAR(08) , @CUS_CD VARCHAR(10) , @PRODUCT_CD VARCHAR(13) , @ORDER_GB VARCHAR(03) , @CANCEL_YN VARCHAR(01) ... We weren't sure if the commented execute codes we defined for testing purposes in SP were actually being ran with the SP calls. From our internal testing, we couldn't reproduce the same behavior, but removing those commented lines of execute seemed to do the trick as sp_who2 no longed showed hundreds of processes stuck on SELECT. But since I have absolutely no way to tell if this was the real cause or not, I have to ask: could such comments actually produce side-effects described here? Or worse, could this have happened from a deliberate attempt to attack our server/database?
Asked by TtT23 (275 rep)
Apr 16, 2014, 04:18 AM
Last activity: Mar 9, 2017, 03:39 AM