I have a stored procedure which launches SSIS reports using xp_cmdshell and dtexec. The stored procedure was working fine for months until this weekend. Starting today, the development server stopped executing the command correctly. The production server is still working fine. This leads me to believe the login information used for the xp_cmdshell proxy setup is still working. We tested the SSIS packages on the server and they ran fine. The DBA logged in to his account in SSMS and ran the dtexec command and got results. However, when we run it under the web applications database credentials it returns nothing. DECLARE @Command varchar(4000) SET @Command = 'dtexec ...' EXEC master..xp_cmdshell @Command This should at least return results similar to the following: Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. ... Instead it just says NULL. Obviously the issue is permissions since one account runs the command and sees results while the other gets nothing. The account showing nothing has db_owner rights, more than the working production environment. Anyone have any ideas where to start debugging this issue?

Hi David DeLella, Is there anything changed on your application? Such as the connection string, SSIS package location and file name. Is there any error reported by the result? You may have a try to change the credential for the xp_cmdshell command on your stored procedure to see if it works. For SQL Server authentication, you can specify the /User, /Password, and /Server options together with the /SQL option. To debug the stored procedure, you can also make use of error event code and dump the files. For more information, please refer to /Dump error code and /DumpOnError option in this article: dtexec Utility. TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here. Stephanie Lv TechNet Community Support

No changes to the web application, packages, and there was no results returned. I tried the /Dump switch and I already use the /U /P /SER and /SQ options. Doing some more Googling the most common solutions for this issue are resources being taken and anti-virus software. I will have our database team investigate both options. I did try a simple command like EXEC master..xp_cmdshell 'echo Hello' and it did not return anything.

first you have to divide your problem in to two steps open cmd prompt on the dev server copy the dtexec command including package location, and just run the DTexec command , and see if you get any verbose that way you will know that there nothing wrong with the pacakge or Dtexec or SSIS engine, and what is the version SQl server are you using , Make sure that you do not have two versions of SQl server instances on the same dev box web applications database credentials are these windows authentication or sql server authentication if it is sql server authentication , make sure user has the proper credentials to access the packages see below make sure you use (web applications database credentials) when you run the below command you should get an output like below EXEC master..xp_cmdshell 'dir *.*'

1. We tested the package directly on the server an it ran successfully 2. We know the authentication is good because our development machine is using the same as production and production is still working 3. We know the dtexec command works because when the DBA runs it under his SQL account it works perfect, just not for my SQL account 4. I attempted a basic echo command in xp_cmdshell and it still returned NULL 5. There is only one instance of SQL Server on the machine in question The authentication is SQL server through a proxy which is a Windows AD user with full database rights.

Me and another database guy were able to figure out the problem today but we don't understand the solution. Both the development and production database servers are running a SQL and SSRS instance. The SSRS instance runs under the global network account with sysadmin rights. The global network account is also used as the proxy for the xp_cmdshell we are attempting to run. When the database guy stops the SSRS service the command runs. When it starts it no longer works and always returns null. The weird part is, this configuration is nothing new and is the same with both environments. Maybe this will help narrow down the issue but we are at a loss.

Well the issue is back again after 3 months. This time we have some more specific details. The issue occurred right after one of our reports failed. It was running fine in the morning and we made some changes to a report. We executed the stored procedure which runs the reports through xp_cmdshell. The report (SSIS package) appeared to be in an infinite loop. We used the stop stored procedure button in SSMS. What this did however was leave the SSIS executing, lock a file on our network drive in the name of the proxy account used by xp_cmdshell. We were able to stop all processes and restart the SQL Server services. We also got the network team to remove the file lock and delete the file. However the xp_cmdshell is still not working again. Any ideas? The same side effects still apply, stopping any other SQL Server service allows the command to work again. It is almost like the SQL Server services have a collective resource pool and it has been maxed out by the lock and unless we stop another resource we can no longer use the proxy account for xp_cmdshell. Unfortunately the guy who fixed the issue last time is no longer here and we can't find any record of what he did.

Hi David, DTEXEC.exe continuing to run after cancelling the query in SSMS is normal. To stop a package executed by xp_cmdshell you have to end task dtexec.exe. xp_cmdshell executes the command using the credentials of the sql server service account. The exception is when you specifically set a proxy account using sp_xp_cmdshell_proxy_account system procedure. http://msdn.microsoft.com/en-us/library/ms190359.aspx Any chance your guy did that and the account he used is having permission problems? You could try setting the proxy account to NULL like so: EXEC sp_xp_cmdshell_proxy_account NULL You could try changing the sql server service to use a domain account instead of built-in network service account and one that is different than the SSRS service account. What SSRS has to do with any of this is definately a mystery. I've never experienced the NULL result. Perhaps take note of whether other DTEXEC.exe processes are running at the time you execute. As for diffs between your prod and dev servers, perhaps check the path environment variable which tells the command which dtexec to run. There are two on a x64 machine. http://msdn.microsoft.com/en-us/library/ms162810(SQL.105).aspx Maybe one server is running 64bit dtexec and the other is running 32bit? Asside from all this, I'm sure you know you need to resolve what's making the package hang. That's probably another thread, but do you have any logging in the package? HTH, Josh

We have killed all locks to any files used by the SSIS job. We killed all instances of the DTEXEC process. We restarted all SQL Server services. We use a proxy account and that account is not experiencing permission issues. I will look into the environment variable issue but the fact that it would change all of a sudden in the morning right after running the report that had the issue it is unlikely that that is the cause. We are also going to force a server reboot and see if that will clear it up.