Adsense

Monday, January 21, 2013

Odd Behavior Using Partitioned Tables in a Replicated Database With SQL Server 2008

I ran into an odd problem today troubleshooting an error trying to switch a partition in SQL Server 2008.  The error read:
  
  Msg 137, Level 15, State 2, Line 1
  Must declare the scalar variable "@VARIABLE".

This was a particularly odd error since the variable was indeed declared and the process worked properly in the non production environment.  After troubleshooting for a while I found that the problem was that you cannot use a variable to specify the partition as in the statement below if you have your database published for replication

  ALTER TABLE PartitionTable SWITCH PARTITION @partition TO NonPartitionTable

Microsoft's workaround to this was to use dynamic SQL.  The full KB article on the problem can be found here: http://support.microsoft.com/kb/2002474

No comments:

Post a Comment