Preklicano

Fix bug in powershell script executed from Sql server

I have a sql trigger that executes a ps1 script.

The script works but I would like to have it work in background. Takes very long to finish.

When I try to set it up as a script block it only works when executed locally from server.

Not when executed from Sql Server:

This is the sql trigger:

-------------------

declare @cmd nvarchar(2000)

declare @folder nvarchar(200)

SET @folder = ''''+'"'+(SELECT CAST(Project_ID as nvarchar)+' '+Name FROM INSERTED)+'"'+''''

set @cmd = 'powershell "D:\PS\[url removed, login to view]"'+' ' +@folder

exec master.dbo.xp_cmdshell @cmd

-------------------------

This is script that works when called from sql but takes very long to finish:

-------------------------

param(

[Parameter(Position=0, Mandatory=$true)] [string]$FolderName

)

$username = "user"

$PasswordFile = "D:\PS\Passwords\[url removed, login to view]"

$KeyFile = "D:\PS\Passwords\[url removed, login to view]"

$key = Get-Content $KeyFile

$UserCred = New-Object [url removed, login to view] ($username, (Get-Content $PasswordFile | ConvertTo-SecureString -Key $key))

$NetWorkCred = New-Object [url removed, login to view]($username , (Get-Content $PasswordFile | ConvertTo-SecureString -Key $key))

$Session = New-PSSession -ConfigurationName [url removed, login to view] -ConnectionUri [url removed, login to view] -Credential $UserCred -Authentication Basic -AllowRedirection

Import-Module D:\PS\[url removed, login to view]

Create-Folder -MailboxName mailbox -Credentials $NetWorkCred -NewFolderName $FolderName -ParentFolder '\Innboks'

Import-PSSession $Session

$ProjectNo = $[url removed, login to view](0,4)

New-InboxRule -Name $ProjectNo -Mailbox mailbox -SubjectOrBodyContainsWords "PREFAB$ProjectNo" -MoveToFolder ":\Innboks\$FolderName" -StopProcessingRules $true -confirm:$false -Force

Remove-PSSession $Session

-------------------------

This is script that works when executed locally on server and works in background, but it does not work when called from sql trigger:

-------------------------

param(

[Parameter(Position=0, Mandatory=$true)] [string]$FolderName

)

$createFolderAndRule = {

param([string]$FolderName)

$username = "user"

$PasswordFile = "D:\PS\Passwords\[url removed, login to view]"

$KeyFile = "D:\PS\Passwords\[url removed, login to view]"

$key = Get-Content $KeyFile

$UserCred = New-Object [url removed, login to view] ($username, (Get-Content $PasswordFile | ConvertTo-SecureString -Key $key))

$NetWorkCred = New-Object [url removed, login to view]($username , (Get-Content $PasswordFile | ConvertTo-SecureString -Key $key))

$Session = New-PSSession -ConfigurationName [url removed, login to view] -ConnectionUri [url removed, login to view] -Credential $UserCred -Authentication Basic -AllowRedirection

Import-Module D:\PS\[url removed, login to view]

Create-Folder -MailboxName mailbox -Credentials $NetWorkCred -NewFolderName $FolderName -ParentFolder '\Innboks'

Import-PSSession $Session

$ProjectNo = $[url removed, login to view](0,4)

New-InboxRule -Name $ProjectNo -Mailbox mailbox -SubjectOrBodyContainsWords "PREFAB$ProjectNo" -MoveToFolder ":\Innboks\$FolderName" -StopProcessingRules $true -confirm:$false -Force

Remove-PSSession $Session

}

Start-job -ScriptBlock $createFolderAndRule -Argumentlist $FolderName

-------------------------

Znanja: Powershell, SQL

Več: database sql server vb script, sql server linux script, sql server login script, aspnet video uploading sql server 2005 script, output select csv format sql server perl script, sql server export script wizard, windows sql server 2000 script, run sql server 2005 script, convert sql server 2005 script oracle, sql server 2005 script role membership, sql server 2000 script, sql server 2005 script data membership profiles, sql server 2000 script sql server agent jobs, sql server 2005 script logins, sql server backup script

O delodajalcu:
( 5 mnenja ) Bjerkreim, Norway

ID projekta: #11656683

7 freelancerjev ponuja v povprečju za €51 na tem delu

€50 EUR v 1 dnevu
(5 Ocen)
3.6
€111 EUR v 1 dnevu
(1 Ocena)
3.0
taran1392

Hi, This can't be done using MySQL, because xp_cmdshell command will block execution and wait for the program to finish. The proper way to do this is detect the condition of trigger,enque the data to be processed in a Več

€28 EUR v 1 dnevu
(6 Ocen)
2.9
ollakolla

The issue is most likely related to the interactions between the layers of processes involved. At each layer there is a relatively expensive marshalling process that moves the data and code. I see three layers in the c Več

€29 EUR v 1 dnevu
(1 Ocena)
2.1
schoudhary1553

Dear Project Poster, I have relevant hands on 5 years of experience .We have a team of all the skills. I assure you about the work quality and time delivery. Please give me chance to complete this project. We hav Več

€33 EUR v 1 dnevu
(0 Ocen)
0.0
Forket

Hello. first of all check which account uses SQL when triggering. Usually this is issue source. In case of any questions feel free to chat. Thanks and have a nice day.

€55 EUR v 1 dnevu
(0 Ocen)
0.0
assuranceES

To know about my work and skills search for Khalid Hussain MBA ISN at [login to view URL]

€50 EUR v 1 dnevu
(0 Ocen)
0.0