VS Code PowerShell Snippets

Just a quick post, as much as a reminder for me as anything, but also useful to those that attended my sessions last week where I talked about snippets in PowerShell ISE

Jeff Hicks wrote a post explaining how to create snippets in VS Code for PowerShell

I love using snippets so I went and converted my snippets list for ISE (available on GitHub) into the json required for VS Code (available on GitHub)

Here is an example of snippet
"SMO-Server": {
        "prefix": "SMO-Server",
        "body": [
            "$$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $$Server"
        ],
        "description": "Creates a SQL Server SMO Object"
    },

I followed this process in this order

Click File –> Preferences –> User Snippets and type PowerShell or edit $env:\appdata\code\user\snippets\powershell.json

In order I converted the code in the existing snippets “Text” like this

        Replace `$ with $$
        Replace \ with \\
        Replace ” with \”
        \r for new line
        \t for tab
        Each line in “”
        , at the end of each line in the body   except the last one
        Look out for red or green squiggles 🙂
I then add
The name of the snippet, first before the : in “”
The prefix is what you type to get the snippet
The body is the code following the above Find and Replaces
The description is the description!!
and save and I have snippets in VS Code 🙂
snippets.gif
That should help you to convert existing ISE snippets into VS Code PowerShell snippets and save you time and keystrokes 🙂
Advertisements

Using the PowerShell SQL Provider with SQL Authentication

Whilst having a conversation with Chrissy LeMaire last week about using the SQL Provider. She asked if it could use SQL Authentication. I had no idea but said I would find out. This is how and what I did.

I am going to demonstrate this in Visual Studio Code with the PowerShell extension installed but I have also tested this in PowerShell version 5.1.14393.576 on Windows 10 and PowerShell Version 5.0.10586.117 on Windows 8.1 but it will work across other versions I believe

First we need to import the sqlserver module

Import-Module sqlserver

Then if we run Get-PSDrive we can see our SQL Server PS Drive

get psdrive.PNG

A quick dir and we can see what is in that drive

get-psdrive

We can navigate this like it is a file system. For example, below I have changed
to the ‘SQL directory’ run dir and found the machine name ROB-SURFACEBOOK and then
changed to that ‘directory’ and dir to find the instances, navigated to the default
instance and run dir to see what is available

cds

This would enable me to do this to get the database names for example

dir-databases

You can connect to remote SQL instances in the same manner by running

CD SQLSERVER:\SQL\SERVERNAME

However I don’t have permission using this account

failed to connect.PNG

We need to create a new PS Drive so first, as always, start with Get-Help

get-help new-psdrive.PNG

So we can create a new PS Drive with  a credential

Parameters
-Credential Specifies a user account that has permission to perform this action. The default is the current user.

Does it accept SQL Server?

Notes
New-PSDrive is designed to work with the data exposed by any provider. To list the providers available in your session, use Get-PSProvider.

get-psprovider.PNG

Yes it does 🙂

So we can add a new PSDrive using

 New-PSDrive -Name SQL2016N1-SQLAuth -PSProvider Sqlserver `
 -Root SQLSERVER:\SQL\SQL2016N1 -Description 'This is the SQL2016N1 SQL `
PSDrive using the  SQL Authenticated login DemoLogin' -Credential Get-Credential

add psdrive.PNG

Which will prompt us for a credential. Once we enter the credential we will have our SQL Server drive for that instance

access-denied

Except we won’t !! This is because the account that VS Code (or PowerShell) is running does not have permissions on the SQL Server. So I need to run VS Code (or PowerShell) as a user with those permissions by right clicking on the icon whilst holding shift down and clicking run as different user. Obviously you won’t need to do this if the current logged on user has permission on the remote SQL Server

right-click-run-as-different-user

and then enter the credentials of the user

creds

Now that we are running as the correct user

whoami
We repeat the code from above and we will have our PS Drive

need an instance.PNG

Nope 🙂 This time we get the following error

New-PSDrive : SQL Server PowerShell provider error: The root path for a drive with credentials needs to include the instance name.

which makes sense if you think about it as the credentials will only be valid at instance level

So we will add the instance name to the command. As this is the default instance we add DEFAULT

New-PSDrive -Name SQL2016N1-SQLAuth -PSProvider Sqlserver `
-Root SQLSERVER:\SQL\SQL2016N1\DEFAULT -Description 'This is the SQL2016N1 SQL `
PSDrive using the  SQL Authenticated login DemoLogin' -Credential Get-Credential

we-have-a-new-psdrive

Success!! So we will be able to see it in our list of PS Drives using Get-PSDrive

sql auth psdrive.PNG

and we can see further details like this

details.PNG

and we can navigate to it using cd NAMEOFDRIVE

dir-sqlauth

and perform our tasks

databases and logins.PNG

If you take a look at the SQL Server you will see that all that is being run is T-SQL

its just tsql.PNG

Unfortunately you cannot use the persist parameter with a SQLSERVER provider to persist the mapping across sessions

This post has shown you how to set up SQL Server PS Drives using SQL Authentication. The important points are

  • Use Get-Help New-PSDrive -ShowWindow for all the help and examples
  • You must be running PowerShell or VS Code as a user with Windows Permissions on the SQL Server
  • You must specify the instance name
  • Use “New-PSDrive -Name PSDRIVENAME -PSProvider SqlServer -Root SQLSERVER:\SQL\SERVER\INSTANCE -Description ‘DESCRIPTION’ -Credential Get-Credential”
  • You can then navigate the remote SQL Server like a file directory from the command line using SQL Authentication
  • It’s just running T-SQL against the instance

Running SQL Queries with Visual Studio Code

Reading this blog post by Shawn Melton Introduction of Visual Studio Code for DBAs reminded me that whilst I use Visual Studio Code (which I shall refer to as Code from here on) for writing PowerShell and Markdown and love how easily it interacts with Github I hadn’t tried T-SQL. If you are new to Code (or if you are not) go and read Shawns blog post but here are the steps I took to running T-SQL code using Code

To download Code go to this link https://code.visualstudio.com/download and choose your operating system. Code works on Windows, Linux and Mac

00-code-download

Once you have downloaded and installed hit CTRL SHIFT and P which will open up the command palette

01-ctrlshiftp

Once you start typing the results will filter so type ext and then select Extensions : Install Extension

02-extensions

Which will open the Extensions tab ( You could have achieved the same end result just by clicking this icon)

03-install-extensions

But then you would not have learned about the command palette 🙂

So, with the extensions tab open, search for mssql and then click install

04-search-mssql

Once it has installed the button will change to Reload so click it

05-reload

And you will be prompted to Reload the window

06-reload-prompt

Accept the prompt and then open a new file (CTRL N) and then change the language for the file.

You can do this by clicking CTRL K and then M (Not CTRL K CTRL M) or click the language button

07-choose-langauga

And then choose SQL

08-choose-sql

This will start a download so make sure you are connected (and allowed to connect to the internet)

09-start-download

Once it has finished it will show this

10-finished-downloading

And offer you the chance to read the release notes

11-release-notes

Which you can get for any extension anytime by finding the extension in the extensions tab and clicking on it. This has links to tutorials as well as information about the release

12-release-notes

The mssql extension enables Intellisence for T-SQL when you open a .sql file or when you change the language to SQL as shown above for a new file

13-intellisense

Write your T-SQL Query and press CTRL SHIFT and E or Right Click and choose Execute Query. This will ask you to choose a Connection Profile (and display any existing profiles)

14-execute

Choose Create Connection Profile and answer the prompts

15-enter-servername

The query will then run

16-query-runs

You can then output the results to csv or json if you wish

17-results

You can find a video showing this whole process with some typos and an error here

Using SQL with VS Code