Taitor
asked on
sql server stored procedure review
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.fm t -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.permit num)))
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_commen t,' ')
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_FLA G, 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_FL AG )
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_FLA G, 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_FL AG
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_commen t , ' ')
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_FLA G, 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_FL AG )
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_FLA G, 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_FL AG
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.permit num))) = upper(rtrim(ltrim(b.b1_alt _id)))
WHERE b.b1_alt_id is null
END
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.
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))
FROM b1permit a, stg_ubill_in b
WHERE a.serv_prov_code = 'TEST'
AND upper(rtrim(ltrim(a.b1_alt
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_commen
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_FLA
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_FL
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_FLA
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_FL
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_commen
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_FLA
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_FL
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_FLA
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_FL
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.permit
WHERE b.b1_alt_id is null
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...also, you can debug stored procedure, step by step, checking if it's already locked or not.
>>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.
The author appears to be still using SQL Server 2005.
ASKER
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?
I'm pretty sure that tracing each sql statement within the s.p. with Profiler is the only way.
You call the SP inside a transaction?
If so look if BEGIN TRANSACTION have correspondent COMMIT (or ROLLBACK).
If so look if BEGIN TRANSACTION have correspondent COMMIT (or ROLLBACK).
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
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
That's why I asked :)
ASKER