August 7, 2008 at 12:10 pm
I created this store procedure to run weekly on one of my server.
This is the job:
use Security
EXEC usp_saveSecurity
The results after running it:
Server: Msg 201, Level 16, State 4, Procedure usp_saveSecurity, Line 0
Procedure 'usp_saveSecurity' expects parameter '@ERRSTR', which was not supplied.
Thanks
-- =============================================
-- Create stored procedure to copy SECURITY DB secOption table to save_secOption table and the secStock table ty save_secStock.
-- processing:
-- 1) secOption
-- - truncates table save_secOption.
-- - copy the rows in secOption to save_secOption.
-- - the procedure is wrapped in one transaction so that we can roll back if there are errors.
-- 1) secStock
-- - truncates table save_secOption.
-- - copy the rows in secOption to save_secStock.
-- - the procedure is wrapped in one transaction so that we can roll back if there are errors.
-- =============================================
-- create the store procedure
use security
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'usp_saveSecurity'
AND type = 'P')
BEGIN
print 'dropping SP'
DROP PROCEDURE usp_saveSecurity
END
else
print 'SP does not exist'
GO
--select top 1000 * from secoption where factor2 <> 0
--select optionsize,* from secstock where optionsize <> 0
CREATE PROCEDURE usp_saveSecurity
@ERRSTR char(100) OUTPUT,
@cntSecOptionint OUTPUT,
@cntSaveSecOption int OUTPUT,
@cntSecStockint OUTPUT,
@cntSaveSecStock int OUTPUT
AS
set nocount on
-------------------------------------------------------------------------------------------------------------------------
------------------------------ wrap the SECOPTION table copy in a transaction -------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
-- truncate the save_secOption table (day old) version
truncate table save_secOption
--delete from save_secOption
if(@@ERROR <> 0)
BEGIN
SELECT @ERRSTR = 'ERROR on truncate of table save_secOption'
print @ERRSTR
RAISERROR ('ERROR usp_saveSecurity sproc - %s - @@ERROR = %d', 16, 1,@ERRSTR,@@ERROR)with log
ROLLBACK
RETURN 00002
END
-- copy the data from the current table to archive
INSERT INTO save_secOption select * from secOption
if(@@ERROR <> 0)
BEGIN
SELECT @ERRSTR = 'ERROR on INSERT INTO save_secOption'
RAISERROR ('ERROR usp_saveSecurity sproc - %s - @@ERROR = %d', 16, 1,@ERRSTR,@@ERROR)with log
ROLLBACK
RETURN 00003
END
-- check the # of rows
select @cntSaveSecOption = count(*) from save_secOption
select @cntSecOption = count(*) from secOption
if(@cntSaveSecOption <> @cntSecOption)
BEGIN
SELECT @ERRSTR = 'ERROR on copy of SecOption table (' + convert(char(5),@cntSecOption) +
' rows) to save_SecOption(' + convert(char(5),@cntSaveSecOption) +
' rows) - # rows'
RAISERROR ('ERROR usp_saveSecurity sproc - %s', 16, 1,@ERRSTR)with log
ROLLBACK
RETURN 00004
END
if(@@ERROR = 0)
BEGIN
SELECT @ERRSTR = 'SUCCESSFUL COMPLETION usp_saveSecurity for save_secOption'
RAISERROR ('usp_saveSecurity sproc - %s', 16, 1,@ERRSTR)with log
COMMIT
END
ELSE
BEGIN
SELECT @ERRSTR = 'ERRORS ENCOUNTERED - save_secOption'
RAISERROR ('usp_saveSecurity sproc - %s - @@ERROR = %d', 16, 1,@ERRSTR,@@ERROR)with log
ROLLBACK
RETURN 00005
END
-------------------------------------------------------------------------------------------------------------------------
------------------------------ wrap the SECSTOCK table copy in a transaction -------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
-- truncate the save_secStock table (day old) version
truncate table save_secStock
if(@@ERROR <> 0)
BEGIN
SELECT @ERRSTR = 'ERROR on truncate of table save_secStock'
print @ERRSTR
RAISERROR ('ERROR usp_saveSecurity sproc - %s - @@ERROR = %d', 16, 1,@ERRSTR,@@ERROR)with log
ROLLBACK
RETURN 00006
END
-- copy the data from the current table to archive
INSERT INTO save_secStock select * from secStock
if(@@ERROR <> 0)
BEGIN
SELECT @ERRSTR = 'ERROR on INSERT INTO save_secStock'
RAISERROR ('ERROR usp_saveSecurity sproc - %s - @@ERROR = %d', 16, 1,@ERRSTR,@@ERROR)with log
ROLLBACK
RETURN 00007
END
-- check the # of rows
select @cntSaveSecStock = count(*) from save_secStock
select @cntSecStock = count(*) from secStock
if(@cntSaveSecStock <> @cntSecStock)
BEGIN
SELECT @ERRSTR = 'ERROR on copy of secStock table (' + convert(char(5),@cntSecStock) +
' rows) to save_SecStock(' + convert(char(5),@cntSaveSecStock) +
' rows) - # rows'
RAISERROR ('ERROR usp_saveSecurity sproc - %s', 16, 1,@ERRSTR)with log
ROLLBACK
RETURN 00008
END
if(@@ERROR = 0)
BEGIN
SELECT @ERRSTR = 'SUCCESSFUL COMPLETION usp_saveSecurity - save_SecStock'
RAISERROR ('usp_saveSecurity sproc - %s', 16, 1,@ERRSTR)with log
COMMIT
RETURN 00000
END
ELSE
BEGIN
SELECT @ERRSTR = 'ERRORS ENCOUNTERED - save_secStock'
RAISERROR ('usp_saveSecurity sproc - %s - @@ERROR = %d', 16, 1,@ERRSTR,@@ERROR)with log
ROLLBACK
RETURN 00009
END
GO
GO
August 7, 2008 at 12:12 pm
You wrote your stored procedure to require 5 parameters and you are not passing any to it.
How can we help you with that?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 7, 2008 at 12:28 pm
I am new to this and my programmer just hand me this SP and told me to schedule it. Where do I past the parameter to it in the by specifying it in the job or recreating the SP.
The Job:
use Security
EXEC usp_saveSecurity
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply