July 3, 2014 at 6:33 pm
Hi,
I need delete old data from my DB, I have this script but when I execute it, it send me this error "Msg 8144, Level 16, State 2, Procedure AdventureWorks2008R2, Line 0
Procedure or function AdventureWorks2008R2 has too many arguments specified."
My script to create procedure is:
USE [AdventureWorks2008R2]
GO
/****** Object: StoredProcedure [dbo].[BMC_ION_Data_DELETE_LOG] Script Date: 06/04/2014 10:28:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[AdventureWorks2008R2]
@severity int,
@message nvarchar(max)
as
begin
-- prepend severity text to message
if @severity = 1 set @message='ERROR: ' + @message
if @severity = 2 set @message='WARN: ' + @message
if @severity = 3 set @message='INFO: ' + @message
-- insert message into table
insert AdventureWorks2008R2 ([time], [spid], [severity], [message])
values (CURRENT_TIMESTAMP, @@SPID, @severity, @message)
end
GO
And my procedured script for execute is:
exec AdventureWorks2008R2 @raw = 30, @quarter = 30, @hour = 30, @day = 30, @week = 30
REGARDS.
July 3, 2014 at 7:48 pm
You are passing too many parameters to your stored procedure. It can only accept two:
create procedure [dbo].[AdventureWorks2008R2]
@severity int, -- param 1
@message nvarchar(max) -- param2
as
...
What do the @severity and @message parameters have to do with the delete?
If you want to delete some records based on a parameter, you could do something like:
DELETE
FROM MyTable
WHERE field1=@param1
AND field2=@param2
July 3, 2014 at 9:19 pm
Hi, thanks for replay,
This procedure was a test, but I execute now in the production DB and send me this error "Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@raw'."
the queries are to create (completed successfully):
USE [ION_Data]
GO
/****** Object: StoredProcedure [dbo].[BMC_ION_Data_DELETE_LOG] Script Date: 06/04/2014 10:28:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[BMC_ION_Data_DELETE_LOG]
@severity int,
@message nvarchar(max)
as
begin
-- prepend severity text to message
if @severity = 1 set @message='ERROR: ' + @message
if @severity = 2 set @message='WARN: ' + @message
if @severity = 3 set @message='INFO: ' + @message
-- insert message into table
insert BMC_ION_Data_DELETE_LOGS ([time], [spid], [severity], [message])
values (CURRENT_TIMESTAMP, @@SPID, @severity, @message)
end
GO
And to execute (failed with error: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@raw'.):
exec BMC_ION _DATA_DELETE @raw = 560, @quarter = 560, @hour = 560, @day = 560, @week = 560
July 3, 2014 at 10:12 pm
Marco,
I'm confused. What is your ultimate goal? You say you want to delete data from your database, but there are no delete statements anywhere. Your stored procedure inserts records into a table...
If you want to just mark the records as "deleted", you could...
1. add a BIT column to each table. (something like DeleteMe)
ALTER TABLE MyTable ADD IsDeleted BIT DEFAULT False;
2. In your procedure, UPDATE the column...
UPDATE MyTable
SET IsDeleted = True
WHERE....;
And if you really want to delete "old" data, define what you mean by "old"? All the existing records? All records older than some days old?
Since you're new, I'll offer a bit of advice - read up on how to post a good question, because that's the key to getting good help (and often to solving the problem yourself, because the real problem suddenly becomes much clearer!)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply