Finding the PS version and process architecture and change 64bit to 32 bit vice versa

Powershell environement

 

to get the version

 

$host.version 

[Environment]::Is64BitProcess

Below script will force the shell to 32 bit process

if ($env:Processor_Architecture -ne "x86")
{
write-warning "Running x86 PowerShell..." 
&"$env:WINDIR\syswow64\windowspowershell\v1.0\powershell.exe" -NonInteractive -NoProfile $myInvocation.Line
exit
}

Get SQL Agent job status

I was working on a script to get the agent job status so i can trigger a necessary action based on the job status. So I thought I I will share that here

Example here I have two sql agent jobs these are simple jobs

sql ajent job 1

Here is the procedure to start with

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[SpGetJobStatus]    Script Date: 11/20/2014 9:25:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[SpGetJobStatus] (
	@Jobname SYSNAME
	,@state INT OUTPUT
	)
AS
DECLARE @job_owner SYSNAME
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @RowCount INT
DECLARE @Jobs AS TABLE (
	RowCounter INT IDENTITY(1, 1)
	,job_id UNIQUEIDENTIFIER
	,job_owner SYSNAME
	,Jobname SYSNAME
	)

INSERT INTO @Jobs (
	job_id
	,job_owner
	,Jobname
	)
SELECT job_id
	,sl.NAME
	,sj.NAME
FROM msdb.dbo.sysjobs sj
INNER JOIN sys.syslogins sl ON sj.owner_sid = sl.sid
WHERE sj.NAME = @Jobname

--Or @Jobname IS NULL
SET @RowCount = @@ROWCOUNT

DECLARE @xp_results TABLE (
	job_id UNIQUEIDENTIFIER NOT NULL
	,last_run_date INT NOT NULL
	,last_run_time INT NOT NULL
	,next_run_date INT NOT NULL
	,next_run_time INT NOT NULL
	,next_run_schedule_id INT NOT NULL
	,requested_to_run INT NOT NULL
	,-- BOOL  
	request_source INT NOT NULL
	,request_source_id SYSNAME COLLATE database_default NULL
	,running INT NOT NULL
	,-- BOOL  
	current_step INT NOT NULL
	,current_retry_attempt INT NOT NULL
	,job_state INT NOT NULL
	)

WHILE @ROWCOUNT > 0
BEGIN
	INSERT INTO @xp_results
	EXECUTE master.dbo.xp_sqlagent_enum_jobs 1
		,@job_owner
		,@job_id

	SET @RowCount = @RowCount - 1
END

SET @state = (
		SELECT job_state
		FROM @xp_results results
		INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
		WHERE sj.NAME = @Jobname
		)

SELECT sj.NAME
	,job_state
	,CASE job_state
		WHEN 1
			THEN 'Executing'
		WHEN 2
			THEN 'Waiting for thread'
		WHEN 3
			THEN 'Between retries'
		WHEN 4
			THEN 'Idle'
		WHEN 5
			THEN 'Suspended'
		WHEN 6
			THEN '<unknown>'
		WHEN 7
			THEN 'Performing completion actions'
		ELSE ''
		END job_state_description
FROM @xp_results results
INNER JOIN msdb.dbo.sysjobs sj ON results.job_id = sj.job_id
WHERE sj.NAME = @Jobname

RETURN @state

Procedure has one input parameter and one output parameter

Input parameter is the job name and output parameter will provide us the job state

agent job status

EXEC [dbo].[SpGetJobStatus] ‘job1’,null

Below script will help us get the job state to a local variable

DECLARE @jobstate INT 

EXEC [dbo].[SpGetJobStatus] 'job1',@state = @jobstate OUTPUT

SELECT @jobstate AS jobstate 

Here is the script that polls on above procedure to get the job state, while loop breaks only when job becomes idle

DECLARE @jobstate INT

EXEC [dbo].[SpGetJobStatus] 'job1'
	,@state = @jobstate OUTPUT

WHILE (@jobstate != 4)
BEGIN
	EXEC [dbo].[SpGetJobStatus] 'job1'
		,@state = @jobstate OUTPUT

	WAITFOR DELAY '00:00:05'
END

SELECT @jobstate
	-- Do something job has finished 
	-- job1 is idle start another job 

We can use above step before start first step in second job as seen below, when second job runs it waits until job 1 is finished

agent job step

Quick tip: Validating powershell parameters

Although powershell offers a great way of validating the parameters using advanced functions we can extend this functionality to make simple scripts robust as they can be

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

for example here in this snipet we can validate the input string using a pattern and length

1
2
3
4
5
6
7
8
9
 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
            [String]
            $str
          ) 
$str

Above script will make sure input string length is at least 5 charcters upt 10 characters max

validate input
input string test less than 5 charecters script throws an exception

validate input 1

[ValidatePattern(“[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]”)]

adding this as a validation attriubute this will make sure string only contains letters a to z

 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
	    [ValidatePattern("[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z]")]
            [String]
            $str
          ) 

$str

validate pattern

 

we can also validate from a script this what I am really interested

below is a simple function that checks if a parameter is null or empty if its not empty does it has any whitespaces similar a static method .Net 4 exposes IsNullOrWhiteSpace

 

function Isnullorempty ($str) 
{
IF( $str -eq $null ) 
 {  $true }  
elseif ( $str.ToString().Trim().Length -eq  0 ){ $true}
else {$false}
}

We are going to embed this script in validation script

 Param
          (
            [parameter(Mandatory=$true)]
            [ValidateLength(5,10)]
 	    [ValidateScript({ IF( $_ -eq $null) 
		{$false }  
	    elseif ($_.ToString().Trim().Length -eq  0 )
		{$false}
	    else {$true}} )]
            [String]$str
          ) 

$str

 

If run with blank white sure it throws an error

PS C:\Powershell> .\powershell.ps1 ” ”
C:\Powershell\powershell.ps1 : Cannot validate argument on parameter ‘str’.
The ” IF( $_ -eq $null)
{$false }
elseif ($_.ToString().Trim().Length -eq 0 )
{$false}
else {$true}” validation script for the argument with value ”
” did not return true. Determine why the validation script
failed and then try the command again.
At line:1 char:19
+ .\powershell.ps1 ” ”
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [powershell.ps1], ParameterBind
ingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,powershell.ps1

PS C:\Powershell> .\powershell.ps1  test1
test1

PowerShell and TFS integration

Team foundation power tools provide set of cmdlets to manage and deploy TFS content

Pre req

  1. Team Foundation – Team explorer
  2. Team Foundation – Power tools

 

I found some very use full must read basics links before we kick start

http://blogs.technet.com/b/heyscriptingguy/archive/2014/04/21/powershell-and-tfs-the-basics-and-beyond.aspx

http://blogs.technet.com/b/heyscriptingguy/archive/2012/12/28/protect-your-powershell-scripts-with-version-control.aspx

http://mscodingblog.blogspot.com/2014/02/introducing-tfsmanager-some-powershell.html

http://www.sds-consulting.com/tfs-training-webcast-automating-team-foundation-server-powershell-slides-and-code-link

Click to access TFS%20Training%20from%20SDS%20-%20Powershell%20Slides%20and%20Code.pdf

http://sdscon.codeplex.com/releases/view/60931

 

 

How to Fix : Script cannot be loaded because the execution of scripts is disabled on this system.

You can fix this issue in lot of different ways depending on your environment by running below command in powershell

1.

Set-ExecutionPolicy RemoteSigned

RemoteSigned  – all scripts you created yourself will be run, all scripts downloaded from the internet will need to be signed by a trusted publisher

2.

Set-ExecutionPolicy unrestricted

Unrestricted – Executes any script

3.

ExecutionPolicy ByPass -File “Script name”

Bypass a particular script

If you are in controlled or restricted environment

4.

Set-ExecutionPolicy -Scope “CurrentUser” -ExecutionPolicy “RemoteSigned”

or

Set-ExecutionPolicy -Scope “CurrentUser” -ExecutionPolicy “Unrestricted”

 
 

PowerShell – Execute Batch files & Executable file, Exes – Dynamically passing Parameters

In PowerShell you can execute a executable in 3 ways

  1. Using Invoke-Expression
  2. Call operator ‘&’  “invocation operator”
  3. System.Diagnostics.ProcessStartInfo

 

Original command c:\Progra~1\NetApp\snapdrive\sdcli.exe snap mount -r  -k -d X -s sqlsnap__sql_08-14-2014_10.15.41

1) Invoke-Expression

$snap= “snap mount -r  -k -d X -s sqlsnap__sql_08-14-2014_10.15.41”

Invoke-Expression -Command “c:\Progra~1\NetApp\snapdrive\sdcli.exe $snap”

2)Call operator

& “c:\Progra~1\NetApp\snapdrive\sdcli.exe ” $snap

3)

$psi = New-Object System.Diagnostics.ProcessStartInfo “c:\Progra~1\NetApp\snapdrive\sdcli.exe”
$psi.Arguments = $snap
[System.Diagnostics.Process]::Start($psi)

please refer to this documentation on http://msdn.microsoft.com/en-us/library/system.diagnostics.processstartinfo.aspx to customize

http://social.technet.microsoft.com/Forums/windowsserver/en-US/b9e0f4f3-ae5d-4c8f-8f80-1600e8b63446/run-executable-from-powershell-passing-parameters-and-return-result?forum=winserverpowershell

 

 

 

 

 

 

How to FIX: Manage Orphaned users in SQL SERVER with a simple script

Using below scripts

 

  • Fixes all orphaned users in the current database if a login exists
  • If a login does not exist in thesql server instance
    •  User will be removed  — @fix = 0
      • If a user is a schema owner then the schema is transferred to DBO and then user is removed from DB
    •  or A login will be created  — @fix = 1

 


DECLARE @t TABLE (

id INT IDENTITY(1, 1)

,username SYSNAME

,usersid VARBINARY(85)

);

DECLARE @username SYSNAME = ;

DECLARE @loopid INT = 0D

ECLARE @DeleteCmd NVARCHAR(1000) = 

DECLARE @fix BIT = 0; — if it is set 0 then all the orphans will be removed 

— if its is set 1 orphans will be created and mapped to the user with a default passwordINSERT INTO @t (

username

,usersid

)

EXEC sp_change_users_login @Action = ‘Report’;SET @loopid = @@IDENTITY;WHILE @loopid > 0

BEGIN

SET @username = (

SELECT username

FROM @t

WHERE id = @loopid

)

IF EXISTS (

SELECT 1

FROM master.dbo.syslogins

WHERE NAME = @username

)

BEGIN

EXEC sp_change_users_login @Action = ‘update_one’

,@UserNamePattern = @username

,@LoginName = @username;

PRINT CONVERT(VARCHAR, @username) + ‘User updated succesfully ‘

END

ELSE

BEGIN

IF @fix = 0

BEGIN

SELECT @DeleteCmd = @DeleteCmd + (

CASE

WHEN s.NAME IS NULL

THEN 

ELSE ‘ALTER AUTHORIZATION ON SCHEMA::’ + ‘[‘ + s.NAME + ‘] TO dbo’ + ‘;’

END

) + ‘DROP USER ‘ + ‘[‘ + dp.NAME + ‘]’ + ‘;’

FROM sys.database_principals dp

LEFT OUTER JOIN sys.schemas s ON s.principal_id = dp.principal_id

WHERE type IN (

‘s’

,‘G’

,‘U’

)

AND dp.principal_id > 4

AND dp.NAME = @username

PRINT CONVERT(VARCHAR, @username) + ‘User Removed succesfully ‘

END

ELSE

BEGIN

EXEC sp_change_users_login ‘Auto_Fix’

,@username

,NULL

,‘Auto_Fix_Pass’;

PRINT CONVERT(VARCHAR, @username) + ‘ User has added to sql server with password Auto_Fix_Pass succesfully ‘

END

END

SET @loopid = @loopid  1;

ENDPRINT @DeleteCmdEXEC sp_executesql @DeleteCmd

— Print un resolved db accounts EXEC sp_change_users_login @Action = ‘Report’;

How to fix: Access to the remote server is denied because the current security context is not trusted SQL Server

You are seeing this error because you are trying to impersonate or  switching the execution context using EXECUTE AS clause and Executing a RPC on a linked server

 

LInked server

 

If you have a procedure on Server X

USE ServerXDB

Create procedure proc

execute as ‘TESTUSER’

Execute ServerY.TestDB.dbo.test

Select * from [ServerY] .TestDb.dbo.testtable

To Fix this you have two options
  1. Enable Trusty worthy on ServerXDB database on Serverx
  2. Remove the execute as clause in the procedure and grant execute privileges to user we used to set up the linked server

 

you can also try by signing a certificate on the proc procedure but I havent tried it

How to fix : Server is not configured for RPC.

.Net SqlClient Data Provider: Msg 7411, Level 16, State 1, Line 3

Server name is not configured for RPC.

 

If you receive an error something like this most likely is from the Linked server its because when you try to run a remote procedure call like stored procedures.. Linked server setup should have RPC enabled

In order to fix this change the Linked server properties

EXEC sp_serveroption ‘server_name’, ‘rpc’, ‘true’;

EXEC sp_serveroption ‘server_name’, ‘rpc out’, ‘true’;

or we can change usi SSMS GUI interface

 

Reomote procedure linked server