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

Откуда:
Сообщений: 24
Значить есть у нас SP, которая получает на вход ИД объектов, признаки сортировки и фильтрации, загоняет это во временную таблицу, сортирует, фильтрует, и если специально не было указано, что результат вывести НЕ в XML выдает его в XML. Используется она везде, чтобы не заморачиваться с одинаковостью XMLя. То есть везде где из базы берется XML, дергается она.

Проблема возникла недавно. Когда ИДов прихордит много, валится с ошибкой, что таг с ИДом 2 не сформирован итд. Если ее дернуть сразуже после этой ошибки с теми же параметрами, отдает нормально XML, без ошибок.

Кто нибудь сталкивался с похожим?
21 фев 05, 10:28    [1334379]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
jimmers
Member

Откуда: Санкт-Петербург - New York City
Сообщений: 5069
Нужен код процедуры и параметры вызова.
21 фев 05, 11:11    [1334511]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
rko
Member

Откуда:
Сообщений: 24
СREATE PROCEDURE XXXXXXXXXXXXXXXXXXXXX
@IDs varchar(8000)='',
@orderClm varchar(8000)='POID',
@boxType varchar(10)='inbox',
@userName varchar(50)=null,
@sortBy varchar(50)=null,
@sortOrder varchar(10)=null,
@output varchar(10)='XML'

AS

CREATE TABLE #temp (serviceIDs nchar(10) NOT NULL)
declare @userid int
if @boxType='outbox'
	begin
		if @userName is null
			begin
				select 'Bad user ID' as error
			end
		else
			begin
				set @userid=(select id from ZZZZZZZZz where name=@userName)
			end
	end


declare @pOrd int
set @pOrd=0
if @sortOrder='DESC'
BEGIN 
set @pOrd=0
END

EXEC unpack_with_union @ids, '#temp'

-- Заполняет ИДшниками из строки  типа '1,2,100, 300' таблицу


select 
1 as Tag
,NULL as Parent
,@pOrd as [pos!10!XML]
,NULL as [itemdetails!1!XML]
,NULL as [WORKITEM!2!POID!element]
,NULL as [WORKITEM!2!processName!element]
,NULL as [WORKITEM!2!SFProcessID!element]
,NULL as [WORKITEM!2!OURProcessID!element]
,NULL as [WORKITEM!2!Ordinal!element]
,NULL as [WORKITEM!2!ClientName!element]
,NULL as [WORKITEM!2!ContractNo!element]
,NULL as [WORKITEM!2!Status!element]
,NULL as [WORKITEM!2!StatusID!element]
,NULL as [WORKITEM!2!StatusImg!element]
,NULL as [WORKITEM!2!Date!element]
,NULL as [WORKITEM!2!DateReal!element]
--,NULL as [WORKITEM!2!DocType!element]
,NULL as [WORKITEM!2!Priority!element]
,NULL as [WORKITEM!2!DocType!element]
,NULL  as [WORKITEM!2!SoStartDate!element]
,NULL as [ADDRESSES!3!XML]
,NULL as [ADDRESSES!3!Address!element]
,NULL as [ADDRESSES!3!ID]

into #tmptbl

union

select 
2 as Tag
,1 as Parent
,1 as [pos!10!XML]
,NULL as [itemdetails!1!XML]
,NULL as [WORKITEM!2!POID!element]
,NULL as [WORKITEM!2!processName!element]
,NULL as [WORKITEM!2!SFProcessID!element]
,NULL as [WORKITEM!2!OURProcessID!element]
,NULL as [WORKITEM!2!Ordinal!element]
,NULL as [WORKITEM!2!ClientName!element]
,NULL as [WORKITEM!2!ContractNo!element]
,NULL as [WORKITEM!2!Status!element]
,NULL as [WORKITEM!2!StatusID!element]
,NULL as [WORKITEM!2!StatusImg!element]
,NULL as [WORKITEM!2!Date!element]
,NULL as [WORKITEM!2!DateReal!element]
--,NULL as [WORKITEM!2!DocType!element]
,NULL as [WORKITEM!2!Priority!element]
,NULL as [WORKITEM!2!DocType!element]
,NULL  as [WORKITEM!2!SoStartDate!element]
,NULL as [ADDRESSES!3!XML]
,NULL as [ADDRESSES!3!Address!element]
,NULL as [ADDRESSES!3!ID]

union
select 
2 as Tag,
1 as Parent
,1 as [pos!10!XML]
,NULL as [itemdetails!1!XML]
,PO.ID as [WORKITEM!2!POID!element]
,wfp.ProcessName as [WORKITEM!2!processName!element]
,wfp.ProcessID as [WORKITEM!2!SFProcessID!element]
,wfo.ProcessID as [WORKITEM!2!OURProcessID!element]
,subPO.ReadableID as [WORKITEM!2!Ordinal!element] 
,Client.ReadableID as [WORKITEM!2!ClientName!element]
,OPP.ContractNo as [WORKITEM!2!ContractNo!element]
,OS.ReadableID as [WORKITEM!2!Status!element]
,OS.ID as [WORKITEM!2!StatusID!element]
,OS.WebImageUrl as [WORKITEM!2!StatusImg!element]
,convert(varchar,wfl.TouchDate, 104)+' '+convert(varchar,wfl.TouchDate, 108) as [WORKITEM!2!Date!element]
,wfl.TouchDate as [WORKITEM!2!DateReal!element]
--,wfo.Type as [WORKITEM!2!DocType!element]
,wfo.HighImportance as [WORKITEM!2!Priority!element]
,CASE when PO.StartSODate is NULL THEN 'PO'  ELSE 'SO' END as [WORKITEM!2!DocType!element]
,PO.StartSODate  as [WORKITEM!2!SoStartDate!element]
,NULL as [ADDRESSES!3!XML]
,NULL as [ADDRESSES!3!Address!element]
,NULL as [ADDRESSES!3!ID]



from WFPRC wfp
inner join WFOB wfo
on wfo.ProcessID=wfp.ID

inner join PO
on wfo.ID=PO.ID

inner join subPO
on subPO.POID=PO.ID and subPO.Ordinal=1

inner join Opportunity as OPP
on OPP.ID=PO.OpportunityID

inner join Client
on OPP.ClientID=Client.ID

inner join OrderStatus as OS
on OS.ID=wfo.StatusID

----------------------------
left outer join WFLog as wfl
on @boxType!='searchbox' and (wfl.objectID=wfo.ID and (wfl.userID=@userID or (@userName is null and @boxType!='outbox')))
----------------------------


where wfo.ID in (select * from #temp)


union 

select 
3 as Tag,
2 as Parent
,1 as [pos!10!XML]
,NULL as [itemdetails!1!XML]
,subPO.POID as [WORKITEM!2!POID!element]
,NULL as [WORKITEM!2!processName!element]
,NULL as [WORKITEM!2!SFProcessID!element]
,NULL as [WORKITEM!2!OURProcessID!element]
,NULL as [WORKITEM!2!Ordinal!element]
,NULL as [WORKITEM!2!ClientName!element]
,NULL as [WORKITEM!2!ContractNo!element]
,NULL as [WORKITEM!2!Status!element]
,NULL as [WORKITEM!2!StatusID!element]
,NULL as [WORKITEM!2!StatusImg!element]
,NULL as [WORKITEM!2!Date!element]
,NULL as [WORKITEM!2!DateReal!element]
--,NULL as [WORKITEM!2!DocType!element]
,NULL as [WORKITEM!2!Priority!element]
,NULL  as [WORKITEM!2!SoStartDate!element]
,NULL as [WORKITEM!2!DocType!element]
,NULL as [ADDRESSES!3!XML]
,Address.ReadableID as [ADDRESSES!3!Address!element]
,Address.ID as [ADDRESSES!3!ID]



from subPO
left outer join Address
on Address.ID=subPO.AddressID
where subPO.POID in (select * from #temp)

ORDER BY  [WORKITEM!2!POID!element], [ADDRESSES!3!ID]



declare @sql varchar(8000)

if @sortBy is null
BEGIN
set @sql='select * from #tmptbl'
END
else
BEGIN
set @sql='
select T1.* from #tmptbl T1
left outer join (select [WORKITEM!2!POID!element], min(['+@sortBy+']) as MIN_FIELD2 from #tmptbl  group by [WORKITEM!2!POID!element]) T2
on T1.[WORKITEM!2!POID!element] = T2.[WORKITEM!2!POID!element]
order by   [pos!10!XML], T2.MIN_FIELD2 '+COALESCE(@sortOrder, '')+', T2.[WORKITEM!2!POID!element]'

END

if @output='XML'
set @sql=@sql+' for xml explicit'
exec(@sql)

drop table #temp
drop table #tmptbl
GO


21 фев 05, 11:17    [1334530]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
rko
Member

Откуда:
Сообщений: 24
вешается на простейшем вызове

@IDs='очень много всякиз идов'
@output='XML'
21 фев 05, 11:18    [1334533]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
jimmers
Member

Откуда: Санкт-Петербург - New York City
Сообщений: 5069
rko
вешается на простейшем вызове

@IDs='очень много всякиз идов'
@output='XML'


Судя по коду, такому вызову соответствует выполнение запроса:

select * from #tmptbl for xml explicit

Разумеется, он приводит к ошибке.
21 фев 05, 14:58    [1335607]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
rko
Member

Откуда:
Сообщений: 24
jimmers
rko
вешается на простейшем вызове

@IDs='очень много всякиз идов'
@output='XML'


Судя по коду, такому вызову соответствует выполнение запроса:

select * from #tmptbl for xml explicit

Разумеется, он приводит к ошибке.


Можно поподробней? Почему "разумеется"? Для меня это далеко не очевидно.
И почему после второго запуска, этой же процедуры с этими же параметрами оно срабатывает?
21 фев 05, 15:08    [1335677]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
rko
Member

Откуда:
Сообщений: 24
Ужель никтоне знает? :(
24 фев 05, 17:20    [1342822]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
Andrey Kruchkov
Member

Откуда: Москва
Сообщений: 3
Попробуй в места типа

NULL as [WORKITEM!2!OURProcessID!element]

в конце поставить "!" чтоб было типа

NULL as [WORKITEM!2!OURProcessID!element!]

расскажи помогло или нет
25 фев 05, 16:04    [1345703]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
Богдан Гоцкий
Member

Откуда: Львов
Сообщений: 504
а может быть
@IDs='очень много всякиз идов'
превышает
@IDs varchar(8000)='',
?
25 фев 05, 16:47    [1345890]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
PTO
Guest
После более детального изучения вроде есть понимание что проблема с тем, что ORDER BY не определен

Смотри, тут простой пример.

Каждый row в result set в UNION должен идти сразу за parent element. например:


SELECT 1 as Tag,

NULL as Parent,

Customers.CustomerID as [Customer!1!CustomerID!id],

NULL as [Order!2!OrderID!element],

NULL as [Order!2!CustomerID!idref],

NULL as [Order!2!OrderDate]

FROM Customers

WHERE Customers.CustomerID IN ('BOLID', 'GROSR')

UNION ALL

SELECT 2,

1,

NULL, -- Customers.CustomerID,

Orders.OrderID, Orders.CustomerID,

Orders.OrderDate

FROM Customers, Orders

WHERE Customers.CustomerID = Orders.CustomerID

AND Customers.CustomerID IN ('BOLID', 'GROSR')

ORDER BY [Customer!1!CustomerID!id], [Order!2!OrderID!element]

FOR XML EXPLICIT

на выходе скорее всего получишь 6833 error. Данное выражение валидно для FOR XML EXPLICIT за исключением одного: второй result set не определяет колонку по которой сортировать. Комментируем строку FOR XML EXPLICIT и становится видно проблему:


Tag Parent Customer!1!CustomerID!id Order!2!OrderID!element Order!2!CustomerID!idref Order!2!OrderDate


----------- ----------- ------------------------ ----------------------- ------------------------ -----------------------

2 1 NULL 10268 GROSR 1996-07-30 00:00:00.000
2 1 NULL 10326 BOLID 1996-10-10 00:00:00.000
2 1 NULL 10785 GROSR 1997-12-18 00:00:00.000
2 1 NULL 10801 BOLID 1997-12-29 00:00:00.000
2 1 NULL 10970 BOLID 1998-03-24 00:00:00.000
1 NULL BOLID NULL NULL NULL
1 NULL GROSR NULL NULL NULL

(7 row(s) affected)

Первые пять значений - child (Orders), но они не идут за row, который для них parent (Customer). Сие происходит из-за того, что ORDER BY
применен к колонке, которая определена как NULL для каждого row во втором result set. В BOL по "ORDER BY Clause" написано "Null values are treated as the lowest possible values" в применении к ORDER BY. Для того чтобы решить проблему нужно определить колонку по которой сортируем (Customers.CustomerID) в каждом resultset чтобы в конце концов результаты всех SELECTов смержились в нормальный порядок.

Tag Parent Customer!1!CustomerID!id Order!2!OrderID!element Order!2!CustomerID!idref Order!2!OrderDate

----------- ----------- ------------------------ ----------------------- ------------------------ -----------------------
1 NULL BOLID NULL NULL NULL
2 1 BOLID 10326 BOLID 1996-10-10 00:00:00.000
2 1 BOLID 10801 BOLID 1997-12-29 00:00:00.000
2 1 BOLID 10970 BOLID 1998-03-24 00:00:00.000
1 NULL GROSR NULL NULL NULL
2 1 GROSR 10268 GROSR 1996-07-30 00:00:00.000
2 1 GROSR 10785 GROSR 1997-12-18 00:00:00.000

(7 row(s) affected)

Поскольку результат ORDER BY не определен - оптимизатор может как угодно (случайно :)) выдавать результаты - иногда везет и parent идет перед child. Но как только child идет перед parent получаешь ошибку.

Если проблема решилась - сообщи плиз
25 фев 05, 17:33    [1346070]     Ответить | Цитировать Сообщить модератору
 Re: Глюк с For XML explicit и большой талицей  [new]
rko
Member

Откуда:
Сообщений: 24
При определенных условиях действительно формировался кривой ордер бай.

Все работает. Спасибо за советы!!
4 мар 05, 11:44    [1362805]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить