When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.
Examples of changes that require table re-creation:
Change a column to no longer allow NULL values
Adding columns in the before another column
Moving a column
I agree with Sander: this is a useful feature, but not something you want to abuse. If you don’t understand the magnitude of your change, it could cause production problems. And if you do understand the magnitude of your change, typically you’ll want to script it out for later.
One of the really basic things I think everyone should understand is how to get scripts from Management Studio (SSMS) and saving them. In fact, I’ve written that everyone should use this button and really not ever execute their GUI changes. Capture the script, save that, and automate things.
However, that’s not what this post is about. This post is about how you get a script to look at changes, or better understand how SSMS might implement your changes.
The ability to script out your changes has a number of benefits, one of which is that you’ll get to learn the code you need to write to perform an action, which could make all the difference in a production-down situation.
One of the biggest frustrations that people find with SQL DW is that you need (or rather, needed) to use SSDT to connect to it. You couldn’t use SSMS. And let’s face it – while the ‘recommended’ approach may be to use SSDT for all database development, most people I come across tend to use SSMS.
But now with the July 2016 update of SSMS, you can finally connect to SQL DW using SQL Server Management Studio. Hurrah!
…except that it’s perhaps not quite that easy. There’s a few gotchas to be conscious of, plus a couple of things that caused me frustrations perhaps more than I’d’ve liked.
Yes, it’s never quite that easy… Read the whole thing.
You’ll notice that when I go over the parentheses the one I’ve selected and it’s pair turn yellow, unless there isn’t a pair of course. You can also use Ctrl-] to flip between the open and close parenthesis in a pair. This can be particularly useful to make sure that you remembered a close parenthesis at the end of a subquery. In this case that last close parenthesis doesn’t have a match. Now finding out that you are missing an open parenthesis doesn’t mean you know where it’s supposed to go. But you can track the different pairs, making sure that each time you open a parenthesis you close it in the correct place. In this case it belonged right at the beginning.
FYI yellow isn’t the default (it’s a light gray). I find the default hard to see (I’m getting old) so I changed it to yellow in the options under fonts and colors.
Read the whole thing.
The easy way to solve this is to just log on directly to the remote server using Remote Desktop and use Management Studio on that session, but this is not really desirable for several reasons: not only will your Remote Desktop session consume quite a bit of memory and server resources, but you’ll also lose all the customizations and scripts that you may have handy in your local SSMS configuration.
Your mileage may vary with these solutions, and I don’t have the requisite skills to elaborate on the finer points with regards to when one solution will work over another, so just give them a try and see what works for you.
I prefer Daniel’s second option, using runas.exe.
By checking the error its obvious that there is something wrong with Width or Height of SSMS Query-Editor window.
So, I went to REGEDIT (In RUN, type regedit.exe) and after navigating here n there got the location where to update this property.
Navigate to folder: HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\13.0\
Here check the MainWindow property value (image below), it was showing: 0 451 109 -120 876 1
Change it to a positive value considering the width of your SSMS editor window, I replaced -120 with 1400
I had no idea that the main window size details were kept in the Registry.
Step 1: configure SSMS to only show file names on the tabs. Click Tools, Options, Text Editor, Editor Tab and Status Bar, and set all of the tab texts to false except file name. After all, not like all this stuff fits on the tab.
I definitely agree with step 1. You can try out steps 2 and 3 and see if they fit your workflow.
The March 2016 Refresh (13.0.13000.55 Changelog) updates SSMS to use the new Visual Studio 2015 shell. Part of that change means that undocked windows are now top-level windows.
Top level windows are windows without parents so the undocked window is not a child window of the main SSMS window (but it is part of the same process). And so it gets its own space in the task bar, and participates in alt+tab when you switch between windows.
Also these undocked windows can be a collection of query windows.
One reason I rarely used child windows is that I’d undock something, switch to a browser tab underneath, and then switch back and watch the undocked window pop over my browser tab. This sounds like a good improvement.
In the above example, there’s not much value-add by using the template replacement. It’s probably easier to just use @variables and highlight-replace.
The template replacement really shines when you have examples where you’d otherwise need to use dynamic SQL. If you have object names or database names that need replacement, this is a great answer. If you work in a multi-tenant hosting environment, and a client name is part of the DB name, this can make your life a lot easier.
Templates work great with auto-replace (a feature several third-party toolkits include). My favorite auto-replace that I’ve created is “die” which asks for an schema and procedure name and generates the DROP PROCEDURE script. Naturally, I also have diet (table), diev (view), and dief (function).
A couple weeks ago I mentioned that we are using Trello to help the community collaborate about what we want next in SQLPS before we submit Connect items to Microsoft.
That effort is going very well. It’s going so well in fact that when the topic of getting some new improvements into SSMS was brought up, the SQL Tools team suggested that a Trello board to collaborate and prioritize what people want improved in SSMS would be very helpful to them. Ultimately Microsoft needs Connect items filed but using Trello helps folks to debate and combine ideas.
The cynic in me says “this is what Connect is supposed to do” but Aaron and Chrissy LeMaire had a great deal of success working with the SQLPS team, so here’s hoping they get traction here as well.