0

I'm writing an application that requires a text file to automatically pop open (visible to the user client) after a sequence of database processes are completed. At the end of a SQL file, I have an xp_cmdshell command that reads:

exec xp_cmdshell 'C:\mattermark_etl_project\powershell "C:\mattermark_etl_project\open_file.ps1"'

which executes a powershell script that contains the following: 

C:\mattermark_etl_project\python.exe "C:\mattermark_etl_project\open_file.py"

The powershell script executes a python script which contains the following:

import os

def openFile():
    fileName = C:\mattermark_etl_project\company_data.txt(ACTIVE)
    os.system("C:\mattermark_etl_project\notepad.exe" + fileName)
openFile()

Can someone please help me understand why the text file isn't popping open? SSMS returns output stating the commands ran successfully with "null" results.

enter image description here

Dale K
  • 25,246
  • 15
  • 42
  • 71
emalcolmb
  • 1,585
  • 4
  • 18
  • 43
  • The `xp_cmdshell` command,' PowerShell script, Python script, and notepad all run on the SQL Server with no UI. If you look at running tasks on the SQL machine, you'll probably see notepad.exe running with no user to see it or close the app. – Dan Guzman Jul 20 '19 at 10:32
  • I dont believe thats correct. Os.system is supposed to open the file similarly to how a user would open it visibly opening a window. – emalcolmb Jul 20 '19 at 14:29
  • SQL Server runs as a service without an interactive user or desktop, as do the spawned processes. I think your question is a [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). – Dan Guzman Jul 20 '19 at 14:43
  • Ok, I'm guessing that means I'm asking the wrong question -- that any external file opening processes triggered by SQL Server's xp_cmdshell will not be visible to the user? Do you have any specific workaround suggestions for how to spawn such a process that is triggered only after certain database processes finish? – emalcolmb Jul 20 '19 at 15:07
  • The UI needs to run on the client machine. The app process running there could use a polling or notification method to determine when the process is complete so the results can be fetched and rendered. – Dan Guzman Jul 20 '19 at 15:46

0 Answers0