EXCEL 上传不了的解决方法

EXCEL 上传不了解决

 

安装 AccessDatabaseEngine.exe

 

配置管理

 

把这2项属性里的 内置账号改成 local system

 

 

 

点击右键  找到方面 点击进去找到外围服务器 把下面的

 改成turn

 

 

 

 

语句：

 

declare @excfile varchar(100),@path varchar(100),@s varchar(8000),@ss varchar(4000) set @excfile='1111.xlsx'

set @path='E:\1111.xlsx'

if cast(SERVERPROPERTY('productversion') as varchar) not like '10.%'

set  @ss =' OPENROWSET(''MSDASQL'',

    ''DRIVER=MICROSOFT EXCEL DRIVER (*.XLS);DBQ='+@path+';UID=sa;PWD=wf'',

    ''SELECT * FROM ['+@excfile+'] '')'

    else

set @ss=' OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',

    ''Excel 8.0;HDR=Yes;Database='+@path+';UID=sa;PWD=wf'',

    ''SELECT * FROM [Sheet1$] '')'

set @s='SELECT * into  tempdb..excelwf FROM  '+@ss

print(@s)

    exec(@s)

 

一下不同是UID=ADMIN;PWD=MyPass

 

 

declare @excfile varchar(100),@path varchar(100),@s varchar(8000),@ss varchar(4000) set @excfile='1111.xlsx'

set @path='E:\1111.xlsx'

if cast(SERVERPROPERTY('productversion') as varchar) not like '10.%'

set  @ss =' OPENROWSET(''MSDASQL'',

    ''DRIVER=MICROSOFT EXCEL DRIVER (*.XLS);DBQ='+@path+';UID=ADMIN;PWD=MyPass'',

    ''SELECT * FROM ['+@excfile+'] '')'

    else

set @ss=' OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',

    ''Excel 12.0;HDR=Yes;Database='+@path+';UID=ADMIN;PWD=MyPass'',

    ''SELECT * FROM [Sheet1$] '')'

set @s='SELECT * into  tempdb..excelwf FROM  '+@ss

print(@s)

    exec(@s)

   

   

 

SELECT * into tempdb..excelwf FROM OPENROWSET

('Microsoft.ACE.OLEDB.12.0',

'Excel 8.0;

Database=E:\1111.xlsx;HDR=YES;IMEX=1',

'SELECT * FROM [Sheet1$]');

drop table  tempdb..excelwf

 

--

Cvs

 

USE [wfp]

GO

/****** Object:  UserDefinedFunction [dbo].[fun_getOpenOwsetCsv]    Script Date: 11/22/2011 11:49:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

 

 

ALTER         function [dbo].[fun_getOpenOwsetCsv]

 (@dir nvarchar(1000),@fielname nvarchar(1000))

returns nvarchar(4000)

as

begin

declare @tsql nvarchar(4000)

 if cast(SERVERPROPERTY('productversion') as varchar) not like '10.%'

  set  @tsql =' OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};

    DEFAULTDIR='+@dir+';Extensions=CSV;'',''SELECT * FROM '+@fielname+' '')'

else

  --set  @tsql =' openrowset(''Microsoft.ACE.OLEDB.12.0'','''+@file+''';''admin'';'''','+@tabname+')'

 set @tsql=' OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Text;

    database='+@dir+';HDR=Yes;'',''SELECT *  FROM '+@fielname+' '')'

return @tsql

 

end

 

--------

 

Execel

 

USE [wfp]

GO

/****** Object:  UserDefinedFunction [dbo].[fun_getOpenOwsetExcel]    Script Date: 11/22/2011 12:06:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

 

 

 

 

ALTER       function [dbo].[fun_getOpenOwsetExcel]

 (@fielname nvarchar(1000),@sheetname nvarchar(1000))

returns nvarchar(4000)

as

begin

declare @tsql nvarchar(4000)

 if cast(SERVERPROPERTY('productversion') as varchar) not like '10.%'

  set  @tsql =' OPENROWSET(''MSDASQL'',

    ''DRIVER=MICROSOFT EXCEL DRIVER (*.XLS);DBQ='+@fielname+';UID=ADMIN;PWD=MyPass'',

    ''SELECT * FROM ['+@sheetname+'] '')'

else

 

 set @tsql=' OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',

    ''Excel 12.0;HDR=Yes;Database='+@fielname+';UID=ADMIN;PWD=MyPass'',

    ''SELECT * FROM ['+@sheetname+'] '')'

return @tsql

 

end