Page 1 of 1
Exec SQL Server Jobs?
Posted: Thu May 17, 2012 7:29 pm
by schlemiel29
Is it possible to execute a bunch of sql server jobs from TM1?
I think I can create a button connect to an TI process. In the Prolog I could call the sql server to execute all needed jobs before importing data.
But how should the code in the prolog look like? Any ideas?
Re: Exec SQL Server Jobs?
Posted: Thu May 17, 2012 7:48 pm
by tomok
Don't know what you mean by SQL "jobs". You can execute SQL stored procedures by just issuing the SQL against an open ODBC connection. If you mean some sort of WSH batch file, you can put the commands in a batch file which can be called from a TI process. Rather than re-create the wheel here just do a search in this site for batch files and TI. There are plenty of examples.
Re: Exec SQL Server Jobs?
Posted: Fri May 18, 2012 12:59 am
by winsonlee
Code: Select all
sqlcmd.exe
[{ { -U login_id [ -P password ] } | –E }]
[-S server_name [ \ instance_name ] ]
-Q"execute msdb.dbo.sp_startjob @job_name = 'PREPROD_ORA_POPULATE'"
You need to have the above code put in a batch file and run the batch file via the ExecuteCommand in the prolog.
Re: Exec SQL Server Jobs?
Posted: Fri May 18, 2012 6:20 am
by rmackenzie
schlemiel29 wrote:Is it possible to execute a bunch of sql server jobs from TM1?
I'm not sure whether tomok meant using ODBCOutput or the actual query in an ODBC TI. However, you can execute a stored procedure from the ODBCOutput command or the TI SQL query. For the former, check the
ODBC functions
schlemiel29 wrote:I think I can create a button connect to an TI process. In the Prolog I could call the sql server to execute all needed jobs before importing data.
How would you account for any timing where you would need to wait for a stored procedure to complete, before you could import the data? If possible, you might want to rethink your strategy of having the call to the database initiated by the user, from an action button.
Re: Exec SQL Server Jobs?
Posted: Fri May 18, 2012 9:24 am
by schlemiel29
I'm new to SQl Server. In MS Access there are macros, which bundles queries to create tables, add data, delete data ... If these are similar to stored procedures, called by batch files, then yes this is a solution.
ExecuteCommand was named to do the job. And the last parameter is called "Wait" (got it from tm1 documentation). If i set this to 1, the process should return after completion. Isn't it?
So I could use this batch file with ExecuteCommand in the prolog. Or I'm wrong?
Re: Exec SQL Server Jobs?
Posted: Mon May 20, 2013 2:48 pm
by AlexanderZ
There is another way to execute stored proc!
Just write on Prolog tab:
Code: Select all
ODBCOpen('sql_server', 'login', 'password');
ODBCOutput('sql_server', 'sqlquery');
ODBCClose('sql_server');