How Do I EDIT ALL SPARSE Columns in SSMS 2012?

Question

Post reply

How Do I EDIT ALL SPARSE Columns in SSMS 2012?

aaron 69254

SSC Enthusiast

Points: 107
More actions
April 23, 2014 at 4:55 pm

#306904

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
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]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1

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);

aaron 69254 SSC Enthusiast Points: 107 More actions · Answer 2

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

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

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);

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

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

😎

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

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);