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 Service\MSSQL$EONE'
INSERT INTO [dbo].[User] ([UserId],[AdUser],[AdUserName],[LoginName],[LoginPassword],[Email],[ChangePassAtLogon],[LoginLockedDateTime],[IsActive],[Password],[IsAdmin], [FirstName],[LastName])
VALUES (NEWID(), @sqlServiceAcct, @sqlServiceAcct,'','','',0,'1900-01-01 00:00:00.000',1,'',1,'SQL','Service')
-- 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 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 any questions on SmartConnect? Leave a comment below or email sales@eonesolutions.com!
Hi,
I want to start a map from SQL and I received this error, can you help me on this ?
ERROR [HY000] [Microsoft] [ODBC Excel Driver] General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x27a4…
MSL
Hi Marie,
I briefly discussed your question with the technical team, sound like you probably don’t have any/or the correct excel driver installed on the machine where SQL Server is. If you are still running into any issues after trying that. Feel free to open up a support case.
I have so much trouble with schedules via the interface, but this method works GREAT! I’m going to use it as my default scheduling method from now on.
Thanks, Chris!
This doesn’t work for me. It says the query is successful but the map doesn’t run. Any ideas?
+1 to TPetersen. Could it be an issue with running this in SC 2016.
In an effort to find a solution for ending a map if the source doesn’t contain data (without generating an error in the Event Viewer), I thought I could use this as a solution.
The real solution would be to stop processing the map if no data was returned without generating an error in the event logs.
Do you have a newer version of this? When I try running it I get “Column name or number of supplied values does not match table definition”
Thanks
Late to the party but I would be interested in knowing if this approach might be appropriate if I need to conditionally run a map. My use case is Map A runs and may or may not raise a flag. If the flag is raised then run Map B. If not raised then run Map C. In either case I need to run Map C afterwards. If Map B is called, I need it to finish processing before calling Map C.
Thanks
I am REALLY late to the party, but I need to know if I can do this if SmartConnect is installed on a different server. If this method won’t work, is there anything that will?
Thanks
I was hoping you may have the most recent update. Whenever I attempt to execute it, I receive the error message “Column name or number of given values does not match table specification.”