Конфликт сопоставления в хранимой процедуре

Моя хранимая процедура выдает ошибку при выполнении:

ERROR: UNABLE TO CREATE DELIMITED TEXT FILE (Reason: Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AI" and "Latin1_General_100_CI_AS_KS_WS_SC" in UNION ALL operator.)

Моя хранимая процедура в основном создает файл с разделителями и принимает в качестве входных данных таблицу или представление вместе с именем файла.

Я знаю, что это проблема сопоставления, но я попытался поместить в запрос 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 < @HeaderCount
      begin
        -- Increase counter
        set @counter = @counter + 1
        -- Get column name
        SELECT TOP 1 @colname = column_name FROM ( SELECT TOP (@counter) 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
        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 < @HeaderCount
      begin
        -- Increase counter
        set @counter = @counter + 1
        -- Get column name
        SELECT TOP 1 @colname = column_name  FROM ( SELECT TOP (@counter) column_name, rank() OVER (ORDER BY ordinal_position) as rank
        FROM INFORMATION_SCHEMA.columns
        where UPPER(table_name) = Upper((REPLACE(REPLACE(@Source,'[',''),']','')))
        ORDER BY rank ASC ) as t ORDER BY rank DESC

        -- Add to main view query
        IF @counter = @HeaderCount
          BEGIN
            SET @SQL = @SQL + ''''+@Qualifier+'''+ cast(min(case ordinal_position when '+cast(@counter as varchar)+' then column_name end) as varchar)   +'''+@Qualifier+''' as ['+@colname+'] '
          END
        ELSE
          BEGIN
            SET @SQL = @SQL + ''''+@Qualifier+'''+cast(min(case ordinal_position  when '+cast(@counter as varchar)+' then column_name end) as varchar)  +'''+@Qualifier+''' as ['+@colname+'], '
          END
      end

    SET @SQL = @SQL + ' from ['+db_name()+'].information_schema.columns where UPPER(table_name) = Upper('''+Upper((REPLACE(REPLACE(@Source,'[',''),']','')))+''') '

  END
-- Finish up the main view query
SET @SQL = @SQL + ' union all '
SET @SQL = @SQL + ' select '
SET @SQL = @SQL + @Header + ' FROM ['+db_name()+']..['+Upper((REPLACE(REPLACE(@Source,'[',''),']','')))+']'

-- Add criteria if exists
IF (@Criteria <> '')
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

Я предполагаю, что у вас есть временная таблица другого сопоставления в миксе. В временных таблицах приводится сопоставление временной базы данных, а не основной базы данных, которую вы используете. Однако я думаю, что кому-либо будет очень сложно дать вам больше советов, чем это, учитывая сложность вашего SQL. Вам просто нужно отлаживать его старомодным способом, создавать свой код блок за раз, чтобы определить точное место нечетного сбоя сопоставления.

Dale K 31.10.2018 01:34

Ошибка говорит о том, что конфликт возникает в UNION ALL, и у вас есть только одно объединение all в вашем запросе. Попробуйте напечатать @SQL после присваивания SET @SQL = @SQL + @Header + ' FROM ['+db_name()+']..['+Upper((REPLACE(REPLACE(@Source,'[',''),'‌​]','')))+']', скопируйте его и попробуйте выполнить, конфликт здесь

sepupic 31.10.2018 10:03

@sepupic Это правильное решение

Jaspreet Saini 01.11.2018 10:48
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
3
453
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Когда вы применяете объединение в запросе sql, он может потребовать, чтобы каждое поле в запросе объединения было с одинаковыми деталями. например, количество полей, тип данных соответствующих полей.

Ваша ошибка говорит о другом сопоставлении значений. Вы должны сделать одинаковую сортировку для столбцов в обоих запросах.

Изменение сценария, приведенное ниже, может решить вашу проблему.

IF @counter = @HeaderCount
  BEGIN
    SET @SQL = @SQL + ''''+@Qualifier+'''+ cast(min(case ordinal_position when '+cast(@counter as varchar)+' then column_name Collate Latin1_General_CI_AI end) as varchar)   +'''+@Qualifier+''' as ['+@colname+'] '
  END
ELSE
  BEGIN
    SET @SQL = @SQL + ''''+@Qualifier+'''+cast(min(case ordinal_position  when '+cast(@counter as varchar)+' then column_name Collate Latin1_General_CI_AI end) as varchar)  +'''+@Qualifier+''' as ['+@colname+'], '
  END

Другие вопросы по теме