Convert week number back into the date range for that week using T-SQL
DECLARE @Week INT
DECLARE @Year NVARCHAR(4);
SELECT @Year = 2014, @Week = 40
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 6, '1/1/' + @Year) + (@Week-1), 6) AS StartOfWeek;
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 5, '1/1/' + @Year) + (@Week-1), 5) AS EndOfWeek;
Monday, August 12, 2013
Wednesday, March 13, 2013
List of Country
<row CountryID="1" CountryName="Afghanistan" CountryCode="AFG" CountryCodeShort="AF" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="2" CountryName="Albania" CountryCode="ALB" CountryCodeShort="AL" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="3" CountryName="Algeria" CountryCode="DZA" CountryCodeShort="DZ" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="4" CountryName="American Samoa" CountryCode="ASM" CountryCodeShort="AS" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="5" CountryName="Andorra" CountryCode="AND" CountryCodeShort="AD" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="6" CountryName="Angola" CountryCode="AGO" CountryCodeShort="AO" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="7" CountryName="Anguilla" CountryCode="AIA" CountryCodeShort="AI" DisplayOrder="1" CountryRegion="Leeward Islands, Caribbean" />
<row CountryID="8" CountryName="Antarctica" CountryCode="ATA" CountryCodeShort="AQ" DisplayOrder="1" CountryRegion="Antarctica" />
<row CountryID="9" CountryName="Antigua and Barbuda" CountryCode="ATG" CountryCodeShort="AG" DisplayOrder="1" CountryRegion="Leeward Islands, Caribbean" />
<row CountryID="10" CountryName="Argentina" CountryCode="ARG" CountryCodeShort="AR" DisplayOrder="1" CountryRegion="Southern South America" />
<row CountryID="11" CountryName="Armenia" CountryCode="ARM" CountryCodeShort="AM" DisplayOrder="1" CountryRegion="Western Asia" />
<row CountryID="12" CountryName="Aruba" CountryCode="ABW" CountryCodeShort="AW" DisplayOrder="1" CountryRegion="Leeward Islands, Caribbean" />
<row CountryID="13" CountryName="Australia" CountryCode="AUS" CountryCodeShort="AU" DisplayOrder="0" CountryRegion="Australia" />
<row CountryID="14" CountryName="Austria" CountryCode="AUT" CountryCodeShort="AT" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="15" CountryName="Azerbaijan" CountryCode="AZE" CountryCodeShort="AZ" DisplayOrder="1" CountryRegion="Western Asia" />
<row CountryID="16" CountryName="Bahamas" CountryCode="BHS" CountryCodeShort="BS" DisplayOrder="1" CountryRegion="Caribbean" />
<row CountryID="17" CountryName="Bahrain" CountryCode="BHR" CountryCodeShort="BH" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="18" CountryName="Bangladesh" CountryCode="BGD" CountryCodeShort="BD" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="19" CountryName="Barbados" CountryCode="BRB" CountryCodeShort="BB" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="20" CountryName="Belarus" CountryCode="BLR" CountryCodeShort="BY" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="21" CountryName="Belgium" CountryCode="BEL" CountryCodeShort="BE" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="22" CountryName="Belize" CountryCode="BLZ" CountryCodeShort="BZ" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="23" CountryName="Benin" CountryCode="BEN" CountryCodeShort="BJ" DisplayOrder="1" CountryRegion="West Africa" />
<row CountryID="24" CountryName="Bermuda" CountryCode="BMU" CountryCodeShort="BM" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="25" CountryName="Bhutan" CountryCode="BTN" CountryCodeShort="BT" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="26" CountryName="Bolivia" CountryCode="BOL" CountryCodeShort="BO" DisplayOrder="1" CountryRegion="Central South America" />
<row CountryID="27" CountryName="Bosnia and Herzegowina" CountryCode="BIH" CountryCodeShort="BA" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="28" CountryName="Botswana" CountryCode="BWA" CountryCodeShort="BW" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="29" CountryName="Bouvet Island" CountryCode="BVT" CountryCodeShort="BV" DisplayOrder="1" CountryRegion="" />
<row CountryID="30" CountryName="Brazil" CountryCode="BRA" CountryCodeShort="BR" DisplayOrder="1" CountryRegion="Central Eastern South America" />
<row CountryID="31" CountryName="British Indian Ocean Territory" CountryCode="IOT" CountryCodeShort="IO" DisplayOrder="1" CountryRegion="" />
<row CountryID="32" CountryName="Brunei Darussalam" CountryCode="BRN" CountryCodeShort="BN" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="33" CountryName="Bulgaria" CountryCode="BGR" CountryCodeShort="BG" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="34" CountryName="Burkina Faso" CountryCode="BFA" CountryCodeShort="BF" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="35" CountryName="Burundi" CountryCode="BDI" CountryCodeShort="BI" DisplayOrder="1" CountryRegion="Eastern Africa, African Great Lakes" />
<row CountryID="36" CountryName="Cambodia" CountryCode="KHM" CountryCodeShort="KH" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="37" CountryName="Cameroon" CountryCode="CMR" CountryCodeShort="CM" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="38" CountryName="Canada" CountryCode="CAN" CountryCodeShort="CA" DisplayOrder="1" CountryRegion="North North America" />
<row CountryID="39" CountryName="Cape Verde" CountryCode="CPV" CountryCodeShort="CV" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="40" CountryName="Cayman Islands" CountryCode="CYM" CountryCodeShort="KY" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="41" CountryName="Central African Republic" CountryCode="CAF" CountryCodeShort="CF" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="42" CountryName="Chad" CountryCode="TCD" CountryCodeShort="TD" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="43" CountryName="Chile" CountryCode="CHL" CountryCodeShort="CL" DisplayOrder="1" CountryRegion="Western South America" />
<row CountryID="44" CountryName="China" CountryCode="CHN" CountryCodeShort="CN" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="45" CountryName="Christmas Island" CountryCode="CXR" CountryCodeShort="CX" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="46" CountryName="Cocos (Keeling) Islands" CountryCode="CCK" CountryCodeShort="CC" DisplayOrder="1" CountryRegion="South-East Asia, Australia" />
<row CountryID="47" CountryName="Colombia" CountryCode="COL" CountryCodeShort="CO" DisplayOrder="1" CountryRegion="North West South America" />
<row CountryID="48" CountryName="Comoros" CountryCode="COM" CountryCodeShort="KM" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="49" CountryName="Congo" CountryCode="COG" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="50" CountryName="Cook Islands" CountryCode="COK" CountryCodeShort="CK" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="51" CountryName="Costa Rica" CountryCode="CRI" CountryCodeShort="CR" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="52" CountryName="Cote D'Ivoire" CountryCode="CIV" CountryCodeShort="CI" DisplayOrder="1" CountryRegion="" />
<row CountryID="53" CountryName="Croatia (local Name: Hrvatska)" CountryCode="HRV" CountryCodeShort="HR" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="54" CountryName="Cuba" CountryCode="CUB" CountryCodeShort="CU" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="55" CountryName="Cyprus" CountryCode="CYP" CountryCodeShort="CY" DisplayOrder="1" CountryRegion="Mediterranean, Western Asia" />
<row CountryID="56" CountryName="Czech Republic" CountryCode="CZE" CountryCodeShort="CZ" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="57" CountryName="Denmark" CountryCode="DNK" CountryCodeShort="DK" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="58" CountryName="Djibouti" CountryCode="DJI" CountryCodeShort="DJ" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="59" CountryName="Dominica" CountryCode="DMA" CountryCodeShort="DM" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="60" CountryName="Dominican Republic" CountryCode="DOM" CountryCodeShort="DO" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="61" CountryName="Ecuador" CountryCode="ECU" CountryCodeShort="EC" DisplayOrder="1" CountryRegion="North West South America" />
<row CountryID="62" CountryName="Egypt" CountryCode="EGY" CountryCodeShort="EG" DisplayOrder="1" CountryRegion="Africa, Middle East" />
<row CountryID="63" CountryName="El Salvador" CountryCode="SLV" CountryCodeShort="SV" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="64" CountryName="Equatorial Guinea" CountryCode="GNQ" CountryCodeShort="GQ" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="65" CountryName="Eritrea" CountryCode="ERI" CountryCodeShort="ER" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="66" CountryName="Estonia" CountryCode="EST" CountryCodeShort="EE" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="67" CountryName="Ethiopia" CountryCode="ETH" CountryCodeShort="ET" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="68" CountryName="Falkland Islands (Malvinas)" CountryCode="FLK" CountryCodeShort="FK" DisplayOrder="1" CountryRegion="Southern South America" />
<row CountryID="69" CountryName="Faroe Islands" CountryCode="FRO" CountryCodeShort="FO" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="70" CountryName="Fiji" CountryCode="FJI" CountryCodeShort="FJ" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="71" CountryName="Finland" CountryCode="FIN" CountryCodeShort="FI" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="72" CountryName="France" CountryCode="FRA" CountryCodeShort="FR" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="73" CountryName="French Guiana" CountryCode="GUF" CountryCodeShort="GF" DisplayOrder="1" CountryRegion="Northern South America" />
<row CountryID="74" CountryName="French Polynesia" CountryCode="PYF" CountryCodeShort="PF" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="75" CountryName="French Southern Territories" CountryCode="ATF" CountryCodeShort="TF" DisplayOrder="1" CountryRegion="Southern South America, Antarctic" />
<row CountryID="76" CountryName="Gabon" CountryCode="GAB" CountryCodeShort="GA" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="77" CountryName="Gambia" CountryCode="GMB" CountryCodeShort="GM" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="78" CountryName="Georgia" CountryCode="GEO" CountryCodeShort="GE" DisplayOrder="1" CountryRegion="Western Asia" />
<row CountryID="79" CountryName="Germany" CountryCode="DEU" CountryCodeShort="DE" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="80" CountryName="Ghana" CountryCode="GHA" CountryCodeShort="GH" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="81" CountryName="Gibraltar" CountryCode="GIB" CountryCodeShort="GI" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="82" CountryName="Greece" CountryCode="GRC" CountryCodeShort="GR" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="83" CountryName="Greenland" CountryCode="GRL" CountryCodeShort="GL" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="84" CountryName="Grenada" CountryCode="GRD" CountryCodeShort="GD" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="85" CountryName="Guadeloupe" CountryCode="GLP" CountryCodeShort="GP" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="86" CountryName="Guam" CountryCode="GUM" CountryCodeShort="GU" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="87" CountryName="Guatemala" CountryCode="GTM" CountryCodeShort="GT" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="88" CountryName="Guinea" CountryCode="GIN" CountryCodeShort="GN" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="89" CountryName="Guinea-bissau" CountryCode="GNB" CountryCodeShort="GW" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="90" CountryName="Guyana" CountryCode="GUY" CountryCodeShort="GY" DisplayOrder="1" CountryRegion="North Eastern South America" />
<row CountryID="91" CountryName="Haiti" CountryCode="HTI" CountryCodeShort="HT" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="92" CountryName="Heard and Mc Donald Islands" CountryCode="HMD" CountryCodeShort="HM" DisplayOrder="1" CountryRegion="" />
<row CountryID="93" CountryName="Honduras" CountryCode="HND" CountryCodeShort="HN" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="94" CountryName="Hong Kong" CountryCode="HKG" CountryCodeShort="HK" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="95" CountryName="Hungary" CountryCode="HUN" CountryCodeShort="HU" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="96" CountryName="Iceland" CountryCode="ISL" CountryCodeShort="IS" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="97" CountryName="India" CountryCode="IND" CountryCodeShort="IN" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="98" CountryName="Indonesia" CountryCode="IDN" CountryCodeShort="ID" DisplayOrder="1" CountryRegion="Maritime South-East Asia" />
<row CountryID="99" CountryName="Iran (Islamic Republic of)" CountryCode="IRN" CountryCodeShort="IR" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="100" CountryName="Iraq" CountryCode="IRQ" CountryCodeShort="IQ" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="101" CountryName="Ireland" CountryCode="IRL" CountryCodeShort="IE" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="102" CountryName="Israel" CountryCode="ISR" CountryCodeShort="IL" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="103" CountryName="Italy" CountryCode="ITA" CountryCodeShort="IT" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="104" CountryName="Jamaica" CountryCode="JAM" CountryCodeShort="JM" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="105" CountryName="Japan" CountryCode="JPN" CountryCodeShort="JP" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="106" CountryName="Jordan" CountryCode="JOR" CountryCodeShort="JO" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="107" CountryName="Kazakhstan" CountryCode="KAZ" CountryCodeShort="KZ" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="108" CountryName="Kenya" CountryCode="KEN" CountryCodeShort="KE" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="109" CountryName="Kiribati" CountryCode="KIR" CountryCodeShort="KI" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="110" CountryName="Korea" CountryCode="KOR" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="111" CountryName="Korea, Democratic People's Republic of" CountryCode="PRK" CountryCodeShort="KP" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="112" CountryName="Kuwait" CountryCode="KWT" CountryCodeShort="KW" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="113" CountryName="Kyrgyzstan" CountryCode="KGZ" CountryCodeShort="KG" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="114" CountryName="Lao People's Democratic Republic" CountryCode="LAO" CountryCodeShort="LA" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="115" CountryName="Latvia" CountryCode="LVA" CountryCodeShort="LV" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="116" CountryName="Lebanon" CountryCode="LBN" CountryCodeShort="LB" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="117" CountryName="Lesotho" CountryCode="LSO" CountryCodeShort="LS" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="118" CountryName="Liberia" CountryCode="LBR" CountryCodeShort="LR" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="119" CountryName="Libyan Arab Jamahiriya" CountryCode="LBY" CountryCodeShort="LY" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="120" CountryName="Liechtenstein" CountryCode="LIE" CountryCodeShort="LI" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="121" CountryName="Lithuania" CountryCode="LTU" CountryCodeShort="LT" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="122" CountryName="Luxembourg" CountryCode="LUX" CountryCodeShort="LU" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="123" CountryName="Macau" CountryCode="MAC" CountryCodeShort="MO" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="124" CountryName="Macedonia" CountryCode="MKD" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="125" CountryName="Madagascar" CountryCode="MDG" CountryCodeShort="MG" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="126" CountryName="Malawi" CountryCode="MWI" CountryCodeShort="MW" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="127" CountryName="Malaysia" CountryCode="MYS" CountryCodeShort="MY" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="128" CountryName="Maldives" CountryCode="MDV" CountryCodeShort="MV" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="129" CountryName="Mali" CountryCode="MLI" CountryCodeShort="ML" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="130" CountryName="Malta" CountryCode="MLT" CountryCodeShort="MT" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="131" CountryName="Marshall Islands" CountryCode="MHL" CountryCodeShort="MH" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="132" CountryName="Martinique" CountryCode="MTQ" CountryCodeShort="MQ" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="133" CountryName="Mauritania" CountryCode="MRT" CountryCodeShort="MR" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="134" CountryName="Mauritius" CountryCode="MUS" CountryCodeShort="MU" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="135" CountryName="Mayotte" CountryCode="MYT" CountryCodeShort="YT" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="136" CountryName="Mexico" CountryCode="MEX" CountryCodeShort="MX" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="137" CountryName="Micronesia" CountryCode="FSM" DisplayOrder="1" CountryRegion="" />
<row CountryID="138" CountryName="Moldova" CountryCode="MDA" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="139" CountryName="Monaco" CountryCode="MCO" CountryCodeShort="MC" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="140" CountryName="Mongolia" CountryCode="MNG" CountryCodeShort="MN" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="141" CountryName="Montserrat" CountryCode="MSR" CountryCodeShort="MS" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="142" CountryName="Morocco" CountryCode="MAR" CountryCodeShort="MA" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="143" CountryName="Mozambique" CountryCode="MOZ" CountryCodeShort="MZ" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="144" CountryName="Myanmar" CountryCode="MMR" CountryCodeShort="MM" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="145" CountryName="Namibia" CountryCode="NAM" CountryCodeShort="NA" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="146" CountryName="Nauru" CountryCode="NRU" CountryCodeShort="NR" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="147" CountryName="Nepal" CountryCode="NPL" CountryCodeShort="NP" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="148" CountryName="Netherlands" CountryCode="NLD" CountryCodeShort="NL" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="149" CountryName="Netherlands Antilles" CountryCode="ANT" CountryCodeShort="AN" DisplayOrder="1" CountryRegion="Caribbean" />
<row CountryID="150" CountryName="New Caledonia" CountryCode="NCL" CountryCodeShort="NC" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="151" CountryName="New Zealand" CountryCode="NZL" CountryCodeShort="NZ" DisplayOrder="1" CountryRegion="Oceania; Australia" />
<row CountryID="152" CountryName="Nicaragua" CountryCode="NIC" CountryCodeShort="NI" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="153" CountryName="Niger" CountryCode="NER" CountryCodeShort="NE" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="154" CountryName="Nigeria" CountryCode="NGA" CountryCodeShort="NG" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="155" CountryName="Niue" CountryCode="NIU" CountryCodeShort="NU" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="156" CountryName="Norfolk Island" CountryCode="NFK" CountryCodeShort="NF" DisplayOrder="1" CountryRegion="" />
<row CountryID="157" CountryName="Northern Mariana Islands" CountryCode="MNP" CountryCodeShort="MP" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="158" CountryName="Norway" CountryCode="NOR" CountryCodeShort="NO" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="159" CountryName="Oman" CountryCode="OMN" CountryCodeShort="OM" DisplayOrder="1" CountryRegion="Middle East" />
<row CountryID="160" CountryName="Pakistan" CountryCode="PAK" CountryCodeShort="PK" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="161" CountryName="Palau" CountryCode="PLW" CountryCodeShort="PW" DisplayOrder="1" CountryRegion="Micronesia, Oceania" />
<row CountryID="162" CountryName="Panama" CountryCode="PAN" CountryCodeShort="PA" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="163" CountryName="Papua New Guinea" CountryCode="PNG" CountryCodeShort="PG" DisplayOrder="1" CountryRegion="Maritime Southeast Asia, Melanesia, Oceania" />
<row CountryID="164" CountryName="Paraguay" CountryCode="PRY" CountryCodeShort="PY" DisplayOrder="1" CountryRegion="Central South America" />
<row CountryID="165" CountryName="Peru" CountryCode="PER" CountryCodeShort="PE" DisplayOrder="1" CountryRegion="Western South America" />
<row CountryID="166" CountryName="Philippines" CountryCode="PHL" CountryCodeShort="PH" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="167" CountryName="Pitcairn" CountryCode="PCN" CountryCodeShort="PN" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="168" CountryName="Poland" CountryCode="POL" CountryCodeShort="PL" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="169" CountryName="Portugal" CountryCode="PRT" CountryCodeShort="PT" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="170" CountryName="Puerto Rico" CountryCode="PRI" CountryCodeShort="PR" DisplayOrder="1" CountryRegion="Greater Antilles, Caribbean" />
<row CountryID="171" CountryName="Qatar" CountryCode="QAT" CountryCodeShort="QA" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="172" CountryName="Reunion" CountryCode="REU" CountryCodeShort="RE" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="173" CountryName="Romania" CountryCode="ROM" CountryCodeShort="RO" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="174" CountryName="Russia" CountryCode="RUS" DisplayOrder="1" CountryRegion="Eastern Europe - Northern Asia" />
<row CountryID="175" CountryName="Rwanda" CountryCode="RWA" CountryCodeShort="RW" DisplayOrder="1" CountryRegion="Eastern Africa, African Great Lakes" />
<row CountryID="176" CountryName="Saint Kitts and Nevis" CountryCode="KNA" CountryCodeShort="KN" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="177" CountryName="Saint Lucia" CountryCode="LCA" CountryCodeShort="LC" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="178" CountryName="Saint Vincent and the Grenadines" CountryCode="VCT" CountryCodeShort="VC" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="179" CountryName="Samoa" CountryCode="WSM" CountryCodeShort="WS" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="180" CountryName="San Marino" CountryCode="SMR" CountryCodeShort="SM" DisplayOrder="1" CountryRegion="Southern Europe within Italy" />
<row CountryID="181" CountryName="Sao Tome and Principe" CountryCode="STP" CountryCodeShort="ST" DisplayOrder="1" CountryRegion="Central Africa" />
<row CountryID="182" CountryName="Saudi Arabia" CountryCode="SAU" CountryCodeShort="SA" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="183" CountryName="Senegal" CountryCode="SEN" CountryCodeShort="SN" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="184" CountryName="Serbia" CountryCode="SRB" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="185" CountryName="Seychelles" CountryCode="SYC" CountryCodeShort="SC" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="186" CountryName="Sierra Leone" CountryCode="SLE" CountryCodeShort="SL" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="187" CountryName="Singapore" CountryCode="SGP" CountryCodeShort="SG" DisplayOrder="1" CountryRegion="Southeast Asia" />
<row CountryID="188" CountryName="Slovakia (Slovak Republic)" CountryCode="SVK" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="189" CountryName="Slovenia" CountryCode="SVN" CountryCodeShort="SI" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="190" CountryName="Solomon Islands" CountryCode="SLB" CountryCodeShort="SB" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="191" CountryName="Somalia" CountryCode="SOM" CountryCodeShort="SO" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="192" CountryName="South Africa" CountryCode="ZAF" CountryCodeShort="ZA" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="193" CountryName="South Georgia & South Sandwich Islands" CountryCode="SGS" DisplayOrder="1" CountryRegion="" />
<row CountryID="194" CountryName="Spain" CountryCode="ESP" CountryCodeShort="ES" DisplayOrder="1" CountryRegion="Southern Europe" />
<row CountryID="195" CountryName="Sri Lanka" CountryCode="LKA" CountryCodeShort="LK" DisplayOrder="1" CountryRegion="South-Central Asia" />
<row CountryID="196" CountryName="St. Helena" CountryCode="SHN" DisplayOrder="1" CountryRegion="" />
<row CountryID="197" CountryName="St. Pierre and Miquelon" CountryCode="SPM" DisplayOrder="1" CountryRegion="" />
<row CountryID="198" CountryName="Sudan" CountryCode="SDN" CountryCodeShort="SD" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="199" CountryName="Suriname" CountryCode="SUR" CountryCodeShort="SR" DisplayOrder="1" CountryRegion="North-Eastern South America" />
<row CountryID="200" CountryName="Svalbard and Jan Mayen Islands" CountryCode="SJM" CountryCodeShort="SJ" DisplayOrder="1" CountryRegion="" />
<row CountryID="201" CountryName="Swaziland" CountryCode="SWZ" CountryCodeShort="SZ" DisplayOrder="1" CountryRegion="Southern Africa" />
<row CountryID="202" CountryName="Sweden" CountryCode="SWE" CountryCodeShort="SE" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="203" CountryName="Switzerland" CountryCode="CHE" CountryCodeShort="CH" DisplayOrder="1" CountryRegion="Western Europe" />
<row CountryID="204" CountryName="Syrian Arab Republic" CountryCode="SYR" CountryCodeShort="SY" DisplayOrder="1" CountryRegion="Middle East, Western Asia" />
<row CountryID="205" CountryName="Taiwan" CountryCode="TWN" CountryCodeShort="TW" DisplayOrder="1" CountryRegion="Eastern Asia" />
<row CountryID="206" CountryName="Tajikistan" CountryCode="TJK" CountryCodeShort="TJ" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="207" CountryName="Tanzania" CountryCode="TZA" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="208" CountryName="Thailand" CountryCode="THA" CountryCodeShort="TH" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="209" CountryName="Togo" CountryCode="TGO" CountryCodeShort="TG" DisplayOrder="1" CountryRegion="Western Africa" />
<row CountryID="210" CountryName="Tokelau" CountryCode="TKL" CountryCodeShort="TK" DisplayOrder="1" CountryRegion="Oceania/Australia" />
<row CountryID="211" CountryName="Tonga" CountryCode="TON" CountryCodeShort="TO" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="212" CountryName="Trinidad and Tobago" CountryCode="TTO" CountryCodeShort="TT" DisplayOrder="1" CountryRegion="Northern South America, Caribbean" />
<row CountryID="213" CountryName="Tunisia" CountryCode="TUN" CountryCodeShort="TN" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="214" CountryName="Turkey" CountryCode="TUR" CountryCodeShort="TR" DisplayOrder="1" CountryRegion="Southeastern Europe, Western Asia" />
<row CountryID="215" CountryName="Turkmenistan" CountryCode="TKM" CountryCodeShort="TM" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="216" CountryName="Turks and Caicos Islands" CountryCode="TCA" CountryCodeShort="TC" DisplayOrder="1" CountryRegion="Caribbean, parts of the Bahamas island chain." />
<row CountryID="217" CountryName="Tuvalu" CountryCode="TUV" CountryCodeShort="TV" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="218" CountryName="Uganda" CountryCode="UGA" CountryCodeShort="UG" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="219" CountryName="Ukraine" CountryCode="UKR" CountryCodeShort="UA" DisplayOrder="1" CountryRegion="Eastern Europe" />
<row CountryID="220" CountryName="United Arab Emirates" CountryCode="ARE" CountryCodeShort="AE" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="221" CountryName="United Kingdom" CountryCode="GBR" CountryCodeShort="GB" DisplayOrder="1" CountryRegion="Northern Europe" />
<row CountryID="222" CountryName="United States" CountryCode="USA" CountryCodeShort="US" DisplayOrder="1" CountryRegion="North America" />
<row CountryID="223" CountryName="United States minor outlying islands" CountryCode="UMI" CountryCodeShort="UM" DisplayOrder="1" CountryRegion="Central America" />
<row CountryID="224" CountryName="Uruguay" CountryCode="URY" CountryCodeShort="UY" DisplayOrder="1" CountryRegion="Central East South America" />
<row CountryID="225" CountryName="Uzbekistan" CountryCode="UZB" CountryCodeShort="UZ" DisplayOrder="1" CountryRegion="Central Asia" />
<row CountryID="226" CountryName="Vanuatu" CountryCode="VUT" CountryCodeShort="VU" DisplayOrder="1" CountryRegion="Melanesia, Oceania" />
<row CountryID="227" CountryName="Vatican City State (Holy See)" CountryCode="VAT" CountryCodeShort="VA" DisplayOrder="1" CountryRegion="Southern Europe within Italy" />
<row CountryID="228" CountryName="Venezuela" CountryCode="VEN" CountryCodeShort="VE" DisplayOrder="1" CountryRegion="Northern South America" />
<row CountryID="229" CountryName="Viet Nam" CountryCode="VNM" CountryCodeShort="VN" DisplayOrder="1" CountryRegion="South-East Asia" />
<row CountryID="230" CountryName="Virgin Islands (British)" CountryCode="VGB" CountryCodeShort="VG" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="231" CountryName="Virgin Islands (U.S.)" CountryCode="VIR" CountryCodeShort="VI" DisplayOrder="1" CountryRegion="Lesser Antilles, Caribbean" />
<row CountryID="232" CountryName="Wallis and Futuna Islands" CountryCode="WLF" CountryCodeShort="WF" DisplayOrder="1" CountryRegion="Polynesia, Oceania" />
<row CountryID="233" CountryName="Western Sahara" CountryCode="ESH" CountryCodeShort="EH" DisplayOrder="1" CountryRegion="Northern Africa" />
<row CountryID="234" CountryName="Yemen" CountryCode="YEM" CountryCodeShort="YE" DisplayOrder="1" CountryRegion="Arabian Peninsula, Middle East" />
<row CountryID="235" CountryName="Zambia" CountryCode="ZMB" CountryCodeShort="ZM" DisplayOrder="1" CountryRegion="Eastern Africa" />
<row CountryID="236" CountryName="Zimbabwe" CountryCode="ZWE" CountryCodeShort="ZW" DisplayOrder="1" CountryRegion="Eastern Africa" />
Tuesday, February 5, 2013
MS-SQL Shrink Database - DBCC SHRINKFILE
DBCC SHRINKFILE
Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.
DBCC SHRINKFILE applies to the files in the current database.
DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE WITH NO_WAIT;
GO
-- Shrink the data file.
DBCC SHRINKFILE (AdventureWorks2012,10);
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL WITH NO_WAIT;
GO
How to check the status / progress of Database Shrink Operation - DBCC SHRINKFILE
Here is the command to show the current status SHRINKFILE Operation
SELECT PERCENT_COMPLETE FROM SYS.DM_EXEC_REQUESTS
WHERE COMMAND = 'DBCCFILESCOMPACT'
Thursday, January 17, 2013
MicroSoft SQL Server - Clear the transaction logs
Shrinks the size of the log files in the specified database.
USE DBNAME
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DBName SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DBName_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DBName SET RECOVERY FULL;
GO
--
Jagadeesh
M: +61 425359215 | H: +61 296881489
Subscribe to:
Posts (Atom)