October 21, 2013 at 2:10 pm
I have a database "AU_Data" on three different servers. These databases are updated on Monday morning through some ETL processes.
I have noticed that some time one or two of databases are not updated by that time.
I want to create a job that will run every Monday and query against all 3 servers and check if the update date of a SQL table is today (i,e Monday).I have the names of the servers saved in a table...
How can I query the update date of SQL table and compare it with today's date and if the update date is less than today's date, the job generates error so I can run the ETL process to update the tables in time.
October 21, 2013 at 3:30 pm
newdba_sql (10/21/2013)
I have a database "AU_Data" on three different servers. These databases are updated on Monday morning through some ETL processes.I have noticed that some time one or two of databases are not updated by that time.
I want to create a job that will run every Monday and query against all 3 servers and check if the update date of a SQL table is today (i,e Monday).I have the names of the servers saved in a table...
How can I query the update date of SQL table and compare it with today's date and if the update date is less than today's date, the job generates error so I can run the ETL process to update the tables in time.
Setup up linked servers on one of the instances and run the 3 queries.
http://technet.microsoft.com/en-us/library/ms188279.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 21, 2013 at 4:29 pm
Set up a powershell command to connect to each of the servers and run a query. You can even use threading and remoting within powershell to have the queries run simultaneously.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 22, 2013 at 9:24 am
I already have linked server and can query the databases remotely but the problem is
--1- I want to put such thing in a sql job to automate the process
--2- I want to compare the result (date) with today's date remaining in the same job
so the job wil fail if any of database was not updated in todays date maybe saying the server name.
October 22, 2013 at 10:33 am
You can either do this via TSQL, using the linked servers and database mail or via Powershell script that connects to each server, pulls down the updated date compares them and emails if they are not correct.
I recommend Powershell anytime you have to do any work that requires doing work against multiple servers or utilizing WMI.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply