T-SQL Script to update string NULL with NULL

Below code sample will generate scripts for updating nullable columns of all tables in a database that has string NULL with NULL

USE [AdventureWorks2012]
GO

SET NOCOUNT ON

DECLARE @query NVARCHAR(MAX),
@table_count INT,
@column_count INT,
@tablename VARCHAR(100),
@Columnname VARCHAR(100),
@i INT = 1,
@j INT = 1


DECLARE @MyTableVar TABLE(Number INT IDENTITY(1,1),
Table_list VARCHAR(200));

DECLARE @MyColumnVar TABLE(Number INT IDENTITY(1,1),
Column_list VARCHAR(200));


INSERT INTO @MyTableVar
SELECT name FROM sys.tables
WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'HumanResources'

SELECT @table_count = MAX(Number) from @MyTableVar

WHILE @i <= @table_count

BEGIN

SELECT @tablename = Table_list FROM @MyTableVar WHERE Number = @i

INSERT @MyColumnVar
SELECT C.name
FROM SYS.columns C
INNER JOIN SYS.tables T ON T.object_id = C.object_id
INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id = C.system_type_id
WHERE SCHEMA_NAME(T.SCHEMA_ID) = 'HumanResources' AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
AND C.is_nullable = 1
ORDER BY C.column_id

SELECT @column_count = MAX(Number) FROM @MyColumnVar

WHILE @j <= @column_count

BEGIN

SELECT @Columnname = Column_list FROM @MyColumnVar WHERE Number = @j

SET @query = 'UPDATE ['+@tablename+'] SET ['+@Columnname+'] = NULL WHERE ['+@Columnname +'] = ''NULL'''

SET @j = @j + 1

PRINT @query

--EXEC (@query)

END

SET @i = @i + 1

END
 
--Note:Schema wise above code will generate update scripts.



 
Reference :

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/31d20dd1-b940-43d8-a834-7a222f472040

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a9ac9d4c-cfe7-47e7-86ad-fbc1db482d72




 
 
 
 

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.
RECONFIGURE
GO

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
 


--create stored procedure 
CREATE PROCEDURE SP_EXEC_Powershell
AS
BEGIN

EXEC master..xp_cmdshell 'powershell.exe Get-Date'
END



--Execute StoredProcedure
EXEC SP_EXEC_Powershell




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:\scripts\Eventlog.txt


Get-Counter -ComputerName $env:COMPUTERNAME -ListSet 'MSSQL*' | Out-File c:\scripts\MSSQLCounters.txt

$wmiQuery = @"SELECT systemName,Name,DriveType,FileSystem,FreeSpace,Capacity,Label FROM Win32_Volume
"@
Get-wmiObject -ComputerName "Sathya-PC" -Query $wmiQuery | Out-File c:\scripts\Drivesproperties.txt

Get-WinEvent -ListLog * | Format-Table -Autosize | Out-File c:\scripts\ListofWinEvent.txt




Like shown in below images ,we have to select the type as Powershell to schedule the execution of powershell commands.

I have piped the output to text file but in Advanced setting of job we have option to specify the job output location.





We can also send mails using powershell commands inside SQL Agent job when previous job step fails

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 = "Powershell is really cool"
$D = "Powershell is really cool"

Compare-Object $C $D -includeequal




Executing Powershell commands using SSIS

In this post ,we are going to see two about two cases

i)saving powershell commands inside a file with file extension as ps1 and then invoking that file to execute the powershell commands

ii)executing powershell commands using SSIS - Execute Process Task

As a first step, i am going to open a notepad and place the below powershell commands inside it and then save it as .ps1 under path location "C:\Scripts\test.ps1"


$UserInput = Read-Host "Is Powershell really a cool feature"
##moving file to archieve folder
Move-Item D:\myflatfile.txt D:\archieve


And in my Execute Process Task i have entered only Executable & Arguments



And when you execute the package






Reference - http://technet.microsoft.com/en-us/library/79d83bd4-1c92-4681-afe7-ab90053b4822

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 = $Xmlvar.STUDENTINFO.student[0]
$firststudent

$secondstudent = $Xmlvar.STUDENTINFO.student[1]
$secondstudent 
 
 
foreach( $student in $Xmlvar.STUDENTINFO.student)

{

Write-host $student.name
Write-host $student.Id

}  


$Xmlvar.SelectSingleNode("/STUDENTINFO/student")

$Xmlvar.SelectNodes("/STUDENTINFO/student")







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,Name
1,powershell1.0
2,powershell2.0
3,sqlps
4,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 values
$tmptable = @()
$flatfile = Get-Content D:\myflatfile.txt
foreach ( $values in $flatfile )
{
$id=$values.split(",")[0]
$name=$values.split(",")[1]
$tmptable += @{id="${id}";name="$name"}}
$tmptable

##to search particular value in a file
Get-Content D:\myflatfile.txt | Select-String "powershell"

##this is really cool,it displays values as separate column
Import-Csv D:\myflatfile.txt

##To export as CSV
Import-Csv D:\myflatfile.txt | Export-Csv D:\myflatfilecsv.txt

##to append text or date to the end of file
$Appendate= Get-Date; Add-Content D:\*.txt
$Appendate

#to compare two files
$A = D:\myflatfile.txt
$B = D:\myflatfilecsv.txt
Compare-Object $A $B

##to clear content of a file
Clear-Content D:\myflatfilecsv.txt

##deleting all text files,you can also exclude files using -exclude *test*
Remove-Item c:\scripts\* -include *.txt


##using powershell we can rename,copy,move,create & delete files
##save output as HTML,text,csv or xml file



Reference - http://technet.microsoft.com/en-us/library/ee332545.aspx

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 $db, "PowerStoredProcedure"
$sp.TextMode = $false
$type = [Microsoft.SqlServer.Management.SMO.DataType]::NVarChar(50)
$param = New-Object -TypeName Microsoft.SqlServer.Management.SMO.StoredProcedureParameter `
-argumentlist $sp,"@retval",$type
$param.IsOutputParameter = $true
$sp.Parameters.Add($param)
$sp.TextBody = " SELECT @retval = (SELECT Name FROM dbo.PowerTable)"
$sp.Create()
















 





Reference - http://msdn.microsoft.com/en-us/library/ms162149.aspx