Exec xp_cmdshell blocking process

[KCWMS]

prod machine: Win2003 Server, Sql Server 2k SP1
dev machine: WinXP Pro, Sql Server 2K SP2

This issue only occurs on the prod machine... during user acceptance testing of course.

WHAT I HAVE DONE
1. I granted the right to run xp_cmdshell with the following (found by Googling "xp_cmdshell permission")

use master
go

xp_sqlagent_proxy_account N'SET'
                        , N'<mydomain>'
                        , N'<ntuser>'
                        , N'<ntuser's password>'
go

-- retrieve the proxy account to check that it's correct.
xp_sqlagent_proxy_account N'GET'
go

-- grant database access in master
sp_grantdbaccess 'LimitedUser'
go

grant exec on xp_cmdshell to LimitedUser
go

2. I then added the exec xp_cmdshell 'dtsrun...' command to the following trigger (very bottom)

ALTER     TRIGGER dbo.tr_gc_item ON dbo.gc_item
FOR INSERT, UPDATE 
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRANSACTION

	DECLARE @active_flag varchar(1)
	DECLARE @action_code varchar(1)

	SET	@action_code = 'C'  --Default To Change

	IF (   	UPDATE([gc_contract_name]) OR
		UPDATE([gc_contract_number]) OR 
		UPDATE([gc_contract_start_date]) OR 
		UPDATE([gc_contract_end_date]) OR 
		UPDATE([gc_contract_requote_date]) OR 
		UPDATE([gc_price_protection_date]) OR 
		UPDATE([gc_contract_tier]) OR 
		UPDATE([gc_contract_tier_price]) OR 
		UPDATE([gc_hipa_required]) OR 
		UPDATE([gc_date_signed]) OR 
		UPDATE([gc_exclude]) OR 
		UPDATE([gc_small_business]) OR 
		UPDATE([gc_large_business]) OR 
		UPDATE([gc_woman_owned]) OR 
		UPDATE([gc_minority_owned]) OR 
		UPDATE([gc_veteran_owned]) OR 
		UPDATE([gc_hub_zone])
	   )
	BEGIN
		INSERT INTO [ob_transaction_out](
			[ob_action], [ob_vendor_number], [ob_account_number], [ob_supplier_part_num], [ob_buyer_part_num], [ob_mfr_num],  
			[ob_global_catalog_num], [ob_unit_price], [ob_uom], [ob_item_description], [ob_gl_account], [ob_qom], [ob_mfr_name], 
			[ob_long_description], 
			[ob_catlevel1], [ob_catlevel2], [ob_catlevel3], [ob_catlevel4], 
			[ob_smartform_name], [ob_contract], [ob_contract_begin_date], [ob_contract_end_date])
		SELECT 	@action_code, gvi.m_vnd_code, gvi.m_vnd_account, gvi.m_vnd_catnum, i.m_item_no, i.m_mfrcatnum, 
			i.gc_item_id, CAST(i.m_uopprice as varchar(255)), i.m_uop, i.m_descr1, i.m_glcode, i.m_uopconv, i.m_mfrname, 
			i.ino_description, 
			isnull(substring(i.ino_unspsc_code, 1, 2) + substring(i.ino_unspsc_desc, 1, 255), 'NUI'),
			isnull(substring(i.ino_unspsc_code, 3, 2) + substring(i.ino_unspsc_desc, 1, 255), 'NUI'),
			isnull(substring(i.ino_unspsc_code, 5, 2) + substring(i.ino_unspsc_desc, 1, 255), 'NUI'),
			isnull(substring(i.ino_unspsc_code, 7, 2) + substring(i.ino_unspsc_desc, 1, 255), 'NUI'),
			NULL, i.gc_contract_number, i.gc_contract_start_date, i.gc_contract_end_date
		FROM 	inserted i LEFT OUTER JOIN gc_vnd_item gvi ON i.gc_item_id = gvi.gc_item_id LEFT OUTER JOIN gc_inv_item gii ON gii.gc_item_id = i.gc_item_id
		WHERE	gvi.m_vnd_primary_flag = 1
	END


	IF (@@ERROR <> 0)
	BEGIN
		ROLLBACK TRANSACTION
		RETURN
	END

	COMMIT TRANSACTION
	--run the DTS package
	EXEC [master].[dbo].[xp_cmdshell] 'dtsrun /S MyServer /U MyUser /P "MyUser Password" /N kevDTS '

END
SET NOCOUNT OFF

WHAT HAPPENS...
An ASP page executes dbo.sp_UPDATE_GC_ITEM (an SP) which makes an update to dbo.gc_item. Sql server reports that the process is blocking, that the last TSQL command batch was the above mentioned SP, and all activity comes to a halt. I have to manually kill the process or reboot the server.

Removing the exec master.dbo.xp_cmdshell line from the trigger allows the update to happen normally without any blocked processes.

Logging into QUERY ANALYZER as MyUser, I can execute EXEC [master].[dbo].[xp_cmdshell] 'dtsrun /S MyServer /U MyUser /P "MyUser Password" /N kevDTS ' without problems.

Thanks in advance

 

[NoCoolHandle]

First - I havn't used xpcmdshell for dtsrun so this might or might not be appropriate - but i do use it alot so it might... you will need to be the judge.

xp_cmdshell tends to hold things open if they don't close by themselves and due to the fact that it runs in the background, you will never see that it is waiting for userinput.

Bottom line is that for xp_cmdshell to exit properly what ever the process is that you are running, it needs to be able to exit by itself.

I dont' know if this is what you are experiencing, but in QA when you run the dtsrun command does the little play button stay red or does it turn green after the package has run.

try executing xp_cmdshell 'calc'

nothing seems to happen, but if you look in task manager you will see calc running.

Hope that helps..

Rob

 

[KCWMS]

NoCoolHandle,

The red button stays red, so clearly you are right about a proces being held open.

But I can copy and execute the exec xp_cmdshell manually from a window withou a problem.

Ahh to hell with it!!! I just gave up and coded this in my ASP page using File System Object and gave IUSR_XX rights to the destination folder. The DTS simply exported a table to a csv file anyway.

Since you use xp_cmdshell often how do you handle this?


This is why I like being the coder and have no desire to be a DBA.

 

[NoCoolHandle]

I must admit, I only use dts in a few situations and most of those run as tasks.. and all of those are nasty multistep purge and merge type actions..

<snip>Since you use xp_cmdshell often how do you handle this?</snip>

I try to find workarounds

a couple of thoughts...

1. If you are dumping to a textfile have you looked at BCP..
It does an execllent job of building asci files.

exec master.dbo.xp_cmdshell 'bcp "select firstname,lastname from northwind..employees" queryout "c:\employeelist.txt" -c -T -t,'

Where -c = Character Mode (ascii results)
-T = Trusted_Connection = yes
-t, = comma sepearated fields...

Produces a text file that contains the following

Nancy,Davolio
Andrew,Fuller
Janet,Leverling
Margaret,Peacock
Steven,Buchanan
Michael,Suyama
Robert,King
Laura,Callahan
Anne,Dodsworth
rob,lynch
Micky,mouse

2. This gets messy, but i do this alot from vb and I suspect that it would work fine here also.

Try executing bcp via a command window that closes after exectuion.

i.e xp_cmdshell 'CMD /C "dtsrun etc....."'

the /C tells the command window to exit after completion.. It might work but I suspect you are best with BCP.


Rob