query multiple sql tables and alert last record written datetime

Question

query multiple sql tables and alert last record written datetime

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Bruin SSCrazy Points: 2058 More actions · Answer 1

Sorry no tables that are queried will change.. this is a static mature environment ..

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 2

Since you are querying in SQL, using something outside of SQL strikes me as a place where things can go wrong with capturing the data, as well as alerting on it and dealing with another scheduler. While I might use PoSh to roll this data up from multiple places, for simply querying and alerting, I'd use SQL Agent. I don't know that I think maintaining a file is harder than maintaining a table for SQL Server monitoring.

Personally, I'd have this run and audit the last date/time for these tables and keep that in another table. That lets me keep additional data if I want, or remove it and keep the last time this ran. This looks like logging stuff, which is easy to handle in SQL, plus it gets backed up and not deleted. The Agent scheduler is very reliable as well.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Bruin wrote:

I would manually update the file from a folder, and this list of tables won't change. The alert will run daily maybe every x hours..
We had an issue where table hadn't been updated in 3 days and caused problems this would be a stop gap to trying to figure out why updates didn't happen.
Thanks.

Why not just query sys.columns for the column name and then write a little dynamic SQL based on object_id (kind of like Thom A did), put it in a stored procedure, schedule it, and never have to worry again. If someone adds a table with "the" column name, it'll auto-magically be added to the job. Same if someone deletes the column or a table. It just won't show up in the sys.columns query that drives it all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 4

This is what I came up with.

$server = "sqltest"
$database = "dbastuff"
$tablequery = "select table_name from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%DateStored%' order by TABLE_NAME"
 
#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
 
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection
 
#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()

# Loop through all tables and get Max(DateStored)

foreach ($Row in $DataSet.Tables[0].Rows)
{
write-host "Processing" $Row.table_name.

$Capuretbl = $Row.table_name

$queryData = "
          Insert into dbastuff.dbo.Tbl_Alerts 
  SELECT '$Capuretbl',Max(DateStored) FROM [$($Row[0])]"
 
 
$command.CommandText = $queryData
$command.Connection = $connection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()
}

Bruin SSCrazy Points: 2058 More actions · Answer 5

How do I get around schema owner <> dbo.. ?

Thanks.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

A few of the other folks share the same sentiment as I... why are you doing this in PoSh? To be sure, that's a rhetorical question. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 7

Just something I wanted to try to use PoSh..

ANy ideas on the schema owner issue ?

Thanks everyone.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 8

Jeff Moden wrote:

A few of the other folks share the same sentiment as I... why are you doing this in PoSh? To be sure, that's a rhetorical question. 😀

Well one reason would be that they don't have access to create SQL server jobs but do have access to a windows scheduler.

Bruin SSCrazy Points: 2058 More actions · Answer 9

I ran a test with dbo schema, and it works great, but these tables I'm querying have a different schema owner.

Phil Parkin SSC Guru Points: 246970 More actions · Answer 10

Add the schema to your table-name list.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Bruin SSCrazy Points: 2058 More actions · Answer 11

I changed this piece:

select table_schema+'.'+table_name from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%DateStored%' order by TABLE_NAME

It's throwing up the error: It's getting the correct schema owner and Table ...

Exception calling "Fill" with "1" argument(s): "Invalid object name 'webrepadmin.BoL'."

At C:\ps_scripts\table_updt_alerts.ps1:42 char:1

+ $SqlAdapter.Fill($DataSet)

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : SqlException

frederico_fonseca SSCoach Points: 16161 More actions · Answer 12

that's because you failed to understand what you are doing on your powershell script.

Insert into dbastuff.dbo.Tbl_Alerts

SELECT '$Capuretbl',Max(DateStored) FROM [$($Row[0])]"

you are surrounding the contents with square brackets - so the table name becames

defaultuserschema.[webrepadmin.BoL]

which is not what you intended.

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 13

What you seem to be doing here is reading data from SQL Server, then processing that and inserting data from SQL Server into another table. There's no reason to do this outside of SQL Server at all. If you want to call this from PoSh, but if it were me, I'd grab the list of tables and insert the data all from a stored proc with one call from PoSh.

What's likely happening with your error is the second query you assemble isn't formatted correctly. Rather than execute this as an INSERT, print the query and check that it is well formed.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 14

ZZartin wrote:

Jeff Moden wrote:
A few of the other folks share the same sentiment as I... why are you doing this in PoSh? To be sure, that's a rhetorical question. 😀
Well one reason would be that they don't have access to create SQL server jobs but do have access to a windows scheduler.

Sounds like a really good reason to take all privs away from anyone that does that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bruin SSCrazy Points: 2058 More actions · Answer 15

Thanks for all comments and suggestions.

foreach ($Row in $DataSet.Tables[0].Rows)
{
write-host "Processing" $Row.Column1


$Capuretbl = $Row.Column1


$queryData = "
          Insert into dba_usage.dbo.Tbl_Alerts 
  SELECT '$Capuretb'l,Max(DateStored),getdate() FROM $($Row[0])"

...