以下是我写的一段储存过程函数 其中 pathname为路径名 大概为‘D:\aa\bb.mdb’
用vb来调用此过程 但是在运行存储过程就提示@pathname 错误 不知道错在哪里了 谢谢告之
CREATE PROCEDURE [AUTOSAVE]@PATHNAME CHAR(38), @STARTDATE CHAR(8) ,@STOPDATE CHAR(8)ASinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,BlobsHotMillFlatnessGauge) select * from BlobsHotMillFlatnessGauge
where datetime between @STARTDATE and @STOPDATE
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,BlobsHotMillProfileGauge) select * from BlobsHotMillProfileGauge
where datetime between @STARTDATE and @STOPDATEinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,CalHotMillProfileGauge) select * from CalHotMillProfileGauge
where datetime between @STARTDATE and @STOPDATEinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,ValuesHotMillProfileGauge) select * from ValuesHotMillProfileGauge
where datetime between @STARTDATE and @STOPDATEinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,ValuesHotMillFlatnessGauge) select * from ValuesHotMillFlatnessGauge
where datetime between @STARTDATE and @STOPDATEGO
用vb来调用此过程 但是在运行存储过程就提示@pathname 错误 不知道错在哪里了 谢谢告之
CREATE PROCEDURE [AUTOSAVE]@PATHNAME CHAR(38), @STARTDATE CHAR(8) ,@STOPDATE CHAR(8)ASinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,BlobsHotMillFlatnessGauge) select * from BlobsHotMillFlatnessGauge
where datetime between @STARTDATE and @STOPDATE
insert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,BlobsHotMillProfileGauge) select * from BlobsHotMillProfileGauge
where datetime between @STARTDATE and @STOPDATEinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,CalHotMillProfileGauge) select * from CalHotMillProfileGauge
where datetime between @STARTDATE and @STOPDATEinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,ValuesHotMillProfileGauge) select * from ValuesHotMillProfileGauge
where datetime between @STARTDATE and @STOPDATEinsert into openrowset('Microsoft.Jet.OLEDB.4.0',
@PATHNAME;;,ValuesHotMillFlatnessGauge) select * from ValuesHotMillFlatnessGauge
where datetime between @STARTDATE and @STOPDATEGO
select * from BlobsHotMillFlatnessGauge where datetime between @STARTDATE and @STOPDATE--其它照抄GO
建议各个insert用变量拼接,外层用事务,不让很容易产生数据不一致。
GOBEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
INSERT INTO t2 VALUES (6)
COMMIT TRAN
GO
SET XACT_ABORT ON
GO
BEGIN TRANexec ('insert into openrowset(''Microsoft.Jet.OLEDB.4.0'', '''+@PATHNAME+''';;,BlobsHotMillFlatnessGauge)')
select * from BlobsHotMillFlatnessGauge where datetime between @STARTDATE and @STOPDATE--其它照抄COMMIT TRANGO
ERROR_HANDLE:
ROLLBACK TRAN
.....
首先,把存储过程里面多余的GO去掉。其次,这个应该要启动分布式事务。最后,能否 ROLLBACK OPENROWSET 里面的操作,不知道,没测试过。
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[autosave]
-- Add the parameters for the stored procedure here
@pathname varchar(11) = 'd:\aaaa.mdb',
@startdate varchar(10) = '2010-09-01',
@stopdate varchar(10)='2010-09-02'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;exec ('insert into openrowset(''Microsoft.Jet.OLEDB.4.0'', '''+@PATHNAME+''';;,abcd)')
select * from abcd where datetime between @STARTDATE and @STOPDATE -- Insert statements for procedure here
SELECT @pathname, @startdate, @stopdate
END以上是存储过程 执行没有错误
以下是调用存储过程
USE [ims]
GODECLARE @return_value intEXEC @return_value = [dbo].[autosave]SELECT 'Return Value' = @return_valueGO
提示错误代码
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.(1 row(s) affected)
但是结果中是可以显示查询的数据的 不知道怎么办 谢谢告之了
D:\aaaa.mdb是access数据库 需要把SQL里的数据导入到ACCESS里面
直接执行
insert into openrowset('Microsoft.Jet.OLEDB.4.0', D:\aaaa.mdb;;,abcd)')
select * from abcd where datetime between '2010-09-12' and '2010-09-13'是可以成功运行的 但是用过程来实现 就不行了 急需啊 !!!