存储过程代码如下:
CREATE PROCEDURE ChangeDate
@riqi datetime
AS
BEGIN
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'xp_cmdshell', 1
reconfigure
master.dbo.xp_cmdshell 'date '+convert(varchar(10),@riqi,102)
sp_configure 'show advanced options', 0
reconfigure
END
GO 出错信息:
消息 102,级别 15,状态 1,过程 ChangeDate,第 10 行
'sp_configure' 附近有语法错误。
CREATE PROCEDURE ChangeDate
@riqi datetime
AS
BEGIN
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'xp_cmdshell', 1
reconfigure
master.dbo.xp_cmdshell 'date '+convert(varchar(10),@riqi,102)
sp_configure 'show advanced options', 0
reconfigure
END
GO 出错信息:
消息 102,级别 15,状态 1,过程 ChangeDate,第 10 行
'sp_configure' 附近有语法错误。
@riqi datetime
AS
BEGIN
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
--exec master.dbo.xp_cmdshell 'date '+convert(varchar(10),@riqi,102)
exec sp_configure 'show advanced options', 0
reconfigure
END
GO
@riqi datetime
AS
declare @s varchar(100)
BEGIN
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
set @s='master.dbo.xp_cmdshell date '+convert(varchar(10),@riqi,102)
exec (@s)
exec sp_configure 'show advanced options', 0
reconfigure
END
GO
@riqi datetime
AS
declare @s varchar(100)
BEGIN
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
set @s='master.dbo.xp_cmdshell ''date '+convert(varchar(10),@riqi,102)+''''
exec (@s)
exec sp_configure 'show advanced options', 0
reconfigure
END
GO
您的代码能执行
我用它成功建立了过程
按如下执行
exec changedate '2009-5-20'
出错:
配置选项 'show advanced options' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。
配置选项 'xp_cmdshell' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。
消息 102,级别 15,状态 1,第 1 行
'2009.05' 附近有语法错误。
配置选项 'show advanced options' 已从 1 更改为 0。请运行 RECONFIGURE 语句进行安装。
出错如下:
客户端没有所需的特权。
CREATE PROCEDURE ChangeDate
@riqi datetime
AS
declare @s varchar(100)
BEGIN
exec sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
exec sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
set @s='master.dbo.xp_cmdshell ''date '+convert(varchar(10),@riqi,102)+''''
exec (@s)
exec sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
END
GO
这样试试
--当convert(varchar(10),@riqi,102)时,要执行
exec changedate '2009.05.20'
--或
changedate '2009.05.20' --2
--当convert(varchar(10),@riqi,20)时,要执行
exec changedate '2009-05-20'
--或
changedate '2009-05-20' --注意输入 ‘2009-5-20’ 可能查不出数据。
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
I sum up the three responses and get what I want.
Time to close the thread!