存储过程中的排序规则冲突

奋斗才配拥有

我的存储过程在执行时抛出错误：
- Y2 O! V& k( V$ `8 g
错误：无法创建分隔的文本文件（原因：无法进行从varchar值到varchar的隐式转换，因为在UNION ALL运算符中，由于“) z! r* A* x" D1 R5 Y8 o) O- S
Latin1_General_CI_AI”和“
, _1 t4 b8 J! d3 ZLatin1_General_100_CI_AS_KS_SC”之间的排序规则冲突，导致无法解析该值的排序规则。）$ Q5 x' N) l  K5 M

我的存储过程基本上会创建一个带分隔符的文件，并将表或视图与文件名一起作为输入。
我知道这是排序规则问题，但是我尝试在查询中放置Collate Database_Default和Collate
Catalog_Default，但是该错误无法解决。
有人能指导我做错什么吗？
ALTER PROCEDURE Usp_Delfile
   (@Source VARCHAR(MAX),
   @DestinationFile VARCHAR(MAX),
   @ColumnList VARCHAR(MAX) = '',
   @Delimiter VARCHAR(256) = ',',
   @Qualifier VARCHAR(256) = '"',
   @Criteria VARCHAR(MAX) = '',
   @FirstRow INT = 0,
   @LastRow INT = 0,
   @Username VARCHAR(256) = '',
   @Password VARCHAR(256) = '',
   @Server VARCHAR(256) = '',
   @SourceType VARCHAR(100) = '',
   @SourceTableName VARCHAR(128) = '',
   @OtherConnection VARCHAR(MAX) = '')
AS
BEGIN
-- Declare variable
DECLARE @HeaderCount INT
DECLARE @Header VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)
DECLARE @COLNAME VARCHAR(MAX)
DECLARE @SUBSQL VARCHAR(MAX)
DECLARE @TEMPVIEWNAME VARCHAR(MAX)
DECLARE @counter INT
-- Otherconnection is not used but kept for future development
SET @OtherConnection = ''
-- Set the name of the temporary view
SET @TEMPVIEWNAME = 'uTEMPVIEW'+convert(varchar(max),newid())
BEGIN TRY
-- Try to figure out the source type in case one is not given and it appears something other than SQL may be given
BEGIN TRY
IF (charindex('\',@SOURCE) > 0 AND charindex('.', reverse(@SOURCE)) = 4 AND @SourceType='')
BEGIN
      SET @SourceType = SUBSTRING(UPPER(@SOURCE),LEN(@SOURCE)-2,3)
END
ELSE
      SET @SourceType = 'SQL'
end try
begin catch
-- If an error occurs during this time, ignore it and assume SQL source type
  SET @SourceType = 'SQL'
end catch

IF (UPPER(@SourceType)  'SQL')
BEGIN
  IF (@OtherConnection  '')
-- This will be used in the future but disabled for now from previous set statement (I left this in here because I have a horrible memory!)
exec ('create view ['+@TEMPVIEWNAME+'-other] as (select * from OpenRowset('+@OtherConnection+'))')
  ELSE
  BEGIN
-- If the source is a delimited file, create a view to the file
DECLARE @filepath varchar(256)
DECLARE @filename varchar(256)
DECLARE @OtherViewSQL varchar(max)
-- Get the file path and filename
select @filepath=reverse(substring(reverse(@Source), charindex('\', reverse(@Source))+1, len(@Source) - charindex('\', reverse(@Source)) ))
select @filename=reverse(substring(reverse(@Source), 0, charindex('\', reverse(@Source)) ))
-- Create view to the file using its connector
If(UPPER(@SourceType) = 'DELIMITED' OR UPPER(@SourceType) = 'CSV' OR Upper(@SourceType) = 'TEXT' OR Upper(@SourceType) = 'TXT')
exec('create view ['+@TEMPVIEWNAME+'-other] as (select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir='+@filepath+';'',''select * from ['+@filename+']''))')
else if(UPPER(@SourceType) = 'DBF' OR UPPER(@SourceType) = 'DBASE' OR UPPER(@SourceType) = 'DBASE3' OR UPPER(@SourceType) = 'DBASEIII' OR UPPER(@SourceType) = 'DBASE 3' OR UPPER(@SourceType) = 'DBASE III' OR UPPER(@SourceType) = 'FOXPRO')
exec('create view ['+@TEMPVIEWNAME+'-other] as (select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft dBase Driver (*.dbf)};DBQ='+@filepath+';'',''select * from ['+@filename+']''))')
else IF(UPPER(@SourceType) = 'ACCESS' OR UPPER(@SourceType) = 'MDB')
exec('create view ['+@TEMPVIEWNAME+'-other] as (select * from OpenRowset(''MICROSOFT.JET.OLEDB.4.0'', '''+@filepath+'\'+@filename+''' ;;,['+@SourceTableName+']))')
else IF(UPPER(@SourceType) = 'EXCEL' OR UPPER(@SourceType) = 'XLS')
exec('create view ['+@TEMPVIEWNAME+'-other] as (select * from OpenRowset(''MICROSOFT.JET.OLEDB.4.0'', ''Excel 8.0;DATABASE='+@filepath+'\'+@filename+''',''select * from ['+@SourceTableName+'$]''))')
  END

-- Set the source table to the new view
SET @Source = @TEMPVIEWNAME+'-other'
END
ELSE
  SET @SourceTableName = @Source
-- Check to see if columnlist is provided
IF (@ColumnList  '')
  BEGIN
-- Get header count from columnlist
SELECT @HeaderCount = ((LEN(RTRIM(LTRIM(@ColumnList))) - LEN(REPLACE(RTRIM(LTRIM(@ColumnList)), ',', '')))+1)
-- Build delimited file header row
SELECT @Header = COALESCE(@Header  + ',', '') +
   CASE WHEN @Qualifier = '' THEN ' '''+@Qualifier+'''+CASE when isnumeric(['+column_name+']) = 1 AND
case when exists(select ordinal_position  from
INFORMATION_SCHEMA.COLUMNS where Upper(table_name) = '''+Upper((REPLACE(REPLACE(@Source,'[',''),']','')))+'''
and (UPPER(data_type) ''VARCHAR'' OR UPPER(data_type) ''NVARCHAR'' OR UPPER(data_type) ''CHAR'' OR UPPER(data_type)  ''NCHAR'')
and Upper(column_name) ='''+UPPER(column_name) COLLATE CATALOG_DEFAULT +''') THEN -1 ELSE 0
END = 0
THEN cast(cast(['+column_name+']  as decimal(38, 38)) as varchar(max)) ELSE cast(CASE when ['+column_name+']  = '''' THEN NULL ELSE ['+column_name+']  END as varchar(max)) END+'''+@Qualifier+''' as ['+column_name+']'
ELSE ' '''+@Qualifier+'''+ISNULL(CASE when isnumeric(['+column_name+']) = 1 AND
case when exists(select ordinal_position  from
INFORMATION_SCHEMA.COLUMNS where Upper(table_name)  = '''+Upper((REPLACE(REPLACE(@Source,'[',''),']','')))+'''
and (UPPER(data_type)  ''VARCHAR'' OR UPPER(data_type)  ''NVARCHAR'' OR UPPER(data_type)  ''CHAR'' OR UPPER(data_type) ''NCHAR'')
and Upper(column_name)='''+UPPER(column_name)+''') THEN -1 ELSE 0
END = 0
THEN cast(cast(['+column_name+'] as decimal(38, 38)) as varchar(max)) ELSE cast(CASE when ['+column_name+'] = '''' THEN NULL ELSE ['+column_name+'] END as varchar(max)) END,'''')+'''+@Qualifier+''' as ['+column_name+']'
      END
FROM ( SELECT column_name, rank() OVER (ORDER BY ordinal_position) as rank
FROM INFORMATION_SCHEMA.columns
where UPPER(table_name) = Upper((REPLACE(REPLACE(@Source,'[',''),']',''))) AND charindex(','+column_name+',',RTRIM(LTRIM(','+@ColumnList+','))) > 0
) t ORDER BY t.rank ASC
-- Initialize main view query
SET @SQL = 'SELECT '
-- Build main view query
set @counter = 0
while @counter  0
      ORDER BY rank ASC ) as t ORDER BY rank DESC
      -- Add to main view query
      IF @counter = @HeaderCount
      BEGIN
         SET @SQL = @SQL + ''''+@Qualifier+'''+ SUBSTRING(RTRIM(LTRIM('''+@ColumnList+''')),charindex('''+@colname+''',RTRIM(LTRIM('''+@ColumnList+'''))), LEN('''+@colname+'''))+'''+@Qualifier+''' as ['+@colname+'] '
      END
      ELSE
      BEGIN
         SET @SQL = @SQL + ''''+@Qualifier+'''+ SUBSTRING(RTRIM(LTRIM('''+@ColumnList+''')),charindex('''+@colname+''',RTRIM(LTRIM('''+@ColumnList+'''))), LEN('''+@colname+'''))+'''+@Qualifier+''' as ['+@colname+@Delimiter+'], '
      END
   end
  END
ELSE
  BEGIN
-- Get header count from columnlist
SELECT @HeaderCount = count(column_name)
FROM INFORMATION_SCHEMA.columns
where UPPER(table_name) = Upper((REPLACE(REPLACE(@Source,'[',''),']','')))
-- Build delimited file header row
SELECT @Header = COALESCE(@Header  + ',', '') +
   CASE WHEN @Qualifier = '' THEN ' '''+@Qualifier+'''+CASE when isnumeric(['+column_name+']) = 1 AND
case when exists(select ordinal_position from
INFORMATION_SCHEMA.COLUMNS where Upper(table_name) COLLATE CATALOG_DEFAULT = '''+Upper((REPLACE(REPLACE(@Source,'[',''),']','')))+'''
and (UPPER(data_type)  ''VARCHAR'' OR UPPER(data_type)  ''NVARCHAR'' OR UPPER(data_type)  ''CHAR'' OR UPPER(data_type) ''NCHAR'')
and Upper(column_name)='''+UPPER(column_name)+''') THEN -1 ELSE 0
END = 0
THEN cast(cast(['+column_name+'] as decimal(38, 38)) as varchar(max)) ELSE cast(CASE when ['+column_name+'] = '''' THEN NULL ELSE ['+column_name+'] END as varchar(max)) END+'''+@Qualifier+''' as ['+column_name+']'
ELSE ' '''+@Qualifier+'''+ISNULL(CASE when isnumeric(['+column_name+']) = 1 AND
case when exists(select ordinal_position from
INFORMATION_SCHEMA.COLUMNS where Upper(table_name) = '''+Upper((REPLACE(REPLACE(@Source,'[',''),']','')))+'''
and (UPPER(data_type)  ''VARCHAR'' OR UPPER(data_type)  ''NVARCHAR'' OR UPPER(data_type)  ''CHAR'' OR UPPER(data_type) ''NCHAR'')
and Upper(column_name)='''+UPPER(column_name)+''') THEN -1 ELSE 0
END = 0
THEN cast(cast(['+column_name+'] as decimal(38, 38)) as varchar(max)) ELSE cast(CASE when cast(['+column_name +'] as varchar(max)) = '''' THEN NULL ELSE ['+column_name+'] END as varchar(max)) END,'''')+'''+@Qualifier+''' as ['+column_name+']'
END
FROM ( SELECT column_name, rank() OVER (ORDER BY ordinal_position) as rank
FROM INFORMATION_SCHEMA.columns
where UPPER(table_name) = Upper((REPLACE(REPLACE(@Source,'[',''),']','')))
) t ORDER BY t.rank ASC
-- Initialize main view query
SET @SQL = 'SELECT '
-- Build main view query
set @counter = 0
while @counter  '')
BEGIN
  SET @SQL = @SQL + ' WHERE '+@Criteria+' '
END
-- Create temporary view
exec('create view ['+@TEMPVIEWNAME+'] as ('+@SQL+')')
-- Execute bcp on temporary view
DECLARE @bcpcmd varchar(8000)
SET @bcpcmd = 'bcp ["'+db_name()+']..['+@TEMPVIEWNAME+']" out "'+@DestinationFile+'" -k -c ACP -t "'+@Delimiter+'"'
-- Add first row and last row arguments to bcp command
IF (@FirstRow > 0)
SET @bcpcmd = @bcpcmd + ' -F '+cast(@FirstRow as varchar)
IF (@LastRow > 0)
SET @bcpcmd = @bcpcmd + ' -L '+cast(@LastRow as varchar)

-- Add server login information
IF (@Username  '')
BEGIN
  SET @bcpcmd = @bcpcmd + ' -U '+@Username
  IF (@Password  '')
  SET @bcpcmd = @bcpcmd + ' -P '+@Password
END
ELSE
BEGIN
  SET @bcpcmd = @bcpcmd + ' -T '
END
IF (@Server  '')
SET @bcpcmd = @bcpcmd + ' -S '+@Server
exec master..xp_cmdshell @bcpcmd
-- Drop temporary view
exec('IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'''+@TEMPVIEWNAME+''')) DROP View ['+@TEMPVIEWNAME+']')
exec('IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'''+@TEMPVIEWNAME+'-other'')) DROP VIEW ['+@TEMPVIEWNAME+'-other]')
end try
begin catch
  -- show error if one occurs
  SELECT 'ERROR: UNABLE TO CREATE DELIMITED TEXT FILE (Reason:' + error_message() + ')'
  begin try
-- Drop view if an error occurs
exec('IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'''+@TEMPVIEWNAME+''')) DROP View ['+@TEMPVIEWNAME+']')
exec('IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'''+@TEMPVIEWNAME+'-other'')) DROP VIEW ['+@TEMPVIEWNAME+'-other]')
  end try
  begin catch
  end catch
end catch
END

解决方案:

存储过程中的排序规则冲突

奋斗才配拥有 LV1