Setting the default file type for a new file in VS Code

Just a short post today. When you open a new file in VS Code (Using CTRL + N) it opens by default as a plain text file.

To change the language for the file use CTRL +K, M.

That’s CTRL and K together and then M afterwards separately.

then you can choose the language for the file. It looks like this

01 - Change language

However, if you just want your new file to open as a particular language every time you can change this in the settings.

Click File –> Preferences –> Settings

or by clicking CTRL + ,

02 - Open Preferences.PNG

This opens the settings.json file. Search in the bar for default and scroll down until you see file

03 - File defaults.PNG

If you hover over the setting that you want to change, you will see a little pencil. Click on that and then Copy to Settings which will copy it to your user settings in the right hand pane.

NOTE – You will need to enter powershell and not PowerShell. For other languages, click on the language in the bottom bar and look at the value in the brackets next to the language name

04 - langauge.PNG

Once you have entered the new settings save the file (CTRL + S) and then any new file you open will be using the language you have chosen

It looks like this

05 - Change settings.gif

and now every new file that you open will be opened as a PowerShell file (or whichever language you choose)

You will still be able to change the language with CTRL K, m

Just to be clear, because people sometimes get this wrong. That’s CTRL and K, let go and then M. You will know you are doing correctly when you see

(CTRL + K) was pressed waiting for second key of chord……

06 - waiting for key

If you get it wrong and Press CTRL + K + M then you will open the Extensions search for keymaps.

 

07 - incorrect.PNG

This is a brilliant feature enabling you to copy key mappings for the programmes you use all the time and save you from learning the Code key mappings. You can find the keymaps in the Extensions Marketplace as well as by pressing CTRL + K + M

Advertisements

Using Twitter with VS Code

So today I saw this tweet from Mathias in reply to Stefan and Amanda

01 - tweet.PNG

 

That looks cool. Twitter in VS Code, one less programme to open Here how it works

Open VS Code and hit CTRL + P and type ext install twitter or hit CTRL + SHIFT + X to open the extensions and search

02 - install.PNG

 

Hit install and then reload

03 - reload

 

Accept the prompt

04 - prompt.PNG

and you will have a Twitter button in the bar at the bottom

05 - bar

If you click it then the top bar will change to the set up wizard

06 - forst time.PNG

Follow it along and create a Twitter App.

07 - want to continue

08 - create an appWhich will open up the website.

 

09 - create an app.PNG

Just fill in the blanks

010 - fil in the blanks.PNG

and then  you will have this window

011 - app settings.PNG

Click on keys and Access tokens

012 - settings and appl

Check the App Permissions are set to read and write

014 - app permissions

and then click create my access token

Then go back to VS Code and click

016 - settings details

Now you are shown the settings.json (which you can always find by File –> Preferences –> Settings )

017 - settingsjson.PNG

This bit, the wizard doesn’t explain very well (hence this post) If you have already some settings between the curly braces, you will need to put a comma and then paste the below code. If you do not then paste the below code between the curly braces

"twitter.consumerkey":"",
"twitter.consumersecret":"",
"twitter.accesstokenkey":"",
"twitter.accesstokensecret":"" 

and then paste the relevant keys and tokens from your twitter app between the double quotes

THEN PRESS CTRL + S to save the settings.json – Its in caps as when I showed someone they were too excited and didn’t save it!!

Now its all set up you can use the extension. Click the twitter button in the bar and

018 - Twitter actions

Now you can have your Home timeline in Code

019 - twitter home.PNG

Yes there are still a few seats left for the Europe PowerShell Conference You can search, see your mentions, your user page

020- mentions.PNG

and post 🙂 You can just press F1 and start typing twitter to get the commands

021 - comands

022 - tweet.png

and

 

023 - tweet.PNG

You can also message people using D message username. You can see the extension repo on GitHub which will be a good place to raise issues, bugs, feature requests

I’m not suire it will Increase my productivity !! but it really pleases the nerd in me!

 

 

Why VS Code Increases my Productivity

Last week I was showing a co-worker some PowerShell code and he asked what the editor was that I was using. Visual Studio Code I said. Why do you use that? What does it do?

This is what I showed him

Runs on any Operating System

Code (as I shall refer to it) is free lightweight open source editor which runs on all the main operating systems. So you have the same experience in Linux as on Windows. So there is less to learn

Extensions

You can add new languages, themes, debuggers and tools from the extensions gallery to reduce the number of programmes you have open and the need to switch between programmes

You can add extensions using CTRL + SHIFT  + X and searching in the bar

01 - Extensions

or by going to the Extensions gallery searching for the extensions and copying the installation command

02 - extensions gallery.PNG

Debugging

There is a rich de-bugging experience built in

03 - debugging.PNG

You can learn about debugging from the official docs and Keith Hill wrote a blog post on Hey Scripting Guys about debugging PowerShell

Intellisense

An absolute must to make life simpler. Code has intellisense for PowerShell and T-SQL which I use the most but also for many more languages . Read more here

Git integration

I love the Git integration, makes it so easy to work with GitHub for me. I can see diffs, commit, undo commits nice and simply. Just open the root folder of the repository and its there

04 - git

This page will give you a good start on using git with Code

No distractions

With full screen mode (F11) or Zen mode (CTRL +K, Z) I can concentrate on coding and not worry about distractions

Stay in one programme and do it all

I have a Markdown document, a PowerShell script and a T-SQL script all in one Git repository and I can work on all of them and version control in one place. The screencast below also shows some of the new capabilities available in the insiders version I managed to leave the screen recording dialogue open as well, apologies and the mistake was deliberate!

I used the GitLens and SQL beautify extensions as well as the dbatools module in that demo

That’s why I am using Code more and more these days, hope it helps

Happy Automating!

 

 

Max Length of a column in a DataTable in PowerShell

Whilst I was writing my Test-DbaLastBackup Posts I ran into a common error I get when importing datatables into a database

I was using this table

01 - table

and when I tried to add the results of the Test-DbaLastBackup I got this

02 -error.PNG

Exception calling “WriteToServer” with “1” argument(s): “The given value of type String from the data source cannot be converted to type nvarchar of the
specified target column.”
At line:356 char:4
+             $bulkCopy.WriteToServer($InputObject)
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : InvalidOperationException

Hmm, it says that it can’t convert a string to a nvarchar, that doesn’t sound right.To find out what was happening I used a little bit of code that I use every single day

 $Error[0] | Fl -force

All errors from your current session are stored in the $error array so [0] accesses the most recent one and fl is an alias for Format-List and the force switch expands the object. This is what I saw

03 expanded error.PNG

System.Management.Automation.MethodInvocationException: Exception calling “WriteToServer” with “1” argument(s): “The given value of
type String from the data source cannot be converted to type nvarchar of the specified target column.” —>
System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the
specified target column. —> System.InvalidOperationException: String or binary data would be truncated.

String or binary data would be truncated. OK that makes sense, one of my columns has larger data than the destination column but which one? Lets take a look at some of the data

SourceServer  : SQL2016N3
TestServer    : SQL2016N1
Database      : RidetheLightning
FileExists    : Skipped
RestoreResult : Restore not located on shared location
DbccResult    : Skipped
SizeMB        : 4.08
BackupTaken   : 3/19/2017 12:00:03 AM
BackupFiles   : C:\MSSQL\Backup\SQL2016N3\RidetheLightning\FULL\SQL2016N3_RidetheLightning_FULL_20170319_000003.bak

SourceServer  : SQL2016N3
TestServer    : SQL2016N1
Database      : TheCallofKtulu
FileExists    : Skipped
RestoreResult : Restore not located on shared location
DbccResult    : Skipped
SizeMB        : 4.08
BackupTaken   : 3/19/2017 12:00:04 AM
BackupFiles   : C:\MSSQL\Backup\SQL2016N3\TheCallofKtulu\FULL\SQL2016N3_TheCallofKtulu_FULL_20170319_000004.bak

SourceServer  : SQL2016N3
TestServer    : SQL2016N1
Database      : TrappedUnderIce
FileExists    : Skipped
RestoreResult : Restore not located on shared location
DbccResult    : Skipped
SizeMB        : 4.08
BackupTaken   : 3/19/2017 12:00:04 AM
BackupFiles   : C:\MSSQL\Backup\SQL2016N3\TrappedUnderIce\FULL\SQL2016N3_TrappedUnderIce_FULL_20170319_000004.bak

Hmm, its not going to be easy to work out which bit of data is too big here.

All I need to know is the maximum length of the columns in the datatable though so I have a little snippet that will do that for me

$columns = ($datatable | Get-Member -MemberType Property).Name
foreach($column in $Columns) {
$max = 0
foreach ($a in $datatable){
       if($max -lt $a.$column.length){
        $max = $a.$column.length
       }
}
Write-Output "$column max length is $max"
}

and the output looks like this

04 - max length.PNG

So we can quickly see that the backupfiles property is too big and change the table accordingly and no more error.

Its pretty quick too, scanning 105 rows in 56 milliseconds in this example
05 - how long.PNG

I keep this little snippet in my snippets list for PowerShell ISE which you can find here

Here is the code to add this as a snippet to ISE
## A list of snippets
$snips = Get-IseSnippet
## Add a snippet
if(!$snips.Where{$_.Name -like 'Max Length of Datatable*'})
{
$snippet = @{
 Title = 'Max Length of Datatable'
 Description = 'Takes a datatable object and iterates through it to get the max length of the string columns - useful for data loads'
 Text = @"
`$columns = (`$datatable | Get-Member -MemberType Property).Name
foreach(`$column in `$Columns)
{
`$max = 0
foreach (`$a in `$datatable)
{
if(`$max -lt `$a.`$column.length)
{
`$max = `$a.`$column.length
}
}
Write-Output "`$column max length is `$max"
}

"@
}
New-IseSnippet @snippet
}
and if you want to add it to your VSCode snippets then you need to edit the PowerShell.json file which is located in your user home AppData folder ‘C:\Users\User\AppData\Roaming\Code\User\snippets\powershell.json’ or by clicking File –> Preferences –> User Snippets and typing PowerShell
04 user snippets.gif

Then you can add this bit of json inside the curly braces

 "Max Length of Datatable": {
"prefix": "Max Length of Datatable",
"body": [
"$$columns = ($$datatable | Get-Member -MemberType Property).Name",
"foreach($$column in $$Columns) {",
"    $$max = 0",
"    foreach ($$a in $$datatable){",
"        if($$max -lt $$a.$$column.length){",
"            $$max = $$a.$$column.length",
"        }",
"    }",
"    Write-Output \"$$column max length is $$max\"",
"}"
],
"description": "Takes a datatable object and iterates through it to get the max length of the string columns - useful for data loads"
}, 

and you have your snippet ready for use

07 - snippet in vscode.gif

Happy Automating

 

 

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 🙂

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