存储过程中的排序规则冲突 [英] Collation conflict in stored procedure
本文介绍了存储过程中的排序规则冲突的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的存储过程在执行时抛出错误:
My stored procedure is throwing an error while executing:
ERROR: UNABLE TO CREATE DELIMITED TEXT FILE(原因:无法执行 varchar 值到 varchar 的隐式转换，因为 UNION ALL 运算符中Latin1_General_CI_AI"和Latin1_General_100_CI_AS_KS_WS_SC"之间的排序规则冲突导致值的排序规则未解析.)
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.)
我的存储过程基本上创建了一个分隔文件，并将表或视图与文件名一起作为输入.
My stored procedure basically creates a delimited file and takes a table or view as input along with filename.
我知道这是一个整理问题，但我已经尝试将 Collate Database_Default 和 Collate Catalog_Default 放在查询中，但错误并未由此解决.
I know it's a collation issue, but I have tried placing Collate Database_Default and Collate Catalog_Default in the query, but the error is not resolved by that.
任何人都可以指导我做错什么吗?
Can anyone please guide me as to what I am doing wrong?
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 查询中应用 union 时，它可能要求 union 查询中的每个字段必须具有相同的详细信息.例如字段数、各个字段的数据类型.
When you apply union in sql query it might demand each field in union query must be with same details. e.g number of fields, data type of the respective fields.
您的错误表明值的整理不同.您必须对两个查询中的列进行相同的整理.
Your error says collation of value is different. You have to make same collation for columns in both query.
以下脚本更改可以解决您的问题.
Below change in script could fix your problem.
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
这篇关于存储过程中的排序规则冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
