select * INTO #tmpMark
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\******(import on 20101124).xls;HDR=YES', ' SELECT * FROM [*****$]') AS T
INNER JOIN AS S ON COLLATE Chinese_PRC_90_CI_AS =
INNER JOIN AS C ON COLLATE Chinese_PRC_90_CI_AS =
IF OBJECT_ID(N'tempdb..#) IS NOT NULL DROP TABLE #
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--run in master database
--drop table EvalRes
USE master
Create table EvalRes (expVal real)
go
--drop Function dbo.Eval
USE master
Create Function dbo.Eval (@exp varchar(5000))
returns real
as
begin
declare @ans real
declare @osqlStmt varchar(5000)
set @osqlStmt = 'osql.exe -E -q' + '" Insert master..EvalRes select ' + @exp + '"'
exec master..xp_cmdShell @osqlStmt
select @ans = expVal From EvalRes
set @osqlStmt = 'osql.exe -E -q' + '" delete master..EvalRes ' + '"'
exec master..xp_cmdShell @osqlStmt
return @ans
end
go
---example call : select master.dbo.Eval ('((4.5)+5)*6-1-(3*7)+3.512')
--To enable xp_cmdshell extended stored procedure, you can run the below code as the sa account or another administrator account;
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
----