Running the Mathematica script in SQL Server

Question

Hi i am trying to learn mathematica.

I am following instruction in one of the questions asked before and did the following:

In SQL Server a stored procedure is created as follows:

CREATE PROCEDURE dbo.math_execute_external_script
    @scriptName [varchar](1000)
WITH EXECUTE AS CALLER
AS
BEGIN
    Declare @command varchar(1000)  
    set @command='"C:\Users\atfai\OneDrive\Documents\Visual Studio 2015\Projects\rproject1\rproject1\' + @scriptName + '"'
    Exec Master..xp_Cmdshell @command
END

I am executing the file as follows

EXECUTE dbo.math_execute_external_script 'mathematicaScript.bat'

The batch file contain the following

@echo off
setlocal
PATH = C:\Program Files\Wolfram Research\Mathematica\10.4;%PATH%
start MathKernel -script "C:\Users\atfai\WolframWorkspaces\Base\MathematicaProject1\MathematicaProject1.m"
endlocal

MathematicaProject1.m contains 1+1

now when i execute i get NULL in sql server

Can someone explain how to get the answer 2 in sql server?

Answer 1

first of all make sure to enable xp_cmdshell. Next is that your SQL Server Service Account needs to have access permission to the file you are trying to run. As this is located in C:\Users probably this is not the case. I would recommend creating a seperate directory on your data drive and giving the SQL Server service Account read and write access to it.

Last of all: What are you trying to accomplish here? Generally speaking activating xp_cmdshell can be considered a security risk. You are opening up executing all sorts of command line calls from SQL...just think that a hacker could format your disk drives over SQL injection. I admit that this discussion is controversial and there are also voices that say it boils down to security settings. I personally would prefer to write a powershell or cmd script executing mathematica and writing the result into a SQL table. This script can be scheduled in SQL Server Agent. Alternatively you can run your mathematica script from the agent in a commandline task.

Best regards

Martin