How Do I EDIT ALL SPARSE Columns in SSMS 2012?

  • I am using SSMS and want to use the EDIT CONTEXT MENU to update some Rows.

    Only some SPARSE fields display at any one time and the issue is also in 2014.

    There is no control over what fields decide to display.

    Any Ideas?

    In the attached image it can be seen that the Text 1-7 and Int 1-10 fields are not displayed

    There are about 70 fields in total

  • Can you provide a screen shot or some sample data that displays the problem because I have no idea what you mean.

    TSQL doesn't have an EDIT command, so your description is confusing.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Requested Updates made to the original ? with image added.. thankyou for your time

  • I understand now, thanks, but sorry I don't have an answer.

    You can use the edit feature for sparse columns by manually adding the column names to the select statement, but that would be a real pain to have to do that every time (mind you, I wouldn't imagine that there are many occasions when editing the data in a table like that would be something I would recommend).

    Good luck finding a solution 🙂 I suspect you would need a custom Addin to "fix" this.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • In SSMS table content editor, press CTRL+3 and change the sql statement to select * from table_name, then press CTRL+R.

    😎

  • Yes, it is hard coded for version 10+ of the database engine:

    // Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.OpenTableHelperClass

    public static DataTable GetColumnNames(Server server, Urn urn)

    {

    Request request = new Request();

    if ([highlight="#ffff11"]server.Version.Major >= 10[/highlight] && !DatabaseEngineTypeExtension.IsMatrix(server.DatabaseEngineType))

    {

    request.Urn = string.Format("{0}/{1}", urn.ToString(),

    "Column[[highlight="#ffff11"]@IsSparse=0[/highlight]]");

    }

    else

    {

    request.Urn = string.Format("{0}/{1}", urn.ToString(), "Column");

    }

    request.Fields = new string[]

    {

    "Name"

    };

    request.OrderByList = new OrderBy[]

    {

    new OrderBy

    {

    Dir = OrderBy.Direction.Asc,

    Field = "ID"

    }

    };

    Enumerator enumerator = new Enumerator();

    EnumResult enumResult = enumerator.Process(server.ConnectionContext, request);

    DataTable result;

    if (enumResult.Type == ResultType.DataTable)

    {

    result = enumResult;

    }

    else

    {

    result = enumResult.Tables[0];

    }

    return result;

    }

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

    Viewing 6 posts - 1 through 5 (of 5 total)

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