January 22, 2008 at 3:58 am
Hi there,
I am currently deploying a stored proc on the master database on one of our servers.
The stored proc deployed fine, but when I add the following command:
EXEC master.dbo.xp_cmdshell @cmd in the proc, the altering of the proc takes quite long and then afterwards gives me the following error:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
Can anybody please help me with understanding this problem. Deployment on other servers works fine. What do I need to check for?
Regards
IC
January 22, 2008 at 9:39 am
That is very strange. If you create a new proc, same code, including the cmdshell, does it work?
January 23, 2008 at 3:15 am
Yes, it works on databases on other servers but not in this particular one.
January 23, 2008 at 4:45 am
probably access via cmdshell is prohibited.
I've seen installations that raise a system error and log it into an audit log.
Contact the sysadmin(s).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 23, 2008 at 8:02 pm
The following link may help.
January 24, 2008 at 4:28 am
Verify via the SQL Server Surface Area Configuration Tool that you have xp_cmdshell enabled on this server. Usually when something takes that long before returning an error, it's stuck check to see if it can "communicate" with that particular command.
January 25, 2008 at 12:16 am
-if the cmdshell hadn't been actived, the exec would never work.
- put a print statement right befor the exec statement
print 'cmd: ' + @cmd
this way you'd actualy see what's being presented for the cmdshell
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 25, 2008 at 6:10 am
Imke Cronje (1/23/2008)
Yes, it works on databases on other servers but not in this particular one.
I presume you mean that the code you provided works on other servers but not this server. Does it work on other databases on this server?
At any rate, as other people have said, the cmdshell option is probably disabled on this server. I have used the following code to temporarily enable it then set it back to its original value when done. It does require sa privileges. If cmdshell is enabled to begin with it does a little processing that it doesn't have to, but I find that's better than code failing.
EDIT: WARNING - use this only when performing upgrades to the database, not in daily processing. I would consider that to be a dangerous practice. If the cmdshell is required for daily processing then review it with the server administrator and enable it.
2nd EDIT (I'll get my act together eventually :blush:): Wrap the code that peforms the actions you need to take in a Try...Catch block to ensure that failure does not leave the cmdshell in an undesirable state. What I usually do is run the code that sets cmdshell in one batch or script, the code I need to run in a different batch or script, then the code that resets cmdshell in yet another batch or script. That way I ensure that the cmdshell is reset no matter what happens to the code.
Enable the cmdshell:
-- Get configuration
select name, value, value_in_use
into original_config
from sys.configurations
where name = 'show advanced options'
or name = 'xp_cmdshell';
declare @advanced_config int;
declare @cmdshell_config int;
select @advanced_config = cast(value_in_use as int)
from original_config
where name = 'show advanced options';
select @cmdshell_config = cast(value_in_use as int)
from original_config
where name = 'xp_cmdshell';
--If xp_cmdshell is turned off, turn it on.
if @cmdshell_config = 0
begin
if @advanced_config = 0
begin
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
end
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
end
(do the work that requires cmdshell here)
Set cmdshell status to its original value:
declare @advanced_current_config int;
declare @cmdshell_current_config int;
-- Get current configuration
select @advanced_current_config = cast(value_in_use as int)
from sys.configurations
where name = 'show advanced options';
select @cmdshell_current_config = cast(value_in_use as int)
from sys.configurations
where name = 'xp_cmdshell';
-- Get original configuration
declare @advanced_original_config int;
declare @cmdshell_original_config int;
select @advanced_original_config = cast(value_in_use as int)
from original_config
where name = 'show advanced options';
select @cmdshell_original_config = cast(value_in_use as int)
from original_config
where name = 'xp_cmdshell';
if @cmdshell_original_config <> @cmdshell_current_config
begin
EXEC sp_configure 'xp_cmdshell', @cmdshell_original_config;
RECONFIGURE;
end
if @advanced_original_config <> @advanced_current_config
begin
EXEC sp_configure 'show advanced options', @advanced_original_config;
RECONFIGURE;
end
drop table original_config;
go
January 25, 2008 at 1:49 pm
I know you said altering, but I am assuming you meant executing.
What is the command that you are trying to run? The reason I ask is because it sounds like the issue is a UNC that isn't accessable from the machine that you are trying to run the command.
Something like \\MyPersonalPC\C$\tmp
Realize that all commands run as the user that owns the service. It is possible that all of your other systems are installed as a domain account, but that one is as local service or something.
July 18, 2012 at 6:30 am
Did anyone find a solution to this?
July 18, 2012 at 7:26 am
UnicornsRreal (7/18/2012)
Did anyone find a solution to this?
Start a new thread with all relevant details (including the answers to the questions on this thread). You'll get more hits that way. There are a lot of Threadzians who look at posts that haven't been answered before they look at old posts.
February 19, 2019 at 8:44 am
If anyone still cares BTW I came across this today; the ALTER or CREATE PROC statements don't work from remote SSMS. Run them directly on the server and you're OK.
February 19, 2019 at 9:35 am
call.copse - Tuesday, February 19, 2019 8:44 AMIf anyone still cares BTW I came across this today; the ALTER or CREATE PROC statements don't work from remote SSMS. Run them directly on the server and you're OK.
Can you please post a link to the source of this information?
April 1, 2019 at 9:06 am
<strong class="d4pbbc-bold">call.copse - Tuesday, February 19, 2019 8:44 AMIf anyone still cares BTW I came across this today; the ALTER or CREATE PROC statements don't work from remote SSMS. Run them directly on the server and you're OK.Can you please post a link to the source of this information?
Just personal experience, which makes it not definitive of course, simply the symptoms were the same and that's what I found worked.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply