xp_cmdshell for copy - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

xp_cmdshell for copy

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2008-07-31 : 08:06:00

Hi all how can i copy file with xp_cmdshell

i want to copy test.xls as "test monthly - 07312008.xls"

DECLARE @cmd VARCHAR(255),@msg datetime
set @msg = getdate()
SET @cmd = 'copy C:\test.xls C:\"test Monthly"'+'-'+cast(@msg as varchar(20))+'.xls'

EXEC master.dbo.xp_cmdshell @cmd

but it doesn't work!!

please help!!

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 08:09:17

don't use cast use convert()
select replace(convert(varchar(10), getdate(), 101), '/', '')

KH
[spoiler]Time is always against us[/spoiler]

PatDeV
Posting Yak Master

197 Posts

Posted - 2008-07-31 : 09:11:31

Hi, but if it try to run this query it fails with The Microsoft Jet database engine could not find the object 'Sheet1$'

Declare @cm varchar(255)
set @cm = 'C:\"test monthly"'+'-'+replace(convert(varchar(10), getdate(), 101), '/', '')+'.xls'
INSERT INTO
OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database= @cm',
'SELECT id,desc FROM [Sheet1$]')
SELECT
id,desc
FROM testtable
ORDER BY id

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 09:15:44

do a print on the @cm to verify

print @cm

KH
[spoiler]Time is always against us[/spoiler]

PatDeV
Posting Yak Master

197 Posts

Posted - 2008-07-31 : 09:23:52

it doesn't work no matter where i put!! it gives same error : The Microsoft Jet database engine could not find the object 'Sheet1$'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 09:45:50

i mean print out the value of @cm to verify if the filename etc is correct and check that the file exists in that folder on the server

KH
[spoiler]Time is always against us[/spoiler]

PatDeV
Posting Yak Master

197 Posts

Posted - 2008-07-31 : 09:55:38

yes it does exist on the server side as well.

Lucky
Starting Member

10 Posts

Posted - 2008-08-01 : 02:20:40

Hi Dears,

can u plz help me out urgently, Actually I am passing @empno to > c:\Output.vbs' but its not creating, can u plz help me out.
there is no error in Trigger.

many thanks,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[t_Term]

ON [dbo].[EMTER]

FOR UPDATE

AS

DECLARE @tdate1 DATETIME

DECLARE @tdate2 DATETIME

DECLARE @empno INT

SET @tdate1=(select ter_dated from INSERTED)

SET @tdate2=(select ter_dated from DELETED)

IF (@tdate1 <> @tdate2)

BEGIN TRY

SET @empno=(SELECT det_numbera FROM Inserted)

DECLARE @cmd sysname, @var sysname
SET @var = @empno
SET @cmd = 'echo ' + @var + ' > c:\Output.vbs'
EXEC master..xp_cmdshell @cmd

--INSERT INTO testEmp VALUES(@empno)
-- select * from testemp

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() as ErrorNumber,

ERROR_MESSAGE() as ErrorMessage;

END CATCH

______________________________________________________________________________________

"We can be Knowledgeable with other mens Knowledge, but we can't be wise with other mens wisdom"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources