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