I'm not sure exactly where this question ought to reside.....

In our production environment, we have 2 sql servers that we alternate
between different days production totals. For instance, on Friday,
Server #1 (the primary one) will load the previous night's production
totals to it for reporting. Server #2 would not run anything then. The
next night, Server #2 would load production totals from Friday night for
reporting, and Server #1 would not do anything. It alternates between
those two servers 6 days a week.

I need to run a job that would run on the first of each month, for the
previous month's totals for a single customer & email the results after
they've been loaded into an excel spreadsheet.

My question is - since we alternate servers, the first of the month
could fall on either server - how can I make this job run 1 time, on the
data on the appropriate server?

I know that Sunday/Monday, Wednesday, and Friday would be on Server #1,
and Tuesday, Thursday & Saturday would be on Server #2.

I'd like to schedule one job to do this and email the report. It'd be
no problem to have separate steps to do that.

I'd like any opinions......

BC

Hi,

Cant you just keep one more table in both these databases that would
have details as to which process ran when? Whichever has the max date
would be the one where the last update took place.

Hope this would work for you.

Blasting Cap wrote:

I don't know if this helps but you could use
"select DATENAME(weekday,getdate())"
to find out what the current day of the week it is?

"Blasting Cap" wrote:

REQUIREMENT: You need to run a job (DTS) as follows,

a) First of the month
b) Run on one server
c) Fetch data for a customer for the whole of last month from the
alternating servers and export to EXCEL.

SOLUTION:

a) Create Linked Servers between the two servers for easy data referencing.
b) Create a Execute SQL Task to fetch data from both of the tables using the
following query
SELECT * FROM [SERVER1].[DBO].[TABLENAME] WHERE [DATEFIELD]
IN('StartofMonth', 'EndOfMonth')
UNION
SELECT * FROM [SERVER2].[DBO].[TABLENAME] WHERE [DATEFIELD]
IN('StartofMonth', 'EndOfMonth')
c) Create an EXCEL Connection
d) Use transform data task to export the SQL results to EXCEL.

Hope this helps.

--
SAJJAS


"Blasting Cap" wrote: