xp_cmdshell Runs the Process not the Application

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xp_cmdshell Runs the Process not the Application

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-02 : 07:19:01

Derrick writes "I am running

2000 Adv. Server - SQL 2000 Ent.
2000 Server - SQL 7 Standard
XP Pro - SQL 2000 Developer
Win98 - SQL 7 Developer

I know how to use xp_cmdshell though the problem that I am experiencing is that it executes in the background so to speak. It executes, for instance master..xp_cmdshell 'c:\windows\notepad' will execute and run at least as a process (NOTEPAD.exe) anyway as I can see it in Task Manager under processes, though the application never launches or shows in the application section of task manager as it should. In my Win98 edition it runs perfectly, though only in my Wib98 Edition. I had assumed that there was a setting off somewhere but could not find that to be true. It is not a permissions problem as I have checked that out as well. I really need to get it going for a project and I am not sure where else to turn. So I am hopeful that you can offer some end to my dismay!"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-02 : 07:40:06

You should not run apps like notepad from xp_cmdshell - this is designed to run batch-type processes not processes that require user interaction.

Opening notepad type apps should be handled on the client side.

Duane.

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-02 : 10:21:35

I am not running Notepad.exe type of programs, that was used as an example, I need to execute other processes such as VBScript Code to do various things, though the execution of a simple batch process or many other things are not functioning properly.

Derrick Walker

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-02 : 10:29:44

If it's not interactive, you shouldn't have any problems running anything else. The account that runs SQL Server needs to have the appropriate permissions to execute the program though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-07 : 15:01:03

Sounds good but still the below script does not run, there is no user intervention needed. Again, on my laptop, this runs, on the server or my development box it doesn't.

master..xp_cmdshell 'C:\copy_procedures.vbs'

Derrick Walker

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 15:05:34

Could you post the code? We could try it out on our machines.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 18:01:03

The code is user interactive, so you will not be able to run it using xp_cmdshell. You will need to make the code non interactive in order to use xp_cmdshell.

Also, are you using the Local System Account for the MSSQLSERVER service? If so, you'll need to change that. You'll want to use a domain account that has local admin privileges.

Tara

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-07 : 18:02:34

Here is a simple example. I haven't designed a production version of what will actually be needed because I'm not that far into the project yet, though I will have to deal with reaching out to ftp servers and such. Any ideas would be very helpful!

Dim CreateFolderNow
Dim fObject
Dim f
Dim File
Dim whereto
Dim fpath
Dim fcount
Dim Folder
Dim fc
Dim f1
Dim f2
Dim msg
Dim ans
Dim create_ans

fcount = 0
whereto = "Z:\pcfinance\Other\CASH\NFM\Architecture\SQLScripts\"

logFile = "C:\copylog\copylog.log"
errorFile = "C:\copylog\copylog.err"

Set fObject = CreateObject("Scripting.FileSystemObject")

fldr = "C:\copylog\"

If Not (fObject.FolderExists(fldr)) Then

Call create_folder
End If
End If

Call copy_my_files

Function create_folder
Set f = fObject.CreateFolder(fldr)
create_folder = f.Path
End Function

Function copy_my_files

Set Folder = fObject.GetFolder("C:\Accenture Projects\Verizon\SQL_FILES\nfm\")
Set fc = Folder.Files

For Each f1 In fc

Set f2 = CreateObject("Scripting.FileSystemObject")
Set File = fObject.GetFile(f1)

If ((InStr(1, File.Name, ".sql") > 0)) Then

If DateDiff("d", File.DateLastModified, Date) <= 1 Then
fpath = Folder & "\" & File.Name
fObject.copyfile fpath, whereto, True

'Const ForReading = 1, ForWriting = 2, ForAppending = 8

Set f = fObject.OpenTextFile(logFile, 8, True)
f.Write File.Name & " " & Now() & VbCrLf
f.Close

fcount = fcount + 1

End If

End If

Next

End Function

Derrick Walker

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-07 : 18:08:18

So now your code is not user interactive, but it is referring to a Z drive, which is probably a mapped drive. The account that runs under the MSSQLSERVER service needs to have access to that mapped drive. Does it? See my last post about not using the local system account as well.

Tara

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-07 : 18:21:14

Yes, it is running under a user account that has admin priveldges. What happens is that the process just runs and basically never stops until I go into Taskman and kill it.

Derrick Walker

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-06-07 : 18:25:26

As Tara asked, is Z: mapped? Can you run

exec master..xp_cmdshell 'dir Z:\pcfinance\Other\CASH\NFM\Architecture\SQLScripts\'

If not, try changing Z:\ to \\servername\share

WalkerDA
Yak Posting Veteran

61 Posts

Posted - 2004-06-07 : 18:44:58

I changed the path to servername and it worked... Thanks!

Derrick Walker

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources