Execute Powershell Commands inside StoredProcedure

To show an example for executing powershell commands inside a stored procedure, i am going to get the current data and time using Get-Date cmdlet. EXEC sp_configure ‘show advanced options’, 1 GO– To update the currently configured value for advanced options.RECONFIGUREGO– To enable the feature.EXEC sp_configure ‘xp_cmdshell’, 1GO– To update the currently configured value for…

Details

Using Powershell commands inside SQL Agent Jobs

In this post,I will show an example of using powershell commands inside SQL Agent jobs. For Example ,I am going to add the below powershell commands in a job as one of the step and schedule it. Get-EventLog System -Newest 5 | Out-File c:scriptsEventlog.txt Get-Counter -ComputerName $env:COMPUTERNAME -ListSet ‘MSSQL*’ | Out-File c:scriptsMSSQLCounters.txt $wmiQuery = @”SELECT…

Details

Handling strings using Powershell commands

This post is just about handling strings using powershell commands ##to split string that are separated with space “Powershell is really cool” -split ” “ ##to join strings with pipe symbol “Powershell 1.0″,“Powershell 2.0″-join “|” ##to compare string values $A = “Powershell is really cool”$B = “Powershell is really hot” Compare-Object $A $B -includeequal $C…

Details

Reading XML file using Powershell

This post deals with reading XML file and then traversing through the nodes of XML and fetching node values using powershell commands. consider the below xml fragment with file path location <STUDENTINFO><student ID=”1″ name=”Sathya”></student><student ID=”2″ name=”Deepak”></student></STUDENTINFO> #loading xml from mentioned path location into xml variable [xml]$Xmlvar = Get-Content D:myxml.xml  #fetching values of xml nodes $firststudent =…

Details

Handling files using Powershell

In this post i have covered about handling files using powershell commands For below examples i have created flatfile in the path location “D:myflatfile.txt“with content as shown below: Id,Name1,powershell1.02,powershell2.03,sqlps4,sqlcmd ##test whether file exists in that path Test-Path D:myflatfile.txt ##reading comma separated values from file ##and storing it in an variable which can hold list of…

Details

Powershell – Create Database,Table and Storedprocedure using SMO

Below commands are used to create database , table & stored procedure using SMO objects #create database $srv= new-Object Microsoft.SqlServer.Management.Smo.Server(“(local)”)$db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -argumentlist $srv, “PowerDatabase”$db.Create()  #Create the Table$db= New-Object Microsoft.SqlServer.Management.Smo.Database$db= $srv.Databases.Item(“PowerDatabase”)$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, “PowerTable”)$col1 = new-object Microsoft.SqlServer.Management.Smo.Column($tb,“Name”, [Microsoft.SqlServer.Management.Smo.DataType]::NChar(50))$col2 = new-object Microsoft.SqlServer.Management.Smo.Column($tb, “ID”, [Microsoft.SqlServer.Management.Smo.DataType]::Int)$tb.Columns.Add($col1)$tb.Columns.Add($col2)$tb.Create()   # Create stored procedure $sp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedure `-argumentlist…

Details