Wednesday, March 7, 2012

Copy a text file to a different server

I have a stored procedure that makes a call to the BCP and creates a
text file. I then need to copy this text file over to a different
server in the same domain. What is the best way to do it?
Also, I would like to know what would be the best way to copy this same
file over to another server that is not in the same domain.
Users running this stored procedure will have minimal rights they will
not have SA rights.
Any help or suggestion in this reagrd will be greatly appreciated.
Thanks
ShubHi
From Haidong "Alex" Ji
CREATE proc usp_CopyFiles @.SourceDirectory varchar(200),
@.DestinationDirectory varchar(200) as
--Created by Haidong "Alex" Ji 05/15/03
--Given source and destination folder, this procedure copies
--files
--Usage: exec dbo.usp_CopyFiles 'SourceFolder', 'DestinationFolder'
declare @.DOSCommand varchar(150)
set nocount on
--Check whether the user supply \ in the directory name
if not (right(@.SourceDirectory , 1) = '\')
set @.SourceDirectory = @.SourceDirectory + '\'
--Check whether the user supply \ in the directory name
if not (right(@.DestinationDirectory, 1) = '\')
set @.DestinationDirectory = @.DestinationDirectory + '\'
--The following DOS command will copy files. The /D switch only copies new
files.
--If a file exists in the destination folder, it will not try to copy it
again.
set @.DOSCommand = 'xcopy /D ' + '"' + @.SourceDirectory + '*.*' + '"' + ' ' +
'"'
+ @.DestinationDirectory + '"'
print @.DOSCommand
exec master..xp_cmdshell @.DOSCommand
GO
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>|||Thanks for your response but when I run this i get the following
message...
xcopy /D "C:\source\*.*" "C:\dest\"
output
File not found - *.*
0 File(s) copied
NULL
If I run this same command from the dos promt it works fine. Any ideas?|||Hi,
Is the directory C:\Source and C:\dest available in the SQL Server machine?
Thanks
Hari
SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1126529298.347453.164080@.f14g2000cwb.googlegroups.com...
> Thanks for your response but when I run this i get the following
> message...
> xcopy /D "C:\source\*.*" "C:\dest\"
> output
> File not found - *.*
> 0 File(s) copied
> NULL
> If I run this same command from the dos promt it works fine. Any ideas?
>|||No actually I was just trying this on my local computer. I have not yet
tried the real test. In my real example C:\source will and C:\dest will
be in two different servers. In some cases both the servers will be in
the same domain and in others it may not be in the same domain.
Thanks|||Below is a file copy sp I created for a project a long time ago...The thing
I wanted to mention is xcopy might fail, the file may not get copied...
There is an undoc-d I think sp ms_file_exists, that I called after the copy
to see if the copy was successfulll..
--add p_copy_file
--Aug 25, 19999 13:30 kws added error message for failed ms_exists_File call
/*
declare @.ret_status int
exec @.ret_Status =p_copy_file 'C:\party.sql', 'c:\backup\'
--exec @.ret_status = dbo.sp_MSexists_file 'c:\party2.sql', 'party2.sql'
select @.ret_status
--DOES NOT SUPPORT SPACES OR SPECIAL CHARACTERS IN DIRECTORIES< FILENAMES
*/
drop proc p_copy_file
go
create proc p_copy_file
(
@.inputfile udt_entire_filename = null
,@.outputfile udt_entire_filename = null
,@.debug int = 0
)
as
set nocount on
Declare @.trancount int
,@.msg varchar(4000)
,@.msg2 varchar(1000)
,@.me varchar(128)
,@.error int
,@.rowcount int
,@.filecopy_cmd nvarchar(1000)
,@.ret_status int
,@.filename udt_filename
set nocount on
select @.me = 'p_copy_file'
select @.debug = @.debug & isnull(dbg_prmtr_value,0) from debug_parameter
where dbg_prmtr_name = @.me
select @.trancount = @.@.trancount
/*
if @.debug &2 = 2 -- display input parameters
begin
-- select @.msg = 'Input parameters for ' + @.me + ' are:' + char(10) +
-- '@.param1:'-- + @.param1
select @.msg
end
*/
if @.inputfile is null or @.outputfile is null
begin
select @.msg = 'Usage: Exec ' + @.me + ' @.inputfile, @.outputfile' + char(10)
+
' All parameters are required.'
raiserror (@.msg,16,1)
return -500
end
select @.filecopy_cmd = 'copy ' + @.inputfile + ' ' + @.outputfile
if @.debug & 3 = 3 select @.filecopy_cmd
EXEC @.ret_status = master..xp_cmdshell @.filecopy_cmd, NO_OUTPUT
select @.error = @.@.error
IF @.ret_status <> 0 OR @.@.ERROR <> 0
BEGIN
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, null)
exec master..xp_logevent 90108, @.msg, Error
return (-1)
END
--maybe use this if needed --0 = not found 1 = found
exec @.ret_status = p_parse_filename @.full_path = @.outputfile, @.filename =@.filename OUTPUT
if @.ret_status != 0
begin
select @.msg = formatmessage(90107, @.me, 'p_parse_filename', @.ret_status,
null)
exec master..xp_logevent 90107, @.msg, Error
return -500
end
exec @.ret_status = dbo.sp_MSexists_file @.full_path = @.outputfile, @.filename
= @.filename
if @.ret_status != 1
begin
select @.msg = formatmessage(90108, @.me, @.filecopy_cmd, @.ret_status,
@.error, 'File was not copied(it does not exist).')
exec master..xp_logevent 90108, @.msg, Error
return (-1)
end
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"shub" <shubtech@.gmail.com> wrote in message
news:1126527460.523695.164650@.z14g2000cwz.googlegroups.com...
>I have a stored procedure that makes a call to the BCP and creates a
> text file. I then need to copy this text file over to a different
> server in the same domain. What is the best way to do it?
> Also, I would like to know what would be the best way to copy this same
> file over to another server that is not in the same domain.
>
> Users running this stored procedure will have minimal rights they will
> not have SA rights.
> Any help or suggestion in this reagrd will be greatly appreciated.
> Thanks
> Shub
>

No comments:

Post a Comment