SQL Strange Timeout Issue
Ok, so I am trying to create a procedure that calls an extended procedure. I am getting the following error:
Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
Even with the simplest test I get this error:
CREATE PROCEDURE Test
AS
BEGIN
EXEC xp_cmdshell 'dir *.exe'
END
However, if I just run it xp_cmdshell 'dir *.exe'
myself, it works.
This runs on a clustered SQL 2005 server. Any help is appreciated.
a source to share
Are you getting an error when creating a procedure or when EXECINGING it?
If you get it while executing it, then there may be permission issues. Since in later versions the caller SQL is xp_cmdshell
heavily blocked and is not enabled by default in the surface configuration, I could see the potential for this.
What do you call SP? If you are using dynamic SQL, you know that it is executed with the privileges of the caller, not the SP creator.
Do you need a SP prefix with a master like in master.dbo.xp_cmdshell
?
Have you tried EXECUTE AS
either in building the SP or doing the SP?
Also, in my experience, transport layer errors are sometimes due to a problem with the server, but the problem is with the client caching its connection object for the time the server has deleted it (thus invalid for the connection object). Given that you are supposedly running this in the short initial connection time, it looks like this is not a problem, but you might have some kind of proxy / network filter issue that is dropping packets due to certain keywords being detected in the packets? I know this is a long shot, but I had to ask ...
a source to share