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

Laerte Junior SQL SERVER   Powershell   Importing CSV File Into Database   VideoLaerte 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 :

pscsv SQL SERVER   Powershell   Importing CSV File Into Database   Video

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 ( GetChildItem“c:\SQLAuthority\*.csv”)

$DataTable=Out-DataTableInputObject$DataImport

Write-DataTableServerInstanceR2D2-DatabaseSQLServerRepositoryTableNameCSV_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-ForceDisableNameChecking} `

ScriptBlock {    `

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

$DataTable=Out-DataTableInputObject$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-ForceDisableNameChecking} `

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

               $DataTable=Out-DataTableInputObject$DataImport

               Write-DataTableServerInstance“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-ForceDisableNameChecking} `

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

               $DataTable=Out-DataTableInputObject$DataImport

               Write-DataTableServerInstance“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.

pscsv1 SQL SERVER   Powershell   Importing CSV File Into Database   Video

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

pserror 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

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

pserrorsol1 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

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

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

Thanks Ravikanth one more time for excellent tip.

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

SQL SERVER – PowerShell Version Info

I have multiple computer systems at home. I have previously taken a picture of my home office and published it here. Also, I recently had a scenario where I was listing a PowerShell version installed in my computer systems. While searching online, I found two different commands that can determine the version of PowerShell. One of them worked fine in Version 1, while both worked on Version 2.
The commands are:

$PSVersionTable

and

$host

I have run both the commands on different PowerShell versions and found the following output. This is a call to all PowerShell experts to help me out by letting me know the reasons why these became the results. I am sure that I’m missing something very small, so I ask your help to  clarify this.

PowerShell Version 1

powershell1 SQL SERVER – PowerShell Version Info

PowerShell Version 2

powershell2 SQL SERVER – PowerShell Version Info

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

SQLAuthority News – Guest Post – Performance Counters Gathering using Powershell

Laerte Junior
Laerte Junior

Laerte Junior has previously helped me personally to resolve the issue with Powershell installation on my computer. He did awesome job to help. He has send this another wonderful article regarding performance counter for readers of this blog. I really liked it and I expect all of you who are Powershell geeks, you will like the same as well.

As a good DBA, you know that our social life is restricted to a few movies over the year and, when possible, a pizza in a restaurant next to your company’s place, of course.

So what we have to do is to create methods through which we can facilitate our daily processes to go home early, and eventually have a nice time with our family (and not sleeping on the couch).

As a consultant or fixed employee, one of our daily tasks is to monitor performance counters using Perfmom. To be honest, IDE is getting more complicated. To deal with this, I thought a solution using Powershell. Yes, with some lines of Powershell, you can configure which counters to use. And with one more line, you can already start collecting data. Let’s see one scenario:

You are a consultant who has several clients and has just closed another project in troubleshooting an SQL Server environment.

You are to use Perfmom to collect data from the server and you already have its XML configuration files made with the counters that you will be using- a file for memory bottleneck f, one for CPU, etc.

With one Powershell command line for each XML file, you start collecting. The output of such a TXT file collection is set to up in an SQL Server. With two lines of command for each XML, you make the whole process of data collection.

Creating an XML configuration File to Memory Counters:

Get-PerfCounterCategory -CategoryName "Memory" | Get-PerfCounterInstance  | Get-PerfCounterCounters |Save-ConfigPerfCounter -PathConfigFile "c:\temp\ConfigfileMemory.xml" -newfile

Creating an XML Configuration File to Buffer Manager, counters Page lookups/sec, Page reads/sec, Page writes/sec, Page life expectancy:

Get-PerfCounterCategory -CategoryName "SQLServer:Buffer Manager" | Get-PerfCounterInstance | Get-PerfCounterCounters -CounterName "Page*" | Save-ConfigPerfCounter -PathConfigFile "c:\temp\BufferManager.xml" –NewFile

Then you start the collection:

Set-CollectPerfCounter -DateTimeStart "05/24/2010 08:00:00" -DateTimeEnd "05/24/2010 22:00:00" -Interval 10 -PathConfigFile c:\temp\ConfigfileMemory.xml -PathOutputFile c:\temp\ConfigfileMemory.txt

To let the Buffer Manager collect, you need one more counters, including the Buffer cache hit ratio.

Just add a new counter to BufferManager.xml, omitting the new file parameter

Get-PerfCounterCategory -CategoryName "SQLServer:Buffer Manager" | Get-PerfCounterInstance | Get-PerfCounterCounters -CounterName "Buffer cache hit ratio" | Save-ConfigPerfCounter -PathConfigFile "c:\temp\BufferManager.xml"

And start the collection:

Set-CollectPerfCounter -DateTimeStart "05/24/2010 08:00:00" -DateTimeEnd "05/24/2010 22:00:00" -Interval 10 -PathConfigFile c:\temp\BufferManager.xml -PathOutputFile c:\temp\BufferManager.txt

You do not know which counters are in the Category Buffer Manager? Simple!

Get-PerfCounterCategory -CategoryName "SQLServer:Buffer Manager" | Get-PerfCounterInstance | Get-PerfCounterCounters

Let’s see one output file as shown below. It is ready to bulk insert into the SQL Server.

Notepad SQLAuthority News   Guest Post   Performance Counters Gathering using Powershell

As you can see, Powershell makes this process incredibly easy and fast. Do you want to see more examples? Visit my blog at Shell Your Experience

You can find more about Laerte Junior over here:

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

SQL SERVER – Size of Index Table for Each Index – Solution 3 – Powershell

Laerte Junior
Laerte Junior

If you are a Powershell user, the name of the Laerte Junior is not a new name. He is the one man with exceptional knowledge of Powershell. He is not only very knowledgeable, but also very kind and eager to those in need. I have been attempting to setup Powershell for many days, but constantly facing issues. I was not able to get going with this tool. Finally, yesterday I sent email to Laerte in response to his comment posted here. Within 5 minutes, Laerte came online and helped me with the solution. He spend nearly 15 minutes working along with me to solve my problem with installation. And yes, he did resolve it remotely without looking at my screen – What a skilled and exceptional person!! I will soon post a detail note about the issue I faced and resolved with the help of Laerte.

Here is his solution to my earlier puzzle in his own words. Read the original puzzle here and Laerte’s solution from here.

Hi Pinal,

I do not say better, but maybe another approach to enthusiasts in powershell and SQLSPX library would be:

1 – All indexes in all tables and all databases
Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

2 – All Indexes in all tables and specific database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

3 – All Indexes in specific table and database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable “YourTable” | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

and to output to txt.. pipe Out-File

Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused | out-file c:\IndexesSize.txt

If you have one txt with all your servers, can be for all of them also.

Lets say you have all your servers in servers.txt: something like
NameServer1
NameServer2
NameServer3
NameServer4

We could Use :
foreach ($Server in Get-content c:\temp\servers.txt) {
Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
}

:)

After fixing my issue with Powershell, I ran Laerte‘s second suggestion – “All Indexes in all tables and specific database” and found the following accurate output.

Laerte Junior
Laerte Junior

If you are a Powershell user, the name of the Laerte Junior is not a new name. He is the one man with exceptional knowledge of Powershell. He is not only very knowledgeable, but also very kind and eager to those in need. I have been attempting to setup Powershell for many days, but constantly facing issues. I was not able to get going with this tool. Finally, yesterday I sent email to Laerte in response to his comment posted here. Within 5 minutes, Laerte came online and helped me with the solution. He spend nearly 15 minutes working along with me to solve my problem with installation. And yes, he did resolve it remotely without looking at my screen – What a skilled and exceptional person!! I will soon post a detail note about the issue I faced and resolved with the help of Laerte.

Here is his solution to my earlier puzzle in his own words. Read the original puzzle here and Laerte’s solution from here.

Hi Pinal,

I do not say better, but maybe another approach to enthusiasts in powershell and SQLSPX library would be:

1 – All indexes in all tables and all databases
Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

2 – All Indexes in all tables and specific database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

3 – All Indexes in specific table and database
Get-SqlDatabase -sqlserver “Yourserver” “Yourdb” | Get-SqlTable “YourTable” | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused

and to output to txt.. pipe Out-File

Get-SqlDatabase -sqlserver “Yourserver” | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused | out-file c:\IndexesSize.txt

If you have one txt with all your servers, can be for all of them also.

Lets say you have all your servers in servers.txt: something like
NameServer1
NameServer2
NameServer3
NameServer4

We could Use :
foreach ($Server in Get-content c:\temp\servers.txt) {
Get-SqlDatabase -sqlserver $Server | Get-SqlTable | Get-SqlIndex | Format-table Server,dbname,schema,table,name,id,spaceused
}

:)

After fixing my issue with Powershell, I ran Laerte‘s second suggestion – “All Indexes in all tables and specific database” and found the following accurate output.

Click to Enlarge
Click to Enlarge

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

Click to Enlarge
Click to Enlarge

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