Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ошибка в запросе при выполнении объединения varchar  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1198
Исходные данные:
У всех клиентов устанавливается база с Collation Cyrillic_General_CI_AS
И как бы с этими вопросами я не заморачивался, пока не попался один клиент, у которого стояло почему-то Cyrillic_General_CI_AI
Делали очередное обновление БД, скрипт обновления базы упал на строке
select       @CardCategoryName = 'Категория "' + C.Name +'". Схема "' + W.Name + '"'
From....


Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Cyrillic_General_CI_AI" and "Cyrillic_General_CI_AS" in add operator.

Решили сделать у данного клиента collation как у всех, т.е.
alter database xxx collate Cyrillic_General_CI_AS

Но появилось куча ошибок типа
The object 'UDF_STRLIST_TO_TABLE' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1


Вопрос 1:
А где увидеть, что The object 'UDF_STRLIST_TO_TABLE' is dependent on database collation?
Вопрос 2:
Как победить данную ситуацию? И как Remove the dependencies on the database collation? Грохнуть данные объекты и создать заново после изменения Collation?
Вопрос 3: как вообще правильно писать подобные запросы, чтоб избежать подобных ошибок? или лучше всех клиентов выставить Collation Cyrillic_General_CI_AS, ибо ничего другого и не нужно

На всякий случай текст функции
ALTER       FUNCTION [dbo].[UDF_STRLIST_TO_TABLE] (@list varchar(8000), @Separator char(1))
      RETURNS @tbl TABLE (STR_ITEM varchar(100) NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @str      varchar(8000),
              @tmpstr   varchar(8000)
      SET @tmpstr = @list
      SET @pos = charindex(@Separator, @tmpstr)
      WHILE @pos > 0
      BEGIN
           
          SET @str = substring(@tmpstr, 1, @pos - 1)
          INSERT @tbl (str_item) VALUES(ltrim(rtrim(@str)))
          SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
          SET @pos = charindex(@Separator, @tmpstr)
      END

      IF ltrim(rtrim(@tmpstr)) <> ''
      INSERT @tbl (STR_ITEM) VALUES(ltrim(rtrim(@tmpstr)))

      RETURN
   END
9 янв 19, 11:29    [21780376]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
У вас в определении функции наверно есть WITH SCHEMABINDING (вы-то показали текст без этого параметра).
Иногда так писать просто необходимо. Но объекты, объявленные как WITH SCHEMABINDING, накладывают определённые ограничения.
9 янв 19, 12:07    [21780420]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
У вас в определении функции наверно есть WITH SCHEMABINDING (вы-то показали текст без этого параметра).
Иногда так писать просто необходимо. Но объекты, объявленные как WITH SCHEMABINDING, накладывают определённые ограничения.
Возможно, я и неправ насчёт SCHEMABINDING?
9 янв 19, 12:10    [21780425]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1198
iap, Сделал Script Function as... Create to
Нет там такого параметра.

В перечне ошибок, из-за которых база не преобразуется, еще и есть
The statistics 'FuelSell.IX_FuelSell_By_AccountNumber' is dependent on database collation

Остальные 7 ошибок на функции
9 янв 19, 12:12    [21780429]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
minva
iap, Сделал Script Function as... Create to
Нет там такого параметра.

В перечне ошибок, из-за которых база не преобразуется, еще и есть
The statistics 'FuelSell.IX_FuelSell_By_AccountNumber' is dependent on database collation

Остальные 7 ошибок на функции
Вообще, сменить COLLATE у всей базы - это очень смело.
Речь, по-видимому, идёт не о конкретной функции, а о других объектах базы, которые создавались с COLLATE по-умолчанию.
9 янв 19, 12:21    [21780444]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
aleks222
Member

Откуда:
Сообщений: 923
minva
Вопрос 3: как вообще правильно писать подобные запросы, чтоб избежать подобных ошибок? или лучше всех клиентов выставить Collation Cyrillic_General_CI_AS, ибо ничего другого и не нужно


select       @CardCategoryName = 'Категория "' + C.Name +'". Схема "' + W.Name + '"' collate Cyrillic_General_CI_AS
From....
9 янв 19, 12:45    [21780471]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
minva
Вопрос 1:
А где увидеть, что The object 'UDF_STRLIST_TO_TABLE' is dependent on database collation?

Multi-statement Table-valued Functions со строковыми полями в резалтсете
наверняка все будут провоцировать подобную ошибку.
потому что все они возвращают table variable,
а table variable наследует коллэйшен базы.
ну или не наследует, если в явном виде прописывать коллэйшены строковых полей
(что вряд ли кто делает)
ваш случай как раз тот самый, возвращаете строки в функции.

полный перечень того, что будет блокировать ваш ALTER DATABASE
можно найти тут:
ALTER DATABASE - Changing the Database Collation

minva
Вопрос 2:
Как победить данную ситуацию? И как Remove the dependencies on the database collation? Грохнуть данные объекты и создать заново после изменения Collation?

именно.
заскриптовать, грохнуть, заново создать в базе с новым коллэйшеном
9 янв 19, 13:01    [21780492]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1198
Yasha123, спасибо!
9 янв 19, 13:24    [21780508]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
minva
Member

Откуда: г. Калуга
Сообщений: 1198
Подниму снова темку

>полный перечень того, что будет блокировать ваш ALTER DATABASE
Перечень всего этого выпадает в ошибках. С момента написания этого топика проблема была решена, взяты объекты из выпавших ошибок, пересозданы, все хорошо
Сейчас вернулся к к скрипту, который пересоздавал объекты, сразу не смог вспомнить, откуда я взял их перечень. Потом вспомнил, как это было, нашел этот свой топик, но остался невыясненным один вопрос

А где увидеть, что The object 'UDF_STRLIST_TO_TABLE' is dependent on database collation?

Т.е. как увидеть перечень этих объектов, как это сделать запросом?
1 авг 19, 09:27    [21939494]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка в запросе при выполнении объединения varchar  [new]
invm
Member

Откуда: Москва
Сообщений: 9279
minva
как увидеть перечень этих объектов, как это сделать запросом?
Примерно так
select
 o.type, schema_name(o.schema_id), o.name, m.is_schema_bound, null as columns_list
from
 sys.objects o left join
 sys.sql_modules m on m.object_id = o.object_id
where
 o.type = 'C' or 
 (o.type in ('V', 'FN', 'IF', 'TF') and m.is_schema_bound = 1)

union all

select
 o.type, schema_name(o.schema_id), o.name, null, case when o.type = 'TF' then string_agg(c.name, ', ') within group (order by c.column_id) end
from
 sys.columns c join
 sys.objects o on o.object_id = c.object_id
where
 c.is_computed = 1 or
 (o.type = 'TF' and c.collation_name = databasepropertyex(db_name(), 'Collation'))
group by
 o.type, schema_name(o.schema_id), o.name;
1 авг 19, 11:07    [21939601]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить