declare @targeturl nvarchar(100)
declare @message nvarchar(100)
declare @sourceurl nvarchar(100)select @targeturl =
N'\\10.48.169.67\excel_convert\order_'+convert(nvarchar(17), GETDATE())+'.inh'
select @sourceurl = N'\\itdev\intranet\excel_convert\order.inh'
--print @targeturl
exec [dbo].[udsp_file_copy] @sourceurl,@targeturl,@message output
需要在[udsp_file_copy]这个存储过程中根据日期复制指定的文件,我这里是根据日期,但是传参时候出错(存储过程没错误的,只是传参有问题)
Msg 8146, Level 16, State 2, Procedure udsp_file_copy, Line 0
Procedure udsp_file_copy has no parameters and arguments were supplied.
把存储过程代码贴出来看看
declare @targeturl nvarchar(100)
declare @message nvarchar(100)
declare @sourceurl nvarchar(100)
declare @dt varchar(10)select @targeturl =
N'\\10.48.169.67\excel_convert\order_'+convert(nvarchar(17), '''+@dt + ''')+'.inh'
select @sourceurl = N'\\itdev\intranet\excel_convert\order.inh'
--print @targeturlexec [dbo].[udsp_file_copy] @sourceurl,@targeturl,convert(varchar(10),getdate(),120) @message output
declare @targeturl nvarchar(100)
declare @message nvarchar(100)
declare @sourceurl nvarchar(100)
declare @dt varchar(10)select @targeturl =
N'\\10.48.169.67\excel_convert\order_'+convert(nvarchar(17), '''+@dt + ''')+'.inh'
select @sourceurl = N'\\itdev\intranet\excel_convert\order.inh'
--print @targeturlexec [dbo].[udsp_file_copy] @sourceurl,@targeturl,convert(varchar(10),getdate(),120), @message output
\\10.48.169.67\excel_convert\order_12 22 2010 3:39P.inh
--结果如下,你确定这个对?
--------------------------------------------------------
\\10.48.169.67\excel_convert\order_12 22 2010 3:40P.inh(1 行受影响)
--SQL语句是这个
select N'\\10.48.169.67\excel_convert\order_'+convert(nvarchar(17), GETDATE())+'.inh'
as
declare @srcfileName varchar(255)
declare @descfilename varchar(255)
--@message varchar(1024) --OUTPUT
--select @srcfileName='\\it26\excel_convert\order.inh'
--select @descfilename='\\10.48.169.67\excel_convert\abc.inh'BEGIN
DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255) -- Create an object.
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
--SET @message = @src+ ': '+@desc
--select @message
--RETURN
END EXEC @hr = sp_OAMethod @object, 'CopyFile',null,@srcfilename,@descfilename
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc --SET @message = @src+ ': '+@desc
--select @message
--RETURN
END
exec @hr=sp_oadestroy @object
end
楼上正解exec [dbo].[udsp_file_copy]
@A=@sourceurl,
@B=@targeturl,
@C=convert(varchar(10),getdate(),120),
@D=@message output