SQL 存储过程出错!

[风清云淡，云卷云舒]

实现功能:从excel 表导入数据到MS SQL数据库中
1:创建存储过程:up_pro
2:通过PB调用存储过程.

存储过程如下:
CREATE PROCEDURE up_pro
@ls_filename varchar(30), //excel 文件
@ls_tablename varchar(20), //excel 工作表
AS


Declare @ls_sql varchar(8000)
Declare @ls_sql2 varchar(8000)


Set @ls_sql2 ="OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="+@ls_filename+";User

ID=Admin;Password=;Extended properties=Excel 5.0') "
Set @ls_sql2 = @ls_sql2 + '...' + @ls_tablename + '$'



If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[protest]') and

OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin --该表存在
Set @ls_sql = 'Insert Into protest '
Set @ls_sql = @ls_sql + ' Select testme, finity, '
Set @ls_sql = @ls_sql + ' Convert(smalldatetime,produc) As proddate,'
Set @ls_sql = @ls_sql + ' itsc, ita,tras '
Set @ls_sql = @ls_sql + ' From '
Select @ls_sql = @ls_sql + @ls_sql2
--print @ls_sql
End
Else
Begin --该表不存在
Set @ls_sql = 'Select testme,finity,'
Set @ls_sql = @ls_sql + ' Convert(smalldatetime,produc) As
proddate,'
Set @ls_sql = @ls_sql + ' itsc,ita,tras '
Set @ls_sql = @ls_sql + ' Into protest From '
Select @ls_sql = @ls_sql + @ls_sql2
--print @ls_sql
End

EXECUTE(@ls_sql)


If @@error <> 0 Goto Error

Comp:
Commit Transaction
print 'ok'
Return
Error:
RollBack Transaction
print 'no'
Return
GO

PB 调用存储过程如下:
DECLARE up_pro PROCEDURE FOR up_pro @ls_filename=:ls_filename,@ls_tablename=:ls_tablename,@ld_date_beg=:ld_date_beg, @ld_date_end =:ld_date_end;
EXECUTE up_pro;
If Sqlca.Sqlcode < 0 Then
MessageBox('',Sqlca.SqlErrText)
RollBack;
Return
End If

程序运行提示如下:已拒绝对 OLE DB 提供程序 'Microsoft.Jet.OLEDB.4.0' 的特殊访问。必须通过链接服务器来访问此提供程序。

请教这如何解决!!

[风清云淡，云卷云舒]

邹建老大你真牛!马上给分(sa登陆)

[txlicenhe]

你的EXCEL文件是在服务器上吗？

[风清云淡，云卷云舒]

马可你好!我的excel 是在服务器上.!

[zjcxc]

--试试
CREATE PROCEDURE up_pro
@ls_filename varchar(30), //excel 文件
@ls_tablename varchar(20), //excel 工作表
AS
Declare @ls_sql varchar(8000)
Declare @ls_sql2 varchar(8000)

Set @ls_sql2 ='OPENROWSET (''Microsoft.Jet.OLEDB.4.0'', ''Excel 5.0;DATABASE='
+@ls_filename+',[' + @ls_tablename + '$])'


If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[protest]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin --该表存在
Set @ls_sql = 'Insert Into protest '
Set @ls_sql = @ls_sql + ' Select testme, finity, '
Set @ls_sql = @ls_sql + ' Convert(smalldatetime,produc) As proddate,'
Set @ls_sql = @ls_sql + ' itsc, ita,tras '
Set @ls_sql = @ls_sql + ' From '
Select @ls_sql = @ls_sql + @ls_sql2
--print @ls_sql
End
Else
Begin --该表不存在
Set @ls_sql = 'Select testme,finity,'
Set @ls_sql = @ls_sql + ' Convert(smalldatetime,produc) As
proddate,'
Set @ls_sql = @ls_sql + ' itsc,ita,tras '
Set @ls_sql = @ls_sql + ' Into protest From '
Select @ls_sql = @ls_sql + @ls_sql2
--print @ls_sql
End

EXECUTE(@ls_sql)


If @@error <> 0 Goto Error

Comp:
Commit Transaction
print 'ok'
Return
Error:
RollBack Transaction
print 'no'
Return
GO

[zjcxc]

你的登陆用户是否为sa,如果不是,用sa登陆