Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Problem with SQL Query Expand / Collapse
Author
Message
Posted Tuesday, January 14, 2014 5:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:17 AM
Points: 6, Visits: 29
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.
Post #1530625
Posted Tuesday, January 14, 2014 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 13,325, Visits: 12,810
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530678
Posted Tuesday, January 14, 2014 12:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 13,325, Visits: 12,810
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530832
Posted Tuesday, January 14, 2014 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:17 AM
Points: 6, Visits: 29
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.
Post #1530840
Posted Tuesday, January 14, 2014 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 13,325, Visits: 12,810
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530854
Posted Tuesday, January 14, 2014 5:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 3,428, Visits: 5,381
Sean Lange (1/14/2014)

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.


True enough. I just like being tidy.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1530912
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse