Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 Oct 8 '12 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 :( –  PhantomDrummer Oct 9 '12 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 Oct 29 '12 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. –  PhantomDrummer Oct 30 '12 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 Jun 18 '13 at 10:48
show 1 more comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
1  
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) –  PhantomDrummer Oct 9 '12 at 9:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.