How can I start the SQL minishell without going through SSMS

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 7 months ago.

  • Author
    Posts
  • #6356

    by willsteele at 2012-08-17 08:39:35

    I am a long time user of SSMS/TSQL, but, am looking to get into PowerShell for SQL Server. I know that I can start an instance of the PowerShell SQL minishell by right clicking a database in SSMS and clicking Start PowerShell. To try and set up some automated tasks (read that to mean scheduled tasks) I want to see if the same shell can be accessed via a script, and, if so, how. My first concern is how to pass credentials. I have located where the sqlps.exe is and I need to be sure and log on with an account that is in SQL. I presume the credentials are automatically passed from SQL Server via token to the minishell when you start from the context menu. In my case, I have to figure out how to ensure the same credentials are passed via script. Any links or code samples would be helpful. From a PowerShell perspective, I am a total SQL Server beginner.

    by DonJ at 2012-08-17 09:55:10

    So... you know the minishell is gone in SQL2012, right? And it isn't special except that it won't let you add other snap-ins. You can open a normal PowerShell console and add the SQL snap ins, and get the same functionality the minishell allows.

    by willsteele at 2012-08-17 10:06:52

    Ah, that's news to me. Thanks for bringing me up to speed. At this point, we are still on 2008, but, I suspect we will migrate relatively soon. Any idea why the decided to go with the SnapIn for 2012 as opposed to modules? I thought most everything was shifting to module-based extensions.

    For anyone else curious about this, I get the following on my machine (PowerShell v3/SQL Server 2012) when I check:
    Get-PSSnapin -Registered

    Name : SqlServerCmdletSnapin100
    PSVersion : 2.0
    Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

    Name : SqlServerProviderSnapin100
    PSVersion : 2.0
    Description : SQL Server Provider

    Once I do this I can start working with cmdlets:
    Add-PSSnapin -Name SqlServerCmdletSnapin100
    Guess I'll be playing around with my laptop this weekend!

    by DonJ at 2012-08-17 10:16:48

    So, in 2008 you've got a Snap-In, which can be loaded into any PowerShell session; the SQL minishell simply preloads it and then prohibits loading any others.

    In 2012, you've got a module (two, I think), and a mainly provider-based model. So you get a SQLSERVER drive. But it's based as a module, and there's no minishell to preload it and exclude the loading of others (the minishell concept was well-intended but ultimately a bad idea).

    So 2012 did repack the functionality into a module.

    by willsteele at 2012-08-17 10:19:26

    Got it. Thanks for filling in the blanks Don.

    by cmille19 at 2012-08-19 08:44:14

    One other point on SQL 2012. The sqlps host (sqlps.exe) provided which is in addition to the sqlps module no longer implements a shellid and so allow snappins and modules to be added. To use SQL authentication instead of Windows authentication as you requested you would need to use new-psdrive with cred parameter as described here:
    http://blogs.msdn.com/b/buckwoody/archi ... ssion.aspx

You must be logged in to reply to this topic.