1: IF NOT EXISTS(SELECT SCHEMA_ID('Tools'))
2: EXEC('CREATE SCHEMA Tools') -- CREATE SCHEMA needs to be the first statement of a batch
3:
4: IF EXISTS (select * from sysobjects where id = object_id(N'[Tools].[PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
5: DROP PROCEDURE [Tools].[PutFile]
6: GO
7:
8: CREATE PROCEDURE [Tools].[PutFile]
9: (
10: @FTPServer nvarchar(128) ,
11: @FTPUser nvarchar(128) ,
12: @FTPPWD nvarchar(128) ,
13: @FTPPath nvarchar(128) ,
14: @FTPFileName nvarchar(128) ,
15:
16: @SourcePath nvarchar(128) ,
17: @SourceFile nvarchar(128) ,
18:
19: @WorkDir nvarchar(128)
20: )
21: AS
22: SET NOCOUNT ON;
23: /*
24: exec [Tools].[PutFile]
25: @FTPServer = 'myftp.com' ,
26: @FTPUser = 'usr' ,
27: @FTPPWD = 'pwd' ,
28: @FTPPath = '/Upload_Here/' ,
29: @FTPFileName = 'from_sql_2005.txt' ,
30: @SourcePath = 'c:\temp\' ,
31: @SourceFile = 'mytable.txt' ,
32:
33: @workdir = 'c:\temp\'
34: */
35:
36: declare @cmd varchar(1000)
37: declare @workfilename varchar(128)
38: DECLARE @returncode int
39:
40: /* We insert a 4 character random string to the temporary ftp script - Otherwise two concurrent calls would interfere w/ eachother */
41: select @workfilename = 'ftpcmd_{rand}.txt'
42: SELECT @workfilename = REPLACE(@workfilename,'{rand}', LEFT(CAST(NEWID() AS nvarchar(max)),4))
43:
44: -- deal with special characters for echo commands
45: select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
46: select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
47: select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
48: select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')
49:
50: select @cmd = 'echo ' + 'open ' + @FTPServer
51: + ' > ' + @workdir + @workfilename
52: exec master..xp_cmdshell @cmd, NO_OUTPUT
53: select @cmd = 'echo ' + @FTPUser
54: + '>> ' + @workdir + @workfilename
55: exec master..xp_cmdshell @cmd, NO_OUTPUT
56: select @cmd = 'echo ' + @FTPPWD
57: + '>> ' + @workdir + @workfilename
58: exec master..xp_cmdshell @cmd, NO_OUTPUT
59: select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
60: + ' >> ' + @workdir + @workfilename
61: exec master..xp_cmdshell @cmd, NO_OUTPUT
62: select @cmd = 'echo ' + 'quit'
63: + ' >> ' + @workdir + @workfilename
64: exec master..xp_cmdshell @cmd, NO_OUTPUT
65:
66: select @cmd = 'ftp -s:' + @workdir + @workfilename
67:
68: DECLARE @result TABLE (id int identity(1,1), s varchar(1000))
69: insert @result
70: exec @returncode = master..xp_cmdshell @cmd
71:
72: IF(@returncode = 0)
73: INSERT @result SELECT 'FTP command successfully executed'
74:
75: select [output] = s from @result WHERE s IS NOT NULL
76: AND s NOT LIKE 'quit'
77:
78: /* Remove the ftp cmd afterwards*/
79: select @cmd = 'delete ' + @workdir + @workfilename
80: exec master..xp_cmdshell @cmd, NO_OUTPUT
81: go
82: