存储过程中的问题 [英] problem on stored procedure

问题描述

你好朋友
这是我的存储过程

Hi friends
This is my stored procedure

alter procedure sp_excelfortest
 as
-- declare @datavar varchar(200)
 declare @sql varchar(500)
-- set @datavar  = 'Excel 8.0;DATASOURCE=' + @filname

 set nocount on
  begin
  --create table #temptable (Date date ,Day varchar(30),Reason varchar(100))
  --set @sql = (SELECT *  FROM OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','+@datavar +;HDR=YES','SELECT Data,Day,Reason FROM [Sheet1$]')
  Set @sql ='select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
 ''Excel 8.0;Database=D:\baskarbook1.xls;HDR=YES;'',
  ''SELECT * FROM [Sheet1$]'')'
  exec (@sql)
 end

这一次可以成功运行，但是当我重新启动sql server时，它显示了一些错误.
错误是

This works onetime successfully but when i restart the sql server it shows some error.
The error is

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

我不知道该如何解决.我针对此错误撰写了一些文章，但我无法获得正确的解决方案.请帮助我

I dont know how to solve it.and i red some article regarding with this error,but i couldnt get correct solution.please help me

推荐答案

') 设置 @ sql = ' 选择* FROM OPENROWSET(``Microsoft.Jet.OLEDB.4.0''， ''Excel 8.0; Database = D:\ baskarbook1.xls; HDR = YES;''， ''SELECT * FROM [Sheet1

') Set @sql ='select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=D:\baskarbook1.xls;HDR=YES;'', ''SELECT * FROM [Sheet1

'')' exec ( @ sql ) 结束

'')' exec (@sql) end

这一次可以成功运行，但是当我重新启动sql server时，它显示了一些错误.
错误是

This works onetime successfully but when i restart the sql server it shows some error.
The error is

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

我不知道该如何解决.并且我用了一些有关此错误的文章，但我无法获得正确的解决方案.请帮助我

I dont know how to solve it.and i red some article regarding with this error,but i couldnt get correct solution.please help me

在sql server 2005中尝试此查询并设置配置-> sql服务器外围应用配置-> ..

Try this query in sql server 2005 and set the Configuration--> sql server surface area configuration-->..

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