August 7, 2012 at 2:10 pm
select run_date, server_name, database_name, table_name, rows
from table_rows as a
where run_date > GETDATE() -1
order by server_name, database_name
i have a table where i import row count data for master and replicated copies of tables. we've had issues before where replication stops working and the distributor doesn't throw any alarms.
currently the system works where every server and database has its own table and then a query runs against the data and several reports are emailed out via a SQL Job.
i'm trying to set it up where the data is stored in a separate table and one report is sent out via SSRS that looks nicer than the sql email version
say server1 is the master server and i'm importing data from 3 servers with the same database name.
i would like something like the above query but with an added column that has the difference from the master table. any way to do it via computed columns? looked into pivot but it also seems like it wont work the way the data is set up now
August 7, 2012 at 2:58 pm
alen teplitsky (8/7/2012)
select run_date, server_name, database_name, table_name, rowsfrom table_rows as a
where run_date > GETDATE() -1
order by server_name, database_name
i have a table where i import row count data for master and replicated copies of tables. we've had issues before where replication stops working and the distributor doesn't throw any alarms.
currently the system works where every server and database has its own table and then a query runs against the data and several reports are emailed out via a SQL Job.
i'm trying to set it up where the data is stored in a separate table and one report is sent out via SSRS that looks nicer than the sql email version
say server1 is the master server and i'm importing data from 3 servers with the same database name.
i would like something like the above query but with an added column that has the difference from the master table. any way to do it via computed columns? looked into pivot but it also seems like it wont work the way the data is set up now
Ask yourself if you think you could possibly provide much help with what you posted here.
This is a quote from the MSDN page about computed columns "A computed column is computed from an expression that can use other columns in the same table." http://msdn.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx
That was the first link in google when I typed "sql server computed column".
If you can explain more clearly what you are trying to do we can probably help.
_______________________________________________________________
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/
August 7, 2012 at 6:32 pm
I may understand what you're looking for.
Have you considered creating a table with 6 columns:
- Server name, database name, table name (for the master table)
- Server name, database name, table name (for the replicated table)
So now you could take the results of the query you posted and JOIN it twice to this adjacency list, first to get the master table name then the replicated table name, thus establishing whether the row is for a master or replicated table. Then JOIN it one more time to itself to match up the masters and replicated tables to calculate the row difference.
Doesn't sound particularly efficient but if you don't have too many tables it should be that bad either.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply