Monthly Archives: July 2014

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

Posted on July 31, 2014 by vijred

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

Posted on July 31, 2014 by vijred

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

Compare CHAR, NCHAR, VARCHAR, NVARCHAR

Posted on July 24, 2014 by vijred

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)

Posted on July 22, 2014 by vijred

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

Posted on July 17, 2014 by vijred

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

Posted on July 15, 2014 by vijred

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

Posted on July 10, 2014 by vijred

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 @ http://technet.microsoft.com/en-us/library/aa337525(v=sql.105).aspx 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

Posted on July 8, 2014 by vijred

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

Posted on July 3, 2014 by vijred

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

Posted on July 1, 2014 by vijred

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