Problem with SQL Query

Question

Problem with SQL Query

xxxxxxxx

Old Hand

Points: 369
More actions
January 13, 2014 at 8:50 am

#396098

Hi,
I ran this query multiple times last week in my SQL Server 2005 database and it worked fine. Today, I go to run the exact same query and it only returns the first row (code = 30001). I've tried other values of the BETWEEN statement and still the same behavior is evident. It's almost like the BETWEEN or the following AND is not working. This is driving me nuts. Would someone be kind enough to help me please? I restarted the database and rebooted the server too!!
SELECT(
SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_FIRSTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT
FROM HPSITE.DEMOGRAPHICS
INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE
INNER JOIN HPSITE.NOTES_MASTER ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_MASTER.IMRENOTE_CODE
INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.NOTES_MASTER.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE
WHERE IMREDEMEC_CODE BETWEEN '30001' AND '32000'
AND DEM_LASTNAME <>'Demonstration'
AND DEM_LASTNAME <>'Test'
AND DEM_LASTNAME <>'Train'
AND DEM_LASTNAME <>'Erroneous'
FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL
) AS COL_XML;

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Skuldouggery (1/13/2014)
Hi,
I ran this query multiple times last week in my SQL Server 2005 database and it worked fine. Today, I go to run the exact same query and it only returns the first row (code = 30001). I've tried other values of the BETWEEN statement and still the same behavior is evident. It's almost like the BETWEEN or the following AND is not working. This is driving me nuts. Would someone be kind enough to help me please? I restarted the database and rebooted the server too!!
SELECT(
SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_FIRSTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT
FROM HPSITE.DEMOGRAPHICS
INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE
INNER JOIN HPSITE.NOTES_MASTER ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_MASTER.IMRENOTE_CODE
INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.NOTES_MASTER.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE
WHERE IMREDEMEC_CODE BETWEEN '30001' AND '32000'
AND DEM_LASTNAME <>'Demonstration'
AND DEM_LASTNAME <>'Test'
AND DEM_LASTNAME <>'Train'
AND DEM_LASTNAME <>'Erroneous'
FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL
) AS COL_XML;

Without table definitions and some data we can't even take a shot in the dark.

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

If you haven't changed the query, then the data might have changed. Verify that as you might be in trouble.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

xxxxxxxx Old Hand Points: 369 More actions · Answer 3

Sorry, does this help? Also, I followed the link that the other gentleman had for putting some data together.

[HPSITE].[DEMOGRAPHICS]

[IMREDEM_CODE] [int]

[DEM_EXTERNALID] [varchar]

[DEM_LASTNAME] [varchar]

[DEM_FIRSTNAME] [varchar]

[DEM_DATEOFBIRTH] [datetime]

[HPSITE].[CHARTATTACHMENT]

[IMREDEMEC_CODE] [int]

[IMRENOTE_CODE] [int]

[CHAT_DATE] [datetime]

[HPSITE].[NOTES_MASTER]

[IMRENOTE_CODE] [int]

[HPSITE].[NOTES_TEXTDATA]

[IMRENOTE_CODE] [int]

[NOTE_TEXT] [text]

For the data:

SELECT 'SELECT '

+ DEM_EXTERNALID(ID,'''')+','

+ IMREDEM_CODE(CODE,'''')+','

+ DEM_LASTNAME(LN,'''')+','

+ DEM_LASTNAME(FN,'''')+','

+ DEM_DATEOFBIRTH(DOB,'''')+','

+ CHAT_DATE(DATE,'''')+','

+ NOTE_TEXT(TEXT,'''')+','

+ ' UNION ALL'

FROM HPSITE.DEMOGRAPHICS

INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE

INNER JOIN HPSITE.NOTES_MASTER ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_MASTER.IMRENOTE_CODE

INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.NOTES_MASTER.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE

WHERE IMREDEMEC_CODE BETWEEN '1' AND '5000'

AND DEM_LASTNAME <>'Demonstration'

AND DEM_LASTNAME <>'Test'

AND DEM_LASTNAME <>'Train'

AND DEM_LASTNAME <>'Erroneous'

Data:

SELECT '856','1688','LN1','A','Nov 29 1948 12:00AM','Sep 27 2004 12:23PM','LARGE TEXT FILE'

SELECT '11239','3036','LN2','L','Sep 18 1937 12:00AM','Sep 28 2004 9:58AM','LARGE TEXT FILE'

SELECT '255','1088','LN3','B','Jul 19 1955 12:00AM','Sep 29 2004 6:17PM','LARGE TEXT FILE'

SELECT '1602','816','LN4','C','Jul 9 1923 12:00AM','Sep 30 2004 10:29AM','LARGE TEXT FILE'

SELECT '804','1637','LN5','D','Feb 6 1933 12:00AM','Oct 7 2004 10:55AM','LARGE TEXT FILE'

SELECT '138','130','LN6','P','Jun 5 1943 12:00AM','Oct 12 2004 10:44AM','LARGE TEXT FILE'

SELECT '12505','4249','LN7','H','Jul 7 1925 12:00AM','Oct 13 2004 9:55AM','LARGE TEXT FILE'

SELECT '12731','4467','LN8','J','Mar 16 1933 12:00AM','Oct 13 2004 10:40AM','LARGE TEXT FILE'

SELECT '11929','3693','LN9','E','Sep 18 1922 12:00AM','Oct 18 2004 2:09PM','LARGE TEXT FILE'

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Skuldouggery (1/13/2014)
Sorry, does this help? Also, I followed the link that the other gentleman had for putting some data together.

None of this really helps a whole lot. We can turn this into actual create table scripts easily enough but we still don't have data for these tables. The idea here is to allow us to create these tables on our machine so we can see if the code actually works.

Also, I would HIGHLY recommend you change your text columns to varchar(max). The text datatype has been deprecated and is a complete PITA to work with. It is possible that you don't even need max because the varchar datatype can hold as much as 8,000 characters before being forced to use max.

_______________________________________________________________

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/

xxxxxxxx Old Hand Points: 369 More actions · Answer 5

I'm a little confused. Is that data at the bottom of what I posted not appropriate? Also, I do not know how to convert the Text datatype to what you specify.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Skuldouggery (1/13/2014)
I'm a little confused. Is that data at the bottom of what I posted not appropriate? Also, I do not know how to convert the Text datatype to what you specify.

Well you have 4 tables in your query. You provided data but it doesn't match any of the tables you posted.

If you want to alter your datatype you just need to change the datatype with an alter command.

alter table [HPSITE].[NOTES_TEXTDATA]

alter column [IMRENOTE_CODE] varchar(max);

_______________________________________________________________

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/

xxxxxxxx Old Hand Points: 369 More actions · Answer 7

This what I have. Also, I am a little leary to alter the data type...remember the whole 'newbie' thing.

HPSITE.DEMOGRAPHICS

EID,IMREDEM_CODE,LN,FN,DOB

SELECT '2','4','LN4','FN4','Mar 18 1983 12:00AM'

SELECT '5','7','LN7','FN7','Feb 6 1996 12:00AM'

SELECT '8','10','LN8','FN8','Feb 15 1957 12:00AM'

SELECT '20','22','LN9','FN9','Jun 9 1971 12:00AM'

SELECT '25','27','LN10','FN10','Jul 22 1996 12:00AM'

SELECT '28','30','LN11','FN11','Apr 13 1993 12:00AM'

HPSITE.CHARTATTACHMENT

IMREDEMEC_CODE,IMRENOTE_CODE,CHAT_DATE

SELECT '2','146669','Feb 3 2005 1:34PM'

SELECT '2','146720','Feb 3 2005 2:00PM'

SELECT '2','146955','Feb 3 2005 3:47PM'

SELECT '2','147394','Feb 4 2005 8:37AM'

SELECT '2','591108','Mar 8 2006 2:09PM'

SELECT '2','2589211','Mar 24 2011 12:37PM'

SELECT '5','863598','Oct 9 2006 12:47PM'

SELECT '5','2322272','Jun 17 2010 2:49PM'

SELECT '5','2781700','Oct 24 2011 4:56PM'

SELECT '7','104338','Dec 23 2004 11:28AM'

SELECT '8','667930','May 9 2006 2:13PM'

SELECT '8','879185','Oct 20 2006 8:41AM'

SELECT '8','2203832','Feb 17 2010 2:12PM'

SELECT '8','2322216','Jun 17 2010 2:29PM'

SELECT '20','1490261','Feb 19 2008 8:03PM'

SELECT '20','1783630','Jan 5 2009 11:03AM'

SELECT '20','2380446','Aug 18 2010 11:05AM'

SELECT '20','2490167','Dec 6 2010 1:21PM'

SELECT '25','120797','Jan 11 2005 7:19AM'

HPSITE.NOTES_TEXTDATA

IMRENOTE_CODE, NOTE_TEXT

SELECT '146669',Text1

SELECT '146720',Text2

SELECT '146955',Text3

SELECT '147394',Text4

SELECT '591108',Text5

SELECT ''2589211',Text6

SELECT '863598',Text7

SELECT '2322272',Text8

SELECT '2781700',Text9

SELECT '104338',Text10

SELECT '667930',Text11

SELECT '879185',Text12

SELECT '2203832',Text13

SELECT '2322216',Text14

SELECT '1490261',Text15

SELECT '1783630',Text16

SELECT '2380446',Text17

SELECT '2490167',Text18

SELECT '120797',Text19

This is the code again, I realized that I had an extra Inner Join.

SELECT(

SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_LASTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT

FROM HPSITE.DEMOGRAPHICS

INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE

INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE

WHERE IMREDEMEC_CODE BETWEEN '1' AND '10'

AND DEM_LASTNAME <>'Demonstration'

AND DEM_LASTNAME <>'Test'

AND DEM_LASTNAME <>'Train'

AND DEM_LASTNAME <>'Erroneous'

FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL

) AS COL_XML;

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

I will make the assumption that you didn't actually read the article that was suggested. If you had you would have realized that we want is create table statements and insert statements to those tables.

Something like this.

create table #DEMOGRAPHICS

(

IMREDEM_CODE int,

DEM_EXTERNALID varchar(10),

DEM_LASTNAME varchar(10),

DEM_FIRSTNAME varchar(10),

DEM_DATEOFBIRTH datetime

)

create table #CHARTATTACHMENT

(

IMREDEMEC_CODE int,

IMRENOTE_CODE int,

CHAT_DATE datetime

)

create table #NOTES_TEXTDATA

(

IMRENOTE_CODE int,

NOTE_TEXT text

)

insert #DEMOGRAPHICS

SELECT '2','4','LN4','FN4','Mar 18 1983 12:00AM' union all

SELECT '5','7','LN7','FN7','Feb 6 1996 12:00AM' union all

SELECT '8','10','LN8','FN8','Feb 15 1957 12:00AM' union all

SELECT '20','22','LN9','FN9','Jun 9 1971 12:00AM' union all

SELECT '25','27','LN10','FN10','Jul 22 1996 12:00AM' union all

SELECT '28','30','LN11','FN11','Apr 13 1993 12:00AM'

insert #CHARTATTACHMENT

SELECT '2','146669','Feb 3 2005 1:34PM' union all

SELECT '2','146720','Feb 3 2005 2:00PM' union all

SELECT '2','146955','Feb 3 2005 3:47PM' union all

SELECT '2','147394','Feb 4 2005 8:37AM' union all

SELECT '2','591108','Mar 8 2006 2:09PM' union all

SELECT '2','2589211','Mar 24 2011 12:37PM' union all

SELECT '5','863598','Oct 9 2006 12:47PM' union all

SELECT '5','2322272','Jun 17 2010 2:49PM' union all

SELECT '5','2781700','Oct 24 2011 4:56PM' union all

SELECT '7','104338','Dec 23 2004 11:28AM' union all

SELECT '8','667930','May 9 2006 2:13PM' union all

SELECT '8','879185','Oct 20 2006 8:41AM' union all

SELECT '8','2203832','Feb 17 2010 2:12PM' union all

SELECT '8','2322216','Jun 17 2010 2:29PM' union all

SELECT '20','1490261','Feb 19 2008 8:03PM' union all

SELECT '20','1783630','Jan 5 2009 11:03AM' union all

SELECT '20','2380446','Aug 18 2010 11:05AM' union all

SELECT '20','2490167','Dec 6 2010 1:21PM' union all

SELECT '25','120797','Jan 11 2005 7:19AM'

insert #NOTES_TEXTDATA

SELECT '146669','Text1' union all

SELECT '146720','Text2' union all

SELECT '146955','Text3' union all

SELECT '147394','Text4' union all

SELECT '591108','Text5' union all

SELECT '2589211','Text6' union all

SELECT '863598','Text7' union all

SELECT '2322272','Text8' union all

SELECT '2781700','Text9' union all

SELECT '104338','Text10' union all

SELECT '667930','Text11' union all

SELECT '879185','Text12' union all

SELECT '2203832','Text13' union all

SELECT '2322216','Text14' union all

SELECT '1490261','Text15' union all

SELECT '1783630','Text16' union all

SELECT '2380446','Text17' union all

SELECT '2490167','Text18' union all

SELECT '120797','Text19'

Now we both have the same temp tables that we are working with so we have a very solid common ground.

Let's take your query. You are including a predicate for IMREDEMEC_CODE. Since this is an int datatype you should not use strings in your query.

Given the nature of what you are returning you need to remove the XML portion so you can see the rows being returned.

--SELECT(

SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_LASTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT

FROM #DEMOGRAPHICS

INNER JOIN #CHARTATTACHMENT ON #DEMOGRAPHICS.IMREDEM_CODE = #CHARTATTACHMENT.IMREDEMEC_CODE

INNER JOIN #NOTES_TEXTDATA ON #CHARTATTACHMENT.IMRENOTE_CODE = #NOTES_TEXTDATA.IMRENOTE_CODE

WHERE IMREDEMEC_CODE BETWEEN 1 AND 10

AND DEM_LASTNAME <>'Demonstration'

AND DEM_LASTNAME <>'Test'

AND DEM_LASTNAME <>'Train'

AND DEM_LASTNAME <>'Erroneous'

--FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL

--) AS COL_XML;

That all seems to be working just fine to me. Can you explain what is not working correctly?

Of course let's not forget to include the cleanup to drop our temp tables.

drop table #DEMOGRAPHICS

drop table #CHARTATTACHMENT

drop table #NOTES_TEXTDATA

_______________________________________________________________

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/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

Sean Lange (1/13/2014)
Of course let's not forget to include the cleanup to drop our temp tables.
drop table #DEMOGRAPHICS
drop table #CHARTATTACHMENT
drop table #NOTES_TEXTDATA

This is awesome advice. Is that in the referenced article? If not, it should be.

Do you have any idea how many times I've typed in those nefarious DROPs? Yuck!

Of course, I do usually throw in a "GO" directive just before them to be sure they get dropped when I code up some kind of syntax or compile error in my solution attempt.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

xxxxxxxx Old Hand Points: 369 More actions · Answer 10

The script without the xml portion runs fine, as you noticed. However, with the xml portion, the script returns only the first record from the BETWEEN statement, and even then only a partial result. It does not actually even close the xml fully. My frustration is that this was working perfectly fine last week, but then when I ran it again yesterday it started with the aforementioned behavior.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

dwain.c (1/13/2014)
Sean Lange (1/13/2014)
Of course let's not forget to include the cleanup to drop our temp tables.
drop table #DEMOGRAPHICS
drop table #CHARTATTACHMENT
drop table #NOTES_TEXTDATA
This is awesome advice. Is that in the referenced article? If not, it should be.
Do you have any idea how many times I've typed in those nefarious DROPs? Yuck!
Of course, I do usually throw in a "GO" directive just before them to be sure they get dropped when I code up some kind of syntax or compile error in my solution attempt.

It isn't mentioned but I agree. I usually just end up commenting out the create and inserts since I can do that with fewer keystrokes than typing in the drop statements. Of course those temp tables will be dropped implicitly when the connection closes. I know you know that but I mention it for the sake of completeness.

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Skuldouggery (1/14/2014)
The script without the xml portion runs fine, as you noticed. However, with the xml portion, the script returns only the first record from the BETWEEN statement, and even then only a partial result. It does not actually even close the xml fully. My frustration is that this was working perfectly fine last week, but then when I ran it again yesterday it started with the aforementioned behavior.

Are you looking at this in SSMS? If so I suspect it is because your results are getting truncated. SSMS limits the length of text returned.

You can check this setting by going to Tools -> Options -> Query Results -> SQL Server -> Results to Grid.

You can change the value for "Non XML Data" to a max of 65535.

With that setting here is the result returned from the query you posted.

<Results xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Row id="1"><DEM_EXTERNALID>10</DEM_EXTERNALID><IMREDEM_CODE>8</IMREDEM_CODE><DEM_LASTNAME>LN8LN8</DEM_LASTNAME><DEM_DATEOFBIRTH>1957-02-15T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>8</IMREDEMEC_CODE><CHAT_DATE>2006-05-09T14:13:00</CHAT_DATE><NOTE_TEXT>Text11</NOTE_TEXT></Row><Row id="2"><DEM_EXTERNALID>10</DEM_EXTERNALID><IMREDEM_CODE>8</IMREDEM_CODE><DEM_LASTNAME>LN8LN8</DEM_LASTNAME><DEM_DATEOFBIRTH>1957-02-15T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>8</IMREDEMEC_CODE><CHAT_DATE>2006-10-20T08:41:00</CHAT_DATE><NOTE_TEXT>Text12</NOTE_TEXT></Row><Row id="3"><DEM_EXTERNALID>10</DEM_EXTERNALID><IMREDEM_CODE>8</IMREDEM_CODE><DEM_LASTNAME>LN8LN8</DEM_LASTNAME><DEM_DATEOFBIRTH>1957-02-15T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>8</IMREDEMEC_CODE><CHAT_DATE>2010-02-17T14:12:00</CHAT_DATE><NOTE_TEXT>Text13</NOTE_TEXT></Row><Row id="4"><DEM_EXTERNALID>10</DEM_EXTERNALID><IMREDEM_CODE>8</IMREDEM_CODE><DEM_LASTNAME>LN8LN8</DEM_LASTNAME><DEM_DATEOFBIRTH>1957-02-15T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>8</IMREDEMEC_CODE><CHAT_DATE>2010-06-17T14:29:00</CHAT_DATE><NOTE_TEXT>Text14</NOTE_TEXT></Row><Row id="5"><DEM_EXTERNALID>4</DEM_EXTERNALID><IMREDEM_CODE>2</IMREDEM_CODE><DEM_LASTNAME>LN4LN4</DEM_LASTNAME><DEM_DATEOFBIRTH>1983-03-18T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>2</IMREDEMEC_CODE><CHAT_DATE>2005-02-03T13:34:00</CHAT_DATE><NOTE_TEXT>Text1</NOTE_TEXT></Row><Row id="6"><DEM_EXTERNALID>4</DEM_EXTERNALID><IMREDEM_CODE>2</IMREDEM_CODE><DEM_LASTNAME>LN4LN4</DEM_LASTNAME><DEM_DATEOFBIRTH>1983-03-18T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>2</IMREDEMEC_CODE><CHAT_DATE>2005-02-03T14:00:00</CHAT_DATE><NOTE_TEXT>Text2</NOTE_TEXT></Row><Row id="7"><DEM_EXTERNALID>4</DEM_EXTERNALID><IMREDEM_CODE>2</IMREDEM_CODE><DEM_LASTNAME>LN4LN4</DEM_LASTNAME><DEM_DATEOFBIRTH>1983-03-18T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>2</IMREDEMEC_CODE><CHAT_DATE>2005-02-03T15:47:00</CHAT_DATE><NOTE_TEXT>Text3</NOTE_TEXT></Row><Row id="8"><DEM_EXTERNALID>4</DEM_EXTERNALID><IMREDEM_CODE>2</IMREDEM_CODE><DEM_LASTNAME>LN4LN4</DEM_LASTNAME><DEM_DATEOFBIRTH>1983-03-18T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>2</IMREDEMEC_CODE><CHAT_DATE>2005-02-04T08:37:00</CHAT_DATE><NOTE_TEXT>Text4</NOTE_TEXT></Row><Row id="9"><DEM_EXTERNALID>4</DEM_EXTERNALID><IMREDEM_CODE>2</IMREDEM_CODE><DEM_LASTNAME>LN4LN4</DEM_LASTNAME><DEM_DATEOFBIRTH>1983-03-18T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>2</IMREDEMEC_CODE><CHAT_DATE>2006-03-08T14:09:00</CHAT_DATE><NOTE_TEXT>Text5</NOTE_TEXT></Row><Row id="10"><DEM_EXTERNALID>4</DEM_EXTERNALID><IMREDEM_CODE>2</IMREDEM_CODE><DEM_LASTNAME>LN4LN4</DEM_LASTNAME><DEM_DATEOFBIRTH>1983-03-18T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>2</IMREDEMEC_CODE><CHAT_DATE>2011-03-24T12:37:00</CHAT_DATE><NOTE_TEXT>Text6</NOTE_TEXT></Row><Row id="11"><DEM_EXTERNALID>7</DEM_EXTERNALID><IMREDEM_CODE>5</IMREDEM_CODE><DEM_LASTNAME>LN7LN7</DEM_LASTNAME><DEM_DATEOFBIRTH>1996-02-06T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>5</IMREDEMEC_CODE><CHAT_DATE>2006-10-09T12:47:00</CHAT_DATE><NOTE_TEXT>Text7</NOTE_TEXT></Row><Row id="12"><DEM_EXTERNALID>7</DEM_EXTERNALID><IMREDEM_CODE>5</IMREDEM_CODE><DEM_LASTNAME>LN7LN7</DEM_LASTNAME><DEM_DATEOFBIRTH>1996-02-06T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>5</IMREDEMEC_CODE><CHAT_DATE>2010-06-17T14:49:00</CHAT_DATE><NOTE_TEXT>Text8</NOTE_TEXT></Row><Row id="13"><DEM_EXTERNALID>7</DEM_EXTERNALID><IMREDEM_CODE>5</IMREDEM_CODE><DEM_LASTNAME>LN7LN7</DEM_LASTNAME><DEM_DATEOFBIRTH>1996-02-06T00:00:00</DEM_DATEOFBIRTH><IMREDEMEC_CODE>5</IMREDEMEC_CODE><CHAT_DATE>2011-10-24T16:56:00</CHAT_DATE><NOTE_TEXT>Text9</NOTE_TEXT></Row></Results>

That captures ALL of the rows and the XML is 100% clean.

_______________________________________________________________

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/

xxxxxxxx Old Hand Points: 369 More actions · Answer 13

Yep, I am using SSMS. The setting is at max too. My problem is that this was working last week and returning the output to the xml_col without issue. I could then save the result as an xml file. This week, it does not work. The result is being truncated as you stated. Also, the text in the results are LOB files. They are essentially rtf documents.

I just cannot understand how it could work and now it does not. And, I am 100% sure that the data has not changed.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Skuldouggery (1/14/2014)
Yep, I am using SSMS. The setting is at max too. My problem is that this was working last week and returning the output to the xml_col without issue. I could then save the result as an xml file. This week, it does not work. The result is being truncated as you stated. Also, the text in the results are LOB files. They are essentially rtf documents.
I just cannot understand how it could work and now it does not. And, I am 100% sure that the data has not changed.

If you are running this in SSMS and saving the results as a file then it has to be that your output was truncated. There is nothing wrong with query, the problem is how you are executing it. You should find another way to export this data like Powershell or maybe even SSIS.

_______________________________________________________________

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/