locked
Just Exec SP works but Insert into Exec SP goes in infinite loop RRS feed

  • Question

  • Question 1) 

    We are planning to turn off XP CMDSHELL in SQL. Is there any alternative to the method I am using below:

    Question 2) 

    Below SP  

    1. Creates new SQL Job every time
    2. Runs the CMD command passed to it from SP param 
    3. Finally SP return result

    ALTER PROCEDURE [dbo].[CMDSHELL_ALTERNATIVE]
    	-- Add the parameters for the stored procedure here
    	@Command VARCHAR(200)
    	,@InvokedBySP VARCHAR(100) = 'AdhocCalling'
    AS
    BEGIN
    	DECLARE @OutputFileName VARCHAR(max)
    	DECLARE @OutputFilePath VARCHAR(max)
    	DECLARE @JobOutcome INT = 5
    	DECLARE @JobMessage VARCHAR(max)
    	DECLARE @TodayDate DATE = GETDATE()
    	DECLARE @JobExecutionTime DATETIME
    	DECLARE @RandomeNumber BIGINT = CAST(RAND() * 1000000 AS BIGINT)
    	DECLARE @JobName VARCHAR(100) = 'CMDSHELL_JOB_' + CAST(@TodayDate AS VARCHAR) + '_' + 
    CAST(@RandomeNumber AS VARCHAR) SET @OutputFilePath = 'D:\Temp1\' SET @OutputFileName = @JobName + '.txt' DECLARE @FullOutputFilePathName VARCHAR(max) = @OutputFilePath + @OutputFileName DECLARE @jobId BINARY (16) EXEC msdb.dbo.sp_add_job @job_name = @JobName ,@enabled = 1 ,@notify_level_eventlog = 0 ,@notify_level_email = 2 ,@notify_level_page = 2 ,@delete_level = 0 ,@category_name = N'[Uncategorized (Local)]' ,@owner_login_name = N'sa' ,@job_id = @jobId OUTPUT --select @jobId EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName ,@server_name = N'(local)' EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName ,@step_name = N'step-1' ,@step_id = 1 ,@cmdexec_success_code = 0 ,@on_success_action = 1 ,@on_fail_action = 2 ,@retry_attempts = 0 ,@retry_interval = 0 ,@os_run_priority = 0 ,@subsystem = N'CmdExec' ,@command = N'' ,@database_name = N'master' ,@output_file_name = N'' ,@flags = 0 EXEC msdb.dbo.sp_update_job @job_name = @JobName ,@enabled = 1 ,@start_step_id = 1 ,@notify_level_eventlog = 0 ,@notify_level_email = 2 ,@notify_level_page = 2 ,@delete_level = 0 ,@description = N'' ,@category_name = N'[Uncategorized (Local)]' ,@owner_login_name = N'sa' ,@notify_email_operator_name = N'' ,@notify_page_operator_name = N'' EXEC msdb.dbo.sp_update_jobstep @job_name = @JobName ,@step_id = 1 ,@command = @Command ,@output_file_name = @FullOutputFilePathName EXEC msdb.dbo.sp_start_job @JobName SELECT DISTINCT @JobOutcome = SJH.run_status FROM msdb..sysjobhistory SJH ,msdb..sysjobs SJ WHERE SJH.job_id = SJ.job_id AND SJ.Name = @JobName WHILE (@JobOutcome != 3 AND @JobOutcome > 1) BEGIN PRINT 'in a delay loop' WAITFOR DELAY '00:00:02'; SELECT DISTINCT @JobOutcome = SJH.run_status FROM msdb..sysjobhistory SJH ,msdb..sysjobs SJ WHERE SJH.job_id = SJ.job_id AND SJ.Name = @JobName END SET @JobExecutionTime = GETDATE() SELECT @JobMessage = message FROM msdb..sysjobhistory SJH ,msdb..sysjobs SJ WHERE SJH.job_id = SJ.job_id AND SJ.Name = @JobName AND step_id = 1 IF @JobOutcome = 1 BEGIN PRINT 'Job successfull' DECLARE @BulkInsertCommand VARCHAR(max) CREATE TABLE #temp (results NVARCHAR(755)) SET @BulkInsertCommand = 'BULK INSERT #temp FROM ''' + @FullOutputFilePathName + ''' WITH ( ROWTERMINATOR = ''\n'', DataFileType=''widechar'' )' EXEC (@BulkInsertCommand) SELECT results FROM #temp END ELSE BEGIN PRINT 'Dynamic Job failed' END IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @JobName ) EXEC msdb.dbo.sp_delete_job @job_name = @JobName END

    Now I run above SP using below code - 

    DECLARE @cmd VARCHAR(100)
    SET @cmd = 'dir /B D:\Temp'
    DECLARE @dirTable TABLE (oPut VARCHAR(max))
    
    --insert @dirTable
    EXEC [dbo].CMDSHELL_ALTERNATIVE @cmd


    Result without Insert


    Problem 

    Code above without insert works fine, returns result quickly. But when Insert statement is uncommented, SP runs forever. 

    I can't figure out exactly where the blocking is. Any alternative/fix in above? 


    • Edited by VaibhavSQL Friday, May 8, 2020 10:55 AM
    Friday, May 8, 2020 10:32 AM

All replies

  • Hi VaibhavSQL

    How many files are present in D:\temp1 folder ?

    To me its you have large number of files in this folder - If this is the case can you please cleanup few historical files and see

    Thx


    http://uk.linkedin.com/in/ramjaddu

    Friday, May 8, 2020 10:55 AM
  • Hi VaibhavSQL

    How many files are present in D:\temp1 folder ?

    To me its you have large number of files in this folder - If this is the case can you please cleanup few historical files and see

    Just 5 folders in D:\Temp folder. I attached screenshot in original question. 

    Problem is created job runs forever if it has INSERT INTO. Without INSERT INTO proc quickly give result. 

    Friday, May 8, 2020 10:58 AM
  • 5 folders but how many (.txt) files in them in total?

    http://uk.linkedin.com/in/ramjaddu


    • Edited by RamJaddu Friday, May 8, 2020 11:17 AM
    Friday, May 8, 2020 11:15 AM
  • 5 folders but how many files in them in total?

    http://uk.linkedin.com/in/ramjaddu

    Every folder has some 5-10 files. Folder size is not the problem. 

    There is some blocking. 

    sp_who2 shows the blocking. Job that is created is blocked by SPID where "INSERT INTO EXEC SP" code is running. 

    This issue is easily reproduced.

    Friday, May 8, 2020 11:19 AM
  • Code working fine on my laptop - with 10 files. could be issue with msdb as all job history data stored in that database it need cleanup on regular basis.

    Could you please tell me 

    How big is your msdb database?


    http://uk.linkedin.com/in/ramjaddu


    • Edited by RamJaddu Friday, May 8, 2020 11:30 AM
    Friday, May 8, 2020 11:25 AM
  • Code working fine on my laptop - with 10 files. could be issue with msdb as all job history data stored in that database it need cleanup on regular basis.

    Could you please tell me 

    How big is your msdb database?


    Friday, May 8, 2020 11:41 AM
  • Then it could be concurrent issue some where - I have enable the transactions with in code (begin tran .. commit tran) see that fix the problem ... here is modified version.

    Alter PROCEDURE [dbo].[CMDSHELL_ALTERNATIVE]
    -- Add the parameters for the stored procedure here
    @Command VARCHAR(200)
    ,@InvokedBySP VARCHAR(100) = 'AdhocCalling'
    AS
    BEGIN
    DECLARE @OutputFileName VARCHAR(max)
    DECLARE @OutputFilePath VARCHAR(max)
    DECLARE @JobOutcome INT = 5
    DECLARE @JobMessage VARCHAR(max)
    DECLARE @TodayDate DATE = GETDATE()
    DECLARE @JobExecutionTime DATETIME
    DECLARE @RandomeNumber BIGINT = CAST(RAND() * 1000000 AS BIGINT)
    DECLARE @JobName VARCHAR(100) = 'CMDSHELL_JOB_' + CAST(@TodayDate AS VARCHAR) + '_' + CAST(@RandomeNumber AS VARCHAR)

    SET @OutputFilePath = 'D:\Temp1\'
    SET @OutputFileName = @JobName + '.txt'

    DECLARE @FullOutputFilePathName VARCHAR(max) = @OutputFilePath + @OutputFileName
    DECLARE @jobId BINARY (16)
    begin Tran 
    EXEC msdb.dbo.sp_add_job @job_name = @JobName
    ,@enabled = 1
    ,@notify_level_eventlog = 0
    ,@notify_level_email = 2
    ,@notify_level_page = 2
    ,@delete_level = 0
    ,@category_name = N'[Uncategorized (Local)]'
    ,@owner_login_name = N'sa'
    ,@job_id = @jobId OUTPUT

    --select @jobId
    EXEC msdb.dbo.sp_add_jobserver @job_name = @JobName
    ,@server_name = N'(local)'

    EXEC msdb.dbo.sp_add_jobstep @job_name = @JobName
    ,@step_name = N'step-1'
    ,@step_id = 1
    ,@cmdexec_success_code = 0
    ,@on_success_action = 1
    ,@on_fail_action = 2
    ,@retry_attempts = 0
    ,@retry_interval = 0
    ,@os_run_priority = 0
    ,@subsystem = N'CmdExec'
    ,@command = N''
    ,@database_name = N'master'
    ,@output_file_name = N''
    ,@flags = 0

    EXEC msdb.dbo.sp_update_job @job_name = @JobName
    ,@enabled = 1
    ,@start_step_id = 1
    ,@notify_level_eventlog = 0
    ,@notify_level_email = 2
    ,@notify_level_page = 2
    ,@delete_level = 0
    ,@description = N''
    ,@category_name = N'[Uncategorized (Local)]'
    ,@owner_login_name = N'sa'
    ,@notify_email_operator_name = N''
    ,@notify_page_operator_name = N''

    EXEC msdb.dbo.sp_update_jobstep @job_name = @JobName
    ,@step_id = 1
    ,@command = @Command
    ,@output_file_name = @FullOutputFilePathName
     
      Commit Tran
      PRINT 'in a delay loop'

    WAITFOR DELAY '00:00:02';
    begin Tran
    EXEC msdb.dbo.sp_start_job @JobName

    SELECT DISTINCT @JobOutcome = SJH.run_status
    FROM msdb..sysjobhistory SJH
    ,msdb..sysjobs SJ
    WHERE SJH.job_id = SJ.job_id
    AND SJ.Name = @JobName

    WHILE (
    @JobOutcome != 3
    AND @JobOutcome > 1
    )
    BEGIN


    SELECT DISTINCT @JobOutcome = SJH.run_status
    FROM msdb..sysjobhistory SJH
    ,msdb..sysjobs SJ
    WHERE SJH.job_id = SJ.job_id
    AND SJ.Name = @JobName
    END

    SET @JobExecutionTime = GETDATE()

    SELECT @JobMessage = message
    FROM msdb..sysjobhistory SJH
    ,msdb..sysjobs SJ
    WHERE SJH.job_id = SJ.job_id
    AND SJ.Name = @JobName
    AND step_id = 1
    commit tran
    PRINT 'in a delay loop'

    WAITFOR DELAY '00:00:02';
    begin Tran
    IF @JobOutcome = 1
    BEGIN
    PRINT 'Job successfull'

    DECLARE @BulkInsertCommand VARCHAR(max)

    CREATE TABLE #temp (results NVARCHAR(755))

    SET @BulkInsertCommand = 'BULK INSERT #temp
    FROM ''' + @FullOutputFilePathName + '''
    WITH
    (
    ROWTERMINATOR = ''\n'',
    DataFileType=''widechar''
    )'

    EXEC (@BulkInsertCommand)

    SELECT results
    FROM #temp
    END
    ELSE
    BEGIN
    PRINT 'Dynamic Job failed'
    END
    commit tran
    IF EXISTS (
    SELECT job_id
    FROM msdb.dbo.sysjobs_view
    WHERE name = @JobName
    )
    EXEC msdb.dbo.sp_delete_job @job_name = @JobName
    END


    http://uk.linkedin.com/in/ramjaddu

    Friday, May 8, 2020 11:46 AM
  • Then it could be concurrent issue some where - I have enable the transactions with in code (begin tran .. commit tran) see that fix the problem ... here is modified version.


    No luck. Exec SP runs quickly. But below code runs forever. 

    Friday, May 8, 2020 1:39 PM
  • Let's start from the beginning. What are trying to achieve really? And then I don't mean that you want to replace xp_cmdshell. I'm asking why you want to use xp_cmdshell in the first place.

    What I can say at this point that the above is a none-starter. It horribly complex, and since creating and running jobs requires permission, this will be difficult for regular users to use. With xp_cmdshell you can at least set up a proxy account.

    I can answer the question why the INSERT is blocking: the INSERT statement defines a transaction, so the jobs are never committed, and you are also blocking Agent. But as I said, this is a dead, so don't try to get this working. Tell us of your real problem instead.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 8, 2020 1:59 PM
  • Thanks Erland.

    As a security concerns in our company, we have advised to disable the xp_cmdshell server configuration. And we have lots of stored procedures that are using various cmdshell commands in it.

    Once we disable the xp_cmdshell server configuration. we cant execute cmdshell commands inside a stored procedure. In order to find the alternative to it, we have created an stored procedure which will take a command as an argument and returns the result same as like xp_cmdshell command.

    In this alternative stored procedure we are creating dynamic jobs with the cmdexec type step to run the cmdshell command in it.

    We have various file based loads that are running on sql server . So for validating files , we need
    to run various cmdshell commands in our stored procedures

    And We cant have one job which we will updating with the commands as at the same time many stored procedures runs the CMDSHELL commands in it that's why we are creating the jobs inside it and once get the result dropping the jobs as well. 

    this was my idea to go with the alternative of cmdshell. Please let me know if you have any better suggestions on this situations.

    NOTE: We have advised to disable the CLR server configurations as well.

    • Edited by VaibhavSQL Friday, May 8, 2020 4:46 PM
    Friday, May 8, 2020 4:26 PM
  • This still does not explain what you are using xp_cmdshell for...or why you believe it is necessary.  Instead of trying to build something that 'replaces' it - you need to look at what is being done and determine how to do that without calling out to the OS directly.

    For this one - it looks like all you are doing is getting a directory listing to be fed into a BULK INSERT process.  If that is all that is being done then you do not need xp_cmdshell at all - you can use xp_dirtree.

    Depending on what you are actually doing - there may be other methods that work better.  For example - you could schedule an agent job that parses a selected list of folders using Powershell and loads that data into a table for processing - and your other jobs just pull from the table as needed.

    It all depends on what you are really doing.


    Jeff Williams

    Friday, May 8, 2020 6:39 PM
  • As a security concerns in our company, we have advised to disable the xp_cmdshell server configuration. And we have lots of stored procedures that are using various cmdshell commands in it.

    Once we disable the xp_cmdshell server configuration. we cant execute cmdshell commands inside a stored procedure. In order to find the alternative to it, we have created an stored procedure which will take a command as an argument and returns the result same as like xp_cmdshell command.

    In this alternative stored procedure we are creating dynamic jobs with the cmdexec type step to run the cmdshell command in it.We have various file based loads that are running on sql server . So for validating files , we need
    to run various cmdshell commands in our stored procedures

    You did not answer my question. I did not ask why you want to replace xp_cmdshell. I asked why you are using xp_cmdshell. If xp_cmdshell is used in many stored procedures there may be more than one reason. But then you need to investigate all. I would expect that in many cases these are actions that should not be executed from a stored procedure at all, but from something else.

    And whatever, the solution you have now is a dead end. It does not work for reasons I explained, and from a security perspective, I am almost more worried about it than xp_cmdshell.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 8, 2020 9:27 PM
  • For this one - it looks like all you are doing is getting a directory listing to be fed into a BULK INSERT process.  If that is all that is being done then you do not need xp_cmdshell at all - you can use xp_dirtree.

    Jeff, this is Vaibahv's general procedure. The BULK INSERT is there to read the output from the job step. Yikes!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 8, 2020 9:28 PM
  • HI Erland

    These are legacy codes and written around 6-7 years back. I have recently joined  this organization and they have asked me to find the alternative to xp_cmdshell.

     I agreed to all your points that these are not the good practices. But as we have around more than 200 stored procedures that are using various cmdshell commands like DELETE, MOVE, COPY. EXECUTING BAT files these are part of the legacy production codeswhich is currently running. This setup was created by some developers who are not with us now.

    These loads are fully file based and its using 3-4 cmdshell commands in it in each procedure.

    And most of them runs concurrently so we can't go with the permanent tables to load the data OR a single job.

    They don't want to change the entire logic for all for now, instead they need options in which less changes are there. I researched it and create this solution for now. I am still searching the better way to call cmd commands from stored procedures.If you have any suggestions on any alternate approach, it will be very helpful.

    The above code is running totally fine until we need to store the output in any other table. We have tested this code in place of xp_cmdshell and it is completing in a sec. But as we put insert into it blocks and go to infinite loop.

    It would be helpful if you can suggest some alternate approaches for running cmdshell commands without the xp_cmdshell.

    Saturday, May 9, 2020 11:28 AM
  • Again, the alternative with SQL Agent is completely nuts. It is complex, brittle, and as you have found out it is not working. And it does not solve any security problems really.

    There is an alternative which is far more simpler and that is to use the CLR. But if you write a general CLR procedure that accepts a command text, you have only created your own xp_cmdshell and you have the same security issues. On the other hand, if you write a CLR procedure for each task then that procedure cannot be exploited to do something else (provided that you write it correctly).

    But if your organization has gotten a wakeup call on security, you need to tell them that there is no quick fix, but to address these problems a lot of this code has to be moved outside SQL Server. If they are not prepared to do that, they could just as well stay with xp_cmdshell.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Lily Lii Monday, May 11, 2020 8:30 AM
    Saturday, May 9, 2020 11:56 AM
  • For this one - it looks like all you are doing is getting a directory listing to be fed into a BULK INSERT process.  If that is all that is being done then you do not need xp_cmdshell at all - you can use xp_dirtree.

    Jeff, this is Vaibahv's general procedure. The BULK INSERT is there to read the output from the job step. Yikes!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Right - still comes down to the question of what they are doing with xp_cmdshell.  Regardless, this approach is a non-starter and yet the OP is still asking for a magic fix.


    Jeff Williams

    Saturday, May 9, 2020 3:42 PM
  • Hi VaibhavSQL,

    Do the answers above help you? If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 11, 2020 8:29 AM