sql - 在 SQL Server 2014 上运行相同的代码，但在 2016、2017 等更高版本上运行时会出错

Question

我正在将数据从 Microsoft SQL Server 迁移到 Postgres，因此在迁移时我得到了一个代码，该代码将对所选数据库中的所有表进行 bcp，并以 CSV 文件的形式将输出抛出到代码中定义的目录中。

在 Microsoft SQL Server 2014 中运行动态 SQL 语句时，它运行完美，但在 SQL Server 2012、2016、2017 等其他版本上运行相同的代码会导致错误。

我检查了所有 SQL Server 版本的所有 BCP 版本，它们是相同的。

declare @databaseName varchar(100)
declare @folderPath varchar(100)='C:\Data\'
set @databaseName = DB_NAME()

--Export query preparation
select ROW_NUMBER() over (
        order by (
                select 1
                )
        ) rownum
    ,'select * FROM ' + @databaseName + '.' + '[' + SCHEMA_NAME(schema_id) + '].[' + t.name + ']' as col1
    ,'select ' + STUFF((
            select ',' + case 
                    when DATA_TYPE = 'datetime'
                        or DATA_TYPE = 'datetime2'
                        then ' Isnull(nullif(convert(nvarchar(28),' + COLUMN_NAME + + ' ,121) ,' + char(39) + char(39) + '), ' + char(39) + 'null' + char(39) + ')'
                    when DATA_TYPE = 'varchar'
                        or DATA_TYPE = 'nvarchar'
                        then @databaseName + '.dbo.GetString(' + COLUMN_NAME + ')'
                    when DATA_TYPE = 'binary'
                        or DATA_TYPE = 'varbinary'
                        or DATA_TYPE = 'image'
                        then 'substring(master.dbo.fn_varbintohexstr(' + COLUMN_NAME + '), 3, len(master.dbo.fn_varbintohexstr(' + COLUMN_NAME + ')))'
                    else ' Isnull(nullif(cast(' + COLUMN_NAME + + ' as nvarchar(max)) ,' + char(39) + char(39) + '), ' + char(39) + 'null' + char(39) + ')'
                    end
            from INFORMATION_SCHEMA.COLUMNS
            where TABLE_NAME = t.name
                and TABLE_SCHEMA = schema_name(schema_id)
            order by table_schema
                ,table_name
                ,ordinal_position
            for xml PATH('')
            ), 1, 1, '') + ' FROM ' + @databaseName + '.' + '[' + SCHEMA_NAME(schema_id) + '].[' + t.name + ']' as col2
    ,SCHEMA_NAME(schema_id) + '."' + t.name + '"' as col3
into tempQueries
from sys.tables t

select * into  tempQueriesCopy from tempQueries
--Export with bcp script
declare @query1 varchar(MAX)
declare @query2 varchar(MAX)
declare @table varchar(MAX)
declare @row int = 0

while exists (
        select top 1 rownum
        from tempQueries
        )
begin
    select top 1 @query1 = col2
        ,@query2 = col2
        ,@row = rownum
        ,@table = col3
    from tempQueries

    declare @sql varchar(8000) = ''

    select @sql = 'bcp "' + @query2 + '" queryout "' + @folderPath + replace( @table,'"','') + '.csv" -c -t~ -T -S' + @@servername

    print @sql

    exec master..xp_cmdshell @sql

    delete
    from tempQueries
    where rownum = @row
end


---- To allow advanced options to be changed.  
--EXEC sp_configure 'show advanced options', 1;  
--GO  
---- To update the currently configured value for advanced options.  
--RECONFIGURE;  
--GO  
---- To enable the feature.  
--EXEC sp_configure 'xp_cmdshell', 1;  
--GO  
---- To update the currently configured value for this feature.  
--RECONFIGURE;  
--GO  

错误：-

output  
Copy direction must be either 'in' or 'out'.    
Syntax Error in 'queryout'. 
usage: bcp [[db_name.]owner.]table_name[:slice_num] [partition pname] {in | out} [filename] 
    [-m maxerrors] [-f formatfile] [-e errfile] [-d discardfileprefix] 
    [-F firstrow] [-L lastrow] [-b batchsize]
    [-n] [-c] [-t field_terminator] [-r row_terminator]
    [-U username] [-P password] [-I interfaces_file] [-S server]
    [-a display_charset] [-z language] [-v]
    [-i input_file] [-o output_file] 
    [-A packet size] [-J client character set] 
    [-T text or image size] [-E] [-g id_start_value] [-N] [-W] [-X]
    [-M LabelName LabelValue] [-labeled]
    [-K keytab_file] [-R remote_server_principal] [-C]
    [-V [security_options]] [-Z security_mechanism] [-Q] [-Y]
    [-y sybase directory] [-x trusted.txt_file]
    [--clienterr errfile] [--maxconn maximum_connections]
    [--show-fi] [--hide-vcc]
    [--colpasswd [[[db_name.[owner].]table_name.]column_name [password]]]
    [--keypasswd [[db_name.[owner].]key_name [password]]]
    [--initstring ASE initialization string] [--quoted-fname]
    [--filemode file_permission]
NULL

标签： sqlsql-server