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.
| Author |
Topic |
|
WalkerDA
Yak Posting Veteran
61 Posts |
 Posted - 2004-05-28 : 10:36:20
|
| I have no problems executing the procedure however the applications that the procedure calls seems to run in the background. For instance if the procedure calls Notepad, notepad, the process, runs though the app does not launch.Any Ideas?Derrick Walker |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-28 : 11:08:29
|
| Well, xp_cmdshell doesn't know how to handle interactive programs. Since you are running this in the SQL Server processing and memory space it doesn't have anyway to handle the calls you are making. This is a good way to bring down a SQL Server though. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-05-28 : 11:23:58
|
quote:
Originally posted by derrickleggett
Well, xp_cmdshell doesn't know how to handle interactive programs. Since you are running this in the SQL Server processing and memory space it doesn't have anyway to handle the calls you are making. This is a good way to bring down a SQL Server though. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
What I'm trying to do is to execute a script that creates a FileSystemObject that could go out and search for files, whether they are missing are not if so do something, for instance move them somewhere else, and so on. Any ideas?Derrick Walker |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-05-28 : 14:12:35
|
| You can use dir for this with the appropriate options. You will need to dump the results to a temp table and make decisions appropriately. You could also look at OA method.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-05-31 : 02:42:18
|
| You can use xp_getfiledetails to find out if a file exists or other information about it.Create table ##filedetails( alternatename char(20), size char(20), creation_date char(20), creation_time char(20), last_written_date char(20), last_written_time char(20), last_accessed_date char(20), last_accessed_time char(20), attributes char(20))INSERT ##filedetails EXEC master.dbo.xp_getfiledetails "\\server\share\path\file.name"select * from ##filedetails You might also check the MSSQL Service and check "Allow Service to interact with the desktop" under the logon options to help you debug the problem, but in general you probably shouldn't let a service popup applications or dialogs. |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-06-01 : 09:57:45
|
| Thanks for the info... What I needed SQL to do was to run external processes such as VBSripts. For the function that sopke of it seems that xp_getfiledetails will do nicely thanks! Will setting "Allow Service to interact with the desktop" work in executing these external Scripts?Where would I make this setting?Also what is a good article on CreateOA Method?Derrick Walker |
|
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-06-01 : 11:07:10
|
| You don't need to allow the service to interact with the desktop to run extended stored procedures. You would need to set it to run notepad, but there is little point in running something that you need to interact with in a SQL script. I only suggested it so you could debug whatever you were trying to run.Right click My Computer+Manage+Services and Applications+Services+MsSQL Server + Right Click+Properties+Logon+Allow Service To Interact With Desktop.Google for sp_oacreate and filesystemobject for some examples of using oacreate with the filesystem object.You might also look at Data Transformation Services+Local Packates in Enterprise Manager. You can create ActiveX scripts that run in SQL server. They are essentially VBScript with some restrictions. That would probably be better than shelling out and running scripts.If xp_cmdshell will not run your vbscripts they are probably prompting for something or throwing an error. Make sure you can run them interactively on the server with the login account SLQ Server runs as before trying to launch them with xp_cmdshell. |
|
|
|
|
|
|
|
|
xp_cmdshell