8

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.

Improve this question
6
  • 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.
    – pleinolijf
    Commented Oct 8, 2012 at 13:44
  • 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 :(
    – LondonPhantom
    Commented Oct 9, 2012 at 9:57
  • @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.
    – Richard Marskell - Drackir
    Commented Oct 29, 2012 at 19:14
  • 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.
    – LondonPhantom
    Commented Oct 30, 2012 at 20:49
  • 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).
    – StarNamer
    Commented Jun 18, 2013 at 10:48

2 Answers 2

Reset to default
4

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.

Improve this answer
-1

The Query and View Designer options have incorrect characters for either the prefix or suffix. You cannot use the escape character as either a parameter prefix or suffix character.

To correct this error use prefix or suffix characters that do not contain the escape character or change the escape character.

Improve this answer
1
  • 2
    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)
    – LondonPhantom
    Commented Oct 9, 2012 at 9:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .