如何解决错误程序期望类型为'ntext/nchar/nvarchar'的参数'@parameters'? [英] How to fix the error procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'?

问题描述

我正在尝试使用存储过程来显示表，相关表或数据库的最新更改的结果.存储过程为:

I'm trying to use a stored procedure to display the results of a table, and an associated table or recent changes to the database. The stored procedure is:

set ANSI_NULLS ON
set NOCOUNT ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[MKTG_Current]( @added smalldatetime OUTPUT, @named varchar(50) OUTPUT)
AS

DECLARE @pDate smalldatetime;
DECLARE @AIID int;
DECLARE @Table varchar(50);
DECLARE @Bork nvarchar(350);
SET @pDate = GETDATE()


SELECT @Table=[Table], @AIID=AIID, @added=date_added  FROM MKTG_Recent WHERE date_added > DATEDIFF(day, date_added, DATEADD(DD, 30, @pDate))
SET @named = @Table
SET @Bork = 'SELECT * FROM ' + QUOTENAME(@Table) + ' WHERE AIID= ' + cast(@AIID as varchar(100))

EXECUTE sp_executesql @Bork, @added OUTPUT, @named OUTPUT


SELECT @added, @named 

除了select语句的结果外，还应该返回到项目.没有存储过程的输入.该存储过程可以在SQL Management Studio(2008)中正常编译，但是该页面返回错误: SQL Server错误"80040e14"的Microsoft OLE DB提供程序

It's supposed to return to items in addition to the results from the select statement. There are no inputs to the stored procedure. The stored procedure compiles fine in SQL Management Studio (2008), but the page returns an error: Microsoft OLE DB Provider for SQL Server error '80040e14'

该过程需要类型为'ntext/nchar/nvarchar'的参数'@parameters'. index.asp，第61行

Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. index.asp, line 61

页面第61行以粗体显示:

Line 61 of the page is in bold:

  dim Objrs, cmd
  set Objrs = Server.CreateObject("ADODB.RecordSet")
  set cmd = Server.CreateObject("ADODB.Command")
  set conn = Server.CreateObject("ADODB.Connection")
  conn.Open strConnect
  set cmd.ActiveConnection = conn
  cmd.CommandText="MKTG_Current"
  cmd.CommandType=adCmdStoredProc
  cmd.Parameters.Append cmd.CreateParameter("@added", 135, 2)
  cmd.Parameters.Append cmd.CreateParameter("@named", 200, 2, 50)
Line 61 **set Objrs = cmd.Execute**
  name_of_table = cmd.Parameters("@named")
  added = cmd.Parameters("@added")  

我的印象是这是由SQL代码错误引起的，但是我没有看到它.快速检查Objrs.state会返回0，这意味着问题肯定出在SQL代码中.为了我的一生，我无法确定为什么会产生此错误.

I'm of the impression that this is caused by a SQL code error, but I'm not seeing it. A quick check of the Objrs.state is returning a 0, which means the problem definitely lies in the SQL code. For the life of me, I can't identify why this error is being generated.

推荐答案

在此答案中，我将尝试重新创建您在问题中提到的问题，并说明如何解决此问题.

In this answer, I will try to recreate the issue that you mentioned in the question and will also explain how I resolved this.

首先，让我们使用创建表脚本部分下的脚本创建两个名为dbo.MKTG_Recent和dbo.Table_1的表.我根据使用问题中提供的数据所做的一些假设创建了这些表.使用该脚本，表dbo.MKTG_Recent中将填充1条记录.

First, let's create two tables named dbo.MKTG_Recent and dbo.Table_1 using the scripts under Create Tables Script section. I created these tables based on some assumptions that I made using the data provided in the question. Using the script, the table dbo.MKTG_Recent will be populated with 1 record.

接下来，使用创建存储过程脚本部分下提供的脚本创建名为dbo.MKTG_Current的存储过程.

Next, create the stored procedure named dbo.MKTG_Current using the script provided under Create Stored Procedure Script section.

如果尝试使用EXEC命令作为EXEC MKTG_Current null, null执行存储过程，则会抛出错误消息 Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. .请参阅屏幕截图#1

If, we try to execute the stored procedure with EXEC command as EXEC MKTG_Current null, null, the error message Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'. will be thrown. Refer screenshot #1

在阅读 MSDN 之后，过程 sp_executesql ，我发现存储的第二个参数该过程定义输出参数的类型，并且必须为Unicode字符串.因此，我通过以N为前缀提供第二个参数作为Unicode字符串来修改存储过程.有关存储过程的更改，请参见屏幕快照#2 .

After reading through MSDN about the usage of the procedure sp_executesql, I found that the second parameter of the stored procedure defines the types of the output parameters and it has to be a Unicode string. So, I modified the stored procedure by providing the second parameter as Unicode string by prefixing with N. Refer screenshot #2 for the stored procedure change.

截屏#3 显示了进行更改后存储过程dbo.MKTG_Current的输出.该存储过程将产生两个输出.一个用于传递给sp_executesql的变量@Bork中的查询语句，另一个输出对应于显示OUTPUT变量的SELECT语句.

Screenshot #3 shows the output of the stored procedure dbo.MKTG_Current after making the changes. The stored procedure will produce two outputs. One for the query statement in the variable @Bork that is being passed to sp_executesql and the other output corresponds to the SELECT statement that displays the OUTPUT variables.

根据要求，我不确定是否甚至需要调用sp_executesql，您可以编写存储过程，如简化的存储过程部分所示.我可能错了，因为我不完全了解该要求.屏幕快照#4 显示了简化存储过程的输出.不需要 SELECT 语句，因为这些值是通过 OUTPUT 参数传递的.我包含 SELECT 语句只是为了显示查询输出.

Based on the requirement, I am not sure if you even need to call sp_executesql, you can write the stored procedure as shown under Simplified Stored Procedure Section. I could be wrong because I don't fully understand the requirement. Screenshot #4 shows output of the simplified stored procedure. SELECT statement is not required because the values are being passed through the OUTPUT parameters. I have included the SELECT statement only to show the query output.

希望这会为您指明正确的方向.

Hope this points you in the right direction.

创建表脚本:

CREATE TABLE [dbo].[MKTG_Recent](
    [Table] [varchar](40) NOT NULL,
    [AIID] [int] NOT NULL,
    [date_added] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table_1](
    [AIID] [int] NOT NULL,
    [added] [smalldatetime] NOT NULL,
    [named] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.MKTG_Recent ([Table], AIID, date_added) 
VALUES ('Table_1', 1, '2011-08-01')
GO

创建存储过程脚本:

SET ANSI_NULLS ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MKTG_Current]
(       @added  SMALLDATETIME   OUTPUT
    ,   @named  VARCHAR(50)     OUTPUT
)
AS

DECLARE @pDate  SMALLDATETIME;
DECLARE @AIID   INT;
DECLARE @Table  VARCHAR(50);
DECLARE @Bork   NVARCHAR(350);

SET     @pDate  = GETDATE()

SELECT  @Table  = [Table]
    ,   @AIID   = AIID
    ,   @added  = date_added  
FROM    dbo.MKTG_Recent 
WHERE   date_added > DATEDIFF(day, date_added, DATEADD(DD, 30, @pDate))

SET @named  = @Table
SET @Bork   = ' SELECT  * 
                FROM    ' + QUOTENAME(@Table) + ' 
                WHERE   AIID= ' + CAST(@AIID AS VARCHAR(100))

EXECUTE sp_executesql   @Bork
                    ,   @added OUTPUT
                    ,   @named OUTPUT

SELECT  @added
    ,   @named 
GO    

简化的存储过程:

SET ANSI_NULLS ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MKTG_Current]
(       @added  SMALLDATETIME   OUTPUT
    ,   @named  VARCHAR(50)     OUTPUT
)
AS

DECLARE @Table  VARCHAR(50);

SELECT  @named  = [Table]
    ,   @added  = date_added  
FROM    dbo.MKTG_Recent 
WHERE   date_added > DATEDIFF(day, date_added, DATEADD(DD, 30, GETDATE()))

SELECT  @added AS added
    ,   @named AS named 
GO

屏幕截图:

#1:显示错误消息的执行程序

#1: Execution showing the error message

#2:对存储过程所做的更改

#2: Change made to the stored procedure

#3:更改后的存储过程输出

#3: Stored procedure output after the changes

#4:简化的存储过程输出

这篇关于如何解决错误程序期望类型为'ntext/nchar/nvarchar'的参数'@parameters'?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！