Solved

sql server stored procedure review

Posted on 2010-11-17
15
466 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
Comment Utility
Is this question not realistic?  I think I need a refund from this site.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 125 total points
Comment Utility
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
Comment Utility
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
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
Comment Utility
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
Comment Utility
...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
Comment Utility
>>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
Comment Utility
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
Comment Utility
I'm pretty sure that tracing each sql statement within the s.p. with Profiler is the only way.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
That's why I asked :)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now