Skip to content

Commit ceb422f

Browse files
fenixfxfenixfx
authored
Update usp_bcpTableUnload.sql
1 parent b3d489b commit ceb422f

File tree

1 file changed

+100
-36
lines changed

1 file changed

+100
-36
lines changed

‎Stored_Procedure/usp_bcpTableUnload.sql‎

Lines changed: 100 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -1,24 +1,27 @@
11
IF OBJECT_ID('dbo.usp_bcpTableUnload', 'P') IS NULL EXECUTE ('CREATE PROCEDURE dbo.usp_bcpTableUnload AS SELECT 1');
2-
go
2+
GO
33

4-
ALTER PROCEDURE dbo.usp_bcpTableUnload (
4+
ALTER PROCEDURE [dbo].[usp_bcpTableUnload](
55
@path NVARCHAR(900)
6-
, @serverName SYSNAME = @@SERVERNAME
6+
, @serverName SYSNAME = @@SERVERNAME
77
, @databaseName SYSNAME
88
, @schemaName SYSNAME
99
, @tableName SYSNAME
10-
, @field_term NVARCHAR(10) = '|'
11-
, @row_term NVARCHAR(10) = '\n'
12-
, @fileExtension NVARCHAR(10) = 'txt'
13-
, @codePage NVARCHAR(10) = 'C1251'
14-
, @excludeColumns NVARCHAR(MAX) = ''
15-
, @orderByColumns NVARCHAR(MAX) = ''
16-
, @outputColumnHeaders BIT = 1
17-
, @debug BIT = 0
10+
, @field_term NVARCHAR(10) = N'|'
11+
, @row_term NVARCHAR(10) = N'\n'
12+
, @fileExtension NVARCHAR(10) = N'txt'
13+
, @codePage NVARCHAR(10) = N'C65001'
14+
, @excludeColumns NVARCHAR(MAX) = N''
15+
, @orderByColumns NVARCHAR(MAX) = N''
16+
, @outputColumnHeaders BIT = 1
17+
, @savePhysicalOrder BIT = 1 -- 0 - Alphabet column order type; 1 - physical column order type
18+
, @formatFile VARCHAR(4) = 'xml' -- formatfile = @schemaName.@tableName.fmt, -x formatfile = @schemaName.@tableName.xml, blank string without generation format file
19+
, @collation VARCHAR(128) = '' -- blank for keep blank value in formatfile (formatfile value 1 needed)
20+
, @debug BIT = 0
1821
)
1922
AS
2023
/*
21-
bcp docs: https://msdn.microsoft.com/ru-ru/library/ms162802.aspx
24+
bcp docs: https://docs.microsoft.com/en-us/sql/tools/bcp-utility
2225
-- To allow advanced options to be changed.
2326
EXEC sp_configure 'show advanced options', 1;
2427
GO
@@ -32,16 +35,17 @@ GO
3235
RECONFIGURE;
3336
GO
3437
EXECUTE [dbo].[usp_bcpTableUnload]
35-
@path = 'd:\'
36-
, @databaseName = 'DatabaseName'
37-
, @schemaName = 'dbo'
38-
, @tableName = 'TableName'
39-
, @field_term = '|'
40-
, @row_term = '\n'
41-
, @fileExtension = 'txt'
42-
, @excludeColumns = '[CreatedDate],[ModifiedDate],[UserID]'
43-
, @orderByColumns = 'TableNameID'
38+
@path = N'd:\'
39+
, @databaseName = N'DatabaseName'
40+
, @schemaName = N'dbo'
41+
, @tableName = N'TableName'
42+
, @field_term = N'|'
43+
, @row_term = N'\n'
44+
, @fileExtension = N'txt'
45+
, @excludeColumns = N'[CreatedDate],[ModifiedDate],[UserID]'
46+
, @orderByColumns = N'TableNameID'
4447
, @outputColumnHeaders = 1
48+
, @formatFile = N'xml'
4549
, @debug = 0;
4650
*/
4751
BEGIN
@@ -55,6 +59,9 @@ BEGIN
5559
DECLARE @tableFullName SYSNAME = QUOTENAME(@databaseName) + '.' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName);
5660
DECLARE @object_id INTEGER = OBJECT_ID(@tableFullName);
5761
DECLARE @Columns NVARCHAR(MAX) = '';
62+
DECLARE @collation_count INTEGER;
63+
DECLARE @current_collation VARCHAR(128) = '';
64+
DECLARE @CollParmDefinition NVARCHAR(500) = '@OUT VARCHAR(128) OUTPUT';
5865
DECLARE @filePath NVARCHAR(900) = @path + @tableFullName + '.' + @fileExtension;
5966
DECLARE @crlf NVARCHAR(10) = CHAR(13);
6067
DECLARE @orderByColumns_term NVARCHAR(10) = ',';
@@ -64,6 +71,19 @@ BEGIN
6471
IF @debug = 1 PRINT ISNULL('/******* Start Debug' + @crlf + '@tableFullName = {' + CAST(@tableFullName AS NVARCHAR(MAX)) + '}', '@tableFullName = {Null}');
6572
IF @debug = 1 PRINT ISNULL('@object_id = {' + CAST(@object_id AS NVARCHAR(MAX)) + '}', '@object_id = {Null}');
6673

74+
IF @formatFile NOT IN ('', 'xml', 'fmt') THROW 50002, 'Allowed values for parameter @formatFile is xml, fmt or blank value', 1;
75+
--IF @formatFile IN ('xml', 'fmt') AND @savePhysicalOrder <> 1 THROW 50003, 'Creating format file without @savePhysicalOrder = 1 parameter not allowed', 1;
76+
IF @formatFile = '' AND @collation <> '' THROW 50004, 'Using collation parameter not allowed without formatile', 1;
77+
IF @collation <> ''
78+
BEGIN
79+
SET @tsqlCommand = N'select @OUT=COUNT(*)
80+
from sys.fn_helpcollations()
81+
WHERE name = ''' + @collation+ ''';';
82+
IF @debug = 1 PRINT CAST(ISNULL('@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + '}', '@tsqlCommand = {Null}' + @crlf) AS TEXT);
83+
EXECUTE sp_executesql @tsqlCommand, @CollParmDefinition, @OUT=@collation_count OUTPUT;
84+
IF @collation_count=0 THROW 50004, 'Defined collation not found in server collations list', 1;
85+
END;
86+
6787
IF @object_id IS NULL THROW 50000, @TROW50000, 1
6888
IF @orderByColumns <> ''
6989
BEGIN
@@ -84,11 +104,13 @@ BEGIN
84104
N'SELECT @ColumnsOUT = @ColumnsOUT + QUOTENAME(Name) + '',''' + @crlf +
85105
N'FROM sys.columns sac ' + @crlf +
86106
N'WHERE sac.object_id = @object_idIN' + @crlf +
87-
N' AND QUOTENAME(Name) NOT IN (''' + REPLACE(@excludeColumns, ',', ''',''') + ''')' + @crlf +
88-
N'ORDER BY Name;';
107+
N' AND QUOTENAME(Name) NOT IN (''' + REPLACE(@excludeColumns, ',', ''',''') + ''')
108+
__row_order__;';
89109

90-
IF @debug = 1 PRINT ISNULL(N'@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + N'}', N'@tsqlCommand = {Null}');
110+
IF @savePhysicalOrder = 1 SET @tsqlCommand = REPLACE(@tsqlCommand, '__row_order__', '');
111+
ELSE SET @tsqlCommand = REPLACE(@tsqlCommand, '__row_order__', 'ORDER BY Name');
91112

113+
IF @debug = 1 PRINT ISNULL(N'@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + N'}', N'@tsqlCommand = {Null}');
92114
EXECUTE sp_executesql @tsqlCommand, @ParmDefinition, @object_idIN = @object_id, @ColumnsOUT = @Columns OUTPUT SELECT @Columns;
93115

94116
IF @debug = 1 PRINT ISNULL('@Columns = {' + @crlf + @Columns + @crlf + '}', '@Columns = {Null}');
@@ -108,32 +130,74 @@ BEGIN
108130
IF @outputColumnHeaders = 1
109131
BEGIN
110132
SET @tsqlCommand = 'EXECUTE xp_cmdshell ' + '''bcp "SELECT ''''' + REPLACE(@Columns, ',', @field_term) +
111-
'''''" queryout "' + @path + @tableFullName + '_headers.txt' + '" -T -S ' +
133+
'''''" queryout "' + @path + @tableFullName + '_headers.' + @fileExtension + '" -T -S ' +
112134
@serverName + ' -c -' + @codePage + ' -t"' + @field_term + '"' +
113135
' -r"' + @row_term + '"''' + @crlf;
114-
115136
IF @debug = 1 PRINT CAST(ISNULL('@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + '}',
116137
'@tsqlCommand = {Null}' + @crlf) AS TEXT);
117138
ELSE EXECUTE sp_executesql @tsqlCommand;
118139

119-
SET @cmdCommand = 'copy /b ' + @path + @tableFullName + '_headers.' + @fileExtension + ' + ' +
120-
@filePath + ' ' + @path + @tableFullName + '_headers.' + @fileExtension;
121-
122-
IF @debug = 1 PRINT CAST(ISNULL('@cmdCommand = {' + @crlf + @cmdCommand + @crlf + '}', '@cmdCommand = {Null}' + @crlf) AS TEXT)
140+
SET @cmdCommand = 'copy /b "' + @path + @tableFullName + '_headers.' + @fileExtension + '" + "' +
141+
@filePath + '" "' + @path + @tableFullName + '_headers.' + @fileExtension
142+
+ '" && ' + 'del "' + @filePath + '"'
143+
+ ' && ' + 'ren "' + @path + @tableFullName + '_headers.'+ @fileExtension + '" ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + '.' + @fileExtension;
144+
IF @debug = 1 PRINT CAST(ISNULL('@cmdCommand = {' + @crlf + @cmdCommand + @crlf + '}', '@cmdCommand = {Null}' + @crlf) AS TEXT);
123145
ELSE EXECUTE xp_cmdshell @cmdCommand;
124146

125-
SET @cmdCommand = 'del ' + @filePath;
126-
127-
IF @debug = 1 PRINT CAST(ISNULL('@cmdCommand = {' + @crlf + @cmdCommand + @crlf + '}', '@cmdCommand = {Null}' + @crlf) AS TEXT)
128-
ELSE EXECUTE xp_cmdshell @cmdCommand;
129147
END
130148

149+
IF @formatFile IN ('xml', 'fmt')
150+
BEGIN
151+
IF @savePhysicalOrder = 1 SET @tsqlCommand = 'EXECUTE xp_cmdshell ''bcp __full_table_name__ format nul -c -f "__path____filename__del.__format_file__ -t"|" -T''';
152+
ELSE SET @tsqlCommand =
153+
'IF OBJECT_ID(''__databaseName__.__schemaTableName__'') IS NOT NULL DROP TABLE __schemaTableName__; SELECT __Columns__ INTO __schemaTableName__ FROM __full_table_name__ WHERE 1=2; EXECUTE xp_cmdshell ''bcp __databaseName__.__schemaTableName__ format nul -c -f "__path____filename__del.__format_file__ -t"|" -T'';DROP TABLE __schemaTableName__;'
154+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__databaseName__', QUOTENAME(@databaseName));
155+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__Columns__', @Columns);
156+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__schemaTableName__', QUOTENAME(@schemaName) + '.' + QUOTENAME(CASE WHEN @savePhysicalOrder = 1 THEN @tableName WHEN @savePhysicalOrder = 0 THEN 'tmp' + @tableName END));
157+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__filename__', QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName));
158+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__path__', @path);
159+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__format_file__', CASE WHEN @formatFile = 'xml' THEN 'xml" -x' WHEN @formatFile = 'fmt' THEN 'fmt"' END);
160+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__full_table_name__', @tableFullName);
161+
IF @debug = 1 PRINT CAST(ISNULL('@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + '}', '@tsqlCommand = {Null}' + @crlf) AS TEXT);
162+
ELSE EXECUTE sp_executesql @tsqlCommand;
163+
END
164+
165+
IF @formatFile in ('xml', 'fmt')
166+
BEGIN
167+
168+
SET @tsqlCommand = N'select @OUT=CONVERT(VARCHAR(128), DATABASEPROPERTYEX(''__database_name__'', ''collation''));';
169+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__database_name__', @databaseName);
170+
IF @debug = 1 PRINT CAST(ISNULL('@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + '}', '@tsqlCommand = {Null}' + @crlf) AS TEXT);
171+
EXECUTE sp_executesql @tsqlCommand, @CollParmDefinition, @OUT=@current_collation OUTPUT;
172+
173+
SET @tsqlCommand = N'EXECUTE xp_cmdshell ''bcp "SELECT REPLACE(BulkColumn, ''''__replace__'''', ''''__collation__'''') AS BulkColumn FROM OPENROWSET(BULK ''''__path____filename__del.__formatFile__'''', __LOB__) as x" queryout "__path____filename__.__formatFile__" -c -T''';
174+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__LOB__', CASE WHEN @formatFile = 'xml' THEN 'SINGLE_NCLOB' WHEN @formatFile = 'fmt' THEN 'SINGLE_CLOB' END);
175+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__replace__', @current_collation); --'Cyrillic_General_CS_AS'
176+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__filename__', QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName));
177+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__formatFile__', @formatFile);
178+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__path__', @path);
179+
SET @tsqlCommand = REPLACE(@tsqlCommand, '__collation__', CASE WHEN @collation = '' AND @formatFile='fmt' THEN '""""' WHEN @collation = '' AND @formatFile = 'xml' THEN '' ELSE @collation END);
180+
IF @debug = 1 PRINT CAST(ISNULL('@tsqlCommand = {' + @crlf + @tsqlCommand + @crlf + '}', '@tsqlCommand = {Null}' + @crlf) AS TEXT);
181+
ELSE EXECUTE sp_executesql @tsqlCommand;
182+
183+
SET @cmdCommand = 'del "' + @path + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName) + 'del.' + @formatFile;
184+
IF @debug = 1 PRINT CAST(ISNULL('@cmdCommand = {' + @crlf + @cmdCommand + @crlf + '}', '@cmdCommand = {Null}' + @crlf) AS TEXT);
185+
ELSE EXECUTE xp_cmdshell @cmdCommand;
186+
END
187+
131188
IF @debug = 1 PRINT '--End Deubg*********/';
132189
ELSE SET NOCOUNT OFF;
133190
END TRY
134191

135192
BEGIN CATCH
136-
EXECUTE dbo.usp_LogError;
137-
EXECUTE dbo.usp_PrintError;
193+
--EXECUTE dbo.usp_LogError;
194+
PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
195+
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
196+
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
197+
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
198+
', Line: ' + CONVERT(varchar(5), ERROR_LINE()) +
199+
', User name: ' + CONVERT(sysname, CURRENT_USER);
200+
PRINT ERROR_MESSAGE();
138201
END CATCH
139202
END;
203+
GO

0 commit comments

Comments
 (0)