大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE稳定放心使用
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
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/186494.html原文链接：https://javaforall.net
