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.

Requirement: eOne SmartConnect must be installed on the SQL Server machine. If it isn’t installed on the server, the required RunMapConsole.exe cannot be accessed through the SQL command.

Here, the statement below enables the xp_cmdshell command within SQL Server. This is disabled by default, but we need it to call our .exe file.

--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

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\eOne Solutions\SmartConnect\eOne.SmartConnect.RunMapConsole.exe" TEST_MAP', no_output;

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 from sql rather than trying to do it through the interface/smartconnect UI.

You can run the statement below against your SmartConnect database. You’ll notice the password field is left blank—this is intentional, as there’s no need to set an encrypted password here.

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

INSERT INTO [dbo].[User] ([UserId],[AdUser],[AdUserName],[LoginName],[LoginPassword],[Email],[ChangePassAtLogon],[LoginLockedDateTime],[IsActive],[Password],[IsAdmin], [FirstName],[LastName])
VALUES (NEWID(), ltrim(trim(@sqlServiceAcct)), ltrim(rtrim(@sqlServiceAcct)),'','','',0,'1900-01-01 00:00:00.000',0,'',1,'SQL','Service')

Once that statement has been executed, we can run any map we want from inside SQL Server using the command listed below.

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 Solutions\SmartConnect\eOne.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 a question? Please reach out to us at support@eonesolutions.com