none
How to use a running instance of ssms (SQL Server Management Studio) from my app?

    Question

  • Hello guys,

    I'm actually a SQL DBA. However, I have been developing an application that helps us to make our daily jobs easier. In some part of the application, I have a DataGridView which contains a server list (server name, IP address etc.). I added a Popup Menu so that we will be able to connect to the SQL Server instances by right clicking on the server name. Here's the simple code piece I use to do this:

    Process.Start("ssms.exe", "/S " + server_name + " /E /nosplash")However, this code starts a new instance of the SQL Server Management Studio. Do you have any idea if I can use an already running instance of the SSMS? You know, it's not practical every time it opens a new instance of the application...


    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Tuesday, July 10, 2012 2:10 PM

Answers

  • I don't believe this would be possible short of using some bad SendKeys type of hack to try to make it work.

    ssms doesn't have an API that would allow you to send information to an already running instance (for example like Outlook does), so you only have the option of sending in params upon launching the instance of ssms.


    Matt Kleinwaks - MSMVP MSDN Forums Moderator - www.zerosandtheone.com

    • Marked as answer by Ekrem Önsoy Thursday, July 12, 2012 8:37 AM
    Tuesday, July 10, 2012 5:08 PM
    Moderator

All replies

  • I don't believe this would be possible short of using some bad SendKeys type of hack to try to make it work.

    ssms doesn't have an API that would allow you to send information to an already running instance (for example like Outlook does), so you only have the option of sending in params upon launching the instance of ssms.


    Matt Kleinwaks - MSMVP MSDN Forums Moderator - www.zerosandtheone.com

    • Marked as answer by Ekrem Önsoy Thursday, July 12, 2012 8:37 AM
    Tuesday, July 10, 2012 5:08 PM
    Moderator
  • AFAIK SSMO does not have a programmable automation interface, so that would limit control to either a command line argument or sending keystrokes to the application. In addition, it would not be possible to use a command line argument with a current instance. So, you can try sending keystrokes to the app, or have the app perform some clean-up by terminating the application instances. You can use the Process Class (e.g. GetProcessesByName, Kill) to manage existing instances of this app.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 10, 2012 5:16 PM
  • Thanks for the replies guys.

    Do you know how can I focus (bring it front) an open application? For instance, a running instance of SSMS? Should I use that Process Class again for this purpose? I probably can manage to find how to send key strokes from Google. Please just show me which way to go, I'm not asking for a full snippet. I will try to send some key strokes if this is my only chance (and it seems it is), I'd like to give it a try and see how it goes.

    Paul, as I said I'm a DBA and in our daily routine, there are lots of SQL Server instances we have to manage and some of them (especially the critical ones) must be there in the Object Explorer to act rapidly and practically when needed, so killing the old one is not something I want to do. However thanks for the idea.


    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Tuesday, July 10, 2012 5:56 PM
  • The easiest method, and first I would try, is to call GetProcessByName and then AppActivate. Then use SendKeys. If that doesn't work then you may need to resort to Windows API function calls.

    Dim AppProcess As Process = Process.GetProcessesByName("WindowName").First
    AppActivate(AppProcess.Id)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, July 10, 2012 6:23 PM
  • I think the biggest headache with SendKeys is your app won't know what state SSMS is in. If some modal dialog window is up you could send keys that dont do anything, or worse cause some really adverse action to happen. Imaging you happen to have a drop table script up or delete database window, and sendkeys ends up pulling the trigger on you.


    Matt Kleinwaks - MSMVP MSDN Forums Moderator - www.zerosandtheone.com

    Tuesday, July 10, 2012 6:31 PM
    Moderator
  • It seems there's no such thing "First" under GetProcessesByName. I used

    AppActivate("Microsoft SQL Server Management Studio")

    instead (I suspect if this replaces your original code?) and it seems this code activates the window, however it does not bring it to front, so key strokes do not work. Now I need to find out how to bring the window to front. Thanks.


    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Wednesday, July 11, 2012 5:59 AM
  • Yes Matt, you are absolutely correct. Even though, after the Enterprise Manager of SQL Server 2000, new SSMS do not have much modal dialogs, sending some key strokes at the wrong time may result badly. I will seriously consider this point because our system contains very critical database servers.

    Actually the keys I'd like to send are ALT, DOWN, ENTER and writing the name of the server; 4 package. I'll think about if this can result any wrong/critical action. The ALT stroke makes me feel kinda safe =)

    Thanks for the heads-up.


    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Wednesday, July 11, 2012 6:05 AM
  • Ekrem,

    Why do you want this, you can simply give your SQL command using the method Command ExecuteNonQuery and you are disturbing nothing.

    In the way you describe it, it would mean that endless views in management studio would be opened (and not closed) something I would not like.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx


    Success
    Cor

    Wednesday, July 11, 2012 6:51 AM
  • Cor, for some actions, I use ExecuteNonQuery or others of course (for instance to save a user info or server info); but for some other actions, I need to work with SQL Server Management Studio or I need to perform RDP to a server. RDP is easy, because I need to open new instances of "mstsc.exe" every time I want to connect to a server and it accepts the Server parameter. 

    We have a lot of customers and they have a lot of SQL Servers. So to make the management easier, I prepared this application. It contains the details about all the servers. Yes, I can right click on a record and copy the server name and open the SSMS and paste it to the Server Name edit box and connect; but just to make things easier, I want to right click on a SQL Server instance name and connect to the instance using a running instance of SQL Server. So, this is why I'm looking for a solution just to make things more comfortable and practical. Thanks for your interesting.


    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Wednesday, July 11, 2012 7:00 AM
  • Hi Ekrem,

    Thank you for posting on this forum.

    I have read your above post, and I got you that you want to do some management things in SSMS. If I am correct, would you try to do this things with sql command? For example, creating a task: http://msdn.microsoft.com/en-us/library/ms345156.aspx 

    How to use Sql profile: http://msdn.microsoft.com/en-us/library/ff650699.aspx 

    And so on. For more, you can ask it on sql server forum: http://social.msdn.microsoft.com/Forums/en/category/sqlserver 

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, July 11, 2012 9:53 AM
    Moderator
  • I only see a pretty bad hack: :-)

    Enumerate all windows to find the instances of SSMS. Find a sub window with the title "object explorer" (or localized name). One of it's grand children is a treeview, verify the first root node of the treeview. For example the text is "(local)\sqlexpress (SQL Server....".

    So, if you find the server name ("(local)" in this case), you can assume there's already an instance running. :-)

    No, I have no code handy for this purpose. ;)


    Armin

    Wednesday, July 11, 2012 12:21 PM
  • Hi Armin,

    Well, let me make myself clear, I'm not looking for a running SQL Server instance, I'm looking for a running SSMS instance, the management tool itself. Probably it was a little bit confusing when I said "instance" =) So, it's not going to be too complicated.

    For now, the only missing step is to bring the activated application front. I'm looking for this solution, I got nothing for now. I took a considerable way thanks to you guys till yesterday.


    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Wednesday, July 11, 2012 12:39 PM
  • Thank you Mike for your assistance. I believe your recommendations are a little bit far away from what I'm looking for.

    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Wednesday, July 11, 2012 12:43 PM
  • Well, let me make myself clear, I'm not looking for a running SQL Server instance, I'm looking for a running SSMS instance, 

    Yes, that's clear. I said "find the instances of SSMS", I did not say instances of SQL server.

    Armin


    Wednesday, July 11, 2012 12:44 PM
  • OK, thank you Armin =)

    Ekrem Önsoy - MCDBA, MCITP:DBA+DBD, MCSD.Net, MCSE, ITILv3 | http://ekremonsoy.blogspot.com

    Wednesday, July 11, 2012 1:25 PM
  • I suspect that you are targeting an older version of the .NET Framework (2.0 perhaps). The code works fine for me in VS 2010 when targeting version 4.0.

    In any event, try Windows API functions (e.g. FindWindow, SetForegroundWindow). See the below link (next to last post):

    http://www.vbforums.com/showthread.php?t=671719


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, July 11, 2012 1:58 PM