List Links On A Web Page ( Using PowerShell)

Background

At work, we have setup a collaboration site that allows users to post documents and images.

It happens to be back-end by Microsoft SharePoint.

With all the things I love about Microsoft, one of the areas that continues to stumble me is the structurally layout of SharePoint Lists and Document Stores (when one is try to gain that knowledge from the DB).

Yes, I know I should not sneak in and try to access the back-end directly.

But, the exposed Web Services run best when ran on the SharePoint Server.

Code

Here is a working code that I am slaving on.

Configuration File

Synopsis

To allow quick uptake we have a very shallow configuration file.

Here are the elements that we cover:

…  A sample is here.

Configuration Item Usage Sample
configuration/appsettings/outputFileName  Output File name  c:\tmp\pInterestImages0614PM.txt
 configuration/appsettings/searchTag Search Tag pin (as we are targeting http://www.pinterest.com)Will usually be jpeg, gig, docs, pdf, etc
 configuration/appsettings/ WebSiteList List of web pages  https://www.pinterest.com/daniel_adeniji/autumn-october/

Sample XML File

<?xml version="1.0"?>

<configuration>

	<startup>
		<supportedRuntime version="v2.0.50727" safemode="true"/> 
	</startup>


	<appSettings>

		<add
			key="outputFileName"
			value= "c:\tmp\pInterestImages0614PM.txt" 
		/>


		<add
			key="searchTag"
			value="/pin/" 
		/>
		
		<WebSiteList>
				
			<add 
			   name="AutumnOctober"
               URL="https://www.pinterest.com/daniel_adeniji/autumn-october/"
				/>

				
		</WebSiteList>


	</appSettings>

	
</configuration>

PowerShell – Code


#.DESCRIPTION
# Lists the names of Links \ Documents on the submitted Web Sites
#
#.PARAMETER Configuration File
# Configuration File
#
#.EXAMPLE
# listWebPageLinks configurationFile
##############################################################################
param
(
		$configFile = $(throw &amp;quot;You must specify a configuration file&amp;quot;)
)
#Indicates that we need to be running at minimal PowerShell 3.0
#requires -version 3.0

#Declare Module Variables

$HTTP_SUCCESS = 200

$ERROR_MESSAGE_HTTP_ERROR = &amp;quot;Invoke-WebRequest failed while connection to {0}.  Error Code is {1} and Error Description {2}&amp;quot;
$ERROR_MESSAGE_CONFIGURATION_FILE_FOUND  = &amp;quot;File {0} found&amp;quot;
$ERROR_MESSAGE_CONFIGURATION_FILE_NOTFOUND  = &amp;quot;File {0} not found!&amp;quot;
$ERROR_MESSAGE_CONFIGURATION_FILENAME_NOT_INDICATED = &amp;quot;Filename not indicated in XML File&amp;quot;
$ERROR_MESSAGE_CONFIGURATION_FILENAME_NOT_STRING = &amp;quot;Filename not string in XML File - Filename is {0}&amp;quot;
$ERROR_MESSAGE_CONFIGURATION_SEARCHTAG_NOT_INDICATED = &amp;quot;Search Tag not indicated in XML File&amp;quot;

# Initialize Arrays
$objLinks = @()

#Construct an out-array to use for data export
$OutArray = @()

$strLog = &amp;quot;XML Configuration File is {0}&amp;quot; -f $configFile
$strLog

If ( (Test-Path $configFile) -eq $False)
{

     $errLine = $ERROR_MESSAGE_CONFIGURATION_FILE_NOTFOUND -f $configFile
     Write-Host $errLine -ForegroundColor red            

	#http://stackoverflow.com/questions/2022326/terminating-a-script-in-powershell
	throw $errLine

}

# Declare XML Object
$xmlConfig = New-Object -TypeName XML

# Load Configuration File
$xmlConfig.Load($configFile)

#$xmlConfig
&amp;quot;name of  config file $configFile&amp;quot;

#$xmlConfig.configuration | Get-Member

# read app settings
$objAppSettings = $xmlConfig.configuration.appSettings

# read app settings
$objAppSettingsAdd = $xmlConfig.configuration.add

# get output file name courtesy of app settings
$outputFileNameConfig = $objAppSettings.add.outputFileName

# get item list
$addList = $xmlConfig.SelectNodes(&amp;quot;/configuration/appSettings/add&amp;quot;)
[string]$outputFileName = $null;
[string]$searchTag = $null;

# Iterated element list
foreach($add in $addList)
{

	if ($add.key -eq &amp;quot;outputFileName&amp;quot;)
	{
		[string]$outputFileName = $add.value;
	}

	elseif ($add.key -eq &amp;quot;searchTag&amp;quot;)
	{
		[string]$searchTag = $add.value;
	}

}

#http://techibee.com/powershell/check-if-a-string-is-null-or-empty-using-powershell/1889
#if (!$outputFileName)
if ([string]::IsNullOrEmpty($outputFileName))
{

	$errLine = $ERROR_MESSAGE_CONFIGURATION_FILE_NOTFOUND
	Write-Host $errLine -ForegroundColor red            

	#http://stackoverflow.com/questions/2022326/terminating-a-script-in-powershell
	throw $errLine

}

if ([string]::IsNullOrEmpty($searchTag))
{

	$errLine = $ERROR_MESSAGE_CONFIGURATION_SEARCHTAG_NOT_INDICATED
	Write-Host $errLine -ForegroundColor red            

	#http://stackoverflow.com/questions/2022326/terminating-a-script-in-powershell
	throw $errLine

}

$searchTagWildcard = '*' + $searchTag + '*'

# read list of Web Sites
$objWebSiteList = $objAppSettings.WebSiteList

#$objWebSiteList | Get-Member

$strLog = &amp;quot;Output Filename :- {0}&amp;quot; -f $outputFileName
Write-Host $strLog -ForegroundColor black

$strLog = &amp;quot;Search Tag :- {0}&amp;quot; -f $searchTag
Write-Host $strLog -ForegroundColor black

$strLog = &amp;quot;Search Tag (Wildcard) :- {0}&amp;quot; -f $searchTagWildcard
Write-Host $strLog -ForegroundColor black

# for each Web Site in our config file
foreach ($configNode in $objWebSiteList.add)
{

	$webSiteName = $configNode.name
	$webSiteURL = $configNode.URL

	try
	{

		# Added -UseDefaultCredentials, as without it got the error indicated below
		# The remote server returned an error: (401) Unauthorized.
		$htmlWebResponseObject = Invoke-WebRequest -URI $webSiteURL -UseDefaultCredentials -DisableKeepAlive

		# Get Web Request Status Code
		$WROStatusCode = $htmlWebResponseObject.StatusCode 

		# Get Web Request Status Description
		$WROStatusDescription = $htmlWebResponseObject.StatusDescription

		#Clear Collection
		$objLinks.Clear()

		# If we succcesfully connected to Web Site
		if ($WROStatusCode -eq $HTTP_SUCCESS)
		{

			# Get Web Links
			$objLinksAll = $htmlWebResponseObject.Links

			# commented out that lists out type name
			#$linkType = $objLinksAll.GetType().FullName

			# Get # of Links
			$iNumberofLinks = $objLinksAll.Count

			$iLinkID = 0;

			# Iterated through links
			while ($iLinkID -lt $iNumberofLinks)
			{

				# Get Link Node
				$objLinkNodeX = $objLinksAll[$iLinkID]

				# Get Link HREF
				$linkHREF = $objLinkNodeX.href

				# Get Link Text
				$linkText = $objLinkNodeX.innerText

				# If matches what we are looking for
				if ( ($linkHREF -like $searchTagWildcard) -or ($linkText -like $searchTagWildcard))
				{

					$objPSLinkNode = New-Object PSObject
					Add-Member -InputObject $objPSLinkNode -MemberType NoteProperty -Name innerText -Value &amp;quot;&amp;quot;
					Add-Member -InputObject $objPSLinkNode -MemberType NoteProperty -Name href -Value &amp;quot;&amp;quot;

					$objPSLinkNode.href = $objLinkNodeX.href
					$objPSLinkNode.innerText = $objLinkNodeX.innerText

					$objLinks += $objPSLinkNode

				}

				$iLinkID = $iLinkID +1

			}

			if ($objLinks)
			{

				#Add the object to the out-array
				$outarray += $objLinks

			}

		}
		else
		{

			$strLog = $ERROR_MESSAGE_HTTP_ERROR -f $webSiteURL, $WROStatusCode, $WROStatusDescription
			Write-Host $strLog -ForegroundColor red

		}
	}
	catch
	# Powershell try/catch/finally
	# http://stackoverflow.com/questions/6779186/powershell-try-catch-finally
	{

		Write-Host &amp;quot;_____________________________________________________________________&amp;quot; -ForegroundColor red

		$strLog = &amp;quot;Failed to connect to URL :- {0}&amp;quot; -f $webSiteURL
		Write-Host $strLog -ForegroundColor red

		$errLine = &amp;quot;Exception Type :- {0}&amp;quot; -f $_.Exception.GetType().FullName
		Write-Host $errLine -ForegroundColor red

		$errLine = &amp;quot;Exception Message :- {0}&amp;quot; -f $_.Exception.Message
		Write-Host $errLine -ForegroundColor red

		$errLine = &amp;quot;Exception Source :- {0}&amp;quot; -f $_.Exception.Source
		Write-Host $errLine -ForegroundColor red

		$errLine = &amp;quot;Exception HResult :- {0}&amp;quot; -f $_.Exception.HResult
		Write-Host $errLine -ForegroundColor red

		$errLine = &amp;quot;Exception Inner :- {0}&amp;quot; -f $_.Exception.InnerException
		Write-Host $errLine -ForegroundColor red

		$errLine = &amp;quot;Exception StackTrace :- {0}&amp;quot; -f $_.Exception.StackTrace
		Write-Host $errLine -ForegroundColor red            

		Write-Host &amp;quot;_______________________________________________&amp;quot; -ForegroundColor red

		#Emit line breaks
		Write-Host &amp;quot;&amp;quot;
		Write-Host &amp;quot;&amp;quot;			

	}		

	#Clear Collection
	$objLinks.Clear()

}

# Create Folder, if it does not exists
if ( $outputFileName )
{

	#get base folder name
	$outputFolderName = split-path $outputFileName

	#Howto create a folder if it doesn’t exists using PowerShell
	# http://blog.uwe.elflein.eu/?p=42
	$folderExistence = Test-Path -PathType Container $outputFolderName

	if($folderExistence -eq $false)
	{
	    New-Item $outputFolderName -type Directory
	}

}

if ( ($outputFileName) -and ($outarray) )
{

	#export array to CSV
	$outarray | export-csv $outputFileName -notype -force

}

Source Control

GitHub

https://github.com/DanielAdeniji/ListWebPageLinksUsingPowerShell

Dedicated

I have many special ladies in my life.  I must dedicate this post to those ones who do not have the “Spirit of Singleness”, that Paul encouraged, yet out of obedience they are trying to live it out; even if it is in short lurches.

Maurette Brown Clark – The One He Kept For Me
https://www.youtube.com/watch?v=3sE40VAIpfI

Safy-Hallan Farah


Background

As someone who collects quotes, here is one that stayed with me over the years:

“This world taught woman nothing skillful and then said her work was valueless. It permitted her no opinions and said she did not know how to think. It forbade her to speak in public and said the sex had no genius. It robbed her of every vestige of responsibility, and then called her weak. It taught her that every pleasure must come as a favor from men, and when to gain it she decked herself in paint and fine feathers, as she has been taught to do, it called her vain.”—Carrie Chapman Catt

 

Internet

With all the things wrong with the Internet, the words of young, thoughtful, and strong women is so rich, textured, nuanced and so needed to be sought out.

Here is a good empowering voice:

Safy-Hallan Farah

Poetic Justice: Drake and East African Girls
By Safy-Hallan Farah
http://thefeministwire.com/2013/04/poetic-justice-drake-and-east-african-girls/

I am an East African Girl. A couple years ago, one of my friends told me that being an East African meant I’m not really black. A visibly mixed-race girl with a “high yellow” complexion and sandy brown hair telling me I’m not black didn’t sit well with me. I wanted to tell the girl, in the words of CB4, I’m black y’all. I’m black like the back of Forrest Whitaker’s neck. I’m black like Snoop Dogg’s lungs. I’m black like some Helvetica font against a white backdrop trying to sell you stuff.

I’m a black woman. But my nose, my loosely coiled curls and my fivehead make me black in a way that extends the colorism debate, creating this hierarchy of aesthetic value where I’m not just black, I’m also acceptably black.

Back in the day, white people went to East Africa to find Iman, their acceptable black girl. When white people did this, former Essence Editor-in-Chief Marcia Gillespie called East African model Iman Abdulmajid “a white woman dipped in chocolate,” highlighting Iman’s acceptable blackness while also lamenting the fact that black women’s beauty is often measured in their proximity to whiteness.

Two decades later, Bill Cosby in his “Ask the Ethiopian” speech said African Americans should aim higher than menial jobs because menial jobs are for “Ethiopians,” i.e. immigrants, i.e. The Other. Marcia and Bill emphasized the otherness of East Africans like we’re not black, too, which is why I’d like to tell Bill: please let us, East Africans, have all the menial jobs. But in accordance with Marcia Gillepsie’s criticism, make sure those shitty jobs aren’t jobs where the way we look will inspire racists to pat us on the back and deem us more respectable or better than other black people. This is what the fashion industry notably did with Iman.

East African Girls, Iman included, take part in a system that marginalizes and limits other forms of aesthetic blackness. Every image of Iman or Yasmine Warsame or Liya Kebede reinscribes white beauty through black beauty. Reinscribing white beauty through black beauty has always been with us, but in recent years it has inspired rappers to reference East African Girls like we’re the 49th Law of Power, predictably denigrating black women who lack acceptable blackness in the same tired ways.

The first rapper I remember rhyming about East African Girls was Nas. In “The Set Up,” a song from Nas’ “It Was Written” album, Nas raps, “They thought the hoes were Somalian.” The “hoes” in question are “two fly bitches, Venus and Vicious.” On his latest album, “Life Is Good,” Nas references East African Girls again, in a party song called “Summer” ft. Miguel and Swizz Beats.

East African Girls have been referenced in several other songs: Wale’s “No One Be Like You” (“Somalian women, Ethiopian queens/Never could tell the difference, I just know that you mean”) and “Hold Yuh Remix” (“I’m lookin’ for an Ethi-Somali here beside me”); Tinie Tempeh’s remix of Drake’s “The Motto” (“My bitch booty bigger than a fucking Eritrean”); Common’s “Celebrate” (“Exotic broads lobbyin’/Spanish, Somalian”); Drake’s “Where To Now” and Kendrick Lamar’s “Poetic Justice” ft. Drake.

In “Where To Now,” a track off Homecoming Season, Drake’s second mixtape, Drake spits sweet nothings about an East African Girl, over a J. Dilla beat. Drake desires the East African Girl (perhaps as much as he desires getting ghost head from Aaliyah): “Ethiopian girl, Ethiopian girl, with yo long curly hair and yo big ass bootay.”

In “Poetic Justice” by Kendrick Lamar ft. Drake, Drake does it again: “I was trying to put you on game, put you on a plane/Take you and your mama to the motherland/I could do it, maybe one day/When you figure out you’re gonna need someone/When you figure out it’s all right here in the city/And you don’t run from where we come from.” But couched between another lazy description of a faceless, nameless East African Girl, and Drake’s assertion that that East African Girl is busy ignoring him for another man, is a story of afrodiasporic identity, which is what sets Drake apart, narratively, from other rappers.

While Drake’s definition of black beauty may seem limited, his definition of black identity is what Touré would call “post-black,” and Michelle Wright would call “postwar diasporic black.” Drake’s flow in “Poetic Justice” facilitates a broader discussion of black identity and black authenticity, a discussion that implicitly critiques Marcia Gillespie’s “white woman dipped in chocolate” statement, positing that East African Girls “come from” the same city Drake does, Toronto. The underlying message is that Drake considers us black like him. Drake, as a black Jewish man whose Degrassi character Jimmy Brooks dated a fake East African Girl, occupies a similarly hybrid space like East African Girls. For many East African Girls, that feels like poetic justice because the definition of ‘authentically black’— descendants of Africans brought here as slaves— is a limited definition that doesn’t even include Barack Obama, much less East African Girls.

When one does a cursory Twitter search of Drake’s “East African Girl” lyrics, fetishistic things are tweeted by Drake fans, most notably East African Girls themselves. “Poetic Justice” functions, on some level, as a false empowerment anthem, a Song For East African Girls. There is a pleasure many East African Girls I know derive from hearing men, particularly Drake, talk about us to a larger supposedly authentically black population. A pleasure teenage me would no doubt indulge in, too. It’s a reiteration of our own myth that when God created humanity, he started with the Somalis, Ethiopians and Eritreans first— borne out of us is whiteness and blackness. It’s unscientific but when you’re a teenage girl, especially a young East African Girl, there’s no science needed to justify supremacy or fetish, and where those two things interplay.

East African girls are generally not mixed race, yet this idea that we are is deeply embedded in the minds of white racialists, leading some to believe we’re an entirely different, special, exotic breed of people. This goes back to the pseudoscience of Carleton S. Coon’s “The Races of Europe.” Anthropologists and white racialists, which are often one in the same, have been claiming we are of majority Arab or white or “Afro-Asiatic” descent for years. And while that isn’t the sentiment of Drake or Nas’s lyrics, our alleged mixedness underpins their lyrics by virtue of the sheer selectiveness of the East African Girls shouted out in hip-hop lyrics. When Drake or Nas reference East African Girls, it can be easily inferred that they mean Cushites representing the Horn of Africa (Ethiopia, Eritrea and Somalia). “Cushite,” a term derived from “Cush” of the Hebrew Bible and Quran, is in reference to our shared “Afro-Asiatic” language classification, which is often mistakenly typified as a shared racial identity. This little mistake triggers a big mistake: the conflation of biology and genetics with race and ethnicity as a social fact, which reifies the racial categories.

One of the most popular threads on Niketalk.com, a sneakerhead forum, is called, “African Women Appreciation Thread: ‘Young East African Girl/Thoroughbreds.” A commenter in the forum who goes by Macc E-Money claims he was deprived of “beautiful African women,” and wasn’t able to procure a Somali “thoroughbred” until he left his home state of Michigan. Macc E-Money references Drake’s “Young East African Girl” lyric, presenting black beauty in a limited way and privileging East Africans over other Africans while passing it off as an appreciation of African beauty.

The lines between acceptance, fetishism and exoticism are blurry. It would seem that the primary distinction between black (North American) men, East African men and white men exoticizing East African Girls is that for many white men and even some East African men, the exoticism is firmly rooted in a belief in the racial categories—a belief that race is biological when it is in fact social, and a fetishization and romanticism of our Arab World ties and colonial past. For a lot of black men like Drake, it’s way less insidious. At best, it’s a misguided reinscription of the white standard of beauty through acceptably black women. At worst it’s intra-racial discrimination. Usually, it’s a combination of all these things but if representing, hyping and esteeming women with acceptable blackness is good for all girls—Trickle Down Acceptability, if you will— then we’d probably live in a post-racial world where fairies and dragons and Tupac populated the earth.

Sadly, we live in a racist, sexist world where black men and white people can hurt black women in the same ways. Black women hurt black women, too, but differently: we don’t have each other’s back. Those that see themselves represented in the lyrics and the videos, accept it without questioning it. And those who lament the overrepresentation of East African Girls, frequently fail to realize that the “Young East African Girl(s)” of Drake’s lyrics are like all women of color; they are objectified and male-gazed upon in hip-hop. These women are mythic, “exotic” generalized by rappers as the ambASSadors of their ethnicity or nationality. We are an idea rooted in a scant and skewed example— a token— from Drake’s own lived experience, mixed in with a little bit of mainstream imagery and a history that isn’t even our own.

Perhaps my own cousin, Leyla who Drake once bought lunch for, is Drake’s East African Girl. Maybe his East African Girl is my friend Ayan, who Drake met while clubbing. Maybe his East African Girl is like Helen Gedlu or Lola Monroe. Drake’s East African girl, whoever she is, does not account for of all of us. Our varied hip-to-waist ratios and hair textures and booties (or lack thereof) and cultures make us more nuanced than whatever Drake or anyone else needs to believe.

The over-representation of East African Girls cannot be separated from broader media representations of acceptable blackness. Broader representations that, in the 90s, brought us acceptable black women like Tatyana Ali, Stacey Dash, Chilly of TLC, etc.; the biggest face today being Scandal’s Kerry Washington. It’s no wonder Kendrick Lamar believes there is a balance issue. Kendrick cast Brittany Sky, a black woman, as his love interest in the video for “Poetic Justice.” Brittany Sky is a black woman who is neither East African or light-skinned, however she is every bit as acceptably black as Iman. It’s Drake’s love interest—or rather, sex interest— who is actually balancing representation. But she is who Drake is having sexually for that night, not who, as the video and the lyrics suggest, Drake wants; Drake wants the East African Girl he’s talking to on the phone. Drake is talking on the phone with the East African Girl while his sex interest is splayed across the bed, naked. Thus, even within the video there is a hierarchy. There’s a specific depersonalization and objecthood of the non-acceptable black woman’s body. The non-acceptable black woman is granted zero agency, and rendered the least desirable in a video that is supposedly progressive.

There is nothing progressive about acceptable blackness. There is, however, something progressive about Drake and the internal conversation he seems to be having in his music. When Drake raps about this East African Girl as he is talking to this East African Girl on the phone, he is also talking with other black people. He is having a conversation with Marcia Gillepsie and Bill Cosby and me and that girl I used to be friends with who said I wasn’t black. This conversation requires context that can’t be reproduced for an American audience with limited knowledge of the nuances of blackness. This conversation cannot translate externally, hence the phone. The video begs for the consistency of our transmuted presence but the direct presence of an East African Girl wouldn’t make sense to an audience that doesn’t understand Drake’s specific location in the diaspora, what diaspora is, or who East Africans are.

Loon

Introduction

Music – Gospel, Rap, and Country – have always threaded people and life together in meaningful and ever so often unexpected ways.

For me the love affair started when my cousin came down from Boston and started bumping to “6 Minutes Doug e Fresh – You are on“.

Something about Slick Rick and “Bedtime story” always makes me think and appreciate the dullness of life.

 

Loon

Though I hardly share the same core faith with Loon, but how far are off are we really?

I heard a good faith sermon last week.  I am not quite ready to share it yet.  But, the gentleman said something about what the Good book says:

If anyone forces you to go one mile, go with them two miles.

 

Alhamdulillah, All praise is due to God alone.

Wahali, like it were Spoken Words, this dud bared some truth on these messages.

 

 

 

 

 

 

 

Microsoft – SQL Server – Locks – Aggregated

Background

IT life is crazy. You put in 40 hours each week and then take your light rain Autumn weekend to stress test your Application even more.

We have a set of SQL Server Transact Code that takes a while to run and I know that it might be OK to run for a few hours, but it can not impede normal Business flow.

Business still has to run and this back-office code has to run, as well.

Fast Forward

How do we measure transaction locks and hopefully reach for the least amount of database locks at any one time.

Took to the Net

And, so I started trying to see how others have dealt with same problem.

Here are some worthy SQL Server Activity / Lock monitoring tools:

But, unfortunately Adam’s tool is a bit more geared towards a point in time view and not a time span.

On the other hand, Erland’s Beta_lockinfo offers more luminous output than I could easily digest.

Code Baseline

I found Ritesh Shan’s code to be a bit better for my current specific need:

Find Locked Table name with duration and count in SQL Server

http://blog.extreme-advice.com/2012/09/17/find-locked-table-name-with-duration-and-count-in-sql-server/

Code

Here is my redacted version.


set transaction isolation level read uncommitted;
;with cteSession
as
(

SELECT
tblES.login_name as [LoginName]

, tblL.request_session_id AS SessionID

, tblL.request_owner_type

-- , tblL.resource_type
, db_name(resource_database_id)
as [databaseName]

, schema_name(tblO.schema_id) + '.' + tblO.Name
as LockedObjectName

, tblTAT.Transaction_begin_time

, max(tblEC.most_recent_sql_handle)
as most_recent_sql_handle

, COUNT(*) AS NumberofLocks

FROM sys.dm_tran_locks tblL with (nolock)

JOIN sys.dm_exec_sessions tblES with (nolock)
ON tblL.request_session_id = tblES.session_id

JOIN SYS.DM_EXEC_CONNECTIONS tblEC with (nolock)
ON tblEC.SESSION_ID = tblES.SESSION_ID

JOIN sys.dm_tran_session_transactions tblTS with (nolock)
ON tblTS.session_id = tblES.session_id

JOIN sys.dm_tran_active_transactions tblTAT with (nolock)
ON tblTAT.transaction_id
= tblTS.transaction_id

LEFT OUTER JOIN sys.[Partitions] tblP with (nolock)
ON tblL.resource_associated_entity_id
= tblP.hobt_id

LEFT OUTER JOIN sys.objects tblO with (nolock)
ON tblP.object_id = tblO.object_id
and tblO.Type = 'U'

GROUP BY
tblTAT.Transaction_begin_time

,tblES.login_name
,tblEC.most_recent_sql_handle
,tblL.request_session_id
,tblL.request_owner_type
-- ,tblL.resource_type
,db_name(resource_database_id)
,tblO.schema_id
,tblO.Name
--,tblEC.most_recent_sql_handle

)

select top 100

tblS.loginName as [LoginName]

, tblS.SessionID

, tblS.request_owner_type

, tblS.[databaseName]

, tblS.LockedObjectName

, tblS.Transaction_begin_time

--, max(tblS.most_recent_sql_handle)
-- as most_recent_sql_handle

, max(tblQS.query_hash) as query_hash

, tblS.NumberofLocks

, case

when DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) > 60 then
cast(DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) / 60 as varchar)
+ ' hour(s)'
+ ' ' + cast( (DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) % 60 ) as varchar)
+ ' min(s)'

when DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) > 0 then
cast(DATEDIFF(minute, tblS.Transaction_begin_time, GETDATE()) as varchar)
+ ' minute(s)'
+ ' ' + cast( (DATEDIFF(second, tblS.Transaction_begin_time, GETDATE()) % 60 ) as varchar)
+ ' sec(s)'

else
cast(DATEDIFF(second, tblS.Transaction_begin_time, GETDATE()) as varchar)
+ ' sec(s)'

end AS Duration

, SUBSTRING(
max(tblST.text)
, max(tblQS.statement_start_offset/2)+1
,
(
(
CASE max(tblQS.statement_end_offset)
WHEN -1 THEN DATALENGTH(max(tblST.[text])
)
ELSE max(tblQS.statement_end_offset)
END
- max(tblQS.statement_start_offset)
)/2
) + 1
) AS statement_text

from cteSession tblS

LEFT OUTER JOIN sys.dm_exec_query_stats tblQS with (nolock)

ON tblS.most_recent_sql_handle = tblQS.[sql_handle]

outer apply sys.dm_exec_sql_text(tblS.most_recent_sql_handle) tblST

GROUP BY

tblS.loginName

, tblS.SessionID

, tblS.request_owner_type

, tblS.[databaseName]

, tblS.LockedObjectName

, tblS.Transaction_begin_time

, tblS.NumberofLocks

order by

tblS.NumberofLocks desc
, tblS.[databaseName]

, tblS.LockedObjectName

 

Shared

GitHub – Gist

 

LinkedIn – Deleting own comments

Background

A friend recently celebrated a Work Anniversary.  And, per my LinkedIn email notification settings, I received an email advising me of same.

As someone who believes in championing the work ethics of friends, I sent out little quips to each of them.

But, as always I over shared.  And, I suppose cunningly Social Media leads us to thinking we are sharing with one person, when we are in fact sharing with all.

So let me take back some of those quick notes.

Washing my mouth

Here is how to clean out some of your comments on Linked-In:

  • Go to your Home page by clicking on the Home Link
  • Underneath the “Share an update..” box, click on “All Updates” and “choose your updates
  • Transverse the updates and once you find the offending comment move over to the now visible x icon and act on itLinkedIn-HomePage-DeleteComment

 

Customization

I will suggest some customization.

Using the same button in the Home Page, under the Updates edit box, click Customize.

You will see a screen that looks like the following:

TheUpdatesYouSeeOnYourHomePage-All

 

I changed mine to read:

 

TheUpdatesYouSeeOnYourHomePage-After

 

Fustrations

Here are some frustrations:

 

Summary

I agree with Kris Humphries here:

… don’t pay attention to things that don’t matter

As indeed how hard it is increasing becoming not to be distracted by things that don’t matter.

 

Microsoft – SharePoint – List of uploaded documents

 

Background

This simple little code has been bugging me for a while.

I have a set of web pages that contains documents and I need to programmatically get the list of images available via hyperlink.

 

Code

Thanks to our web friends, here is as far as we got:

$url = "https://www.myimages.org"

$r = Invoke-WebRequest -URI $url -UseDefaultCredentials

$r.Links |
	  where {$_.href -like '*jpg*'} |
		select outertext

 

Listening

I will fess up and say I sampled Good Kid Rapper, … don’t kill my vibe.  But, it is not kid friendly. And, so went YouTube fishing and searched for “Christian Music 2014” and found a new group:

Switchfoot — Love Alone Is Worth The Fight
https://www.youtube.com/watch?v=rk9Pj3ID0UE&index=20&list=PLWvahZRxLnLMSWeULnub0CCeQzvxGM8hB

References

 

 

 

References – Powershell – Invoke-WebRequest

 

References – Powershell – Operators

Microsoft – SQL Server – Transact SQL – Identity Columns – Last Assigned Value

Background

As we prepare to move a database to a new environment, I needed a way to see what shape the database is in.

One of the things I wanted to check are the tables that have Identity Columns.

I basically wanted to see their uptake.

 

Code

Here is a code that gets the last used Identity value for each table that has an identity Column.

 

/*
    IDENT_CURRENT (Transact-SQL)

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

    DBCC Check Ident

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

*/
SELECT 

          quotename(schema_name(tblO.schema_id))
                             + '.' 
                             + quotename(object_name(tblO.object_id))
            as objectName                                

        , tblC.name AS columName

        , IDENT_CURRENT(quotename(schema_name(tblO.schema_id))
                             + '.' 
                             + quotename(object_name(tblO.object_id))
                       ) as identityCurrent

        , tblIC.last_value as lastIdentityValueAssigned

FROM sys.objects tblO

        inner join sys.columns tblC

            ON tblO.object_id = tblC.object_id

            and  tblC.is_identity=1

        inner join sys.identity_columns tblIC

            on tblO.object_id = tblIC.object_id

AND   tblO.[type] in (N'U')

order by 3 desc

 

Opportunities

One always wishes Life was awash with easy pickings.  But, even a code like this betrays a few simple assumptions.

Let us see if we can identify a few:

  • If we issue truncate table requests, the system automatically resets this value
  • Not so when we issue deletes.  Even when all records were removed due to an unfiltered deletes, the code branch for deletes does not go through the problem of checking that the table is now empty and have the the identity value re-calibrated

 

Here is a code from SQLServerPlanet.com ( http://sqlserverplanet.com/dba/using-dbcc-checkident-to-reseed-a-table-after-delete ) that gets the current max value from a table and resets the identity value:

    delete from dbo.tblIdentity;

    DECLARE @max_seed BIGINT
    set @max_seed = ISNULL(
                            (
                              SELECT MAX(ID) 
                              FROM [dbo].[tblIdentity]
                            )
                            ,0)

    -- use the current max as the seed
    DBCC CHECKIDENT('[dbo].[tblIdentity]', RESEED, @max_seed)

 

Summary

The code above merely tracks the last Assigned identity value and not quite the value that will be assigned when a new entry is created.

The SQL Server instance possibly considers other things when assigning a new value, but is easier to determine and expose last used values.