Monthly Archives: July 2014

The external metadata column collection is out of synchronization with the data source columns.

One of my SQL Job failed with Error and the failure was due to an issue with SSIS Package. There are multiple reasons when this error occurs but I just share the approach and one of the examples: Do you … Continue reading

Posted in SQL, SQL Error, SSIS | Tagged , , , | Leave a comment

Declaring Variables in SQL – Declaration will take place during PARSING, not at Runtime

All SQL variable declarations will take place during parsing but not during Runtime. Let us try simple test to prove it! Let us try to declare a variable in a loop which runs for 5 times… There are no errors … Continue reading

Posted in knowledge, SQL, SQL Query | Tagged , , | Leave a comment


1. NCHAR, NVARCHAR -> Can Store UniCode Characters 2. CHAR, VARCHAR -> Cannot store all Unicode characters 3. Char, NCHAR -> Fixed Length while storing. If you declare as 100 Characters and store only 2 letters, it uses 100 Characters … Continue reading

Posted in SQL | Tagged , , , , , , | Leave a comment

Time Remaining for SQL task/ Percentage Compelte (Backup/ Restore)

When we take a backup or restore a database using command, we always like to track the progress. This SQL script helps you to do the same!

Posted in Script, SQL, SQL Query | Tagged , , | Leave a comment

C Drive space issue – Tool for disk usage analysis

C Drive space issue on is one of the most common problem I have come across. Basic History/ Temp file deletion are most common tips to help and at one point we may need to do something beyond that, analysis … Continue reading

Posted in Uncategorized | Leave a comment

How to add new articles to existing Transactional Replication

Adding new articles is simple but following steps will avoid publishing snapshot of old articles. 1. Change allow_anonymous and immediate_sync to FALSE. 2. Include new articles ..a. SSMS -> Server/Instance -> Replication -> Local Publications ..b. Right click and select … Continue reading

Posted in Uncategorized | Leave a comment

Buffer Pool Extension – New feature in SQL Server 2014

Before diving into Buffer Pool Extension (BPE) details, it is worth taking a look at the Buffer Management to refresh your knowledge. You may find buffer management details @ Buffer Pool Extension (BPE) can use nonvolatile storage devices (Ex: … Continue reading

Posted in SQL, SQL Query, sql2014 | Tagged , , , | Leave a comment

PIVOT and UNPIVOT code sample

Writing PIVOT and UNPIVOR statements is tricky, I will explain with simple example for easy understanding. You may try on your own in 2 minutes • Create a table with Month, WeekDay, Subtotal • PIVOT table data to view in … Continue reading

Posted in Script, SQL, SQL Query | Tagged , , | Leave a comment

Knowledge Share 2014-07

SYNONYM SYNONYM is used to reference an object in usable format. Syntax: create synonym UsableName for Server.DBName.SchemaName.TableName Note: The base object need not exist at synonym create time. SQL Server checks for the existence of the base object at run time. … Continue reading

Posted in knowledge, SQL, Windows Server | Tagged , , , | Leave a comment

Find and update the owner of SQL Job, Maintenance Plan

When we update Maintenance plan, ownership of the job will be changed back to plan owner (In my case it was inactive account resulting job failure). This is a known bug in SQL Server 2005 and SQL Server 2008. (Ref: … Continue reading

Posted in Productivity, Script, SQL, SQL Query | Tagged , , , , , | Leave a comment