Big Data Analytics: The Revolution Has Just Begun

An Introduction to Apache Oozie (+playlist)

Hadoop Tutorial - Hue: Execute Hive queries and schedule them with Oozie...

What is Oozie? (+playlist)

Checking SQL Server Agent jobs using Windows PowerShell

Two tables in particular are of interest to us to check for job execution information like job name, execution status, run date, run time, etc. - the sysjobs and sysjobhistory tables. 

The script below displays a list of jobs on your SQL Server instance with there status.

USE msdb  GO  SELECT     j.[name] AS [JobName],     run_status = CASE h.run_status     WHEN 0 THEN 'Failed'    WHEN 1 THEN 'Succeeded'    WHEN 2 THEN 'Retry'    WHEN 3 THEN 'Canceled'    WHEN 4 THEN 'In progress'    END,    h.run_date AS LastRunDate,      h.run_time AS LastRunTime FROM sysjobhistory h     INNER JOIN sysjobs j ON h.job_id = j.job_id         WHERE j.enabled = 1          AND h.instance_id IN         (SELECT MAX(h.instance_id)             FROM sysjobhistory h GROUP BY (h.job_id))GO

Notice that the run_date and run_time columns of the sysjobhistory table are of type int and would be a bit challenging to convert the columns to their appropriate data types.  Server Management Objects (SMO) exposes these properties when using Windows PowerShell. The JobServer property of the Server object represents the SQL Server Agent associated with an instance of SQL Server. This includes the SQL Server jobs, operators and alerts.

When translating the T-SQL query above to Windows PowerShell, we would be interested in the Name, LastRunDate andLastRunOutcome properties of the Jobs object. What's really good to note is that the LastRunDate property is in adatetime format that no longer requires conversion to the appropriate data type, similar to what we get from thesysjobhistory table in the msdb database.  I keep trying to highlight this for every tip I've written that uses PowerShell with SMO.  The only aspect of the code that we have changed from the scripts in the previous tips is the last line, i.e. adding new properties for the new objects we are working with. This highlights the power and simplicity of Windows PowerShell from the script.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null $rvs = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2000" #Create an instance of the Jobs object collection from the JobServer property #And pipes that to the filter Where-Object cmdlet to retrieve only those jobs that are enabled but failed $srv.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE} | Select Name,LastRunOutcome, LastRunDate

Notice that the LastRunDate property is in the correct data type. The LastRunOutcome property is returned as they are without the need for further translations as in the T-SQL script above. Let's call to the Excel object as we did in the previous tips to format the results. Again, the script above is more than enough for what we need.

#Create a new Excel object using COM  $Excel = New-Object -ComObject Excel.Application $Excel.visible = $True $Excel = $Excel.Workbooks.Add() $Sheet = $Excel.Worksheets.Item(1) #Counter variable for rows $intRow = 1 #Read thru the contents of the SQL_Servers.txt file foreach ($instance in get-content "D:\SQL_Servers.txt") {      #Create column headers      $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"      $Sheet.Cells.Item($intRow,2) = $instance      $Sheet.Cells.Item($intRow,1).Font.Bold = $True      $Sheet.Cells.Item($intRow,2).Font.Bold = $True      $intRow++       $Sheet.Cells.Item($intRow,1) = "JOB NAME"       $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"       $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"      #Format the column headers      for ($col = 1; $col –le 3; $col++)      {           $Sheet.Cells.Item($intRow,$col).Font.Bold = $True           $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48           $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34      }      $intRow++       #######################################################      #This script gets SQL Server Agent job status information using PowerShell      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null      # Create an SMO connection to the instance      $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance      $jobs=$srv.JobServer.Jobs      #Formatting using Excel ForEach ($job in $jobs)  {        # Formatting for the failed jobs         if ($job.LastRunOutcome -eq 0)        {            $fgColor = 3        }        else        {            $fgColor = 0        }            $Sheet.Cells.Item($intRow, 1) =  $job.Name        $Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString()        $Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor        $Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate                        $intRow ++     }    $intRow ++ } $Sheet.UsedRange.EntireColumn.AutoFit() cls

SQL Server Database Connectivity Testing Using PowerShell

There are several networking issues that can prevent the user from reaching or connecting to the SQL Server database.  This is especially true for a new user or developer connecting for the first time or when standing up a new database or server.  So what are the logical questions you need to answer?

• Is there a DNS entry for the server?
• Will the firewall pass the SQL browser traffic?
• Will the firewall pass SQL Server traffic?

In our environment we have separated the core services behind a firewall, NAT-ed them, and given them a different DNS domain depending on which side of the firewall you are. This allows for many permutations for connection strings, especially if you are running multiple instances on a server. I am a typical lazy DBA with enough sysadmin background to have developed the instinct to automate any solution I've tried more than twice and I've been meaning to dig into PowerShell for a while. I'm also smart enough to let others do the heavy lifting for me (so are you which is why you're reading this tip) and Google is my best friend, so here is what I came up with...

Where are you?

One of the first pieces of information the network team will want is the source IP. This is easy to find and PowerShell does a fine job with just a single line of code. I found this snippet all over the Internet, in fairness though, I've attributed borrowed code in the full script (linked at the end of the tip) to the most complete solution I found and used. This procedure will give all the IP addresses of the local machine, and may remind you if you are on a VPN.

$colItems = Get-WmiObject Win32_NetworkAdapterConfiguration -Namespace "root\CIMV2" |
 where{$_.IPEnabled -eq “True”}
 
Write-Host "# --------------------------------------"
Write-Host "# Local IP address information"
Write-Host "# --------------------------------------"
foreach($objItem in $colItems) { 
 Write-Host "Adapter:" $objItem.Description 
 Write-Host " DNS Domain:" $objItem.DNSDomain
 Write-Host " IPv4 Address:" $objItem.IPAddress[0]
 Write-Host " IPv6 Address:" $objItem.IPAddress[1]
 Write-Host " " 
} 

Here is what you get back...

# -------------------------------------- 
# Local IP address information 
# -------------------------------------- 
Adapter: Intel(R) PRO/1000 MT Network Connection
   DNS Domain: localdomain 
   IPv4 Address: 192.168.2.128 
   IPv6 Address: xxxx:xxxx:5c7:57c6:9303:40a4 

Adapter: Cisco Systems VPN Adapter for 64-bit Windows
   DNS Domain: IPv4 
   Address: 10.10.16.1 
   IPv6 Address: xxxx::xxxx:edcf:f2a3:1778 

Where are you going?

Once we know where we are, we need to verify where we are going. The script will ask for a bare host-name and try out a list of provided domains to see if DNS will give us an IP address, we'll hold on to all the IP addresses returned for use later.

Write-Host "# --------------------------------------" 
Write-Host "# DNS Verification" 
Write-Host "# --------------------------------------" 

ForEach ($Domain in $DomainList){ 
 $ComputerAddress = $Computer + $Domain 
 $IPAddress = $null 
  try { 
   $IPAddress = [System.Net.Dns]::GetHostEntry($ComputerAddress).AddressList[0].IPAddressToString 
   } catch { 
   $IPAddress = $null 
   } 
   if ($IPAddress) { 
    Write-Host "DNS reports IP address for $ComputerAddress is $IPAddress"
   if ($AddressList -notcontains $ComputerAddress) { 
    $AddressList += $ComputerAddress 
    } 
   } 
   else { 
    Write-Host "DNS lookup failure for $ComputerAddress" 
    } 
   } 

The results also tell us which FQDNs can be used be used in a connect string from this machine.

# -------------------------------------- 
# DNS Verification 
# -------------------------------------- 
DNS lookup failure for sql-host-1
DNS lookup failure for sql-host-1.core.fake.edu
DNS reports IP address for sql-host-1.admin.fake.edu is 10.10.4.123 

Can you get there from here?

Next we need to see if we can contact the SQL browser on the target server. This is a trickier bit of PowerShell that I found on Wes Brown's blog. It will query the browser on the standard port for available instances, and report back if the browser is listening. I encourage you to read the blog entry at the end of this tip to learn how he deciphered the UDP transaction, the URL is in the references section below and documented in the code.

$IPAddress = [System.Net.Dns]::GetHostEntry($Computer).AddressList[0].IPAddressToString
$ToASCII = new-object system.text.asciiencoding

$UDPEndpoint = New-Object system.net.ipendpoint([system.net.ipaddress]::Any,0)
$UDPPacket = 0x02,0x00,0x00

$UDPClient = new-Object system.Net.Sockets.Udpclient
$UDPClient.client.ReceiveTimeout = $ConnectionTimeout
$UDPClient.Connect($IPAddress,$Port)
$UDPClient.Client.Blocking = $True 

[void]$UDPClient.Send($UDPPacket, $UDPPacket.length)
$BytesRecived = $UDPClient.Receive([ref]$UDPEndpoint) 
[string]$Response = $ToASCII.GetString($BytesRecived)

If ($Response) { 
 $Response = $Response.Substring(3,$Response.Length-3).Replace(';;','~')
 $Response.Split('~') | ForEach { 
 $Responses += $_ 
 } 
 $UDPClient.close() 
} 

The script displays if the browser is available, then uses the information returned to try several variations of the connection string to see which, if any, will work.

# -------------------------------------- 
# Check SQL Browser Service Connection 
# -------------------------------------- 
Browser service listening on sql-host-1.admin.fake.edu

Loop through the response string ... 
ForEach ($ds in $DataSourceList) {
 
 $result = Invoke-SQL $ds "master" "select @@SERVICENAME"
 
 if ($result) { 
   Write-Host "Successful SQL connection to $ds" 
  } else { 
   Write-Host "Failed to connect to $ds" 
  } 
} 

Displaying the results of each connection string variation by connecting and executing a simple query

# -------------------------------------- 
# Check connect string permutations 
# -------------------------------------- 
Successful SQL connection to sql-host-1.admin.fake.edu 
Successful SQL connection to sql-host-1.admin.fake.edu\INST_PROD_1 
Successful SQL connection to sql-host-1.admin.fake.edu, 1433 
Failed to connect to sql-host-1.admin.fake.edu\INST_PROD_2 

With this information you can either have the user correct their connection string, or report to the networking team where your connection is having problems.  They will appreciate the amount of troubleshooting this script will have saved them and owe you one :).

Import Excel Data into Mssql using SQL Statement

you need to run one line per line, if you run all SQL together.you will get below error message:Incorrect syntax near 'sp_configure' Please Follow below Step Step 1: sp_configure 'show advanced options', 1Output Message: Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.Step 2: reconfigureOutput Message: Command(s) completed successfully.Step 3: sp_configure 'Ad Hoc Distributed Queries', 1Output Message: Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.Step 4: reconfigureOutput Message: Command(s) completed successfully.Step 5: Run your SQL to import Excel FilesInsert Excel Data into New Table (Create New Table)INSERT INTO myTableName
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')Insert Excel Data into Existing TableSELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')SQL Code to import Excel Data into New Table in Databasesp_configure 'show advanced options', 1reconfiguresp_configure 'Ad Hoc Distributed Queries', 1reconfigure 
SQL Code to import Excel Data into New Table in Databasesp_configure 'show advanced options', 1reconfiguresp_configure 'Ad Hoc Distributed Queries', 1reconfigure SQL Code to import Excel Data into New Table in Databasesp_configure 'show advanced options', 1reconfiguresp_configure 'Ad Hoc Distributed Queries', 1reconfigure 
sp_configure 'show advanced options', 1reconfiguresp_configure 'Ad Hoc Distributed Queries', 1reconfigure sp_configure 'show advanced options', 1reconfiguresp_configure 'Ad Hoc Distributed Queries', 1reconfigure SELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')SQL Code to import Excel Data into Existing Table in Databasereconfiguresp_configure 'Ad Hoc Distributed Queries', 1reconfigureSELECT * INTO  myTableName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Staffs.xls', 'SELECT * FROM [Sheet1$]')sp_configure 'show advanced options', 1

Add Linked server using Command

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

• Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly

sp_addlinkedserver

Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Command:

EXEC sp_addlinkedserver @server= SERVER NAME

eg, your server name is myDBserver then 

EXEC sp_addlinkedserver @server = 'myDBserver'

sp_addlinkedsrvlogin

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.

Command:

EXEC sp_addlinkedsrvlogin  @rmtsrvname , 'TRUE' | 'FALSE' | NULL, @locallogin, @rmtuser, 

@rmtpassword 

eg,

EXEC sp_addlinkedsrvlogin 'myDBserver' , 'false', NULL, 'sa', 'password'

SQL DATEDIFF Function

Returns the number of date and time boundaries crossed between two dates

SQL DATEDIFF Syntax
DATEDIFF ( DatePart , StartDate , EndDate )

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-08-05'

SELECT DATEDIFF(Year, @StartDate, @EndDate) AS NewDate
Return Value = 0 Year


SELECT DATEDIFF(quarter, @StartDate, @EndDate) AS NewDate
Return Value = 1 quarter

SELECT DATEDIFF(Month, @StartDate, @EndDate) AS NewDate
Return Value = 2 Month


SELECT DATEDIFF(dayofyear,@StartDate, @EndDate) AS NewDate
Return Value = 61 day


SELECT DATEDIFF(Day, @StartDate, @EndDate) AS NewDateReturn Value = 61 Day


SELECT DATEDIFF(Week, @StartDate, @EndDate) AS NewDate
Return Value = 9 Week


SELECT DATEDIFF(Hour, @StartDate, @EndDate) AS NewDateReturn Value = 1464 Hour


SELECT DATEDIFF(minute, @StartDate, @EndDate) AS NewDate
Return Value = 87840 minute


SELECT DATEDIFF(second, @StartDate, @EndDate) AS NewDateReturn Value = 5270400 second


DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate ='2007-06-05'
SET @EndDate ='2007-06-06'

SELECT DATEDIFF(millisecond, @StartDate, @EndDate) AS NewDate
Return Value = 86400000 millisecond

SQL String Functions

Sql string function is a built-in string function.
It perform an operation on a string input value and return a string or numeric value.
Below is All built-in Sql string function :ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR,DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE,UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM


Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53 


Example SQL String Function - SPACE 
-Returns spaces in your SQL query (you can specific the size of space). 
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS 


Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] ) 
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial') 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20) 
-- Value = 27SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)


Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function


Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] ) 
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]


Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 ) 
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning


Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer) 
SELECT LEFT('TravelYourself', 6) 
-- Value = Travel
SELECT LEFT('BeautyCentury',6) 
-- Value = Beauty


Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)-- Value = urself
SELECT RIGHT('BeautyCentury',6)-- Value = Century


Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)SELECT REPLICATE('Sql', 2) 
-- Value = SqlSql


Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3) 
-- Value = Ser


Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string) 
SELECT LEN('SQLServer')
-- Value = 9


Example SQL String Function - REVERSE
-Returns reverse a string.Syntax - REVERSE( string)SELECT REVERSE('SQLServer') 
-- Value = revreSLQS


Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char) 
SELECT UNICODE('SqlServer') 
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83


Example SQL String Function - LOWER
-Convert string to lowercase.Syntax - LOWER( string )SELECT LOWER('SQLServer') 
-- Value = sqlserver


Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string ) 
SELECT UPPER('sqlserver') 
-- Value = SQLSERVER


Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )SELECT LTRIM(' sqlserver')-- Value = 'sqlserver' (Remove left side space or blanks)


Example SQL String Function - RTRIM 
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)