| Author |
Topic |
|
RawLiquid
Starting Member
4 Posts |
 Posted - 2008-05-02 : 16:13:18
|
| I'm at my wits end with sql server here, perhaps someone can explain to me why this is happening and hopefully a way around it or to fix it...I recently had this happen while working with a table and exporting the results of a query to an xml file, one field in the table is a text field, before being output to xml I must parse out some of the text usually near the beginning, during which the text is converted to varchar(max), I handled that just fine but when I examine the output file, The tail end of the field has several bytes that gets duplicated...Example...empowering them to squeeze every drop of performance available. #x0D;.#x00;ble. #x0D;.#x00;</NOTES>The first #x00; is the terminating character(which ultimatly i need to strip out to make the file comply by the normal xml rules but thats a different issue alltogether)here is another example that has even more data duplicated...encompassing the production, post production, broadcasting and computer video markets.#x0D;.#x00;ing and computer video markets.#x0D;.#x00;#x00;</NOTES>If anyone can shed some light on why this is happening or at least how I can prevent it, your help would be greatly appreciated...A side note on this is that when I run the select statement in the sql manager, the results do not include the duplicated bytes...Nathan V Asdourian |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-02 : 16:20:28
|
quote:
A side note on this is that when I run the select statement in the sql manager, the results do not include the duplicated bytes...
So then the issue is not how the data is stored but rather how you are retrieving it. Please post the code.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
RawLiquid
Starting Member
4 Posts |
Posted - 2008-05-02 : 16:41:59
|
| Here is the code for the UDF I'm using to remove the unwanted text from the stringALTER FUNCTION [dbo].[NotesFix] ( @Notes varchar(max))RETURNS varchar(max)AS BEGIN DECLARE @INDEX INT DECLARE @Result varchar(max), @Name varchar(25) SELECT @INDEX = 1select @result=@notes WHILE @INDEX !=0 BEGIN SELECT @INDEX = patINDEX('%***%***%',@Result) if @index != 0 begin SELECT @Name = substring(@result,@index,charindex(' *** ',@result,@index)+6-@index) select @result=replace(@result,@name,'') end END RETURN rtrim(@result)END a sample of the source text is '*** JA *** April 18, 2008 at 4:09pm Per Jackie- "thanks for the f/u"*** VICTOR *** April 17, 2008 at 4:25pm FMI scheduled an appointment with Jackie Komaschka, Director of Finance, for Wednesday, June 11th @ 10:00am. Very receptive, but will be swamped with clean-up and year-end and specifically wanted to meet the day after June 10th. Jackie with have Cathy Paulsen, her Controller, join her for the meeting. Cathy reports directly to Jackie.Company is doing >$20mm in annual revenues. Currently working with B of A. Have a "substantial" LOC. Looking to utilize some cash management items. Heavy merchant card users and are "always interested in that". From the web:About MesquiteThe modern history of the City of Mesquite began in the mid 1800’s as much of the West was being settled. The Old Spanish Trail and the Morman Road passed through Mesquite along the Virgin River. The earliest European-American settlements in the region, such as St George and Las Vegas, sprang up as Morman outposts at water sources along the trail. After two different attempts to settle Mesquite failed, the third time was a charm. In 1894 six young families established themselves permanently and rebuilt the irrigation canal that had been destroyed by flash floods in the past. For a time raisins were the cash crop in the community until the late 20th century at which time dairies dominated the landscape. After the completion of Interstate Highway 15 in the late 1970’s the momentum began to gather. Master-planned communities began developing, golf courses sprang up along with the Eureka Casino Hotel. 'The desired result being a string with any text between the ***'s removed leaving simply the date stamp before each entry...Nathan V Asdourian |
|
|
|
RawLiquid
Starting Member
4 Posts |
Posted - 2008-05-02 : 16:49:26
|
| After many attempts today at this I managed to come up with a statement that does return the desired result...------------start paste-----------------declare @xml nvarchar(max) set @xml = ( SELECT lastdate+lasttime LastUpdate, COMPANY, CONTACT, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, PHONE1, EXT1, PHONE2, EXT2, UDREFMU, UDREFAPPT, ULASTSTEP UNEXTSTEP, UOFFICE, UOFFICER, UDAPPTSCHD, UDAPPTTIME, UCURRPROV UCRRTBK1, UCRRTBK2, UCURROFFIC, UPROFITABL, UNUMLOC, UEMPGIVEN, UYRSPROVID, UYRSBUS, UWCAMT, ULOCRENEW, ULTRSCRDT, ULIQUID, UTERMDEBT, KEY1 USICDESC, KEY2 USIC, TITLE, DEPARTMENT UDBA, USTRUCTURE, ULEASEOWN, UBUSOCCUP, SOURCE, ULOANAMT, ULOANPUR, UTIMING, ULOCAVG, UCURRLOAN, USALES2005, USALES2006, UFISCALEND, UCAPMKTS, UCARDSVCS, UCASHMGMT, UASSETMGMT, UIMPORTSVC, USALES2007, UAUDITOR, UYRAUDITOR, UCPANAME, UYRCPA, UAMTANNUAL, UAUDIT, UREVIEW, UCOMPILATN,uinsurance UDATAFROM, UTYPESYST, left(goldmine_sales_and_marketing.dbo.notesfix(notes),charindex(char(0),goldmine_sales_and_marketing.dbo.notesfix(notes))) NOTES, WebSite.WebURL, EMail.email, C1.ACCOUNTNO, Ucnbno FROM goldmine_sales_and_marketing.dbo.CONTACT1 C1 JOIN goldmine_sales_and_marketing.dbo.CONTACT2 C2 ON c1.ACCOUNTNO=c2.ACCOUNTNO LEFT OUTER JOIN goldmine_sales_and_marketing.dbo.EMail EMail ON c1.ACCOUNTNO=EMail.ACCOUNTNO LEFT OUTER JOIN goldmine_sales_and_marketing.dbo.WebSite WebSite ON c1.ACCOUNTNO=WebSite.ACCOUNTNOWHERE u_key5='cnb' and (udrefmu is not null or udrefappt is not null )for xml raw,elements,root('Records'))select @xml=replace(@xml,'#x00;','')exec writetofile @xml,'c:\crystal\cnb.xml'-----------end paste---------------however I'd still like to know why I got the results I did as I've previously had to work around the issue in other projects due to the same problem occuring, namely when writing a query that retrieved a list of queries contained in a table, then executed each one in a stored procedure which returned the record count returned by each of the queries...A number of them would return errors relating to syntax which I tracked down to any query that had been modified where the changes resulted in a shorter tsql query, whatever length the query was shortened by is exactly how many characters were duplicated...Nathan V Asdourian |
|
|
|
RawLiquid
Starting Member
4 Posts |
Posted - 2008-05-02 : 17:15:53
|
| and my final review of that resulted in removing the last select line and adding a -1 to the charindex result...declare @xml nvarchar(max) set @xml = ( SELECT lastdate+lasttime LastUpdate, COMPANY, CONTACT, ADDRESS1, ADDRESS2, CITY, STATE, ZIP, PHONE1, EXT1, PHONE2, EXT2, UDREFMU, UDREFAPPT, ULASTSTEP UNEXTSTEP, UOFFICE, UOFFICER, UDAPPTSCHD, UDAPPTTIME, UCURRPROV UCRRTBK1, UCRRTBK2, UCURROFFIC, UPROFITABL, UNUMLOC, UEMPGIVEN, UYRSPROVID, UYRSBUS, UWCAMT, ULOCRENEW, ULTRSCRDT, ULIQUID, UTERMDEBT, KEY1 USICDESC, KEY2 USIC, TITLE, DEPARTMENT UDBA, USTRUCTURE, ULEASEOWN, UBUSOCCUP, SOURCE, ULOANAMT, ULOANPUR, UTIMING, ULOCAVG, UCURRLOAN, USALES2005, USALES2006, UFISCALEND, UCAPMKTS, UCARDSVCS, UCASHMGMT, UASSETMGMT, UIMPORTSVC, USALES2007, UAUDITOR, UYRAUDITOR, UCPANAME, UYRCPA, UAMTANNUAL, UAUDIT, UREVIEW, UCOMPILATN,uinsurance UDATAFROM, UTYPESYST, left(goldmine_sales_and_marketing.dbo.notesfix(notes),charindex(char(0),goldmine_sales_and_marketing.dbo.notesfix(notes))-1) NOTES, WebSite.WebURL, EMail.email, C1.ACCOUNTNO, Ucnbno FROM goldmine_sales_and_marketing.dbo.CONTACT1 C1 JOIN goldmine_sales_and_marketing.dbo.CONTACT2 C2 ON c1.ACCOUNTNO=c2.ACCOUNTNO LEFT OUTER JOIN goldmine_sales_and_marketing.dbo.EMail EMail ON c1.ACCOUNTNO=EMail.ACCOUNTNO LEFT OUTER JOIN goldmine_sales_and_marketing.dbo.WebSite WebSite ON c1.ACCOUNTNO=WebSite.ACCOUNTNOWHERE u_key5='cnb' and (udrefmu is not null or udrefappt is not null )for xml raw,elements,root('Records'))exec writetofile @xml,'c:\crystal\cnb.xml'Nathan V Asdourian |
|
|
|
|
|
|
irritating behaviour of text columns