Fix argument handling in SQL Server 2005 Mgmt Studio custom Keyboard Accelerator shortcuts?

Question

I've long been a fan of Stored Procedure Keyboard Accelerators, as described in this article. When we moved from SQL 2000 to 2005, though, and from Query Analyzer to Management Studio, the handling of the arguments changed. In QA, comma-separated arguments were automatically read as two separate arguments. In SSMS -- at least for me -- it's being read as one argument, with commas in it. Similarly, if I pass in a single argument with single quotes in it, I get a syntax error, unless I escape the quotes (' -> ''). In the article linked above, the author implies that this should not be the case for SSMS, but even with her exact example, comma-separated arguments are still being interpreted as one argument on every SSMS installation I've tried it on (3 of them), running against every SQL Server installation I've tried (4 of them).

E.g., typing the following into SSMS,

Person,4

then selecting it and running the shortcut, I get the error message "Invalid object name 'Person,4'.

Does anybody have any idea how to fix this? Does anybody even use these shortcuts? I've Googled this problem several times over the past two years, and have had no luck.

Edit: May be an issue with a specific build of SSMS. I have a follow-up post below.

Answer 1

I had never tried this until I read your question and then read the article you referenced, so take this with a grain of salt.

That said, I am able to get the process to work on my computer using SSMS, and I am also able to duplicate the error you described.

To get this to work as expected I created the sproc in the master database, assigned the keyboard shortcut and restarted SSMS. I then typed the databasename.schema_name.table_name in single quotes followed by a comma and then an integer value (the sproc I tested was the GetRows sample in the article). I was still connected to the master database.

This worked without incident.

To get the same error that you mentioned, I removed either the reference to the schema name or database name and received the same error you did.

Perhaps you need to add the database name and schema name before the table name?

Answer 2

Tim's suggestion didn't solve my problem on my development PC, but it did convince me to try again from a different PC. When using a different PC's SSMS to log into the development PC's database and trying exactly what Tim describes, I'm having the same behavior Tim describes.

I was also able to re-replicate the argument parsing issue on the other PCs I had tried in the past. I'm hoping Tim can let me know what's the version and build number on his SSMS installation, because my current theory is that the problem is just from the specific build that my coworkers and I have on our dev PCs -- the version string is "Microsoft SQL Server Management Studio 9.00.1399.00". All of our installs of that version took place well over a year ago, so I don't know that I can trace back what disk it's from.

The one that is NOT having the problem is actually our development server, which has "Microsoft SQL Server Management Studio 9.00.3042.00" installed. I don't know if this might be something I can make go away by patching or something, but it currently looks like 1399 reads the entire selection as a single argument, while 3042 does some pre-parsing. I've also recently found that when I pass in a string that contains "--" (comment token) in 3042, everything after the "--" is ignored, while in 1399, it's all included in the first argument.

Answer 3

I am using SSMS version 9.00.3042.00 as well, which probably explains why it is working on my machine.

Answer 4

Agree with Tim. I have just upgraded to SQL Server 05 sp2 and I confirm that this bug gets fixed.