i made a Stored procedure for dropping tables older than 6 months, it was running ok by failed recently and the error is:
Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’
Here is the STP itself:
USE [OLAPPERF]
GO
/****** Object: StoredProcedure [dbo].[stp_removetbls] Script Date: 02/09/2009 12:52:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[stp_removetbls]
— Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON
DECLARE @table_name varchar( 128 )
DECLARE cur_tablenames CURSOR FOR select name from olapperf.sys.all_objects where type = ‘U’ and create_date<dateadd(month,-6,getdate())
DECLARE @sqlstring varchar(1000)
OPEN cur_tablenames
FETCH NEXT FROM cur_tablenames INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstring = ‘drop table ‘+ @table_name
exec sp_executesql @sqlstring
FETCH NEXT FROM cur_tablenames INTO @table_name
END
CLOSE cur_tablenames
DEALLOCATE cur_tablenames
END
After searching the solution i found it is very easy, like here said(http://dotnetjunkies.com/WebLog/richard.dudley/archive/2004/09/29/27169.aspx):
If you are building dynamic SQL, you should declare your parameter as one of the accepted types (e.g., declare @sql nvarchar(4000)). If you are using a direct statement as your query, you need to preface it with the letter N, as seen below.
incorrect:
execute sp_executesql ‘select * from pubs.dbo.employee where job_lvl = @level’,
‘@level tinyint’,
@level = 35
correct:
execute sp_executesql N’select * from pubs.dbo.employee where job_lvl = @level’,
N’@level tinyint’,
@level = 35
So what i did is just change my sqlstring definition from varchar(1000) to nvarchar(1000), done! Everything works again.
Leave a Reply