Exec SQL Server Jobs?

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

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

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

[rmackenzie]

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

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.

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

[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');