Openrowset中的错误 [英] Error in Openrowset

问题描述

下面是在sql server 2008中编写的存储过程

执行此过程后，将发生以下错误.

请帮助纠正此错误.

链接服务器(null)"的OLE DB提供程序"MICROSOFT.JET.OLEDB.4.0"返回消息没有为一个或多个必需参数提供值.".
消息7320，级别16，状态2，过程spImportPreDB，第14行
无法对链接服务器(null)"的OLE DB提供程序"MICROSOFT.JET.OLEDB.4.0"执行查询从Invdetl选择Forx，Mark1，Mark2，Mark3，货运，保险，其中refno = @ pRefno".

存储过程
USE [MetroDB]
GO
/******对象:StoredProcedure [dbo].[spImportPreDB]脚本日期:2012年8月1日12:44:40 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
更改过程[dbo].[spImportPreDB]
-在此处添加存储过程的参数
@pRefNo varchar(50)

AS

开始


-在此处插入过程的语句
从invdetl中删除，其中refno = @pRefno
从trstock中删除，其中refno = @pRefno

插入InvDetl(Forx，Mark1，Mark2，Mark3，货运，保险)
从OPENROWSET中选择Forx，Mark1，Mark2，Mark3，货运，保险 (``MICROSOFT.JET.OLEDB.4.0''，''dBase IV; HDR = NO; IMEX = 2;
DATABASE = D:\ expo \''，``从Invdetl中选择Forx，Mark1，Mark2，Mark3，货运，保险，其中refno = @ pRefno'')


END

below is store procedure wrote in sql server 2008

and after executing this procedure the following error occurs.

pls help to rectify this error.

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "No value given for one or more required parameters.".
Msg 7320, Level 16, State 2, Procedure spImportPreDB, Line 14
Cannot execute the query "select Forx,Mark1,Mark2, Mark3, Freight, Insurance from Invdetl where refno=@pRefno" against OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

STORED PROCEDURE
USE [MetroDB]
GO
/****** Object: StoredProcedure [dbo].[spImportPreDB] Script Date: 08/01/2012 12:44:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spImportPreDB]
-- Add the parameters for the stored procedure here
@pRefNo varchar(50)

AS

BEGIN


-- Insert statements for procedure here
delete from invdetl where refno = @pRefno
delete from trstock where refno = @pRefno

insert into InvDetl (Forx,Mark1,Mark2, Mark3, Freight, Insurance)
select Forx,Mark1,Mark2, Mark3, Freight, Insurance FROM OPENROWSET
(''MICROSOFT.JET.OLEDB.4.0'',''dBase IV;HDR=NO;IMEX=2;
DATABASE=D:\expo\'', ''select Forx,Mark1,Mark2, Mark3, Freight, Insurance from Invdetl where refno=@pRefno'')


END

推荐答案

您添加了，而不是;在第二个参数之后的OPENROWSET函数中.
DATABASE = D:\ expo \''，''选择

You have added , instead of ; in your OPENROWSET function after second parameter.
DATABASE=D:\expo\'',''select

这篇关于Openrowset中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！