Solved

sql server stored procedure review

Posted on 2010-11-17
15
469 Views
Last Modified: 2012-05-10
I am having some locking issues after running this vendor written stored procedure and wondered if someone could take a look at it and see if there were any logic in here that might cause some locking issues or something that may be hanging up after the stored procedure is called the first time.  thanks.  

CREATE PROCEDURE [dbo].[UTILITYBILL_IN]
   with RECOMPILE
 AS
BEGIN
    DECLARE

     @Spc VARCHAR(15),
     @Id1 VARCHAR(5),
     @Id2 VARCHAR(5),
     @Id3 VARCHAR(5),
     @per_type VARCHAR(30),
     @per_sub_type varchar(30),
     @Checkbox_Type VARCHAR(30),
     @PermitNum VARCHAR(30),
     @AccountNum VARCHAR(6),
     @ServiceNum  VARCHAR(6),
     @AccountNum_loaded  VARCHAR(6),
     @ServiceNum_loaded  VARCHAR(6),
     @rc int,
     @count int,
     @c CURSOR
  BEGIN
    SET NOCOUNT ON;
    TRUNCATE TABLE  stg_ubill_in
    TRUNCATE TABLE  stg_exception_report
   
    GO

      EXECUTE   @rc = master.dbo.xp_cmdshell "bcp.exe dbmain.dbo.stg_ubill_in in \\homedir\cstar2\WB\Water.txt -f \\homedir\star\WB\Water.fmt -F 2 -r \n -S PRMSQL -T"

    IF @rc != 0
     INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
     VALUES ('NA','Bulk Copy In', 'There was an error with the bulk copy when attempting to copy the data from the Water.txt file')
       
    SET @c = CURSOR FAST_FORWARD FOR
       SELECT a.serv_prov_code, a.b1_per_id1, a.b1_per_id2, a.b1_per_id3, a.b1_alt_id, a.b1_per_type, a.b1_per_sub_type,
          rtrim(ltrim(b.accountnum)), rtrim(ltrim(b.servicenum))  
       FROM b1permit a, stg_ubill_in b
       WHERE a.serv_prov_code = 'TEST'
         AND  upper(rtrim(ltrim(a.b1_alt_id))) = upper(rtrim(ltrim(b.permitnum)))
     
    OPEN @c
    FETCH NEXT FROM  @c  INTO @spc, @Id1, @Id2, @Id3, @PermitNum, @Per_Type, @per_sub_type, @accountnum, @serviceNum
    WHILE (@@FETCH_STATUS = 0 )            
     BEGIN
            IF @Per_Type = 'WaterMeter'
                  SET @Checkbox_Type = 'ENGINEERING WATER METER'
            ELSE
                  SET @Checkbox_Type = 'ENGINEERING WATER TAP'

       -- logic for account number and service location if value already exists

        SELECT  @count = count(*)
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Account Number'
          AND b1_checklist_comment = @accountnum

        IF @count > 0
          INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
          VALUES (@permitnum,'Data Already Exists', @permitnum + ' The account number was already in database')

        SELECT  @count = count(*)
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Service Location Number'
          AND b1_checklist_comment = @ServiceNum


        IF  @count > 0
          INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
          VALUES (@permitnum,'Data Already Exists', @permitnum + ' The service location number was already in database')

         -- data already exists but  incoming value is different than that of the existing data.

        SELECT  @accountnum_loaded  = isnull(b1_checklist_comment,' ')
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Account Number'

        if @@rowcount =1
           BEGIN  
            if @accountnum != @accountnum_loaded and @accountnum_loaded != ' '
             BEGIN
              INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
              VALUES (@permitnum, 'Different Data', @permitnum + ' - Account number is different from existing data Incoming Value ' + @accountnum+ ' Existing Value ' + @accountnum_loaded )
             END

             UPDATE bchckbox
               SET b1_checklist_comment = @accountnum
                 , REC_FUL_NAM = 'UB ADMIN'
                 , REC_DATE = GETDATE()
              WHERE  serv_prov_code = @spc
               AND b1_per_id1 = @id1
               AND b1_per_id2 = @id2
               AND b1_per_id3 = @id3
               AND b1_checkbox_type = @Checkbox_Type
               AND b1_checkbox_desc = 'Utility Billing Account Number'
           END
        ELSE   -- record does not exist
           BEGIN

             INSERT INTO BCHCKBOX (
               SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3,
               B1_PER_TYPE, B1_PER_SUB_TYPE, B1_CHECKBOX_TYPE, B1_CHECKBOX_DESC,
               B1_CHECKBOX_IND, B1_ACT_STATUS, B1_START_DATE, B1_END_DATE,
               B1_CHECKLIST_COMMENT, REC_DATE, REC_FUL_NAM, REC_STATUS, B1_DISPLAY_ORDER,
               B1_FEE_INDICATOR, B1_ATTRIBUTE_VALUE,  B1_ATTRIBUTE_UNIT_TYPE, B1_ATTRIBUTE_VALUE_REQ_FLAG, B1_VALIDATION_SCRIPT_NAME, RELATION_SEQ_ID,
               SD_STP_NUM, B1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, B1_DEFAULT_SELECTED,
               B1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, B1_REQ_FEE_CALC, B1_SUPERVISOR_EDIT_ONLY_FLAG )
             SELECT   @spc, @id1, @id2, @id3,
               @PER_TYPE, @PER_SUB_TYPE, R1_CHECKBOX_TYPE, R1_CHECKBOX_DESC,
               R1_CHECKBOX_IND, R1_CHECKBOX_CODE, NULL, NULL,
               @ACCOUNTNUM, GETDATE(), 'UB ADMIN' , 'A', R1_DISPLAY_ORDER,
               R1_FEE_INDICATOR, R1_ATTRIBUTE_VALUE,  R1_ATTRIBUTE_UNIT_TYPE, R1_ATTRIBUTE_VALUE_REQ_FLAG, R1_VALIDATION_SCRIPT_NAME, 0,
               0, R1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, R1_DEFAULT_SELECTED,
               R1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, R1_REQ_FEE_CALC, R1_SUPERVISOR_EDIT_ONLY_FLAG
             FROM  r2chckbox
             WHERE   serv_prov_code = @spc
               and r1_checkbox_type = @checkbox_type
               and  r1_checkbox_desc = 'Utility Billing Account Number'
               and r1_checkbox_group = 'APPLICATION'

           end

        SELECT @serviceNum_loaded  = isnull(b1_checklist_comment , ' ')
        FROM  bchckbox
        WHERE  serv_prov_code = @spc
          AND b1_per_id1 = @id1
            AND b1_per_id2 = @id2
          AND b1_per_id3 = @id3
          AND b1_checkbox_type = @Checkbox_Type
          AND b1_checkbox_desc = 'Utility Billing Service Location Number'

        if @@rowcount =1
           BEGIN  
            if @servicenum != @servicenum_loaded and @servicenum_loaded != ' '
             BEGIN
              INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)
              VALUES (@permitnum, 'Different Data', @permitnum + ' - service number is different from existing data Incoming Value ' + @accountnum+ ' Existing Value ' + @accountnum_loaded )
             END

             UPDATE bchckbox
               SET b1_checklist_comment = @servicenum
                 , REC_FUL_NAM = 'UB ADMIN'
                 , REC_DATE = GETDATE()
              WHERE  serv_prov_code = @spc
               AND b1_per_id1 = @id1
               AND b1_per_id2 = @id2
               AND b1_per_id3 = @id3
               AND b1_checkbox_type = @Checkbox_Type
               AND b1_checkbox_desc = 'Utility Billing Service Location Number'
           END
        ELSE   -- record does not exist
           BEGIN
             INSERT INTO BCHCKBOX (
               SERV_PROV_CODE, B1_PER_ID1, B1_PER_ID2, B1_PER_ID3,
               B1_PER_TYPE, B1_PER_SUB_TYPE, B1_CHECKBOX_TYPE, B1_CHECKBOX_DESC,
               B1_CHECKBOX_IND, B1_ACT_STATUS, B1_START_DATE, B1_END_DATE,
               B1_CHECKLIST_COMMENT, REC_DATE, REC_FUL_NAM, REC_STATUS, B1_DISPLAY_ORDER,
               B1_FEE_INDICATOR, B1_ATTRIBUTE_VALUE,  B1_ATTRIBUTE_UNIT_TYPE, B1_ATTRIBUTE_VALUE_REQ_FLAG, B1_VALIDATION_SCRIPT_NAME, RELATION_SEQ_ID,
               SD_STP_NUM, B1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, B1_DEFAULT_SELECTED,
               B1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, B1_REQ_FEE_CALC, B1_SUPERVISOR_EDIT_ONLY_FLAG )
             SELECT   @spc, @id1, @id2, @id3,
               @PER_TYPE, @PER_SUB_TYPE, R1_CHECKBOX_TYPE, R1_CHECKBOX_DESC,
               R1_CHECKBOX_IND, R1_CHECKBOX_CODE, NULL, NULL,
               @servicenum, GETDATE(), 'UB ADMIN' , 'A', R1_DISPLAY_ORDER,
               R1_FEE_INDICATOR, R1_ATTRIBUTE_VALUE,  R1_ATTRIBUTE_UNIT_TYPE, R1_ATTRIBUTE_VALUE_REQ_FLAG, R1_VALIDATION_SCRIPT_NAME, 0,
               0, R1_CHECKBOX_GROUP, MAX_LENGTH, DISPLAY_LENGTH, R1_DEFAULT_SELECTED,
               R1_GROUP_DISPLAY_ORDER , VCH_DISP_FLAG , R1_TASK_STATUS_REQ_FLAG, R1_REQ_FEE_CALC, R1_SUPERVISOR_EDIT_ONLY_FLAG
             FROM  r2chckbox
             WHERE   serv_prov_code = @spc
               and r1_checkbox_type = @checkbox_type
               and  r1_checkbox_desc = 'Utility Billing Service Location Number'
               and r1_checkbox_group = 'APPLICATION'

              -- Insert_asi (@permitnum, @checkbox_type, 'Utility Billing Service Location Number' )
           end

       FETCH NEXT FROM  @c  INTO @spc, @Id1, @Id2, @Id3, @PermitNum, @Per_Type, @per_sub_type,  @accountnum, @serviceNum
     END

    CLOSE @c
    DEALLOCATE @c
  END
         -- load exception table for data in stg_ubil_in but not in b1permit
     INSERT INTO dbo.stg_exception_report (appnum, exception_type, exception_msg)  
     SELECT ltrim(rtrim(a.permitnum)) , 'Invalid Data', 'This app number was in stg_ubill_in but not in b1permit'
     FROM  stg_ubill_in a left  join b1permit b on
       upper(rtrim(ltrim(a.permitnum))) = upper(rtrim(ltrim(b.b1_alt_id)))
     WHERE b.b1_alt_id is null
 
 
END
0
Comment
Question by:Taitor
  • 4
  • 3
  • 3
  • +3
15 Comments
 

Author Comment

by:Taitor
ID: 34161776
Is this question not realistic?  I think I need a refund from this site.
0
 
LVL 47

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
ID: 34162416
Taitor, we need more information. Usually locks are caused by long running queries that can be speed up with creation of indexes.
Can you post the indexes for all the tables that are used in this SP so we can give you a better help?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34162516
Hi,

I have have an issue with how you have declared the cursor.

http://msdn.microsoft.com/en-us/library/ms180169.aspx

I think that there is a little too much in this one procedure to get a handle on it easily - you are using command shell call to bcp for instance.

Since you are using SQL 2005, I'd wrap the whole contents of the procedure in a begin try end tray begin catch end catch - it really aids debugging as it reports where the error occurred - even if there are nexted procedure calls and so forth.

There is a GO in an unusual place - just after the truncate statements - unusual to call a procedure with so many parameters and all that is done is to truncate two tables.

While developing/debugging I'd comment out the nocount on.

HTH
  David

PS Something of this complexity is quite difficult to debug/develop on-line without the actual tables etc.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 250 total points
ID: 34165190
From your description of the problem, "some locking issues or something that may be hanging up", it looks like you don't really know what the problem is. So maybe we could start with clarifying what exactly happens when you run this stored procedure.

The next step most likely will be starting Profiler, and looking at the specific SQL statements as they are being executed within the stored procedure, to find out which one is creating the problem.
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 125 total points
ID: 34165608
I ran a formatting app on your code (so that I could read it more easily ;-) and have attached it in the Code section.  (Using that section makes iteasier to copy and also to read because it uses a different font and doesn't remove leading white space. ;-)

First a couple of points of style that I would suggest adopting.  If you put a semicoln (;) at the end of each "sentence", your code will be easier to read and it may also preclude wome issues.  I have also found the wrapping the then and else clauses of IF statements with BEGIN and END statements can make the code more readable (even if there is only one statement withing the BEGIN/END bracketing ;-).

Now, looking at line 24, why do you have a GO there?  The two TRUNCATE statements will work fine without it. ;-)  This might be something that could cause the SP to hang.

At line 26, you test for a problem with the bulk copy; however,, starting at line 40, you continue to process things in spite of the potential bulk copy error.  Is this what you really want to do?


001 CREATE PROCEDURE [dbo].[UTILITYBILL_IN] WITH RECOMPILE
002 AS
003    BEGIN
004       DECLARE @Spc          VARCHAR(15),
005          @Id1               VARCHAR(5),
006          @Id2               VARCHAR(5),
007          @Id3               VARCHAR(5),
008          @per_type          VARCHAR(30),
009          @per_sub_type      VARCHAR(30),
010          @Checkbox_Type     VARCHAR(30),
011          @PermitNum         VARCHAR(30),
012          @AccountNum        VARCHAR(6),
013          @ServiceNum        VARCHAR(6),
014          @AccountNum_loaded VARCHAR(6),
015          @ServiceNum_loaded VARCHAR(6),
016          @rc                INT,
017          @count             INT,
018          @c
019       CURSOR
020          BEGIN
021             SET NOCOUNT ON;
022             TRUNCATE TABLE stg_ubill_in
023             TRUNCATE TABLE stg_exception_report
024          GO
025          EXECUTE @rc = master.dbo.xp_cmdshell "bcp.exe dbmain.dbo.stg_ubill_in in \\homedir\cstar2\WB\Water.txt -f \\homedir\star\WB\Water.fmt -F 2 -r \n -S PRMSQL -T"
026          IF @rc != 0
027          INSERT
028          INTO dbo.stg_exception_report
029             (
030                appnum,
031                exception_type,
032                exception_msg
033             )
034             VALUES
035             (
036                'NA',
037                'Bulk Copy In',
038                'There was an error with the bulk copy when attempting to copy the data from the Water.txt file'
039             )
040          SET @c =
041          CURSOR FAST_FORWARD FOR
042             SELECT a.serv_prov_code,
043                a.b1_per_id1,
044                a.b1_per_id2,
045                a.b1_per_id3,
046                a.b1_alt_id,
047                a.b1_per_type,
048                a.b1_per_sub_type,
049                RTRIM(ltrim(b.accountnum)),
050                RTRIM(ltrim(b.servicenum))
051             FROM b1permit a,
052                stg_ubill_in b
053             WHERE a.serv_prov_code = 'TEST'
054                AND UPPER(RTRIM(ltrim(a.b1_alt_id))) = UPPER(RTRIM(ltrim(b.permitnum)))
055          OPEN @c
056          FETCH NEXT
057          FROM @c
058          INTO @spc,
059             @Id1,
060             @Id2,
061             @Id3,
062             @PermitNum,
063             @Per_Type,
064             @per_sub_type,
065             @accountnum,
066             @serviceNum
067          WHILE (@@FETCH_STATUS = 0 )
068          BEGIN
069             IF @Per_Type = 'WaterMeter'
070             SET @Checkbox_Type = 'ENGINEERING WATER METER'
071             ELSE
072             SET @Checkbox_Type = 'ENGINEERING WATER TAP'
073             -- logic for account number and service location if value already exists
074             SELECT @count = COUNT(*)
075             FROM bchckbox
076             WHERE serv_prov_code = @spc
077                AND b1_per_id1 = @id1
078                AND b1_per_id2 = @id2
079                AND b1_per_id3 = @id3
080                AND b1_checkbox_type = @Checkbox_Type
081                AND b1_checkbox_desc = 'Utility Billing Account Number'
082                AND b1_checklist_comment = @accountnum
083             IF @count > 0
084             INSERT
085             INTO dbo.stg_exception_report
086                (
087                   appnum,
088                   exception_type,
089                   exception_msg
090                )
091                VALUES
092                (
093                   @permitnum,
094                   'Data Already Exists',
095                   @permitnum + ' The account number was already in database'
096                )
097             SELECT @count = COUNT(*)
098             FROM bchckbox
099             WHERE serv_prov_code = @spc
100                AND b1_per_id1 = @id1
101                AND b1_per_id2 = @id2
102                AND b1_per_id3 = @id3
103                AND b1_checkbox_type = @Checkbox_Type
104                AND b1_checkbox_desc = 'Utility Billing Service Location Number'
105                AND b1_checklist_comment = @ServiceNum
106             IF @count > 0
107             INSERT
108             INTO dbo.stg_exception_report
109                (
110                   appnum,
111                   exception_type,
112                   exception_msg
113                )
114                VALUES
115                (
116                   @permitnum,
117                   'Data Already Exists',
118                   @permitnum + ' The service location number was already in database'
119                )
120             -- data already exists but  incoming value is different than that of the existing data.
121             SELECT @accountnum_loaded = ISNULL(b1_checklist_comment, ' ')
122             FROM bchckbox
123             WHERE serv_prov_code = @spc
124                AND b1_per_id1 = @id1
125                AND b1_per_id2 = @id2
126                AND b1_per_id3 = @id3
127                AND b1_checkbox_type = @Checkbox_Type
128                AND b1_checkbox_desc = 'Utility Billing Account Number'
129             IF @@rowcount =1
130             BEGIN
131                IF @accountnum != @accountnum_loaded
132                AND @accountnum_loaded != ' '
133                BEGIN
134                   INSERT
135                   INTO dbo.stg_exception_report
136                      (
137                         appnum,
138                         exception_type,
139                         exception_msg
140                      )
141                      VALUES
142                      (
143                         @permitnum,
144                         'Different Data',
145                         @permitnum + ' - Account number is different from existing data Incoming Value ' + @accountnum+ ' Existing Value ' + @accountnum_loaded
146                      )
147                END
148                UPDATE bchckbox
149                SET b1_checklist_comment = @accountnum,
150                   REC_FUL_NAM = 'UB ADMIN',
151                   REC_DATE = GETDATE()
152                WHERE serv_prov_code = @spc
153                   AND b1_per_id1 = @id1
154                   AND b1_per_id2 = @id2
155                   AND b1_per_id3 = @id3
156                   AND b1_checkbox_type = @Checkbox_Type
157                   AND b1_checkbox_desc = 'Utility Billing Account Number'
158             END
159             ELSE -- record does not exist
160             BEGIN
161                INSERT
162                INTO BCHCKBOX
163                   (
164                      SERV_PROV_CODE,
165                      B1_PER_ID1,
166                      B1_PER_ID2,
167                      B1_PER_ID3,
168                      B1_PER_TYPE,
169                      B1_PER_SUB_TYPE,
170                      B1_CHECKBOX_TYPE,
171                      B1_CHECKBOX_DESC,
172                      B1_CHECKBOX_IND,
173                      B1_ACT_STATUS,
174                      B1_START_DATE,
175                      B1_END_DATE,
176                      B1_CHECKLIST_COMMENT,
177                      REC_DATE,
178                      REC_FUL_NAM,
179                      REC_STATUS,
180                      B1_DISPLAY_ORDER,
181                      B1_FEE_INDICATOR,
182                      B1_ATTRIBUTE_VALUE,
183                      B1_ATTRIBUTE_UNIT_TYPE,
184                      B1_ATTRIBUTE_VALUE_REQ_FLAG,
185                      B1_VALIDATION_SCRIPT_NAME,
186                      RELATION_SEQ_ID,
187                      SD_STP_NUM,
188                      B1_CHECKBOX_GROUP,
189                      MAX_LENGTH,
190                      DISPLAY_LENGTH,
191                      B1_DEFAULT_SELECTED,
192                      B1_GROUP_DISPLAY_ORDER,
193                      VCH_DISP_FLAG,
194                      R1_TASK_STATUS_REQ_FLAG,
195                      B1_REQ_FEE_CALC,
196                      B1_SUPERVISOR_EDIT_ONLY_FLAG
197                   )
198                SELECT @spc,
199                   @id1,
200                   @id2,
201                   @id3,
202                   @PER_TYPE,
203                   @PER_SUB_TYPE,
204                   R1_CHECKBOX_TYPE,
205                   R1_CHECKBOX_DESC,
206                   R1_CHECKBOX_IND,
207                   R1_CHECKBOX_CODE,
208                   NULL,
209                   NULL,
210                   @ACCOUNTNUM,
211                   GETDATE(),
212                   'UB ADMIN',
213                   'A',
214                   R1_DISPLAY_ORDER,
215                   R1_FEE_INDICATOR,
216                   R1_ATTRIBUTE_VALUE,
217                   R1_ATTRIBUTE_UNIT_TYPE,
218                   R1_ATTRIBUTE_VALUE_REQ_FLAG,
219                   R1_VALIDATION_SCRIPT_NAME,
220                   0,
221                   0,
222                   R1_CHECKBOX_GROUP,
223                   MAX_LENGTH,
224                   DISPLAY_LENGTH,
225                   R1_DEFAULT_SELECTED,
226                   R1_GROUP_DISPLAY_ORDER,
227                   VCH_DISP_FLAG,
228                   R1_TASK_STATUS_REQ_FLAG,
229                   R1_REQ_FEE_CALC,
230                   R1_SUPERVISOR_EDIT_ONLY_FLAG
231                FROM r2chckbox
232                WHERE serv_prov_code = @spc
233                   AND r1_checkbox_type = @checkbox_type
234                   AND r1_checkbox_desc = 'Utility Billing Account Number'
235                   AND r1_checkbox_group = 'APPLICATION'
236             END
237             SELECT @serviceNum_loaded = ISNULL(b1_checklist_comment, ' ')
238             FROM bchckbox
239             WHERE serv_prov_code = @spc
240                AND b1_per_id1 = @id1
241                AND b1_per_id2 = @id2
242                AND b1_per_id3 = @id3
243                AND b1_checkbox_type = @Checkbox_Type
244                AND b1_checkbox_desc = 'Utility Billing Service Location Number'
245             IF @@rowcount =1
246             BEGIN
247                IF @servicenum != @servicenum_loaded
248                AND @servicenum_loaded != ' '
249                BEGIN
250                   INSERT
251                   INTO dbo.stg_exception_report
252                      (
253                         appnum,
254                         exception_type,
255                         exception_msg
256                      )
257                      VALUES
258                      (
259                         @permitnum,
260                         'Different Data',
261                         @permitnum + ' - service number is different from existing data Incoming Value ' + @accountnum+ ' Existing Value ' + @accountnum_loaded
262                      )
263                END
264                UPDATE bchckbox
265                SET b1_checklist_comment = @servicenum,
266                   REC_FUL_NAM = 'UB ADMIN',
267                   REC_DATE = GETDATE()
268                WHERE serv_prov_code = @spc
269                   AND b1_per_id1 = @id1
270                   AND b1_per_id2 = @id2
271                   AND b1_per_id3 = @id3
272                   AND b1_checkbox_type = @Checkbox_Type
273                   AND b1_checkbox_desc = 'Utility Billing Service Location Number'
274             END
275             ELSE -- record does not exist
276             BEGIN
277                INSERT
278                INTO BCHCKBOX
279                   (
280                      SERV_PROV_CODE,
281                      B1_PER_ID1,
282                      B1_PER_ID2,
283                      B1_PER_ID3,
284                      B1_PER_TYPE,
285                      B1_PER_SUB_TYPE,
286                      B1_CHECKBOX_TYPE,
287                      B1_CHECKBOX_DESC,
288                      B1_CHECKBOX_IND,
289                      B1_ACT_STATUS,
290                      B1_START_DATE,
291                      B1_END_DATE,
292                      B1_CHECKLIST_COMMENT,
293                      REC_DATE,
294                      REC_FUL_NAM,
295                      REC_STATUS,
296                      B1_DISPLAY_ORDER,
297                      B1_FEE_INDICATOR,
298                      B1_ATTRIBUTE_VALUE,
299                      B1_ATTRIBUTE_UNIT_TYPE,
300                      B1_ATTRIBUTE_VALUE_REQ_FLAG,
301                      B1_VALIDATION_SCRIPT_NAME,
302                      RELATION_SEQ_ID,
303                      SD_STP_NUM,
304                      B1_CHECKBOX_GROUP,
305                      MAX_LENGTH,
306                      DISPLAY_LENGTH,
307                      B1_DEFAULT_SELECTED,
308                      B1_GROUP_DISPLAY_ORDER,
309                      VCH_DISP_FLAG,
310                      R1_TASK_STATUS_REQ_FLAG,
311                      B1_REQ_FEE_CALC,
312                      B1_SUPERVISOR_EDIT_ONLY_FLAG
313                   )
314                SELECT @spc,
315                   @id1,
316                   @id2,
317                   @id3,
318                   @PER_TYPE,
319                   @PER_SUB_TYPE,
320                   R1_CHECKBOX_TYPE,
321                   R1_CHECKBOX_DESC,
322                   R1_CHECKBOX_IND,
323                   R1_CHECKBOX_CODE,
324                   NULL,
325                   NULL,
326                   @servicenum,
327                   GETDATE(),
328                   'UB ADMIN',
329                   'A',
330                   R1_DISPLAY_ORDER,
331                   R1_FEE_INDICATOR,
332                   R1_ATTRIBUTE_VALUE,
333                   R1_ATTRIBUTE_UNIT_TYPE,
334                   R1_ATTRIBUTE_VALUE_REQ_FLAG,
335                   R1_VALIDATION_SCRIPT_NAME,
336                   0,
337                   0,
338                   R1_CHECKBOX_GROUP,
339                   MAX_LENGTH,
340                   DISPLAY_LENGTH,
341                   R1_DEFAULT_SELECTED,
342                   R1_GROUP_DISPLAY_ORDER,
343                   VCH_DISP_FLAG,
344                   R1_TASK_STATUS_REQ_FLAG,
345                   R1_REQ_FEE_CALC,
346                   R1_SUPERVISOR_EDIT_ONLY_FLAG
347                FROM r2chckbox
348                WHERE serv_prov_code = @spc
349                   AND r1_checkbox_type = @checkbox_type
350                   AND r1_checkbox_desc = 'Utility Billing Service Location Number'
351                   AND r1_checkbox_group = 'APPLICATION'
352                -- Insert_asi (@permitnum, @checkbox_type, 'Utility Billing Service Location Number' )
353             END
354             FETCH NEXT
355             FROM @c
356             INTO @spc,
357                @Id1,
358                @Id2,
359                @Id3,
360                @PermitNum,
361                @Per_Type,
362                @per_sub_type,
363                @accountnum,
364                @serviceNum
365          END
366          CLOSE @c
367          DEALLOCATE @c
368       END
369       -- load exception table for data in stg_ubil_in but not in b1permit
370       INSERT
371       INTO dbo.stg_exception_report
372          (
373             appnum,
374             exception_type,
375             exception_msg
376          )
377       SELECT ltrim(RTRIM(a.permitnum)),
378          'Invalid Data',
379          'This app number was in stg_ubill_in but not in b1permit'
380       FROM stg_ubill_in a
381       LEFT JOIN b1permit b
382       ON UPPER(RTRIM(ltrim(a.permitnum))) = UPPER(RTRIM(ltrim(b.b1_alt_id)))
383       WHERE b.b1_alt_id IS NULL
384    END

Open in new window

0
 
LVL 35

Expert Comment

by:David Todd
ID: 34165886
Hi,

With the GO at line 24 it is likely that what you think of the procedure is not even being properly created.

It is likely that when you are executing most of the body of the procedure itself.

Regards
  David
0
 

Author Comment

by:Taitor
ID: 34169631
Great.  I appreciate the feedback.  The two lines that show up in the activity monitor that are causing the lock on the two tables are below; if I kill the process id = 94 then process 85 goes away but it never sucessfully ran the stored proecure.    

Process ID = 94 ; status = suspended ; command = SET OPTION ON; Wait Type = LCK_M_SCH_S
DETAILS ARE
SET FMTONLY ON select * from Spo.dbo.stg_ubill_in where 1=2 SET FMTONLY OFF

Process ID = 85;; STATUS = Runnable; Command = EXECUTE; Application = Cstar_Permits;
DETAILS ARE
execute SPO.dbo.UTILITYBILL_IN


I did see an index on stg_ubill_in which it is not letting me look at I guess until I restart services because it is saying there is a lock on it even though I killed the processes that were causing the lock before.    I am wondering now if that index is causing the problem.  I will look at the original table specifications and see why it is there or what it is suppose to be.  I also ran a trace right before I had the user start the stored procedure.  so I can add that to if need be but I will be doing more of a review on that as well.  
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
ID: 34169973
If you find that it's the stored procedure that is causing the lock, then you can trace each individual statement of it, so you will see not just that it's s.p, but which specific statement.

But still, the clock in itself shouldn't be a problem. It's deadlock that is, i.e. when process A is waiting for process B to release a resource, while process B is waiting for process A to release another resource. Is this the case? because otherwise sooner or later process A will finish, and B will go ahead.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34169981
...also, you can debug stored procedure, step by step, checking if it's already locked or not.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34170143
>>you can debug stored procedure, step by step, checking if it's already locked or not. <<
The author appears to be still using SQL Server 2005.
0
 

Author Comment

by:Taitor
ID: 34185015
I changed the truncate table commands to delete table and it seemed to execute ok from the application calling it but it did not commit the updates.  I can run this stored procedure in sql server and it does the updates but the developer calls the stored procedure it appears to execute but does not commit the updates.  any ideas?  
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 34185142
I'm pretty sure that tracing each sql statement within the s.p. with Profiler is the only way.
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 34188994
You call the SP inside a transaction?
If so look if BEGIN TRANSACTION have correspondent COMMIT (or ROLLBACK).
0
 
LVL 35

Expert Comment

by:David Todd
ID: 34189839
Hi,

Just an opinion, but that procedure shouldn't be in a transaction. Maybe the transaction is needed inside the procedure. But otherwise the procedure is too big and means the transaction and its locks are held too long.

Regards
  David
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 34190267
That's why I asked :)
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question