June 1, 2012 at 6:05 am
Hi Friends,
Today i met a situation that my tempdb is getting increased to extreme when i execute a T-SQL statement which is created to concatenate rows using for XML PATH()....
well, i don't know what is the problem with this query...?
help me friends....
Select
P.JurisID ,'INC' ModCode,P.IncidentIDRef AS ModIDRef,'C' RecType ,ROW_NUMBER() over(partition by P.IncidentIDRef order by Main.PSFDOC)-1 as SeqNo,P.DIDCU AS CreatedPFIDRef ,P.EntDttm AS CreatedDttm,
'</P>' + REPLACE(REPLACE(Left(Main.PSFDOC,Len(Main.PSFDOC)-1),'</P>','</P>'),'</P>','</P>') As Comment
From(Select distinct FD2.FDDOC#,
(Select FD1.FDNARR + '</P>' AS [text()]
From dbo.PSFDOC FD1
Where FD1.FDDOC# = FD2.FDDOC#
ORDER BY CAST(FD1.FDLINE AS INT)
For XML PATH ('')) [PSFDOC]
From dbo.PSFDOC FD2) [Main]
JOIN dbo.PSDOCI P ON P.DIDOC# = Main.FDDOC#
join GloucMidPolice.dbo.SrcIncident s on s.IncidentIDRef = P.IncidentIDRef and s.JurisID = P.JurisID
--JOIN dbo.SrcCodeMap sc ON RTRIM(LTRIM(Sc.PnxCodeValue)) = RTRIM(LTRIM(P.JurisID))
WHERE DIFILE = 'PLINCD' and (not DIDESC in ('cad system narrative', 'dispatch narrative')
and
not DIDESC like 'Converted Cad Comment%')
Thanks,
Charmer
June 1, 2012 at 8:35 am
Your issues could be slightly different than mine.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.PSFDOC'.
Seriously you know better by now. Maybe somebody else has a new one but my crystal ball is still out for repairs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2012 at 10:59 am
Sean Lange (6/1/2012)
Your issues could be slightly different than mine.Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.PSFDOC'.
Seriously you know better by now. Maybe somebody else has a new one but my crystal ball is still out for repairs.
Oops...sorry sean...I thought i posted the enough details....surely i will post everything...i am sorry for that...
Thanks,
Charmer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply