The Wrecking Bawl

ssis error deploying package

Alex Bransky — Mon, 20 May 2013 23:13:57 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2013/05/20/152966.aspx

I was getting the error below when trying to deploy to my SQL 2008 R2 cluster, which was odd since I had never had any trouble before. The problem ended up being that when I double clicked on the SSISDeploymentManifest file my computer was using the SQL 2012 version of the deployment utility because I had recently installed it. Once I used the 2008 utility everything worked fine.

Exception details: Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: Storing or modifying packages in SQL Server requires the SSIS runtime and database to be the same version. Storing packages in earlier versions is not supported.

---> System.Runtime.InteropServices.COMException: Storing or modifying packages in SQL Server requires the SSIS runtime and database to be the same version. Storing packages in earlier versions is not supported.

index stats histograms for puzzling sql server query plan

Alex Bransky — Sat, 13 Apr 2013 00:00:02 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2013/04/12/152675.aspx

Statistics for INDEX 'IX_DWOrderStatus_DWDateEnd_ExpiredDate_SID_STS_RID'.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name Updated Rows Rows Sampled Steps Density Average Key Length String Index

IX_DWOrderStatus_DWDateEnd_ExpiredDate_SID_STS_RIDApr 12 2013 9:57AM 25043551 25043551 194 0.1969665 37.53718 NO 25043551

All Density Average Length Columns

3.490462E-06 8 DWDateEnd

5.730709E-08 13.53718 DWDateEnd, ExpiredDate

5.69892E-08 17.53718 DWDateEnd, ExpiredDate, StatusID

5.672073E-08 21.53718 DWDateEnd, ExpiredDate, StatusID, SubStatusID

4.005993E-08 29.53718 DWDateEnd, ExpiredDate, StatusID, SubStatusID, StatusTimeStamp

3.993052E-08 33.53718 DWDateEnd, ExpiredDate, StatusID, SubStatusID, StatusTimeStamp, OrderID

3.993044E-08 37.53718 DWDateEnd, ExpiredDate, StatusID, SubStatusID, StatusTimeStamp, OrderID, DWTableID

Histogram Steps

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS

1/6/2013 1:37:38 PM 0 1 0 1

1/6/2013 4:15:16 PM 2109 18 1649 1.278957

1/6/2013 5:00:24 PM 864 16 135 6.4

1/6/2013 5:30:21 PM 618 17 164 3.768293

1/6/2013 8:30:17 PM 1165 15 303 3.844884

1/6/2013 8:55:17 PM 131 14 35 3.742857

1/6/2013 11:10:17 PM 859 16 266 3.229323

1/7/2013 10:41:24 AM 905 17 187 4.839572

1/7/2013 4:55:20 PM 4306 17 732 5.882514

1/8/2013 6:15:20 PM 18655 18 3665 5.090041

1/9/2013 9:11:44 AM 4530 17 1581 2.865275

1/9/2013 1:05:18 PM 5015 18 684 7.331872

1/9/2013 1:25:20 PM 559 17 108 5.175926

1/9/2013 2:15:20 PM 1634 17 272 6.007353

1/9/2013 4:10:20 PM 2875 17 507 5.670611

1/9/2013 4:25:19 PM 476 17 86 5.534883

1/9/2013 5:15:17 PM 871 16 151 5.768212

1/10/2013 8:50:18 AM 5504 17 1697 3.243371

1/10/2013 11:55:17 AM 3804 17 589 6.458404

1/10/2013 12:15:20 PM 184 15 39 4.717949

1/10/2013 12:20:18 PM 30 18 12 2.5

1/10/2013 12:30:29 PM 197 17 23 8.565217

1/10/2013 1:50:18 PM 1524 17 283 5.385159

1/10/2013 2:40:17 PM 722 17 100 7.22

1/10/2013 4:00:28 PM 1936 18 314 6.165605

1/10/2013 5:00:25 PM 1765 17 338 5.221893

1/10/2013 5:20:16 PM 349 15 115 3.034783

1/10/2013 5:50:17 PM 788 18 155 5.083871

1/10/2013 10:05:19 PM 1791 13 686 2.610787

1/11/2013 6:25:20 AM 1115 14 558 1.998208

1/11/2013 6:50:20 AM 279 16 73 3.821918

1/11/2013 8:05:21 AM 677 17 146 4.636986

1/11/2013 8:30:26 AM 461 17 68 6.779412

1/11/2013 9:10:22 AM 1349 16 321 4.202492

1/11/2013 9:35:23 AM 700 17 130 5.384615

1/11/2013 9:40:19 AM 166 18 24 6.916667

1/11/2013 10:15:22 AM 1069 17 222 4.815315

1/11/2013 11:58:19 AM 3387 18 470 7.206383

1/11/2013 12:36:21 PM 1058 17 146 7.246575

1/11/2013 2:48:19 PM 2441 17 420 5.811905

1/12/2013 9:36:21 AM 9872 16 2793 3.534551

1/12/2013 9:58:19 AM 702 16 122 5.754098

1/12/2013 2:06:20 PM 7056 17 1332 5.297297

1/12/2013 3:42:28 PM 2240 17 380 5.894737

1/12/2013 7:30:26 PM 5776 16 1146 5.04014

1/12/2013 8:08:27 PM 894 16 155 5.767742

1/13/2013 6:10:27 AM 3197 16 1019 3.13739

1/14/2013 9:08:20 AM 16276 17 3336 4.878897

1/14/2013 10:32:32 PM 6062 14 1961 3.09128

1/15/2013 7:38:17 AM 2142 15 604 3.546358

1/15/2013 7:46:18 AM 104 17 17 6.117647

1/15/2013 11:42:17 AM 2263 16 371 6.09973

1/15/2013 1:22:15 PM 1217 17 279 4.362007

1/15/2013 2:56:16 PM 846 18 181 4.674033

1/16/2013 5:58:17 AM 5029 15 1584 3.174874

1/16/2013 5:58:17 AM 0 14 0 1

1/16/2013 6:34:16 AM 529 15 169 3.130177

1/16/2013 6:44:16 AM 149 14 52 2.865385

1/16/2013 6:54:15 AM 114 18 40 2.85

1/16/2013 7:40:20 AM 539 14 135 3.992593

1/16/2013 8:34:17 AM 811 15 261 3.10728

1/16/2013 9:16:18 AM 966 18 158 6.113924

1/16/2013 9:32:18 AM 406 19 52 7.807693

1/16/2013 2:04:18 PM 9072 18 1206 7.522388

1/16/2013 2:18:18 PM 336 18 43 7.813953

1/16/2013 2:48:17 PM 805 18 124 6.491935

1/16/2013 5:26:18 PM 5148 18 728 7.071429

1/16/2013 7:28:15 PM 3316 18 479 6.922756

1/16/2013 7:56:16 PM 587 18 76 7.723684

1/16/2013 8:18:17 PM 490 16 97 5.051547

1/16/2013 8:40:16 PM 568 18 131 4.335878

1/16/2013 8:44:16 PM 103 18 33 3.121212

1/16/2013 9:10:17 PM 638 18 115 5.547826

1/16/2013 9:32:17 PM 560 15 121 4.628099

1/16/2013 10:14:19 PM 1085 17 281 3.86121

1/16/2013 10:28:21 PM 377 15 88 4.284091

1/16/2013 10:34:21 PM 135 14 39 3.461539

1/16/2013 10:40:23 PM 173 14 55 3.145455

1/16/2013 10:42:21 PM 70 17 26 2.692308

1/16/2013 11:32:17 PM 1364 17 300 4.546667

1/16/2013 11:54:15 PM 570 17 108 5.277778

1/17/2013 12:26:27 AM 634 18 116 5.465517

1/17/2013 12:58:21 AM 610 18 74 8.243243

1/17/2013 12:56:23 PM 13715 18 2014 6.809831

1/17/2013 3:48:24 PM 2755 17 384 7.174479

1/17/2013 5:22:24 PM 800 17 195 4.102564

1/17/2013 9:32:20 PM 1667 16 402 4.146766

1/18/2013 10:14:22 AM 4404 19 880 5.004546

1/18/2013 11:10:20 AM 1187 18 136 8.727942

1/18/2013 1:06:16 PM 1637 17 305 5.367213

1/18/2013 1:38:18 PM 893 18 101 8.841584

1/18/2013 3:16:16 PM 1179 17 182 6.478022

1/18/2013 4:36:18 PM 1095 17 201 5.447761

1/18/2013 4:46:16 PM 169 18 22 7.681818

1/18/2013 10:12:15 PM 3395 17 582 5.833333

1/19/2013 6:22:17 AM 1682 18 354 4.751412

1/19/2013 2:02:17 PM 6471 18 868 7.455069

1/19/2013 2:04:17 PM 25 18 4 6.25

1/20/2013 12:42:14 PM 6118 15 1226 4.990212

1/21/2013 9:48:22 AM 4355 18 1110 3.923424

1/22/2013 11:31:08 AM 16737 17 2898 5.775362

1/22/2013 12:04:13 PM 906 21 170 5.329412

1/22/2013 2:43:25 PM 4332 18 763 5.677588

1/23/2013 2:43:06 PM 24402 17 4050 6.025185

1/23/2013 2:43:06 PM 44 3 3 14.66667

1/29/2013 3:46:03 PM 65531 7 11848 5.530976

1/30/2013 2:42:57 PM 14166 17 2425 5.84165

2/6/2013 11:07:00 AM 65532 7 12244 5.352172

2/8/2013 2:43:09 PM 27516 16 9466 2.906824

2/12/2013 10:57:56 AM 43592 16 9092 4.794545

2/17/2013 4:57:58 PM 65535 8 13916 4.709327

2/20/2013 2:46:00 PM 43812 17 7503 5.839264

2/25/2013 12:48:57 PM 65532 7 11201 5.850549

2/26/2013 4:54:57 PM 31526 17 5130 6.145419

2/27/2013 11:54:57 AM 62793 16 10244 6.129735

2/28/2013 2:42:54 AM 8690 17 1540 5.642857

3/1/2013 2:42:54 PM 30678 18 5046 6.079667

3/2/2013 11:27:55 AM 7967 17 1302 6.119048

3/4/2013 9:01:12 AM 15376 17 2982 5.156271

3/4/2013 5:01:00 PM 10824 18 1488 7.274194

3/4/2013 6:01:00 PM 953 18 130 7.330769

3/5/2013 2:30:45 PM 9427 17 1570 6.004458

3/5/2013 2:54:46 PM 131 25 19 6.894737

3/5/2013 3:21:47 PM 428 20 57 7.508772

3/6/2013 2:45:54 AM 4827 18 807 5.981412

3/6/2013 12:01:05 PM 7868 17 1404 5.603989

3/7/2013 12:45:52 PM 15558 17 2473 6.291144

3/7/2013 4:21:48 PM 2605 17 406 6.416256

3/8/2013 11:00:58 AM 9983 17 1622 6.154747

3/8/2013 12:01:00 PM 1411 17 184 7.668478

3/11/2013 1:00:57 PM 24709 17 4658 5.304637

3/11/2013 2:42:49 PM 1557 19 232 6.711207

3/12/2013 12:09:55 PM 11626 17 2053 5.662932

3/13/2013 2:42:49 AM 8750 17 1549 5.648806

3/15/2013 8:06:56 AM 33054 18 5213 6.340687

3/18/2013 9:39:55 AM 24091 16 4473 5.385871

3/18/2013 4:01:00 PM 5060 18 840 6.023809

3/18/2013 4:45:53 PM 1111 17 169 6.573965

3/19/2013 11:37:32 AM 11607 20 2002 5.797702

3/19/2013 11:57:56 AM 655 19 75 8.733334

3/19/2013 2:45:57 PM 3494 19 461 7.579175

3/19/2013 4:15:54 PM 1274 21 182 7

3/20/2013 3:18:51 AM 7837 18 1112 7.047662

3/20/2013 9:15:51 AM 5722 20 771 7.42153

3/20/2013 9:42:49 AM 311 21 55 5.654545

3/20/2013 10:01:05 AM 302 17 46 6.565217

3/20/2013 11:09:51 AM 1358 17 220 6.172727

3/20/2013 11:21:47 AM 276 20 34 8.117647

3/20/2013 11:30:49 AM 162 18 26 6.230769

3/20/2013 1:39:55 PM 2418 17 363 6.661157

3/20/2013 3:24:14 PM 2192 16 445 4.925843

3/21/2013 2:42:58 AM 8759 17 1730 5.063006

3/21/2013 2:42:58 AM 17 17 2 8.5

3/21/2013 1:19:55 PM 8425 17 1525 5.52459

3/22/2013 10:42:20 AM 14838 18 2751 5.393675

3/22/2013 3:24:52 PM 4769 18 817 5.837209

3/22/2013 3:51:51 PM 220 16 40 5.5

3/23/2013 7:51:52 AM 7732 16 1460 5.29589

3/24/2013 2:45:56 AM 10154 18 2072 4.900579

3/24/2013 1:51:53 PM 4315 17 1089 3.962351

3/24/2013 4:51:57 PM 2122 16 490 4.330612

3/24/2013 5:51:55 PM 956 16 185 5.167568

3/25/2013 10:51:54 AM 5830 17 1770 3.293785

3/25/2013 11:06:46 AM 276 16 52 5.307693

3/25/2013 1:49:01 PM 2883 17 567 5.084656

3/25/2013 2:02:36 PM 327 18 65 5.030769

3/25/2013 4:16:49 PM 2395 19 445 5.382022

3/25/2013 4:51:01 PM 988 17 181 5.458563

3/25/2013 8:50:50 PM 3655 16 708 5.162429

3/26/2013 11:50:56 AM 10280 17 2120 4.849057

3/26/2013 2:40:55 PM 3469 17 651 5.328725

3/26/2013 2:44:59 PM 57 16 12 4.75

3/27/2013 12:15:12 AM 5499 17 1083 5.077562

3/27/2013 11:52:56 AM 8376 16 1521 5.506903

3/27/2013 4:04:54 PM 5643 16 1099 5.134668

3/28/2013 5:50:53 AM 6764 17 1450 4.664828

3/28/2013 9:50:50 AM 4589 17 1011 4.53907

3/28/2013 10:04:51 AM 225 17 52 4.326923

3/28/2013 2:12:52 PM 4499 15 971 4.633368

4/1/2013 6:50:48 AM 46411 15 10395 4.464743

4/1/2013 8:50:51 AM 2599 16 486 5.347737

4/1/2013 9:42:51 AM 1933 17 328 5.893293

4/1/2013 5:16:49 PM 9346 16 1770 5.280226

4/1/2013 5:46:46 PM 628 19 92 6.826087

4/1/2013 5:56:45 PM 160 19 27 5.925926

4/2/2013 2:50:58 AM 3562 18 720 4.947222

4/2/2013 1:34:44 PM 11545 21 2480 4.655242

4/2/2013 1:58:49 PM 658 18 103 6.38835

4/9/2013 12:23:45 AM 126780 15 29225 4.338067

4/10/2013 1:07:42 PM 40771 16 9364 4.354015

4/11/2013 9:31:44 AM 25070 19 5272 4.755311

4/12/2013 12:16:14 AM 17828 15 4215 4.229656

4/12/2013 9:55:57 AM 9705 1 2136 4.54354

12/31/9999 12:00:00 AM 0 2.358679E+07 0 1

Statistics for INDEX 'IX_DWOrder_DWDateEnd'.

Name Updated Rows Rows Sampled Steps Density Average Key Length String Index

IX_DWOrder_DWDateEnd Apr 10 2013 4:06PM 8023218 8023218 194 0.1833753 28.09117 NO 8023218

All Density Average Length Columns

3.061324E-06 8 DWDateEnd

1.246385E-07 12 DWDateEnd, OrderID

1.246385E-07 16 DWDateEnd, OrderID, CustomerID

1.246385E-07 24 DWDateEnd, OrderID, CustomerID, OrderPlacedDate

1.246385E-07 24.09117 DWDateEnd, OrderID, CustomerID, OrderPlacedDate, PromoCode

1.246383E-07 28.09117 DWDateEnd, OrderID, CustomerID, OrderPlacedDate, PromoCode, DWTableID

Histogram Steps

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS

1/6/2013 3:56:04 PM 0 1 0 1

1/6/2013 4:40:19 PM 1258 19 314 4.00637

1/6/2013 5:00:29 PM 379 17 65 5.830769

1/6/2013 5:45:21 PM 912 16 212 4.301887

1/7/2013 10:50:24 AM 4042 18 949 4.25922

1/7/2013 1:55:21 PM 2040 18 593 3.440135

1/7/2013 3:25:23 PM 979 16 206 4.752427

1/8/2013 7:40:31 AM 5952 18 1862 3.196563

1/8/2013 9:15:31 AM 1414 16 365 3.873973

1/8/2013 11:35:22 AM 2013 20 440 4.575

1/8/2013 5:50:23 PM 13416 20 1549 8.661072

1/9/2013 2:20:33 AM 4401 18 1287 3.41958

1/9/2013 12:40:30 PM 6574 19 1143 5.751531

1/9/2013 3:25:23 PM 5041 19 677 7.446085

1/9/2013 3:35:22 PM 298 18 68 4.382353

1/9/2013 4:00:37 PM 752 19 152 4.947369

1/9/2013 4:25:23 PM 928 18 170 5.458824

1/9/2013 5:00:32 PM 963 19 144 6.6875

1/10/2013 7:55:22 AM 5131 18 1656 3.09843

1/10/2013 1:05:24 PM 7002 19 1053 6.649573

1/10/2013 2:40:20 PM 2130 19 322 6.614907

1/10/2013 4:00:39 PM 2111 18 328 6.435976

1/10/2013 4:40:22 PM 1448 18 335 4.322388

1/10/2013 4:45:21 PM 86 17 13 6.615385

1/10/2013 5:35:22 PM 1188 15 260 4.569231

1/11/2013 8:05:29 AM 4518 18 1541 2.931862

1/11/2013 8:50:29 AM 1361 18 231 5.891775

1/11/2013 9:35:30 AM 1740 18 323 5.386997

1/11/2013 9:55:27 AM 689 19 111 6.207207

1/11/2013 11:16:24 AM 3934 19 545 7.218349

1/11/2013 3:50:21 PM 8641 19 1296 6.667439

1/11/2013 4:04:20 PM 501 19 72 6.958333

1/12/2013 9:48:29 AM 10761 18 2840 3.789084

1/12/2013 1:56:25 PM 11531 18 1694 6.806966

1/12/2013 5:32:35 PM 8109 18 1323 6.129251

1/12/2013 7:38:33 PM 3315 18 561 5.909091

1/13/2013 7:52:36 AM 4956 18 1389 3.568035

1/13/2013 12:48:36 PM 5918 18 945 6.262434

1/13/2013 1:42:34 PM 1144 17 281 4.071174

1/13/2013 6:12:35 PM 6438 17 1145 5.622707

1/14/2013 8:24:26 AM 4522 16 1078 4.194805

1/14/2013 4:20:25 PM 5729 16 1333 4.297824

1/15/2013 7:36:23 AM 5234 18 1845 2.836856

1/15/2013 9:38:21 AM 2074 18 304 6.822369

1/15/2013 12:26:19 PM 2044 17 379 5.39314

1/15/2013 5:00:32 PM 3828 18 745 5.138255

1/16/2013 6:36:19 AM 5950 19 1762 3.376844

1/16/2013 7:24:19 AM 893 15 213 4.192488

1/16/2013 11:36:21 AM 15909 20 1926 8.260124

1/16/2013 3:06:20 PM 13117 20 1568 8.365434

1/16/2013 7:56:19 PM 13020 19 1744 7.465596

1/16/2013 8:58:19 PM 1727 18 329 5.24924

1/16/2013 9:08:19 PM 229 19 38 6.026316

1/16/2013 9:38:19 PM 807 18 156 5.173077

1/16/2013 10:02:24 PM 679 18 152 4.467105

1/17/2013 1:32:25 AM 5295 19 1081 4.898242

1/17/2013 5:50:26 AM 5875 19 706 8.321529

1/17/2013 11:18:34 AM 8991 18 1453 6.187887

1/17/2013 1:08:27 PM 9692 20 986 9.829615

1/18/2013 9:48:28 AM 11310 18 2304 4.908854

1/18/2013 9:52:35 AM 146 19 21 6.952381

1/18/2013 11:58:28 AM 3338 19 457 7.304158

1/18/2013 1:20:19 PM 1684 19 271 6.214022

1/18/2013 6:38:20 PM 5965 18 942 6.332272

1/19/2013 5:42:16 AM 3186 17 695 4.584173

1/19/2013 10:04:19 AM 3707 20 644 5.756211

1/20/2013 8:48:18 AM 10729 18 1853 5.79007

1/21/2013 9:54:30 AM 6786 19 1788 3.795302

1/22/2013 2:14:23 AM 15798 16 3256 4.851965

1/22/2013 8:02:20 AM 2424 18 620 3.909678

1/22/2013 8:06:21 AM 39 17 12 3.25

1/22/2013 8:48:21 AM 854 17 190 4.494737

1/22/2013 11:27:42 AM 2751 16 639 4.305164

1/22/2013 10:10:32 PM 16378 10 2345 6.984222

1/23/2013 11:58:35 AM 15819 17 2123 7.451248

1/23/2013 10:49:31 PM 16375 9 2230 7.34305

1/24/2013 10:07:22 AM 8726 24 1238 7.048465

1/24/2013 12:04:20 PM 5354 16 582 9.199313

1/25/2013 3:13:15 PM 16379 7 2573 6.365721

1/27/2013 2:40:27 PM 16131 17 2913 5.53759

1/27/2013 10:28:16 PM 3479 14 624 5.575321

1/29/2013 10:19:29 AM 16383 3 2762 5.931571

1/30/2013 11:58:31 AM 16383 6 2637 6.212742

1/30/2013 2:40:20 PM 6218 21 808 7.695545

1/31/2013 1:58:12 PM 16382 11 2567 6.381769

2/2/2013 2:40:18 AM 16115 17 2703 5.961894

2/4/2013 6:22:29 AM 16378 8 2788 5.874462

2/4/2013 2:40:15 PM 6340 17 1031 6.14937

2/5/2013 5:28:17 PM 16382 7 2611 6.274224

2/7/2013 2:15:15 PM 15830 16 3779 4.188939

2/7/2013 2:15:16 PM 1615 19 240 6.729167

2/8/2013 10:58:23 AM 14249 18 3443 4.138542

2/9/2013 11:22:27 AM 16381 12 3655 4.481806

2/11/2013 5:49:15 AM 16380 9 3677 4.454719

2/12/2013 6:16:12 AM 16379 10 2694 6.079807

2/12/2013 6:37:07 PM 16381 7 3325 4.926617

2/13/2013 1:46:28 PM 16383 5 3371 4.859982

2/14/2013 12:07:29 PM 16377 8 3595 4.555494

2/15/2013 12:46:35 PM 16380 11 2906 5.636614

2/17/2013 12:04:14 PM 16379 7 2951 5.550322

2/18/2013 4:01:15 PM 16382 9 2581 6.347152

2/19/2013 12:22:13 PM 16374 13 2333 7.018431

2/20/2013 6:34:13 AM 16373 12 2296 7.131098

2/20/2013 12:43:13 PM 16378 6 2092 7.828872

2/20/2013 5:07:15 PM 16379 11 1931 8.482134

2/21/2013 12:37:12 PM 16382 4 2375 6.897684

2/22/2013 12:22:11 PM 16379 9 2368 6.916808

2/23/2013 2:44:14 AM 9765 17 1528 6.390707

2/24/2013 12:10:11 PM 16380 6 2807 5.835412

2/25/2013 3:25:09 AM 6661 1 959 6.945777

2/25/2013 3:07:11 PM 16382 6 2293 7.144352

2/26/2013 9:10:13 AM 16379 8 2519 6.502183

2/26/2013 5:19:13 PM 16382 6 2256 7.261525

2/27/2013 7:04:09 AM 16376 8 2594 6.31303

2/27/2013 5:37:11 PM 16377 10 2261 7.243255

2/28/2013 2:43:11 AM 15343 19 1885 8.139523

2/28/2013 4:31:16 PM 16381 6 2399 6.828262

3/1/2013 5:34:13 AM 5088 18 869 5.855006

3/1/2013 6:07:13 AM 381 11 62 6.145161

3/1/2013 1:43:12 PM 16380 9 2145 7.636364

3/1/2013 2:46:09 PM 3433 4 341 10.06745

3/2/2013 4:31:13 PM 16375 9 2196 7.456739

3/4/2013 8:01:16 AM 14624 18 2531 5.777953

3/4/2013 9:01:27 AM 1299 19 169 7.68639

3/4/2013 4:28:11 PM 16377 10 1838 8.910229

3/4/2013 6:01:13 PM 1977 20 231 8.558441

3/5/2013 2:54:59 PM 11439 19 1673 6.837418

3/5/2013 5:01:14 PM 2601 20 299 8.698997

3/6/2013 2:40:06 AM 4592 19 695 6.607194

3/6/2013 10:01:20 AM 8918 19 1177 7.57689

3/7/2013 6:55:12 AM 16376 10 2170 7.546544

3/7/2013 7:01:14 AM 165 23 18 9.166667

3/7/2013 12:49:05 PM 8925 21 1111 8.033303

3/7/2013 4:22:03 PM 3369 21 431 7.816705

3/8/2013 2:16:08 AM 3859 20 613 6.295269

3/8/2013 8:01:12 AM 3975 20 524 7.585878

3/8/2013 1:01:25 PM 7491 21 868 8.630184

3/9/2013 9:01:12 AM 9591 19 1392 6.890086

3/11/2013 9:01:15 AM 14522 18 2540 5.717323

3/11/2013 2:43:02 PM 5731 21 773 7.413971

3/12/2013 10:31:10 AM 11421 17 1988 5.74497

3/12/2013 2:40:09 PM 6564 22 933 7.03537

3/13/2013 2:25:07 AM 6346 21 1281 4.953942

3/13/2013 3:58:10 PM 16378 12 1953 8.386073

3/14/2013 10:31:09 AM 16374 10 1964 8.337068

3/15/2013 10:07:09 AM 16379 6 2141 7.650164

3/15/2013 2:40:11 PM 5615 22 666 8.430931

3/17/2013 7:43:05 PM 15486 17 2749 5.633321

3/19/2013 2:16:06 AM 18457 21 2624 7.033918

3/19/2013 11:16:17 AM 6814 24 856 7.96028

3/19/2013 11:16:17 AM 0 17 0 1

3/19/2013 11:40:05 AM 880 19 212 4.150943

3/19/2013 2:40:12 PM 4871 20 629 7.744038

3/19/2013 2:43:11 PM 43 19 4 10.75

3/20/2013 2:40:08 AM 9060 20 1303 6.953185

3/20/2013 9:43:02 AM 14777 22 1963 7.527764

3/20/2013 9:49:05 AM 275 20 40 6.875

3/20/2013 12:13:04 PM 3803 20 580 6.556897

3/20/2013 3:19:36 PM 4085 20 590 6.923729

3/21/2013 12:13:20 AM 9008 17 1434 6.281729

3/21/2013 2:43:07 PM 13313 3 2179 6.109684

3/22/2013 5:07:07 AM 16378 8 2574 6.362859

3/22/2013 1:52:09 PM 8095 17 1401 5.778016

3/23/2013 12:04:14 PM 16379 7 2929 5.592011

3/24/2013 2:46:13 AM 9776 17 1764 5.54195

3/24/2013 4:52:13 PM 7726 19 1689 4.574304

3/25/2013 11:07:00 AM 8190 18 1707 4.797891

3/25/2013 4:49:11 PM 7917 20 1419 5.579281

3/26/2013 11:15:13 AM 16382 6 3068 5.339635

3/26/2013 4:53:12 PM 8572 17 1560 5.494872

3/28/2013 6:17:09 AM 32765 7 6070 5.397858

3/28/2013 7:03:08 PM 16380 6 3150 5.2

3/29/2013 4:51:03 AM 2461 16 565 4.355752

3/29/2013 3:07:03 PM 16378 7 3052 5.366317

3/30/2013 3:05:05 PM 16381 5 3332 4.916266

3/31/2013 11:15:05 AM 9927 16 2159 4.597962

4/1/2013 8:51:04 AM 12859 17 2684 4.790984

4/1/2013 10:19:06 AM 2852 18 506 5.636364

4/1/2013 5:56:58 PM 11948 20 2241 5.331548

4/2/2013 11:19:09 AM 16381 4 3193 5.130285

4/2/2013 11:09:04 PM 16382 3 3757 4.360394

4/3/2013 1:31:15 PM 16381 3 4357 3.759697

4/4/2013 2:43:12 AM 16381 6 4783 3.424838

4/4/2013 3:30:01 PM 16379 8 4423 3.703143

4/6/2013 6:56:05 AM 32766 4 9371 3.496532

4/7/2013 2:22:06 AM 16383 5 5146 3.183638

4/8/2013 12:54:13 PM 32770 4 9560 3.427824

4/9/2013 1:08:05 AM 16382 2 5129 3.193995

4/9/2013 12:06:14 PM 16383 3 4900 3.343469

4/10/2013 12:24:00 AM 16382 9 5779 2.834747

4/10/2013 11:50:20 AM 16380 7 4534 3.612704

4/10/2013 2:54:04 PM 16383 3 4041 4.054194

4/10/2013 4:06:03 PM 4383 3 1266 3.462085

12/31/9999 12:00:00 AM 0 6240083 0 1

tfs backup console - adminpthelper.exe has stopped working

Alex Bransky — Sat, 16 Feb 2013 00:04:08 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2013/02/15/152117.aspx

I got this message from the 2012 version of the console after installing the Power Tools found here:

http://visualstudiogallery.msdn.microsoft.com/b1ef7eb2-e084-4cb8-9bc7-06c3bad9148f

The problem was that I was not a sysadmin in the SQL instance, once I fixed that the error went away.

ssis: data flow path editor

Alex Bransky — Sat, 08 Dec 2012 00:34:10 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2012/12/07/151466.aspx

Ever wonder why when you click on the green line in SSIS the Data Flow Path Editor doesn't actually let you edit anything? I did. Then I realized I needed to make the changes I wanted in the source task by right-clicking it and selecting Show Advanced Editor.

ssrs: the report execution has expired or cannot be found

Alex Bransky — Thu, 06 Dec 2012 22:06:59 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2012/12/06/151461.aspx

Today I got an exception in a report using SQL Server Reporting Services 2008 R2, but only when attempting to go to the last page of a large report: The report execution sgjahs45wg5vkmi05lq4zaee has expired or cannot be found.;

Digging into the logs I found this:

library!ReportServer_0-47!149c!12/06/2012-12:37:58:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.;

I knew it wasn't a network problem or timeout because I could repeat the problem at will. I checked the disk space and that seemed fine as well. The real issue was a lack of memory on the database server that had the ReportServer database. Restarting the SQL Server engine freed up plenty of RAM and the problem immediately went away.

arithmetic overflow in VS2010

Alex Bransky — Wed, 20 Jun 2012 16:29:35 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2012/06/20/149997.aspx

For anybody getting an arithmetic overflow error in Microsoft.Data.Schema.TSqlTasks.targets in Visual Studio 2010, what resolved the problem for me was deleting the schema comparison file in my database project and then getting it from TFS again. I guess it must have been corrupted somehow.

UPDATE: That only worked temporarily, the error came back when getting latest from TFS and alternatively when opening a schema comparison in a different database project. Ugh.

export blobs from SQL Server table into their respective files

Alex Bransky — Thu, 03 May 2012 19:08:08 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2012/05/03/149528.aspx

In case anyone is storing their files as blobs in SQL Server and is interested in the best way to export them, check this out:

http://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

SQL Server... help!

Alex Bransky — Wed, 29 Feb 2012 22:43:37 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2012/02/29/148846.aspx

I have two Great Plains databases, let's call them DB1A and DB1B, that are schematically identical and the data is 99% identical but DB1B has a tiny bit more data. I'm trying to optimize a very complex query that uses views nested in views and is way too long to post all of here. The query was averaging 1 min 43 sec before I started. I noticed that all of the tables involved were heaps (Microsoft's fault, not mine) so I started converting them to clustered one by one, checking the plans and parallelism after each change. The first weird thing is that after I convert 3 or 4 of the tables the query stops using parallelism even though its new plan can result in much longer query times. I got to the point in DB1B where the query only took 1 second, was not parallel, and had low logical reads and zero physical reads. When I updated the tables in DB1A to match those clustered-wise in DB1B, the query plan was different, still using parallelism, and when executed it took 56 seconds and had high logical reads but zero physical reads. ??

The two databases are on the same instance, and I assume SQL Server will decide on its own which database gets to use more of the cache. Since DB1A is production and DB1B is only for my testing I'm guessing DB1A gets a lot more of the cache. If there are zero physical reads, doesn't that mean the results are coming from the cache?

To add to the confusion, I ran UPDATE STATISTICS WITH FULLSCAN on the primary table in the query in both databases, and not only did that not help the query in DB1A, it caused the query in DB1B to slow down to 2+ minutes and do millions of logical reads, just like in DB1A. Is it possible that SQL Server only used the fast query plan in DB1B because the statistics were out of date?? Do you think the cache is playing a bigger role than the stats? Am I missing something?

Also, when I did the UPDATE STATISTICS WITH FULLSCAN it caused my page life expectancy to take a nosedive (at least I think that was the cause).

drop and recreate sql server indexes

Alex Bransky — Wed, 04 Jan 2012 21:22:08 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2012/01/04/148243.aspx

I have a real-time reporting server with a database which is basically a copy of my production database, but it's populated using transactional replication and replication only copies the primary keys over, i.e. none of the secondary indexes are included. So what I do is every time I need to recreate replication--which is every deployment with schema changes--I script out DROPs and CREATEs for all the reporting indexes I've created and then run the script after replication has been recreated. The script I use is a modified conglomeration of scripts I've found on the Internet, but you may find it useful. Note that it excludes primary keys and some other things.

PRINT '------------------------- DROP INDEXES     ----------------------------------------------------'
DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR
    SELECT indexes.name, objects.name, schemas.name
    FROM sys.indexes
    JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
    JOIN sys.schemas ON objects.schema_id = schemas.schema_id
    WHERE indexes.index_id > 0
    AND indexes.index_id < 255
    AND objects.is_ms_shipped = 0
    AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
    ORDER BY indexes.name

--SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
SET @sql = N'IF EXISTS (SELECT 1 FROM sys.indexes i WHERE i.name = ''' + @indexname + ''') DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
PRINT @sql
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes

PRINT CHAR(10) + CHAR(13) + '------------------------- CREATE INDEXES     ----------------------------------------------------'

declare
@object_id          int,
@index_id           tinyint,
@schema_name        sysname,
@table_name         sysname,
@index_name         sysname,
@type               tinyint,
@uniqueness         bit,
@indexed_column     sysname,
@included_column    sysname,
@indexed_columns    varchar(max),
@included_columns   varchar(max),
@has_included_cols bit,
@is_descending_key bit,
@stmt               varchar(max),
@crlf               char(2)

set @crlf = char(13) + char(10)

declare indexes cursor
for
    select
    schema_name     = s.name,
    table_name      = t.name,
    index_id        = i.index_id,
    index_name      = i.name,
    type            = i.type,
    uniqueness      = i.is_unique
from
         sys.schemas            s
    join sys.tables             t   on s.schema_id = t.schema_id
    join sys.indexes            i   on t.object_id = i.object_id
where
    i.type > 0 -- none -heap
    AND I.index_id > 0 -- ignore PKs
    AND I.index_id < 255
    AND i.type = 2
    AND INDEXPROPERTY (I.object_id,I.NAME,'ISCLUSTERED') =0
    AND left(I.object_id,3) not in ('sys', 'dt_')
    AND left(I.name,3) not in ('PK_', 'UQ_','nci','ucm','ci_','uk_')
order
    by i.name

open indexes

fetch
    indexes
into
    @schema_name,
    @table_name ,
    @index_id   ,
    @index_name ,
    @type       ,
    @uniqueness

while @@fetch_status<>(-1)
begin

    select @object_id = object_id(@schema_name + '.' + @table_name)
    set @indexed_columns = '('

    declare indexed_columns cursor
    for
        select
            c.name,
            ic.is_descending_key
        from
                sys.index_columns ic
            join sys.columns        c   on ic.column_id = c.column_id
                                       and ic.object_id = c.object_id
        where
            ic.object_id = @object_id
            and ic.index_id = @index_id
            and ic.is_included_column = 0
        order by
            ic.index_column_id

    open indexed_columns

    fetch indexed_columns
    into @indexed_column, @is_descending_key

    while @@fetch_status<>(-1)
    begin

        set @indexed_columns = @indexed_columns + @indexed_column +
                               case @is_descending_key when 1 then ' DESC ' else '' end + ', '

        fetch indexed_columns
        into @indexed_column, @is_descending_key

    end

    close indexed_columns
    deallocate indexed_columns

    set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')'

    if exists
           (select object_id
            from    sys.index_columns
            where   object_id = @object_id
                    and index_id = @index_id
                    and is_included_column = 1 )
    begin
        set @included_columns = 'INCLUDE ('

        declare included_columns cursor
        for
        select
            c.name,
            ic.is_descending_key
        from
                 sys.index_columns ic
            join sys.columns        c   on ic.column_id = c.column_id
                                       and ic.object_id = c.object_id
        where
            ic.object_id = @object_id
            and ic.index_id = @index_id
            and ic.is_included_column = 1
        order by
            ic.index_column_id

        open included_columns

        fetch included_columns
        into @included_column, @is_descending_key

        while @@fetch_status<>(-1)
        begin

            set @included_columns = @included_columns + @included_column +
                                    case @is_descending_key when 1 then ' DESC ' else '' end + ', '

            fetch included_columns
            into @included_column, @is_descending_key

        end

        close included_columns
        deallocate included_columns

        set @included_columns = left(@included_columns, len(@included_columns)-1) + ') '

    end

    set @stmt =
    'CREATE ' + case @uniqueness when 1 then 'UNIQUE ' else '       ' end + case @type when 1 then 'CLUSTERED ' else '' end +
    'INDEX ' + @index_name + ' ON ' + @schema_name + '.' + @table_name + @indexed_columns + ' ' + isnull(@included_columns,'') + @crlf

    set @included_columns = ''

    print @stmt

    fetch
        indexes
    into
        @schema_name,
        @table_name ,
        @index_id   ,
        @index_name ,
        @type       ,
        @uniqueness

end

close indexes
deallocate indexes

annoying SSRS bug fix for building

Alex Bransky — Thu, 30 Jun 2011 18:19:58 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2011/06/30/146037.aspx

If you're like me you've been using SSRS 2008 for a long time with TFS and have constantly had to delete the bin folder in order for builds to work. This has irritated me to no end. Well today is my lucky day! There is a hotfix that actually fixes the bug! Go here: http://support.microsoft.com/kb/2438347

indexes and deadlocks

Alex Bransky — Fri, 24 Jun 2011 18:27:03 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2011/06/24/145971.aspx

I recently discovered that creating indexes in one of my production databases was causing deadlocks. My problem was that I wasn't using ONLINE=ON when I was creating the index. Check out the BOL for more information.

removing an ssrs instance from a scale-out deployment

Alex Bransky — Thu, 03 Mar 2011 19:49:30 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2011/03/03/144173.aspx

If you're like me you had at one time connected one of your Reporting Services instances to a report server database that was already in use by another instance. This allows the instance to show up in the Scale-out Deployment section of the Reporting Services Configuration Manager. My problem was that the server that got joined to the original server was no longer available as it had been repurposed, and when I clicked Remove Server to remove it from my scale-out it would fail because it couldn't contact the server. After searching for a solution for quite some time I decided to look around in the report server database tables, and voila! All I had to do was remove the old server from the Keys table. I can't guarantee there won't be any side effects to this method, but it worked like a charm for me.

migrating sharepoint databases

Alex Bransky — Wed, 16 Feb 2011 22:55:44 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2011/02/16/143970.aspx

If you're wondering how to migrate your SharePoint databases to a new server, this Microsoft article is actually pretty useful, though still overly complex like most of their other articles.

http://technet.microsoft.com/en-us/library/cc512725.aspx

The one thing I would change is that they seem to recommend installing SQL Server Configuration Manager on web servers, when all that was needed in my case was to add an entry to the hosts file on the SharePoint web server that used the IP address of the new SQL Server with the name of the old SQL Server. This might not be appropriate in cases where the old server is not being decommissioned.

recursive history in tfs

Alex Bransky — Fri, 06 Aug 2010 20:45:52 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2010/08/06/141222.aspx

For those of you who, like me, transitioned from Subversion and TortoiseSVN to TFS, you might be confused as to how to view the history of a folder recursively. My problem was that I was wanting to see the recursive history in the Solution Explorer rather than the Source Control Explorer.

creating a list of consecutive integers in c#

Alex Bransky — Wed, 28 Apr 2010 19:17:50 GMT

Originally posted on: http://geekswithblogs.net/influent1/archive/2010/04/28/139572.aspx

If there's already a way to get a List<int> of consecutive integers without a loop in C#, I don't know what it is, so I created a method for it.

        public static List<int> GetIntegerListFromRangeUsingLoop(int start, int end) {
            if (end < start) {
                throw new ArgumentException("Faulty parameter(s) passed: lower bound cannot be less than upper bound.");
            }
            List<int> returnList = new List<int>(end - start + 1);
            for(int i = start; i <= end; i++) {
                returnList.Add(i);
            }
            return returnList;
        }

UPDATE:

I was pointed to Enumerable.Range(), so I updated my code.

        public static List<int> GetIntegerListFromRangeUsingEnumerableRange(int start, int end) {
            IEnumerable<int> list = Enumerable.Range(start, end - start + 1);
            return list.ToList();
        }

Then I used the StopWatch class in my unit tests to compare the two methods.

Results:

GetIntegerListFromRangeUsingEnumerableRange averaged 6.5 milliseconds.

GetIntegerListFromRangeUsingLoop averaged 0.43 milliseconds.

Wow. Apparently Enumerable.Range() is a lot slower than using a loop.