Sql Server Management Studio: Change Prefix or Suffix characters

Question

I have an instance of SSMS 2008, for which the option to edit data in a table doesn't work. If I right-click on any table in the Object Explorer and select 'Edit top 200 rows' I get an error dialog 'Invalid prefix or suffix characters. (MS Visual Database Tools)'. The error seems to be associated specifically with SSMS, not with SQL Server (because this SSMS instance gives the same error no matter what database I connect to, but I've verified I can connect to some of the same databases using SSMS on other machines without the error). (However, our firewall prevents me using SSMS on other machines for some crucial tasks, so I do need to fix the problem).

Googling for the error suggests that I should change the prefix, suffix or escape character, but without any indication of how you can make that change in SSMS. I'd also note that I'm not aware of having done any customization on SSMS since installing it, so would be surprised at having to make such a change now.

Does anyone have any idea what the error message means or what I can do about it? Or how I can change the prefix/suffix/escape characters if that is really the problem.

Solution

I encountered the same error ('Invalid prefix or suffix characters. (MS Visual Database Tools)'), and it was because I was trying to use SSMS 2008R2 with an SQL-Server 2012 database.

I have both SQL Server 2008R2 and 2012 installed on my machine, and I was using the wrong SSMS.

Comments

[Admin]

Do you actually use prefixes/suffixes for your table names ? If so, edit them into your question, please. If not, then it's probably a different issue entirely, like you probably suspect.

[Admin]

We don't use prefixes or suffixes. They are plain names of the form schemaName.tableName with all characters being letters from the English alphabet. I guess that means we are homing on this being a completely different issue :(

[Admin]

@PhantomDrummer - FYI the one answer here is a direct copy of this ( technet.microsoft.com/en-us/library/ms163804.aspx ) Microsoft Tech Net post and, I suspect, doesn't apply to your particular situation (since I'm experiencing the same thing and it's not very helpful). Are you a) connecting to a SQL 2012 database instance and b) running on Windows XP? If so, and you've figured this out, please post here and let us (me :) ) know.

[Admin]

Hi Richard, you're right that the (so far, only) answer isn't relevant, which is why I've not marked it as an accepted answer. I'm afraid I never did solve the problem. As I recall I just worked around it by just writing SQL queries to make the data changes I wanted. Looks to me like the problem is a bug in SSMS, but it's not clear precisely what circumstances are required to activate the bug. Since not that many people are reporting it, I'd surmise that it only occurs in some very particular situation.

[Admin]

FYI, I'm getting this using SMS 2008R2 connecting to a 2008 database. I haven't yet found any way to "change the escape character" for the Query and View Designer. Did anyone ever figure out a solution (other than doing the edits by writing queries).

[Admin]

Correction: I'd had my copy op SSMS open for several days. The dev server has been updated to SS 2012 and the version displayed on the connection was wrong!

[LondonPhantom]

We don't use prefix or suffix characters so there's nothing we can remove in that regard. I'm in principle happy to try changing the escape character on the offchance that's the problem, but don't know how to do that? (Have to admit I didn't even know that SSMS had a customizable escape character)