· 8 years ago · Apr 13, 2018, 07:14 PM
1CREATE PROCEDURE spWriteStringToFile (@String VARCHAR(MAX), @FileAndPath VARCHAR(500))
2AS
3DECLARE @objFileSystem INT,
4 @objTextStream INT,
5 @objErrorObject INT,
6 @strErrorMessage VARCHAR(1000),
7 @Command VARCHAR(1000),
8 @hr INT
9
10SET NOCOUNT ON
11
12SET @strErrorMessage = 'opening the File System Object'
13EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
14
15IF @HR=0 SELECT @objErrorObject = @objFileSystem , @strErrorMessage = 'Creating file "' + @FileAndPath + '"'
16IF @HR=0 EXECUTE @hr = sp_OAMethod @objFileSystem, 'OpenTextFile', @objTextStream OUT, @FileAndPath, 8, True
17
18IF @HR=0 SELECT @objErrorObject = @objTextStream, @strErrorMessage = 'writing to the file "' + @FileAndPath + '"'
19IF @HR=0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Write', Null, @String
20
21IF @HR=0 SELECT @objErrorObject=@objTextStream, @strErrorMessage='closing the file "' + @FileAndPath + '"'
22IF @HR=0 EXECUTE @hr = sp_OAMethod @objTextStream, 'Close'
23
24IF @hr<>0
25 BEGIN
26 DECLARE
27 @Source VARCHAR(255),
28 @Description VARCHAR(255),
29 @Helpfile VARCHAR(255),
30 @HelpID INT
31
32 EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,@Description output,@Helpfile output,@HelpID output
33 SET @strErrorMessage = 'Error whilst ' + COALESCE(@strErrorMessage,'doing something') + ', ' + COALESCE(@Description,'')
34 RAISERROR (@strErrorMessage,16,1)
35 END
36EXECUTE sp_OADestroy @objTextStream
37EXECUTE sp_OADestroy @objFileSystem
38
39CREATE PROCEDURE [dbo].[up_FTPPushFile]
40 @file_to_push VARCHAR(255),
41 @ftp_to_server VARCHAR(255),
42 @ftp_login VARCHAR(255),
43 @ftp_pwd VARCHAR(255)
44as
45Set Nocount On
46--STEP 0
47--Ensure we can find the file we want to send.
48Create table #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
49Insert #FileExists EXEC master.dbo.xp_fileexist @file_to_push
50
51IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
52BEGIN
53 Drop table #FileExists
54 RAISERROR ('File %s does not exist. FTP process aborted.', 16, 1, @file_to_push)
55 RETURN 1
56END
57--STEP 1
58--Create xxx.bat batch file using bcp utility, file path/name is the same as @file_to_push
59--batch file will hold 4 records:
60--1) login
61--2) password
62--3) ftp command and file to push
63--4) exit command
64
65DECLARE @WriteString NVARCHAR(MAX)
66
67SET @WriteString = @ftp_login + N'
68' + @ftp_pwd + N'
69cd /VendorFolder/OutFolder
70put ' + @file_to_push + N'
71bye
72del ' + @file_to_push
73
74
75declare @sql VARCHAR(255), @cmd VARCHAR(255), @batch_ftp VARCHAR(255), @ret int
76set @batch_ftp = Left(@file_to_push, Len(@file_to_push)-4) +'.bat'
77EXECUTE spWriteStringToFile @WriteString, @batch_ftp
78
79--STEP 2
80--Ensure we can find the batch file we just created.
81Delete #FileExists
82Insert #FileExists EXEC master.dbo.xp_fileexist @batch_ftp
83IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
84BEGIN
85 Drop table #FileExists
86 RAISERROR ('Unable to create FTP batch file %s. FTP process aborted.', 16, 1, @batch_ftp)
87 RETURN 1
88END
89Drop table #FileExists
90
91--STEP 3
92--Execute newly created .bat file, save results of execution
93Create table #temp_ftp_results (ftp_output VARCHAR(255))
94set @cmd = 'ftp -s:'+@batch_ftp+' '+@ftp_to_server
95Insert #temp_ftp_results Exec master.dbo.xp_cmdshell @cmd
96IF EXISTS (SELECT * FROM #temp_ftp_results WHERE (ftp_output like '%Login failed%' or ftp_output like '%Access is denied%'))
97BEGIN
98 Drop table #temp_ftp_results
99 RAISERROR ('Unable to FTP file %s. Login failed or access denied. FTP process aborted.', 16, 1, @file_to_push)
100 RETURN 1
101END
102
103--STEP 4
104--delete batch file
105set @cmd = 'del ' + @batch_ftp
106INSERT #temp_ftp_results EXEC master.dbo.xp_cmdshell @cmd
107set @cmd = 'del ' + @file_to_push
108INSERT #temp_ftp_results EXEC master.dbo.xp_cmdshell @cmd
109
110Drop table #temp_ftp_results
111
112CREATE TRIGGER dbo.<TableName>Change --TODO: Fill out table name here
113 ON dbo.<TableName> --TODO: Fill out table name here
114 AFTER INSERT, UPDATE
115AS
116BEGIN
117 --Insert code here to separate our data data from other customer data if applicable
118 --IF (SELECT CompanyName FROM INSERTED) = 'My Company'
119 --BEGIN
120
121 --Temporary Directory on SQL server to store files before transfer
122 DECLARE @TempDirectory VARCHAR(MAX) = 'C:Temp'
123
124 --FTP site credentials
125 DECLARE @FTPServer VARCHAR(MAX) = 'FTPServer.com'
126 DECLARE @FTPUsername VARCHAR(MAX) = 'Username'
127 DECLARE @FTPPassword VARCHAR(MAX) = 'Password'
128
129 --Find the triggered table name
130 declare @TableName sysname
131 SET @TableName = (SELECT object_name(parent_id) from sys.triggers where object_id = @@PROCID)
132
133 --Find the column names in that table
134 DECLARE @Columns TABLE (ColumnName VARCHAR(MAX), ColumnCount DECIMAL(18, 0) IDENTITY(0, 1))
135
136 INSERT INTO @Columns
137 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = @TableName
138
139 --Loop through each of the column names and append the inserted values for each modified row
140 DECLARE @ColumnCount DECIMAL(18, 0) = (SELECT COUNT(*) FROM @Columns)
141 DECLARE @Counter DECIMAL(18, 0) = 0
142 DECLARE @WriteString NVARCHAR(MAX) = ''
143 DECLARE @CurrentColumn VARCHAR(MAX)
144 DECLARE @SQL NVARCHAR(MAX)
145 DECLARE @Params NVARCHAR(MAX)
146 DECLARE @TempString VARCHAR(MAX)
147 DECLARE @LineString NVARCHAR(MAX) = ''
148 DECLARE @CurrentRowIndex DECIMAL(18, 0) = 0
149 DECLARE @RowCount DECIMAL(18, 0) = (SELECT COUNT(*) FROM INSERTED)
150
151 SELECT * INTO #INSERTED FROM INSERTED
152
153 --Add an identity column so each row has a unique number on it to loop through
154 ALTER TABLE #INSERTED ADD IDX DECIMAL(18, 0) IDENTITY(0, 1)
155
156 WHILE @CurrentRowIndex < @RowCount
157 BEGIN
158 WHILE @Counter < @ColumnCount
159 BEGIN
160 SET @CurrentColumn = (SELECT ColumnName FROM @Columns WHERE ColumnCount = @Counter)
161
162 SET @SQL = 'SELECT @WriteStringOUT = ISNULL(CONVERT(VARCHAR(MAX), (SELECT ' + @CurrentColumn + ' FROM #INSERTED WHERE IDX = ' + CONVERT(VARCHAR(MAX), @CurrentRowIndex) + ')), '''')'
163 SET @Params = '@WriteStringOUT VARCHAR(MAX) OUTPUT'
164
165 EXECUTE sp_executesql @SQL, @Params, @WriteStringOUT = @TempString OUTPUT
166
167 SET @LineString = @LineString + '|' + @TempString
168
169 SET @Counter = @Counter + 1
170 END
171
172 --Eliminate beginning pipe and add a NewLine
173 SET @WriteString = @WriteString + RIGHT(@LineString, LEN(@LineString) - 1) + N'
174'
175
176 SET @LineString = N''
177 SET @Counter = 0
178 SET @CurrentRowIndex = @CurrentRowIndex + 1
179 END
180
181 --Create file name formatted TableNameYYYYMMDDHHmmSSsss.txt
182 DECLARE @FileName VARCHAR(MAX) = @TempDirectory + @TableName + REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(CHAR(23),CONVERT(DATETIME,CURRENT_TIMESTAMP,101),121), ':', ''), '-', ''), ' ', ''), '.', '') + '.txt'
183
184 --Eliminate the ending new line
185 SET @WriteString = LEFT(@WriteString, LEN(@WriteString) - 1)
186
187 --Write string to temp file
188 EXECUTE spWriteStringToFile @WriteString, @FileName
189
190 --Send temp file to FTP server and delete temp files
191 EXECUTE up_FTPPushFile @FileName, @FTPServer, @FTPUsername, @FTPPassword
192 --END
193END