3

In Sql Server Management Studio, there is strange situation that made me lose a full day work. It is the problem of opening database objects multiple times. When I try to edit a table by right clicking from the object explorer, it is opening the edit screen for the first time, at second time it is re-opening the previously opened but not in a new edit screen. Despite that, if I try to select from that table by right clicking from the object explorer, it is opening the new query with the select statement multiple times while I retry this process. It is also doing exactly the same thing for the stored procedures and functions. I lost my changes in a stored procedure because of opening multiple times.In Visual Studio it is not possible to re-open a file in new editor multiple times by default. Are there any option to reconfigure sql server management studio to not open an object which is already opened in editor?

Note: I know this is my problem to remember which objects I have already opened, but I am asking for help, if there is an option that I am not aware.

Important EDIT:

I need to clarify that I can understand the SQL programming and I am not a newbie. I just don't used to SSMS and I don't know the all features of that tool. In my question's Note Section, I marked that it is my responsibility to keep records of scripts. I am only asking for if there is an option to prevent opening same script multiple times. If your answer is not more than suggesting to using third-party tool etc. please keep it for yourself.

  • While not an answer to your question, you should download and install the free version of SSMSBoost (ssmsboost.com). It periodically saves open SSMS windows (among a lot of other good things) so you can retrieve the contents if SSMS dies for some reason or you accidentally close out a window – Scott Hodgin Aug 17 '16 at 13:26
  • 1
    actually the problem depends on opening the file multiple times, because I executed all the opened files before I check what is it for some reasons. I need that for preventing myself to execute all opened files. – Alican Uzun Aug 17 '16 at 13:34
  • For Table design view SSMS will open just one copy, where you can edit table structure. But for all other object you just create script for the object and as you can create multiple copies of script it will open it in a new query window. Same thing will happen on Table if you just select Generate Script. So its a feature not a bug. – aasim.abdullah Aug 17 '16 at 14:23
  • 1
    @aasim.abdullah I know it is NOT a bug and I am just asking that can we prevent this? Is there any option about this? – Alican Uzun Aug 17 '16 at 14:46
  • Yes. That cam be prevented. You can create New Project and do edits within your project and deploy it to SQL Server only once. – Slava Murygin Aug 17 '16 at 15:24
3
+50

No, there no option for this, at least not as of SSMS 2014 (I haven't yet tried 2016). The problem is that when you right-click in Object Explorer and choose either Modify or Script Stored Procedure as   ▶, you are not "opening" anything: you are merely scripting it. When you are editing a table, that is a specialized UI that knows the table name that is being edited, and that name (and possibly object_id) can be checked against other instances of the "table editor". But when scripting out other objects as either "Modify", "ALTER To", or "DROP And CREATE To", then you aren't in a specialized editor, you are placed into a file editor. And being a file editor, it only guards against multiple instances of the same file path being edited concurrently. When you have a query tab open, you can even see the file name on the left side of the tab itself (the connection info is on the right). When scripting out an object, by default it opens a new query tab with a file name of SQLQuery{N}.sql where the {N} is an incrementing number. If you right-click on the tab itself and select Copy Full Path and paste that somewhere, you will see that the real file name is similar to C:\Users\{windows_login}\AppData\Local\Temp\~vsEFF9.sql. Whether you go by the proposed name shown in the tab or the temp file name, it is different for each new tab so there is no way for SSMS to know that you have scripted the same object already.

This does not work any differently than Visual Studio / SSDT (with one notable exception). The reason why Visual Studio goes to the same tab when clicking on a file in the project, or even opening the file from File Explorer (assuming that Visual Studio is the app associated with that file extension), is that it knows the full path name of the file being edited in each tab and the file being requested to be opened. It has nothing to do with source control. But when scripting a database object via the SQL Server Object Explorer (in Visual Studio, and using Script As ▶), it also creates a new tab each time you script the same object, since it has a unique file name each time.

The "notable exception" I mentioned regarding Visual Studio is when using the View Code option in SQL Server Object Explorer. This particular function does (somehow) generate the exact same file name when it scripts an object, so it will go to the same tab each time and hence you will not get multiple tabs! The file name that you see in the tab when using the View Code option is in the format of {schema_name}.{procedure_name}.sql. If you right-click on the tab (the tab opened by View Code) and select Copy Full Path, and then paste that somewhere, you will see something along the lines of: MSSQL::/{server_or_instance_name}/{database_name}/True/SqlProcedure/{schema_name}.{procedure_name}.sql. Since this format has the connection info embedded in it, that should protect against editing the same object across two different instances and losing track of which one was which :-). This approach needs to be mimicked over in SSMS in order for you to accomplish what you are trying to do.

I'm not sure if using Visual Studio is an option (technically it's not 3rd party ;-), but even if it is, the ideal solution is to never, ever edit Stored Procedures, Functions, etc by scripting them out of the database. Objects should (again, ideally) only be edited from a singular source file that is (ideally) linked to some sort of version / source control (Git, Subversion/SVN, TFS, etc). The database, where the object exists in its natural state in system tables, is not source control: it provides no history and provides no safe-guards against other people making changes at the same time. Even if you don't use version control (you really should :), you should at least have a common location for object scripts that everyone uses. And if you did at least that much, then you wouldn't be able to open up the same script into multiple tabs because the file editors (both SSMS and Visual Studio) only allow a file to be edited in a single tab.

  • 1
    As I don't have enough reputation to upvote answers, I can't upvote this, but you made a good explanation. thanks for your time. – Alican Uzun Aug 23 '16 at 6:26
  • 1
    @AlicanUzun You are quite welcome, and hopefully some of the info here helps improve your situation :-). – Solomon Rutzky Aug 23 '16 at 15:09
3

No, there is no such option.

SSMS doesn't open the database object and lock it down for you like Visual Studio w/ Data Tools add on does. Instead, it generates a sql script depending on what you want to do. It is more of a "Oh, you want to modify 'dbo.sp_awesomeness'? Not a problem! Here is the sql script containing alter procedure code you need at the time you asked of it." 2 minutes later, you ask again, and it pops open a new window going thru the same as above. It is always giving you the information at the time of the request to SSMS.

Visual Studio does the "Oh hey, you want the dbo.sp_awesomeness! Sweet. I see you checked it out of git hub, that's all good. Here is the copy." When you ask for it two minutes later, it just brings up the original screen. But then again, you are not creating sql scripts from SSDT. You are building a database package to deploy.

1

You did not get the point of SQL programming. You can have 1000 copies on multiple machines of a script to create/alter an object, but until one of those scripts is executed against a server, that object on that server will be unchanged.

Yes, you can have multiple tabs/windows with the same procedure, but for SQL Server all of those are different/competing sessions and whoever is last - wins.
On another hand: SSMS. You can have multiple copies of a script of the same object in different tabs, which is very handy - you can try different scenarios just by switching tabs. It is not job of SSMS to track your changes and keep records which script is a "Master Copy". That is YOUR responsibility.

However, if you are in a project development, you and your boss can't completely rely only on your discipline. Project manager have to choose which way to do a source control. Here is a not full list of things, which can be done:
- Implement Change Data Capture on the server;
- Restrict permissions on Dev/Test and allow "playing" only on Dev or personal machine;
- Use change tracking/deployment/source control tools, which will keep records of any changes.

And do not be surprised with SQL. All developers are experiencing that issue with source control. See it here: https://www.google.com/#q=SQL+source+code+control+tools

  • "Is there a switch multiple opening OFF button or not?" That's what I am looking for. – Alican Uzun Aug 22 '16 at 14:41
  • What do you mean? You can open as many tabs/windows as you want at put there ANY script you want and it will have ABSOLUTELY NO EFFECT on SQL SERVER until you execute a statement without an error. – Slava Murygin Aug 22 '16 at 17:58
1

I'll answer this simply - No, there is not a switch or setting in SSMS that will prevent you from doing what you are doing. You are using powerful tools and need to use them with the responsibility that entails. I'm not preaching, just voicing the expectation.

The comments and answers up to this point are with regards to your practices and behaviors. No tool in the world will help you if you are not disciplined with your usage.

  • It is not about my discipline of using. Sometimes, in urgent situations I need to close everything and pack up immediately to leave office. In these situations, I am executing procedures and sometimes same procedure is opened more than one. Therefore, I require such thing to save my work. If there is no such thing, there is simpler answers like: no there is no such thing. Also, there is no need to be rude or insult. – Alican Uzun Aug 22 '16 at 15:03
  • This isn't a personal dig or insult. This is an answer with a criticism of what you are doing. Simply, it's poor development practice. You don't seem to want to hear what we're telling you - what you are doing needs to change. I'm sorry if that hurts your feelings. – Steve Mangiameli Aug 22 '16 at 15:10
  • 1
    So I do something similar. However, instead of executing all the procedures at the end of the day, I save them in a folder called "review." And I review them all the next day and merge when necessary. – Anthony Genovese Aug 22 '16 at 15:10
  • @SteveMangiameli it doesn't hurt me. Like I said, I am not doing this everytime but urgent situations as I mentioned in my previous comment. I am sick of hearing sugggestions about using third-party. your answer could be acceptable but rude. sorry for that. – Alican Uzun Aug 22 '16 at 15:15
  • It's too bad you feel that way. You might consider the reason you are receiving the advice you are getting and that it is all similar - we might be right. And if you read it again with reading tone into it you will see it is meant to help you not hurt you. – Steve Mangiameli Aug 22 '16 at 16:11
0

I cannot comment yet, so posting my suggestion here. Try using third-party tools. Sadly to say that, but when it comes to professional SQL development all Microsoft tools are not very usable/convenient. We are using RedGate, Toad and some ApexSQL tools.

  • 2
    thanks for your suggestion but I am allright with the microsoft tools except this problem, and looking for any solution this problem. Using another tools is not the solution. I hope you understand me.thanks. – Alican Uzun Aug 22 '16 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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