0

I have a stored procedure which performs following steps: it updates a default values table for the software, and just runs a powershell cmdlet which is reload tradeloader defaults

The stored procedure takes a parameter like DefaultValue=ABC, where DefaultValue is the field that needs to be changed, and ABC be the value updated.

Now, this runs perfectly fine, when executed manually, but when I pass it from another stored procedure which does a lot of functions including running some other powershell commands, it fails; in fact, just locks the database and keeps running.

I am new to SQL, so I haven't tried much other than try catch and begin tran, commit tran etc.. but none work

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[changeDefaultSettings] 
    (@DefaultChange NVARCHAR(MAX))
AS
BEGIN;
    BEGIN TRAN;
        SET NOCOUNT ON;

        DECLARE @DefaultField NVARCHAR(300)
        DECLARE @DefaultValue NVARCHAR(1000)
        DECLARE @PSString NVARCHAR(4000)
        DECLARE @IPAdd NVARCHAR(50)
        DECLARE @Port NVARCHAR(20)
        DECLARE @FilePath NVARCHAR(1000)
        DECLARE @FileName NVARCHAR(1000)
        DECLARE @Hostname NVARCHAR(50)
        DECLARE @SQL_Powershell NVARCHAR(4000)
        DECLARE @CurrentDerivationRelease NVARCHAR(100)

        SET @DefaultField = (SELECT [Item] 
                             FROM Automation.config.fnsplit(@DefaultChange, '=') 
                             WHERE [ID] = 1)
        SET @DefaultValue = (SELECT [Item] 
                             FROM Automation.config.fnsplit(@DefaultChange,'=') 
                             WHERE [ID] = 2)
        SET @DefaultValue = CONVERT(INT, @DefaultValue)

        IF (@DefaultChange = 'Reset')
        BEGIN
            UPDATE Derivation_Automation.dbo.TradeLoaderSetting 
            SET iValue = -1 
            WHERE vc40Name NOT IN ('DefaultTrader', 'DifferentAccountPerAssetClass')

            UPDATE Derivation_Automation.dbo.TradeLoaderSetting 
            SET iValue = (SELECT idsystemuser 
                          FROM Derivation_Automation.dbo.systemuser 
                          WHERE vc30Name = 'Admin') 
            WHERE vc40Name = 'DefaultTrader'

            UPDATE Derivation_Automation.dbo.TradeLoaderSetting 
            SET iValue = 0 
            WHERE vc40Name = 'DifferentAccountPerAssetClass'
        END
        ELSE
        BEGIN
            UPDATE Derivation_Automation.dbo.TradeLoaderSetting 
            SET iValue = @DefaultValue 
            WHERE vc40Name = @DefaultField
    End

Set @Hostname = (Select HOST_NAME())
Set @IPAdd = (Select GlobalVariables.VariableValue from Automation.dbo.GlobalVariables where GlobalVariables.VariableName = 'PowerShellIP')
If (@Hostname = 'MU1QA01')
    Begin
        Set @Port = (Select GlobalVariables.VariableValue from Automation.dbo.GlobalVariables where GlobalVariables.VariableName = 'PowerShellPort1')
    End
Else IF (@Hostname = 'MU1QA02') 
    Begin
        Set @Port = (Select GlobalVariables.VariableValue from Automation.dbo.GlobalVariables where GlobalVariables.VariableName = 'PowerShellPort2')
    End
Set @FilePath = (Select GlobalVariables.VariableValue from Automation.dbo.GlobalVariables where GlobalVariables.VariableName = 'PowerShellFilePath')
Set @FileName = (Select GlobalVariables.VariableValue from Automation.dbo.GlobalVariables where GlobalVariables.VariableName = 'DefaultChangepowerShellFile')
Set @CurrentDerivationRelease = (Select GlobalVariables.VariableValue from Automation.dbo.GlobalVariables where GlobalVariables.VariableName = 'DerivationRelease')

Set @FilePath = '\\'+@Hostname+'\'+@FilePath

Set @PSString = 'import-module "C:\PROGRA~1\' + @CurrentDerivationRelease + '\DerivationPowershellModuleTradeLoader\Core.PowershellModule.TradeLoader.dll"  -Verbose'
+ char(13) + char(10) +'Invoke-ReloadTradeLoaderDefaults -IPAddress ' + @IPAdd + ' -Port ' + @Port

Exec [dbo].[spWriteStringToFile] @PSString,@FilePath,@FileName

Set @FilePath = ''''+@FilePath + '\' + @FileName+''''

Set @SQL_Powershell = 'c:\PROGRA~1\PowerShell\6\pwsh.exe -Command Invoke-Command -ComputerName '+@hostname+ ' -ScriptBlock { c:\PROGRA~1\PowerShell\6\pwsh.exe -File '+@FilePath+ ' }'

Print(@SQL_Powershell)

EXEC master..xp_cmdshell @SQL_Powershell,no_output

WAITFOR DELAY '00:00:2'

SET nocount off;
Commit Tran;
End;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vijay Madhani
  • 9
  • 4
  • It looks to me like there is a lock on the table tradeloadersetting even after coming out of the sp, since I am not able to run select on this table in debug mode. I am not sure if this is a correct assumption though – Vijay Madhani Aug 18 '19 at 17:21
  • Why is `@DefaultChange` an `nvarchar(MAX)`, when the *only* time you check its value yoi use a `varchar(5)`? – Thom A Aug 18 '19 at 17:30
  • @DefaultChange can accept a large string, reset is just one condition. – Vijay Madhani Aug 18 '19 at 17:48
  • 2
    My guess is that the Powershell script touches rows that were modified by the proc and is blocked because it's a separate process and the transaction is uncommitted. – Dan Guzman Aug 18 '19 at 20:04
  • It does touch probably, since it will reload the default settings for the service; but even if I put the update statement in begin tran and commit tran, it doesn't help. – Vijay Madhani Aug 19 '19 at 09:46

0 Answers0