|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Query analyzer and truncated result rowsin an effort to move some data from one bug tracking system to another. Simplified down to the basics, my query looks like this: SELECT '<MyXMLTag>'+CAST(TextField AS VARCHAR(8000)) + '</MyXMLTag>' WHERE DATALENGTH(TextField) < 8000 What I'm seeing is that some records simply don't process completely. That is to say, the text field data is truncated and the close tag doesn't appear for that record. I have configured query analyzer to return rows up to the max (8192 bytes) size. I'm finding that the failure occurs on records that are in the 3000-4000 byte range although some larger records succeed and some records in the same size range also succeed. Any thoughts on how to make this go? Thanks, Ted -- Did it happen simply or simply happen? I need to correct one piece of information. I incorrectly stated that larger
records have processed successfully. That is, in fact not the case. 3584 is the magic number. Anything larger than that demonstrates the truncation. Thinking that was suspisciously close to the default 4096 packet size, I tried bumping that up with no success. -- Show quoteDid it happen simply or simply happen? "tedp" wrote: > I'm running a query through query analyzer, outputting the results to a file > in an effort to move some data from one bug tracking system to another. > Simplified down to the basics, my query looks like this: > > SELECT '<MyXMLTag>'+CAST(TextField AS VARCHAR(8000)) + '</MyXMLTag>' > WHERE DATALENGTH(TextField) < 8000 > > What I'm seeing is that some records simply don't process completely. That > is to say, the text field data is truncated and the close tag doesn't appear > for that record. I have configured query analyzer to return rows up to the > max (8192 bytes) size. I'm finding that the failure occurs on records that > are in the 3000-4000 byte range although some larger records succeed and some > records in the same size range also succeed. > > Any thoughts on how to make this go? > > Thanks, > Ted > -- > Did it happen simply or simply happen? Any chance the data is from an nText instead of Text column? But in any
case why are you doing it that way? Have you looked at using DTS or even BCP to export the data? It should save you a lot of work. -- Show quoteAndrew J. Kelly SQL MVP "tedp" <t***@discussions.microsoft.com> wrote in message news:F787B70B-C841-4E7D-87B8-9436A6396E9F@microsoft.com... > I'm running a query through query analyzer, outputting the results to a > file > in an effort to move some data from one bug tracking system to another. > Simplified down to the basics, my query looks like this: > > SELECT '<MyXMLTag>'+CAST(TextField AS VARCHAR(8000)) + '</MyXMLTag>' > WHERE DATALENGTH(TextField) < 8000 > > What I'm seeing is that some records simply don't process completely. > That > is to say, the text field data is truncated and the close tag doesn't > appear > for that record. I have configured query analyzer to return rows up to > the > max (8192 bytes) size. I'm finding that the failure occurs on records > that > are in the 3000-4000 byte range although some larger records succeed and > some > records in the same size range also succeed. > > Any thoughts on how to make this go? > > Thanks, > Ted > -- > Did it happen simply or simply happen? Thanks for the reply Andrew!
Taking those one at a time... 1. I've just reconfirmed that it is a Text rather than NText column (good idea on that though). 2. I'm doing it this way for three reasons... - I don't want the entire table, just a subset. I could, of course do my subsetting into a temp table so good idea there. - I need to do a little entity encoding of things like ampersands, quotes and left/right brackets. A nested REPLACE() is a handy way to do it. - The XML I need to generate is trivial and quite easy to do out of a simple SQL statement. Or at least it would be if this were working out. In looking a little further at the problem, the number in question appears to be suspisciously close to the network packet size. I've tried bumping it on the client without impact so I'm wondering know if I need to round up one of the sysadmins to try to bump it on the server. -- Show quoteDid it happen simply or simply happen? "Andrew J. Kelly" wrote: > Any chance the data is from an nText instead of Text column? But in any > case why are you doing it that way? Have you looked at using DTS or even > BCP to export the data? It should save you a lot of work. > > -- > Andrew J. Kelly SQL MVP > > > "tedp" <t***@discussions.microsoft.com> wrote in message > news:F787B70B-C841-4E7D-87B8-9436A6396E9F@microsoft.com... > > I'm running a query through query analyzer, outputting the results to a > > file > > in an effort to move some data from one bug tracking system to another. > > Simplified down to the basics, my query looks like this: > > > > SELECT '<MyXMLTag>'+CAST(TextField AS VARCHAR(8000)) + '</MyXMLTag>' > > WHERE DATALENGTH(TextField) < 8000 > > > > What I'm seeing is that some records simply don't process completely. > > That > > is to say, the text field data is truncated and the close tag doesn't > > appear > > for that record. I have configured query analyzer to return rows up to > > the > > max (8192 bytes) size. I'm finding that the failure occurs on records > > that > > are in the 3000-4000 byte range although some larger records succeed and > > some > > records in the same size range also succeed. > > > > Any thoughts on how to make this go? > > > > Thanks, > > Ted > > -- > > Did it happen simply or simply happen? > > > Ahhh...now I see the issue. There is an implicit conversion to nvarchar
going on as part of the query result. If I select this into another table, I get an nvarchar column on the destination side. Thanks Andrew for pointing me in the right direction with the NText business. I'm not sure I can resolve this but at least I've got something to work with. Ted -- Show quoteDid it happen simply or simply happen? "Andrew J. Kelly" wrote: > Any chance the data is from an nText instead of Text column? But in any > case why are you doing it that way? Have you looked at using DTS or even > BCP to export the data? It should save you a lot of work. > > -- > Andrew J. Kelly SQL MVP > > > "tedp" <t***@discussions.microsoft.com> wrote in message > news:F787B70B-C841-4E7D-87B8-9436A6396E9F@microsoft.com... > > I'm running a query through query analyzer, outputting the results to a > > file > > in an effort to move some data from one bug tracking system to another. > > Simplified down to the basics, my query looks like this: > > > > SELECT '<MyXMLTag>'+CAST(TextField AS VARCHAR(8000)) + '</MyXMLTag>' > > WHERE DATALENGTH(TextField) < 8000 > > > > What I'm seeing is that some records simply don't process completely. > > That > > is to say, the text field data is truncated and the close tag doesn't > > appear > > for that record. I have configured query analyzer to return rows up to > > the > > max (8192 bytes) size. I'm finding that the failure occurs on records > > that > > are in the 3000-4000 byte range although some larger records succeed and > > some > > records in the same size range also succeed. > > > > Any thoughts on how to make this go? > > > > Thanks, > > Ted > > -- > > Did it happen simply or simply happen? > > > Hopefully that will solve your immediate issue but you can do data
manipulation like that with DTS or even BCP. Both allow you to specify a query just as you are now. DTS even allows more transformations relatively easy. -- Show quoteAndrew J. Kelly SQL MVP "tedp" <t***@discussions.microsoft.com> wrote in message news:98FE8AFA-A11D-458E-806F-CC9BABD2A33B@microsoft.com... > Ahhh...now I see the issue. There is an implicit conversion to nvarchar > going on as part of the query result. If I select this into another > table, I > get an nvarchar column on the destination side. > > Thanks Andrew for pointing me in the right direction with the NText > business. I'm not sure I can resolve this but at least I've got something > to > work with. > > Ted > > > -- > Did it happen simply or simply happen? > > > "Andrew J. Kelly" wrote: > >> Any chance the data is from an nText instead of Text column? But in any >> case why are you doing it that way? Have you looked at using DTS or even >> BCP to export the data? It should save you a lot of work. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "tedp" <t***@discussions.microsoft.com> wrote in message >> news:F787B70B-C841-4E7D-87B8-9436A6396E9F@microsoft.com... >> > I'm running a query through query analyzer, outputting the results to a >> > file >> > in an effort to move some data from one bug tracking system to another. >> > Simplified down to the basics, my query looks like this: >> > >> > SELECT '<MyXMLTag>'+CAST(TextField AS VARCHAR(8000)) + '</MyXMLTag>' >> > WHERE DATALENGTH(TextField) < 8000 >> > >> > What I'm seeing is that some records simply don't process completely. >> > That >> > is to say, the text field data is truncated and the close tag doesn't >> > appear >> > for that record. I have configured query analyzer to return rows up to >> > the >> > max (8192 bytes) size. I'm finding that the failure occurs on records >> > that >> > are in the 3000-4000 byte range although some larger records succeed >> > and >> > some >> > records in the same size range also succeed. >> > >> > Any thoughts on how to make this go? >> > >> > Thanks, >> > Ted >> > -- >> > Did it happen simply or simply happen? >> >> >>  |
|||||||||||||||||||||||
Other interesting topics