Tech Tuesday: Executing SmartConnect 2018 Maps from SQL

There are many ways to execute a SmartConnect map; through the interface, scheduled, called by the web service, etc. But one method that also works which isn’t talked about as much is executing a map directly from SQL. The SQL code below covers both the initial setup needed to run maps in this manner as well as the actual call to execute them.

The following instructions only work with SmartConnect 2018 and below, they do not work with SmartConnect 21 and above. 

First we need to make sure that SQL can run the command that is needed and that it will pass SmartConnect security. In the code below, the only value that should need to be changed is @sqlServiceAcct. This should be set to the account that is running the actual SQL Server windows service. This is needed as any map executed directly from SQL is running under that account and it needs to be in SmartConnect security – it normally isn’t a standard domain account so we add it through here rather than trying to do it through the interface. The rest of the statement enables the xp_cmdshell command within SQL Server. This is disabled by default, but we need it to call our .exe file.

  -- Set the parameter to the account running the SQL Server Windows Service
DECLARE @sqlServiceAcct Char(100)
SELECT @sqlServiceAcct = 'NT ServiceMSSQL$EONE'

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
--To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Once that statement has been executed, we can run any map we want from inside of SQL server using the command listed below. One requirement for this to work is that SmartConnect has to be installed on the SQL Server machine. If SmartConnect isn’t installed on the server then we can’t access the needed .exe from our SQL command. The only value that would change in the following statement is the Map ID, in this example I’m calling a map called TEST_MAP and that should be changed to whatever ID is needed (The path would also be changed if SmartConnect was installed somewhere other than the default location).

exec master..xp_cmdshell '"C:Program Files (x86)eOne SolutionsSmartConnecteOne.SmartConnect.RunMapConsole.exe" TEST_MAP', no_output;

That command calls the RunMapConsole.exe file and passes in the Map ID that we want to run. The map will be executed with its data source and setup from SmartConnect. The no_output command is added to the call as this .exe shows a lot of unneeded SQL statements while it is running. One thing that the .exe does not do is report the result of the actual map run. There will need to be a process for logging errors and/or checking the results through the error tables in SQL or the event viewer to determine if the map completed or not when using this method.

Have any questions on SmartConnect? Leave a comment below or email sales@eonesolutions.com!