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 }
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
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
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
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
input string test less than 5 charecters script throws an exception
[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
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
If you are trying to install MySQL ODBC driver on Windows server you might see this error in order to fix this just install the Visual Studio C++ redistrubutable
Download here, 32- or 64-bit.
http://www.microsoft.com/en-us/download/confirmation.aspx?id=14632
http://www.microsoft.com/en-us/download/confirmation.aspx?id=5555
Team foundation power tools provide set of cmdlets to manage and deploy TFS content
Pre req
I found some very use full must read basics links before we kick start
http://mscodingblog.blogspot.com/2014/02/introducing-tfsmanager-some-powershell.html
Click to access TFS%20Training%20from%20SDS%20-%20Powershell%20Slides%20and%20Code.pdf
http://sdscon.codeplex.com/releases/view/60931
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”
In PowerShell you can execute a executable in 3 ways
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
Using below scripts
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’;
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
If you have a procedure on Server X
execute as ‘TESTUSER’
Execute ServerY.TestDB.dbo.test
Select * from [ServerY] .TestDb.dbo.testtable
you can also try by signing a certificate on the proc procedure but I havent tried it
.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
Sqljunkieshare 