Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ArviL
Starting Member
25 Posts |
 Posted - 2010-09-14 : 03:48:58
|
| HiI have 2 different servers. On one of them is installed 654bit TS 2005 (referred as TS1), and on another is installed 32bit TS 2005 Express (TS2). TS2 is defined as linked server in TS1.In TS2 I have a couple of databases (DB1, DB2, ...). In those databases I have according stored procedures (SP1, SP2, ...) - one for each database. (SP's will refresh data in databases tables based on various 3rd party applications not accessible from 64bit terminal server.)I want to create a job in TS1, which executes all those stored procedures in TS2. It looks like there are 2 ways to do it.1) I create a stored procedure in TS1 (SP_Main), which executes stored procedures in TS1, and create a single-step job, which executes SP_Main;2) I create a multi-step job in TS1 where every step executes some SP from TS2 directly (I hope it is possible - I haven't checked it jet).What is the best practice for such task?Thanks in advance!Arvi Laanemets |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-14 : 04:03:56
|
I favour (2).The trouble with (1) is when it breaks you have to go to TS2 to investigate why. Any logging data created by SP_Main on TS2 is local to TS2 (doesn't have to be, but you are making life hard if you try to use a "TS2 SP_Main" approach and put the logging data backup onto TS1 !!)With (2) you can:INSERT INTO TS1_Log VALUES('About to call TS2 DB1 SP1', NULL)EXEC @intErrNo = TS2.DB1.SP1INSERT INTO TS1_Log VALUES('Call TS2 DB1 SP1 done', @intErrNo)and so on. Downside is that any changes to the process (new steps added / removed) need to be done on TS1, however I see this as an advantage too. An Admin User can "disable" a step that is currently failing (assuming they know it is safe to do so) thus allowing other steps to run. That Admin User can also check the logs easily (just needs a query on TS1_Log table, and then can probably see that the logs are failing consistently every hour with the step "About to call TS2 DB7 SP12" rather than them seeing a bunch of log entries for DB8, DB9, ...and a final entry of "This is TS1 signing out for the night ..."  If TS2 is down then no updates will occur on TS1 - which might be an issue for you? |
 |
|
|
ArviL
Starting Member
25 Posts |
Posted - 2010-09-15 : 09:13:56
|
| >If TS2 is down then no updates will occur on TS1 - which might be an issue for you?No, it isn't. And the possibility to disable certain steps, whenever needed, suits well too - those databases only read data from 3rd-party applications, and are mutually independent. So when some of them doesn't refresh, this doesn't affect others at all. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-15 : 13:14:23
|
| Same scenario as me then.We have a table of "steps" (Target DB, Sproc name, Description-of-task) which the controlling Master Sproc reads, and executes. There are columns for Sequence Number and Active/Inactive. Simple user-admin tool that allows them to mark steps as Inactive etc. Easy to add a step too of course, although the actual Sproc has to be built! |
|
|
|
|
|
|
|
|
Best practice for Job to exceute SP's on LS?