Error in procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in procedure

Author

Topic

siprem
Starting Member

6 Posts

Posted - 2008-12-16 : 00:38:35

Hi All,
I am getting the following error. Please help me fix this problem.
[Error]
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
[Error]

The prodecure is below:
[Code]
CREATE PROCEDURE dbo.sp_ValidateFooterCount
(@FileName NVARCHAR(50),@Flag INT OUTPUT)
AS
BEGIN
SET nocount ON
DECLARE @NumLines VARCHAR(100)
DECLARE @XPCmdString VARCHAR(8000)
DECLARE @SqlStatement VARCHAR(200)
DECLARE @FooterCount VARCHAR(10)

SET @XPCmdString = 'find /V /C " " ' + @FileName
CREATE TABLE #XPOutput (XPLineOut varchar(1000))
INSERT INTO #XPOutput EXEC master..xp_cmdshell @XPCmdString
DELETE FROM #XPOutput WHERE XPLineOut IS NULL
SELECT @NumLines = SUBSTRING (XPLineOut, 12 + len(@FileName) + 2, 1000) FROM #XPOutput

SET @SqlStatement ='BULK INSERT #tempfile FROM ''' + @FileName + ''''
Create table #tempfile (line varchar(8000))
EXEC sp_executesql @SqlStatement
DELETE FROM #tempfile WHERE DATALENGTH(line) >6
SET @FooterCount= (SELECT line from #tempfile)
PRINT @NumLines
PRINT @FooterCount
select @Flag=1

--RETURN @Flag
END

[Code]

The way I am running the procedure is as below:

declare @flag int
exec [TUAM].[dbo].sp_ValidateFooterCount @Filename='F:Oracle.txt',@Flag=@flag OUT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 00:52:23

the parameter @SqlStatement should be of nvarchar type rather than varchar.
also dont use sp_ for procedure names