none
Data truncation on dataset.load RRS feed

  • Question

  • Hey folks,

    I've got a Sql Stored proc that I'm calling and putting the return values into a DataReader like so:

     

    SqlDataReader rdr = null;
    
    SqlCommand sqlCmd = new SqlCommand("StoredProcName", DataConnection);
    
    sqlCmd.CommandType = CommandType.StoredProcedure;
    
    rdr = sqlCmd.ExecuteReader();
    
    DataSet ds = new DataSet();
    
    ds.Load(rdr, LoadOption.OverwriteChanges, "sometable"); 
    
    

     

    Everything is great *except* that the stored proc has a very large text column that's coming back -- something like 2K of text in one column. The data reader reads this, but when the "ds.Load()" loads the data, it truncates the text in that column (to the first 255 characters? I'd guess?)

    Is there a way around this?

    I tried replacing the data reader with a data adapter and calling the adapter.Fill(), but i get the same behavior. I tried using a DataTable.load() as well, with the same results.

    I can write a custom loader that will loop through the values and populate the dataset, but is there a way to get this (or something similar) to work?

    thanks in advance,

    --kevin

    [Edit]

    Forgot to mention: this is VS2008/.Net 3.5 and SQL server 2008.

     

    Thursday, July 21, 2011 3:01 PM

Answers

  • ah... got it. Compiler thing.

    I had done a clean & rebuild with no improvement. But when I modified the code to manually walk through the reader and assign the data to the dataset, it all "magially" started to work.

    thanks for all the ideas.

    Sorry for the fire drill.

    --kevin

    • Marked as answer by kjhngisd Thursday, July 21, 2011 5:31 PM
    Thursday, July 21, 2011 4:33 PM

All replies

  • Is it possible to change the column type from nText/Text to nvarchar(max) in your SQLServer Table?  I could be mistaken, but I believe that the nText/Text types are being or have been depreciated.

    Edit: if you know that it will only have 2000 characters, then you should set the field size to this --- using the MAX specifier might be unncessary and could potentially have performance ramifications.  As in any other time you are designing your database, you should go for optimization, which would entail setting the field legnth to the size you believe it SHOULD BE set to (rather than just guessing and setting things to MAX).


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, July 21, 2011 3:16 PM
  • Why dont you try achieving the same via a dataadapter

    something like

    string queryString =
      "SELECT CustomerID, CompanyName FROM dbo.Customers";
    SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);

    DataSet customers = new DataSet();
    adapter.Fill(customers, "Customers");


    Abhinav
    Thursday, July 21, 2011 3:18 PM
  • As mentioned, a dataadapter gives the same results.
    Thursday, July 21, 2011 3:29 PM
  • the values aren't coming from a SQLServer Table, but a Stored Procedure. The Stored Proc isn't pulling them from a table directly, but doing a concat on several table values with some text manipulations.

    That said, I'm not really interested in changing the database. The DataReader is pulling everything correctly. It's *specifically* the "load()" that is truncating the data.

    Thursday, July 21, 2011 3:30 PM
  • why dont you move the concatination code to .net, return just the data from SQL and concatenate in ur code
    Abhinav
    Thursday, July 21, 2011 3:40 PM
  • the values aren't coming from a SQLServer Table, but a Stored Procedure. The Stored Proc isn't pulling them from a table directly, but doing a concat on several table values with some text manipulations.

    That said, I'm not really interested in changing the database. The DataReader is pulling everything correctly. It's *specifically* the "load()" that is truncating the data.


    So then in the SProc where it is setting up an nText field, change this to varchar or nVarchar.  It is still returning a set-based result with specific field types/columns, no?  It may very well be congregating data from various tables, but you still have the ability to return those fields.  How else is your Dataset/Datatable going to know what field types to match up to?

    Besides, I don't see the value in you using an outdated and depreciated field type (text). 

    Your answer may reside with the Sproc itself.  You can post it up here or on the Transact-SQL forum too. 

     


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, July 21, 2011 3:40 PM
  • Well, there's multiple stored procs that I can call, so it would require multiple changes to legacy systems.

    But I took a look and the one I picked is actually pushing the value into a temp table as a varchar already. So I'm not sure that's it.

    I'll check the datatype of the datareader, which seems to be wokring.

     

    Thursday, July 21, 2011 4:06 PM
  • Well, there's multiple stored procs that I can call, so it would require multiple changes to legacy systems.

    But I took a look and the one I picked is actually pushing the value into a temp table as a varchar already. So I'm not sure that's it.

    I'll check the datatype of the datareader, which seems to be wokring.

     


    What is the size of that varchar filed in the #temp table?

     

    THAT may very well be where it's getting truncated!  I'd check the results in a new Query Window in SQL Server Mgmt Studio and see if you are getting truncated values in that field.


    James Crandall ~ http://javitechnologies.com Spatial Database Solutions
    Thursday, July 21, 2011 4:08 PM
  • ah... got it. Compiler thing.

    I had done a clean & rebuild with no improvement. But when I modified the code to manually walk through the reader and assign the data to the dataset, it all "magially" started to work.

    thanks for all the ideas.

    Sorry for the fire drill.

    --kevin

    • Marked as answer by kjhngisd Thursday, July 21, 2011 5:31 PM
    Thursday, July 21, 2011 4:33 PM