Logging the execution of stored procedures in Sql Server

Have you ever needed to know how often a stored procedure is called, or whether it is used at all? This is easy to find out within the database, but of course an unknown number of outside applications may check in from time to time and they may be have been written by end users. It would be useful to be able to switch on logging of executions for a period of time (prior to an upgrade of the database for example) so that obsolete code can be removed and problems pre-empted. Continue reading Logging the execution of stored procedures in Sql Server

Planning the use of Oracle DML Error Logging to make an ETL Batch more reliable

An ETL batch should not fail because of minor source data errors. In data warehousing it is assumed that there will be data quality issues. These should be dealt with. If they are not handled properly the batch may break on flaws which have no importance in reporting terms.

Oracle’s DML Error Logging feature has been around for a while and can prevent batch failures Continue reading Planning the use of Oracle DML Error Logging to make an ETL Batch more reliable

Concatenation of row values in Sql Server 2012 and 2014

The requirement here is to concatenate multiple rows into a single string, not simply to do a concatenation of columns within a row, which is easy with the the “+” operator or T-Sql’s CONCAT function.

Concatenation of rows is now very straightforward – I believe the change came in with Sql 2012. Continue reading Concatenation of row values in Sql Server 2012 and 2014

Dropping columns from large tables in Oracle

In a data warehouse you may have to drop columns on very large tables (try to avoid it if you can), and the size of the table may cause complications. The most obvious is the time it may take to do a straightforward ALTER TABLE DROP COLUMN statement. You probably don’t want to wait for an hour in the middle of your implementation for the DROP statement to execute. Continue reading Dropping columns from large tables in Oracle