SQL SERVER – FIX – The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet

It’s always a fun to learn and share new thing which I learn about any technology. So I decided to spend my weekend at home quietly, so I could learn some scripting using PowerShell and SQL Server. My laptop has tons of software installed so I always use my Virtual Machine to learn new things because I want to see how the things work when someone is working as a learner. My day didn’t start well as I was hit by errors as soon as I started Invoke-SQLCMD. This is part of PowerShell command and is worth a look.

This is the first error I got as soon as I wanted to run Invoke-SQLCMD

Error # 1 The term ‘Invoke-Sqlcmd’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

The Error was because of the fact that I just installed SQL Server Engine on the machine. I didn’t install any client components. So essentially the error appears because the Invoke-sqlcmd cmdlet is not included as part of Windows PowerShell, but instead it is of sqlps (SQL Server 2008 PowerShell Host). So, before using invoke-sqlcmd we should install SSMS or the SQL Server Feature Pack (latest is SQL 2014 which can be downloaded from here)

Once installation was done, I ran the command and I got a new error.

Error # 2The ‘invoke-sqlcmd‘ command was found in the module ‘SQLPS’, but the module could not be loaded. For more information, run ‘Import-Module SQLPS’.

Here is the complete error message

As we can see above, I can also run Import-Module SQLPS to know more. Here is the output of the command

Error # 3 Import-Module : File E:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.ps1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

Here is the complete error message

Why this error? If you know the basics of PowerShell, every command is called cmdlet (pronounced as command let). Invoke-SQLCmd is also a cmdlet provided either by the snap-in SqlServerCmdletSnapin100 (prior to SQL Server 2012) or module SQLPS (SQL Server 2012+). So, we need any one loaded into PowerShell (for example, at the beginning of your script) before you can call the cmdlet.

Assuming PowerShell is installed on the SQL server. You can open the Windows PowerShell Command prompt as below and get on to SQL power shell environment.

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Once I followed above, I was able to use Invoke-SQLCMD as below

Hope this would help others in finding the solution to the errors. How many of you here are frequent users of PowerShell to administer and automate SQL Server tasks?
Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell way of Restarting SQL Server Service

I learn a lot when I go for conferences all around the world. There are styles, content and cultural differences that I get a chance to learn and enjoy as part of my trips. Most of these trips I make are geared towards at least one learning experience I can get end of the day. With these travels, I also attend and speak at local user groups to get a flavour of what is the neat and coolest way to work with SQL Server. Recently at our local user group (SQL Server Bangalore UG) UG meeting, I was vividly stumped by a question from one of the speakers. Here he was talking about various ways to shut down or start SQL Server.

This is was an interesting quiz and I was pleasantly surprised by the most common methods and wanted to participate in this quiz. The answers were flowing all over and here are some that I would like to share which were part of audience interactions:

  1. Object Explorer in SSMS (SQL server Management Studio)
  2. Net Start Command.
  3. SQL Server Configuration Manager.
  4. Windows Services.
  5. Using the Sqlservr.exe executable

I thought I had known most of these and the usage of sqlservr.exe was something I wanted to check as it had been ages. For complete list of startup parameters refer book online.

Getting back to UG meet, I couldn’t hold my curiosity back and I was quick to raise my hand to give what I thought could be yet another answer. I shouted out loud the “SHUTDOWN” command. This was something I thought would fit the bill too. The speaker was understanding to say, this was one of the partially correct answer. Taken aback, I eagerly waited for the explanation.

Though the answer was correct, it was to shut down the service and didn’t allow us to restart our SQL Server service. And the speaker, waited for more answers. This made me curious of the fact that there was more to what I knew.

Welcome PowerShell

The speaker was quick to say, now that we have exhausted all the options available – let me talk about Windows PowerShell and it can be yet another way to restart SQL Server Service. It completely slipped my mind about this option. PowerShell is a powerful way to script and do activities with SQL Server. If you haven’t played around, then I strongly suggest to have a look into the same.

Now, how can I restart SQL Server Service? In this example below, I have used the PowerShell ISE IDE to execute the command as identified below:

Restart-Service -Force MSSQLSERVER

 

Since there was no feedback from the console, I was curious to know if the SQL Server service did restart properly. On examining the Task manager I could confirm that the service did restart because the PID values were completely different after the command got executed.

Sometimes, we carry of knowledge that needs a refresher. Hence taking a student approach to everything we do can help a lot in this learning experience.

Do let me know if you found any other way to restart SQL Server and I would be more than happy to learn it from you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Start Services or Stop Services with PowerShell – Answer to Question

Earlier I asked a very interesting question related to Powershell and T-SQL command. Read my earlier blog post SQL SERVER – Start Services or Stop Services with PowerShell – Question to Readers?.

Here is the question which I asked – Stop-Services ‘mysqlserver in powershell is equivalent to Shutdown or Shutdown With Nowait in T-SQL?

Well, I asked this question because I really had no idea about the answer. However, my personal friend and Powershell Guru Laerte Junior immediately took the challenge to find answers to this question. He comes up with a fantastic explanation of the answer with proper findings and experiments.

Laerte did multiple test to figure out the answer to the question. He used traceflags 2592 and 3605 and to log the status of the checkpoint to the SQL Server log files. I strongly encouraged everyone to read his blog post as it will absolutely teach you something new. Here is the link to his blog post: Stop-Service is like the T-SQL Shutdown or Shutdown with Nowait ?

After doing multiple test Laerte proves that STOP-SERVICE -force  is like to use SHUTDOWN, performing the checkpoint in each database. Indeed a correct answer.

I did not know this before and I learned – Thanks Laerte. Did you know about this one? If you have learned this today as well, please leave a comment. The first step of learning anything is to accept we do the topic. The next step is to learn it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Start Services or Stop Services with PowerShell – Question to Readers?

Recently I received a very interesting question- I have no idea about its answer so I will request your help to answer this question.

Question:

“I am using a Powershell script to shutdown SQL Services. Here is the command I use.

Stop-Services 'mysqlserver' - Force

When I have to start the services, I use

Start-Services 'mysqlserver'

If you notice when I start this service, I do not have to use Force flag/command. If I do not use -Force flag when I try to stop services it gives me error that.

Stop-Service : Cannot stop service ‘SQL Server (MSSQLSERVER) (mssqlserver)’ because it has dependent services. It can only be stopped if the Force flag is set.

Now, here is the question to you – When I use -Force flag does it execute SHUTDOWN process internally or it does SHUTDOWN WITH NOWAIT?”

This is a very interesting question. I honestly have no answer to this question. From the syntax we can guess that it might be doing Shutdown with Nowait but again, guessing is not a good idea. In the past, there have been enough time I have been proven wrong when I have guessed anything. There is always a chance when what we see is not what happens internally.

Here is few additional information. When we execute SHUTDOWN command in T-SQL it waits for all the currently running Transact-SQL statements or stored procedures to finish. However, when we use syntax SHUTDOWN WITH NOWAIT it shuts down server immediately without even doing Checkpoint. This increases more work for the server when it restarts.

So here is the question again – Stop-Services ‘mysqlserver in powershell is equivalent to Shutdown or Shutdown With Nowait in T-SQL?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Powershell – Importing CSV File Into Database – Video

Laerte Junior is my very dear friend and Powershell Expert. On my request he has agreed to share Powershell knowledge with us. Laerte Junior is a SQL Server MVP and, through his technology blog and simple-talk articles, an active member of the Microsoft community in Brasil. He is a skilled Principal Database Architect, Developer, and Administrator, specializing in SQL Server and Powershell Programming with over 8 years of hands-on experience. He holds a degree in Computer Science, has been awarded a number of certifications (including MCDBA), and is an expert in SQL Server 2000 / SQL Server 2005 / SQL Server 2008 technologies. Let us read the blog post in his own words.


I was reading an excellent post from my great friend Pinal about loading data from CSV files, SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video,   to SQL Server and was honored to write another guest post on SQL Authority about the magic of the PowerShell.

The biggest stuff in TechEd NA this year was PowerShell. Fellows, if you still don’t know about it, it is better to run. Remember that The Core Servers to SQL Server are the future and consequently the Shell. You don’t want to be out of this, right?

Let’s see some PowerShell Magic now. To start our tour, first we need to download these two functions from Powershell and SQL Server Master Jedi Chad Miller.Out-DataTable and Write-DataTable.

Save it in a module and add it in your profile. In my case, the module is called functions.psm1.

To have some data to play, I created 10 csv files with the same content. I just put the SQL Server Errorlog into a csv file and created 10 copies of it.

#Just create a CSV with data to Import. Using SQLErrorLog

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

$ServerInstance=new-object (Microsoft.SqlServer.Management.Smo.Server) $Env:Computername

$ServerInstance.ReadErrorLog() | export-csv-path“c:\SQLAuthority\ErrorLog.csv”-NoTypeInformation

for($Count=1;$Count-le 10;$count++)  {

      Copy-Item“c:\SQLAuthority\Errorlog.csv”“c:\SQLAuthority\ErrorLog$($count).csv”

}

Now in my path c:\sqlauthority, I have 10 csv files :

Now it is time to create a table. In my case, the SQL Server is called R2D2 and the Database is SQLServerRepository and the table is CSV_SQLAuthority.

CREATE TABLE [dbo].[CSV_SQLAuthority](
[LogDate] [datetime] NULL,
[Processinfo] [varchar](20) NULL,
[Text] [varchar](MAX) NULL
)

Let’s play a little bit.

I want to import synchronously all csv files from the path to the table:

#Importing synchronously

$DataImport=Import-Csv-Path ( Get-ChildItem“c:\SQLAuthority\*.csv”)

$DataTable=Out-DataTable-InputObject$DataImport

Write-DataTable-ServerInstanceR2D2-DatabaseSQLServerRepository-TableNameCSV_SQLAuthority-Data$DataTable

Very cool, right? Let’s do it asynchronously and in background using PowerShell  Jobs:

#If you want to do it to all asynchronously

Start-job-NameImportingAsynchronously `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock {    `

$DataImport=Import-Csv-Path ( Get-ChildItem“c:\SQLAuthority\*.csv”)

$DataTable=Out-DataTable-InputObject$DataImport

Write-DataTable   -ServerInstance“R2D2″`

                  -DatabaseSQLServerRepository`

                  -TableNameCSV_SQLAuthority`

                  -Data$DataTable

            }

Oh, but if I have csv files that are large in size and I want to import each one asynchronously. In this case, this is what should be done:

Get-ChildItem“c:\SQLAuthority\*.csv” | % {

Start-job-Name“$($_)” `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock { $DataImport=Import-Csv-Path$args[0]

               $DataTable=Out-DataTable-InputObject$DataImport

               Write-DataTable-ServerInstance“R2D2″`

                              -DatabaseSQLServerRepository`

                              -TableNameCSV_SQLAuthority`

                              -Data$DataTable

            } -ArgumentList$_.fullname

}

How cool is that?

Let’s make the funny stuff now. Let’s schedule it on an SQL Server Agent Job.

If you are using SQL Server 2012, you can use the PowerShell Job Step. Otherwise you need to use a CMDexec job step calling PowerShell.exe. We will use the second option.

First, create a ps1 file called ImportCSV.ps1 with the script above and save it in a path. In my case, it is in c:\temp\automation. Just add the line at the end:

Get-ChildItem“c:\SQLAuthority\*.csv” | % {

Start-job-Name“$($_)” `

-InitializationScript  {IpmoFunctions-Force-DisableNameChecking} `

-ScriptBlock { $DataImport=Import-Csv-Path$args[0]

               $DataTable=Out-DataTable-InputObject$DataImport

               Write-DataTable-ServerInstance“R2D2″`

                              -DatabaseSQLServerRepository`

                              -TableNameCSV_SQLAuthority`

                              -Data$DataTable

            } -ArgumentList$_.fullname

}

Get-Job | Wait-Job | Out-Null

Remove-Job -State Completed

Why? See my post Dooh PowerShell Trick–Running Scripts That has Posh Jobs on a SQL Agent Job

Remember, this trick is for  ALL scripts that will use PowerShell Jobs and any kind of schedule tool (SQL Server agent, Windows Schedule)

Create a Job Called ImportCSV and a step called Step_ImportCSV and choose CMDexec.

Then you just need to schedule or run it.

I did a short video (with matching good background music) and you can see it at:

That’s it guys. C’mon, join me in the #PowerShellLifeStyle. You will love it. If you want to check what we can do with PowerShell and SQL Server, don’t miss Laerte Junior LiveMeeting on July 18. You can have more information in : LiveMeeting VC PowerShell PASS–Troubleshooting SQL Server With PowerShell–English

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fix: Error: File cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details

Yesterday I formatted my computer and did fresh install as it was due from long time. After the fresh install when I tried to install Semantic Search application using powershell, I was stopped by following error.

File cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details

Fix/Solution/Workaround:

The solution is very simple. Open the Powershell window and type following two lines and everything will fine right after that.

Set-ExecutionPolicy Unrestricted
Set-ExecutionPolicy RemoteSigned

Again, this is I have done for my environment where I am very careful what I will run. You can change the policy back to original restricted policy if you want to restrict future execution of the powershell scripts.

Simple – isn’t it? Well all complex looking problems are very simple to solve.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Powershell – Get a List of Fixed Hard Drive and Free Space on Server

Earlier I have written this article SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server. I recently received excellent comment by MVP Ravikanth. He demonstrated that how the same can be done using Powershell. It is very sweet and quick solution.

Here is the powershell script. Run the same in your powershell windows.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Well, I ran this script in my powershell window, it gave me following result – very accurately and easily.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Thanks Ravikanth one more time for excellent tip.

Reference: Pinal Dave (http://blog.SQLAuthority.com)