February 21, 2017 at 2:59 pm
I have a agent task that calls a SSIS routine and in that routine is a stored proc call.. Sometime is runs fine, other times it fails. about 40/60.
I get the same result with just running the proc inside of SSMS.
I get ..failed with the following error... "An error occured during the execution of xp_cmdshell. A call to 'CreateProcess' failed with error code 5. Possible failure reasons: Problems with the query "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly"
Usually this is a permissions issues, but no account changes have been made and as stated it does run cleanly sometimes.
I also noticed that when the proc runs it seems to lock many tables.. which is odd because the proc has several begin tran..comit tran wrappers..
I am on SQL Server 13.0,4001,0
I did have an odd log table blow out at some point also.. I have corrected that and also put DB in simple mode..
I can't seem to find the issue..
Suggestions?
-
February 21, 2017 at 3:08 pm
What exactly is the the call to xp_cmdshell doing? And are you by any chance getting any errors in the windows logs at the time?
February 21, 2017 at 3:24 pm
Sue
February 22, 2017 at 6:56 am
There are three xp_cmd's
EXEC master..xp_dirtree @Directory, 10, 1
EXEC master.dbo.xp_cmdshell @Cmd; (@cmd does a rename)
EXEC master.dbo.xp_cmdshell @Cmd; (@cmd does a file copy)
I added a lot on logging to everything.. hopefully that will help me nail it down.. but it seems the PROCESS is failing to create..
I see nothing in system logs:
The Windows Error Reporting Service service entered the stopped state.
The Application Experience service entered the stopped state.
seems like these services stop and start alot.. I will look into why.. seems of no value.. no errors in the system logs
however I do see a CMD error in the app logs:
Faulting application name: cmd.exe, version: 6.3.9600.17415, time stamp: 0x545042b1
Faulting module name: KERNELBASE.dll, version: 6.3.9600.18438, time stamp: 0x57ae642e
Exception code: 0xc0000142
Fault offset: 0x00000000000ecdd0
Faulting process id: 0x28dc
Faulting application start time: 0x01d28d1115d2c64d
Faulting application path: C:\Windows\system32\cmd.exe
Faulting module path: KERNELBASE.dll
Report Id: 54c724e9-f904-11e6-80ca-00155d021507
Faulting package full name:
Faulting package-relative application ID:
No idea what is causing that.. hopefully the logging will tell me what command was running while it failed.. there is plenty of RAM and storage..
-Ken
February 22, 2017 at 7:32 am
I'd definitely consider moving any file or renaming operations into SSIS and not use TSQL to do it. better error handling occurs there.
i'd think it might be a concurrency issue: a procedure can be called multiple times, in the same . if you don't prevent that, you've got two processes trying to write/copy/create identical files form the cmdline.
so if my call to the proc renames a file to a specific filename, and another proc tries to do the same, or the file already exists prior to the nameing operation......error
i would at least consider capturing the xp_cmdshell output so you can see what the issue really is.DECLARE @Results table(
ID int identity(1,1) NOT NULL,
TheOutput varchar(1000))
BEGIN TRY
--other proc logic goes here, where it assigns @cmd
insert into @Results (TheOutput)
EXEC master..xp_dirtree @Directory, 10, 1
insert into @Results (TheOutput)
EXEC master.dbo.xp_cmdshell @Cmd;-- (@cmd does a rename)
insert into @Results (TheOutput)
EXEC master.dbo.xp_cmdshell @Cmd; --(@cmd does a file copy)
END TRY
BEGIN CATCH
--insert this text into a logging table or something, due to error?
select * from @Results
END CATCH
Lowell
February 22, 2017 at 7:39 am
Error code 5 usually means 'Access is denied'. Check the permissions of the account under which SQL Server Agent is running the job.
February 22, 2017 at 7:53 am
ktl - Wednesday, February 22, 2017 6:56 AMThere are three xp_cmd'sEXEC master..xp_dirtree @Directory, 10, 1
EXEC master.dbo.xp_cmdshell @Cmd; (@cmd does a rename)
EXEC master.dbo.xp_cmdshell @Cmd; (@cmd does a file copy)I added a lot on logging to everything.. hopefully that will help me nail it down.. but it seems the PROCESS is failing to create..
I see nothing in system logs:The Windows Error Reporting Service service entered the stopped state.
The Application Experience service entered the stopped state.seems like these services stop and start alot.. I will look into why.. seems of no value.. no errors in the system logs
however I do see a CMD error in the app logs:
Faulting application name: cmd.exe, version: 6.3.9600.17415, time stamp: 0x545042b1
Faulting module name: KERNELBASE.dll, version: 6.3.9600.18438, time stamp: 0x57ae642e
Exception code: 0xc0000142
Fault offset: 0x00000000000ecdd0
Faulting process id: 0x28dc
Faulting application start time: 0x01d28d1115d2c64d
Faulting application path: C:\Windows\system32\cmd.exe
Faulting module path: KERNELBASE.dll
Report Id: 54c724e9-f904-11e6-80ca-00155d021507
Faulting package full name:
Faulting package-relative application ID:No idea what is causing that.. hopefully the logging will tell me what command was running while it failed.. there is plenty of RAM and storage..
-Ken
0xc0000142 is DLL failed to initialize. So it may be something more related to the Windows Server rather than SQL Server. There are quite a few articles related to that error and several different explanations such as missing or damaged files, running out of desktop heap memory, profiles becoming corrupt. You may want to search on that particular error (0xc0000142 and Kernelbase.dll) as there are several different suggestions to troubleshoot the error. Most of the articles I read did mention running system file checker to check for missing or damaged files so that may be worth considering but you'd likely need to do that during a maintenance window.
With getting the earlier reported error of access denied and with it being sporadic makes me wonder about the profile. Depends on how the command is running but if it's via a sysadmin account, xp_cmdshell will run under the SQL Server service account.
Not sure of any decent tools to check the heap memory - used to be one for earlier versions of Windows but nothing anymore. Maybe one of the sysinternal tools.
Sue
February 23, 2017 at 5:48 am
Thank you for the replies...
I have decided to built a new VM with OUT the 2012 base OS... I hate it..
IT has bothered me that when I run this sproc the database hangs.. I can't run any other query..
The sproc is large but not crazy.. does basic inserts / updates in transactions and calls a few other sprocs at the end..
There is no logical reason this would cause the locking it does... Even getting database properties is frozen while it is running.. it does not make sense.
The whole database is just to load a file, do some basic processing and create text files to load into another server running magento (ecomm) is is basic in my world..
I have a new server up, latest SQL installed on it.. I will import the database and move the SSIS code..
Come to think of it.. I am not sure how to move all of the SQL Server Agent Jobs.. I think you can script them right? I will look..
I am very curious to see what this does..
and to answer another question.. I have separate user account running SQL, SSIS, and Agent all within the local admin group... the server is NOT domain joined. So it is NOT a permissions thing.. since it does run cleanly sometimes.
November 28, 2017 at 1:01 pm
Hello SSC-Enthusiastic,
Did you ever find a resolution for this issue? I am experiencing the same thing now on multiple systems.
Thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply