непонятки collate / Microsoft SQL Server / Sql.ru

Добро пожаловать в форум, Guest >> Войти \| Регистрация \| Поиск \| Правила \|			В избранное \| Подписаться
Все форумы / Microsoft SQL Server

Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все

непонятки collate [new]

нуб987
Guest

не пойму логики преобразования collate.
Вводная: есть некий сервер, на котором лежит справочник. Из этого справочника мне нужно скопировать новые записи в мой справочник на "моем" сервере.

У того удаленного сервера collate установлен по дефолту в "SQL_Latin1_General_CP1251_CI_AS", соот-но у всех баз и строковых полей таблиц тоже.
На "моем" сервере стоит "Cyrillic_General_CS_AI", у всех баз и таблиц тоже.

на удаленном сервере есть такие записи:
ГиперМаркет
гипермаркет
МиниМаркет
минимаркет

На моем такие:
ГиперМаркет
МиниМаркет

делаю такой запрос:

select * 
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrvName>;UID=<UID>;PWD=<PWD>',
   '
SELECT distinct MagazineTypeName
	FROM <DBName>..<LkpName>
') AS a
where MagazineTypeName is not NULL
   and MagazineTypeName collate Cyrillic_General_CS_AI not in (select Name from POSKinds_Total)

возвращает:
гипермаркет
минимаркет

Делаю другой (похожий) запрос:

select * 
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrvName>;UID=<UID>;PWD=<PWD>',
   '
SELECT distinct MagazineTypeName collate Cyrillic_General_CS_AI -- перенес сюда
	FROM <DBName>..<LkpName>
') AS a
where MagazineTypeName is not NULL
   and MagazineTypeName /*collate Cyrillic_General_CS_AI*/ not in (select Name from POSKinds_Total) -- отсюда убрал, перенес выше

возвращает пустой набор данных

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

Подскажите плз, в чем я ошибаюсь? Почему есть разница?

ПС. MSSQL 2000

20 июл 15, 04:29 [17911132] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

Glory
Member

Откуда:
Сообщений: 104751

нуб987
и строковых полей таблиц тоже.

Строковые типы бывают разные. Вот у вас они какие ?

нуб987
Т.е., как я понимаю, разницы в запросах как бы нет.

нуб987
Делаю другой (похожий) запрос: ... возвращает пустой набор данных

OPENROWSET возвращает или весь запрос ?

нуб987
У того удаленного сервера collate установлен по дефолту в "SQL_Latin1_General_CP1251_CI_AS"

Что сказано про collate в настройках вашего linked server-а ?

20 июл 15, 10:02 [17911473] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

o-o
Guest

у ТС единственный возможный вариант типа колонки на линкеде -- это nvarchar.
это если верить тому, что там сплошной SQL_Latin1_General_CP1_CI_AS
(может, там и varchar, но тогда коллэйшен колонки кириллический)
а OPENROWSET возвращает в серверном коллэйшене, т.е. в коллэйшене базы мастер таргета.
это хорошо видно на сервере с левым коллэйшеном типа греческий.
показываю на 2008-ом, т.к. все то же самое, что и на 2000-ом, но есть SQL_VARIANT_PROPERTY:

select *, SQL_VARIANT_PROPERTY(expr, 'collation') as col_collation_on_target
from OPENROWSET('SQLNCLI',
   'Server=localhost;Trusted_Connection=yes;',
   '
SELECT id, 
       SQL_VARIANT_PROPERTY(col, ''collation'') as col_collation_on_source,
       col collate Cyrillic_General_CS_AI as expr,
       SQL_VARIANT_PROPERTY(col collate Cyrillic_General_CS_AI, ''collation'') as expr_collation_on_source
	FROM db0.dbo.t
') AS a

id	col_collation_on_source	expr	expr_collation_on_source	col_collation_on_target
1	SQL_Latin1_General_CP1_CI_AS	ГиперМаркет	Cyrillic_General_CS_AI	Greek_100_CI_AS
2	SQL_Latin1_General_CP1_CI_AS	гипермаркет	Cyrillic_General_CS_AI	Greek_100_CI_AS
3	SQL_Latin1_General_CP1_CI_AS	МиниМаркет	Cyrillic_General_CS_AI	Greek_100_CI_AS
4	SQL_Latin1_General_CP1_CI_AS	минимаркет	Cyrillic_General_CS_AI	Greek_100_CI_AS

20 июл 15, 11:00 [17911731] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

Glory

нуб987
и строковых полей таблиц тоже.

Строковые типы бывают разные. Вот у вас они какие ?

на удаленном и на моем varchar (НЕ nvarchar)

Glory

нуб987
Делаю другой (похожий) запрос: ... возвращает пустой набор данных

OPENROWSET возвращает или весь запрос ?

пустой набор возвращает весь запрос. Если запустить только Openrowset, то вернет все данные (с учетом distinct), которые есть на удаленном.

Glory

нуб987
У того удаленного сервера collate установлен по дефолту в "SQL_Latin1_General_CP1251_CI_AS"

Что сказано про collate в настройках вашего linked server-а ?

я ж говорю: на удаленном везде (и в св-вах сервера, и в св-вах базы) стоит SQL_Latin1_General_CP1251_CI_AS. У строковых полей стоит <database default>

20 июл 15, 11:36 [17911897] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

Glory
Member

Откуда:
Сообщений: 104751

нуб987
я ж говорю: на удаленном везде

А я спрашивал про свойства linked server-а

нуб987
Если запустить только Openrowset, то вернет все данные (с учетом distinct), которые есть на удаленном.

И какой collation получается у полей, которые возвращает Openrowset

20 июл 15, 11:39 [17911926] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

o-o
Guest

нуб987
на удаленном и на моем varchar (НЕ nvarchar)

да-да-да, и в строках типа varchar с коллэйшеном SQL_Latin1_General_CP1251_CI_AS у вас лежит кирилица.
NLS-ы винды что ли трогали?
я не верю, короче.
покажите на линкованном

select name, collation, object_name(id)
from syscolumns
where object_name(id) = 'authors' -- your table name

20 июл 15, 11:47 [17911975] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

Glory

нуб987
я ж говорю: на удаленном везде

А я спрашивал про свойства linked server-а

он же не linked... Или я чего-то не понимаю? Где посмотреть?

нуб987
Если запустить только Openrowset, то вернет все данные (с учетом distinct), которые есть на удаленном.

И какой collation получается у полей, которые возвращает Openrowset[/quot]
сделал так:

select * 
into tmpPosKinds -- добавил это
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrvName>;UID=<UID>;PWD=<PWD>',
   '
SELECT distinct MagazineTypeName
	FROM <DBName>..<LkpName>
') AS a
where MagazineTypeName is not NULL

получил строковое поле типа varchar, collation <database default>

20 июл 15, 11:47 [17911977] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]
o-o Guest	нуб987, ой, пардон, 1251 -- это тоже вроде кириллица? извиняюсь тогда. + у вас не линкед, свойств никаких нет, я когда говорю линкед, имею в виду удаленный. короче, OPENROWSET отдает всегда в том коллэйшене, что у базы мастер. на сервере, где выполняете. я не про BULK-и сейчас, а про обычный селект
20 июл 15, 11:55 [17912023] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]
o-o Guest	картинкой всегда быстрее и доходчивее. вот 2 сервера: sistocd2 + sistocd2,2675, ни один из них не кириллический, но зато на втором -- "удаленном" sistocd2,2675 -- создана кириллическая база cyr_1251, в ней таблица с варчарами, выборка из нее на родном сервере в контексте родной базы -- кириллическая. выборка через OPENROWSET даже в контексте КИРИЛЛИЧЕСКОЙ базы TEST все равно идет в серверном коллэйшене таргета К сообщению приложен файл. Размер - 111Kb
20 июл 15, 12:29 [17912201] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

o-o
выборка через OPENROWSET даже в контексте КИРИЛЛИЧЕСКОЙ базы TEST все равно идет в серверном коллэйшене таргета

т.е. получается, что:
- если я внутри openrowset'а в запросе укажу нужный collate
- или уже "снаружи" (в where) преобразую поле
то должно получиться:

o-o
в серверном коллэйшене таргета

так почему тогда оба моих запроса выдают разный результат?

20 июл 15, 13:51 [17912788] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]
o-o Guest	нуб987, нет, как раз не должно вообще ничего зависеть от вашего "внутреннего", все надо делать "снаружи". т.к. о вашем "внутреннем" преобразовании таргет не узнает, ему об этом инфо не передают. он тупо вернет результат в коллэйшене мастера сервeра-таргета. видели же, как он уделал исходную кириллицу (см. картинку слева). если у вас при явном приведении результата на таргете что-то поменялось по сравнению с неприведением вообще, то у вас мастер таргета имеет не Cyrillic_General_CS_AI
20 июл 15, 13:59 [17912831] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

o-o

нуб987
на удаленном и на моем varchar (НЕ nvarchar)

select name, collation, object_name(id)
from syscolumns
where object_name(id) = 'authors' -- your table name

вот:

name	collation	(No column name)
Id	NULL	<LkpName>
Active	NULL	<LkpName>
MagazineTypeId	SQL_Latin1_General_CP1251_CI_AS	<LkpName>
MagazineTypeName	SQL_Latin1_General_CP1251_CI_AS	<LkpName>
Comments	SQL_Latin1_General_CP1251_CI_AS	<LkpName>
msrepl_tran_version	NULL	<LkpName>
SyncSaveTime	SQL_Latin1_General_CP1251_CI_AS	<LkpName>

20 июл 15, 14:01 [17912836] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

а на моем вот:

name	collation	(No column name)
id	NULL	POSKinds_Total
Name	Cyrillic_General_CI_AS	POSKinds_Total

20 июл 15, 14:04 [17912861] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]
o-o Guest	нуб987, эээ...у меня там вышел казус с 1251, т.е. т.к. у меня SQL_Latin1_General_CP1_CI_AS (без 1251), то показалось, что и у вас тоже, так что нет, с 1251 все хорошо. у меня там выше извинение по этому поводу покажите лучше вот это: select databasepropertyex('master', 'collation') на таргете
20 июл 15, 14:06 [17912872] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]

o-o
Guest

нуб987

а на моем вот:

name	collation	(No column name)
id	NULL	POSKinds_Total
Name	Cyrillic_General_CI_AS	POSKinds_Total

Cyrillic_General_CI_AS
что и требовалось доказать.
у вас на таргете CI.
поэтому и приходится явно приводить к CS

20 июл 15, 14:08 [17912887] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

o-o
Cyrillic_General_CI_AS что и требовалось доказать. у вас на таргете CI. поэтому и приходится явно приводить к CS

ЁПРСТ......
точно :) Недоглядел и перепутал.

Т.е. у меня в базе CI (case-insensitive - НЕчувствительный к регистру), а я в своих запросах его преобразую в CS (case-sensitive - Чувствительный к регистру)

поэтому в первом случае

нуб987

select * 
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrvName>;UID=<UID>;PWD=<PWD>',
   '
SELECT distinct MagazineTypeName
	FROM <DBName>..<LkpName>
') AS a
where MagazineTypeName is not NULL
   and MagazineTypeName collate Cyrillic_General_CS_AI not in (select Name from POSKinds_Total)

возвращает:
гипермаркет
минимаркет

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

А во втором запросе:
[quot нуб987]

select * 
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrvName>;UID=<UID>;PWD=<PWD>',
   '
SELECT distinct MagazineTypeName collate Cyrillic_General_CS_AI -- перенес сюда
	FROM <DBName>..<LkpName>
') AS a
where MagazineTypeName is not NULL
   and MagazineTypeName /*collate Cyrillic_General_CS_AI*/ not in (select Name from POSKinds_Total) -- отсюда убрал, перенес выше

возвращает пустой набор данных
===========================
потому что openrowset вернул данные в collation таргет-сервера (моего), независимо от того, что я указал в запросе (ведь так?). Т.е. пришло:
гипермаркет
минимаркет
(пофигу, в каком регистре)
но

and MagazineTypeName /*collate Cyrillic_General_CS_AI*/ not in (select Name from POSKinds_Total)

их отсеял, т.к. MagazineTypeName уже находится в моем collate

теперь все ясно, спасибо :)

Только еще уточнить:
получается, что даже если я внутри openrowset в запросе укажу преобразование collate:

OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrvName>;UID=<UID>;PWD=<PWD>',
   '
SELECT distinct MagazineTypeName collate Cyrillic_General_CS_AI -- перенес сюда
	FROM <DBName>..<LkpName>
') AS a

то на мой сервер данные все равно придут в collate моего сервера?

20 июл 15, 14:29 [17913018] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

o-o
Guest

нуб987
получается, что даже если я внутри openrowset в запросе укажу преобразование collate: то на мой сервер данные все равно придут в collate моего сервера?

да

20 июл 15, 14:35 [17913063] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]
нуб987 Guest	o-o, спасибо :)
20 июл 15, 14:40 [17913095] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

o-o
короче, OPENROWSET отдает всегда в том коллэйшене, что у базы мастер.

столкнулся с еще одной проблеммой с collation
есть 2 сервера. С одного на другой копирую записи справочника.
- на SrcServer collation всех баз SQL_Latin1_General_CP1251_CI_AS
- на DestSrver collation базы master SQL_Latin1_General_CP1_CI_AS. А у базы, в которую я копирую данные, Cyrillic_General_CI_AS

так вот, когда я выполняю на DestServer такой запрос:

select PriceTypeName -- collate Cyrillic_General_CI_AS -- нет никакой разницы, есть collate или нет
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrcServer>;Trusted_Connection=yes;Integrated Security=SSPI;Persist Security Info=False;',
   '
SELECT distinct PriceTypeName
	FROM <DBName>..Table1
where Active = 1
') AS a

, то получаю крякозябры.

Почему так?

Причем если в запросе (внутри OPENROWSET) заменить
SELECT distinct PriceTypeName
на
SELECT distinct cast(PriceTypeName as nvarchar(20)) as PriceTypeName
то выводится нормальный русский текст.

Но мне больше непонятно, почему collate никак не влияет на результат?

17 мар 16, 03:10 [18941127] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]
o-o Guest	Вы же сами только что процитировали ответ. Результат openrowset отдается в коллэйшене мастера сервера-таргета, а у вас там латиница. Что не сходится-то? А юникод он везде юникод, он сам хранит кодовую страницу каждого символа, ему не нужен коллэйшен, в коллэйшене для юникод интересует только case sensitivity
17 мар 16, 08:58 [18941334] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

o-o
Вы же сами только что процитировали ответ. Результат openrowset отдается в коллэйшене мастера сервера-таргета, а у вас там латиница. Что не сходится-то?

не сходится то, что как бы на таргете я не менял collate, результат не меняется. Я там выше в комментарий сунул преобразование collate:

select PriceTypeName -- collate Cyrillic_General_CI_AS -- нет никакой разницы, есть collate или нет
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrcServer>;Trusted_Connection=yes;Integrated Security=SSPI;Persist Security Info=False;',
   '
SELECT distinct PriceTypeName
	FROM <DBName>..Table1
where Active = 1
') AS a

17 мар 16, 18:30 [18944939] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]

o-o
Guest

нуб987

o-o
Вы же сами только что процитировали ответ. Результат openrowset отдается в коллэйшене мастера сервера-таргета, а у вас там латиница. Что не сходится-то?

select PriceTypeName -- collate Cyrillic_General_CI_AS -- нет никакой разницы, есть collate или нет
from OPENROWSET('SQLOLEDB.1',
   'DRIVER={SQL Server};SERVER=<SrcServer>;Trusted_Connection=yes;Integrated Security=SSPI;Persist Security Info=False;',
   '
SELECT distinct PriceTypeName
	FROM <DBName>..Table1
where Active = 1
') AS a

потому что все коллэйты в вашем запросе
применяются к уже полученному результату.
т.е. кириллица отправляется в путь на другой сервер в виде ascii,
на родном сервере в родной базе известна ее кодовая страница,
она берется из коллэйшена базы/столбца.
как только это коды попали на второй сервер,
он уже не знает, что это кириллица.
он видит какие-то коды и считает, что это латиница,
потому что такой коллэйшен его мастера.
все, символы потеряны.
вы дописываете ему COLLATE, но это применяется уже к кракозябрам.
и нет способа применить "до", т.к. это надо было реализовывать
или отсылая кодовую страницу (но это OPENROWSET не делает)
или передавая юникодом (а вот это вы реализовали)

17 мар 16, 19:11 [18945092] Ответить | Цитировать Сообщить модератору

Re: непонятки collate [new]
нуб987 Guest	o-o, т.е., кроме как преобразованием в nvarchar на стороне исходника, это никак не реализовать? select PriceTypeName from OPENROWSET('SQLOLEDB.1', 'DRIVER={SQL Server};SERVER=<SrcServer>;Trusted_Connection=yes;Integrated Security=SSPI;Persist Security Info=False;', ' SELECT distinct cast(PriceTypeName as nvarchar(20)) as PriceTypeName -- <<< вот здесь FROM <DBName>..Table1 where Active = 1 ') AS a
17 мар 16, 19:33 [18945158] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]
o-o Guest	нуб987, Можно передавать не в символьном виде, а в бинарном. Тогда ничего не перекодируется. На втором сервере конвертировать обратно в символы уже в нужной базе с подходящим коллэйшеном. У меня на картинке 2 сервера: латинец(дефолтный) и грек. Т. Е. у обоих серверный коллэйшен некириллический. На обоих есть по кириллической базе Cyr. Теперь на латинском сервере кладу в таблицу кириллицу в кириллической же базе, на греческом пытаюсь ее селектить через OPENROWSET в контексте кириллической базы. Если передавать символы, сервер видит ascii и считает, что это греческий. А бинарные данные не имеют коллэйшена, зато если их откастить обратно в символы в контексте кириллической базы, сервер считает, что это 1251,потому что коллэйшен базы кириллический К сообщению приложен файл. Размер - 60Kb
17 мар 16, 20:49 [18945382] Ответить \| Цитировать Сообщить модератору

Re: непонятки collate [new]

нуб987
Guest

o-o, спасибо вам за разъяснения :)
все же оставил внутри openrowset преобразование в nvarchar, а не бинарный, чтобы на таргете не пришлось преобразовывать из бинарного в строковый.

и еще появился вопрос о collation. Опять "удаленный" сервер и таргет-сервер. На удаленном collation SQL_Latin1_General_CP1251_CI_AS. На таргете Cyrillic_General_CI_AS.
И есть длинный запрос вида:

insert(...)
select ...
from ...,
openrowset(..., SrcDB..SrcTable) as src -- << здесь получаем таблицу с кучей полей
left join Lkp1 on Lkp1.Name = Src.ChildType_Desc,
....
where 1 = 1
   and src...
   and src...
   and ...

нужно с удаленного сервера получить таблицу с кучей строковых и числовых полей (они используются в where). Естественно данные приходят поврежденными (у этой таблицы строковые поля имеют тип varchar, а не nvarchar). И условия в where не работают (потому что сравниваются поврежденные данные).

Чтобы все работало, я так понимаю, нужно получить данные в юникоде. Т.е. преобразовать строки в nvarchar еще внутри openrowset, т.е. на стороне удаленного сервера, пока они не пришли на таргет-сервер и не повредились там.
Т.е. нужно строку

openrowset(..., SrcDB..SrcTable) as src -- << здесь получаем таблицу с кучей полей

заменить на что-то вроде:

openrowset(...,
'select
   cast(Field1 as nvarchar(255)) as Field1,
   cast(Field2 as nvarchar(255)) as Field2,
   ..., 
   cast(FieldN as nvarchar(255)) as FieldN,
   IntField1, ..., IntFieldN
') as src

правильно ли я делаю? Как вообще нормальные люди поступают в таких случаях?
ПС. предлагать изменить формат полей на nvarchar на удаленном сервере не надо - он не в моей ответственности.

22 мар 16, 04:35 [18961653] Ответить | Цитировать Сообщить модератору

Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все

Все форумы / Microsoft SQL Server

Ссылка на сообщение

Ссылка (включая название темы)

Ссылка (URL)