tag:blogger.com,1999:blog-76357810114243976422024-03-05T20:58:10.825+05:30Manjunath C Bhat's BlogManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.comBlogger58125tag:blogger.com,1999:blog-7635781011424397642.post-83750149499742032542014-01-27T23:39:00.000+05:302014-01-27T23:39:20.988+05:30General Approach To SQL Server Performance Tuning<div dir="ltr" style="text-align: left;" trbidi="on">
General Approach to Performance Tuning<br />
<br />Examine the Nature of Software/Application which uses the SQL Server.<br />
<br />Gather the average number of users/sessions during peak hours/non-peak hours.<br />
<br />Gather/Review the existing hardware<br />a. RAM<br />b. CPU<br />c. Hard Disk<br />d. Disk level<br />e. Physical server or virtual server<br />
<br />Gather information on various network bandwidth/latency/limitations<br />
<br />Gather the exiting SQL Server Maintenance Plans<br />a. Backup Strategy <br />b. Index Rebuild<br />c. Index Reorganise<br />d. Update Statistics<br />
<br />Gather top expensive queries/long running queries over period of time<br />
<br />Gather all missing/unused indexes<br />
<br />Get the database design, so as to check whether the queries coming in are dynamic or Ad Hoc queries or using stored procedures.<br />
<br />Once all this is done<br />
<br />Tune the Software/Application by increasing the heap memory/code modification, avoid ad hoc queries, use stored procedure etc.<br />
<br />Try if you can upgrade the existing hardware to maximum level affordable<br />
<br />Build a clean backup strategy so as to have full backup outside business hours<br />
<br />Use Perfmon to capture DISK IO, Index Information, Page Latency, User connections, Blocked process, lock waits, memory, CPU etc<br />
<br />Review and rebuild the existing maintenance plans to achieve more performance<br />Check for blockings<br />
<br />Review the expensive queries, study the query plan and improve the same<br />
<br />RUN Database Tuning Advisor on Poor performance/long running/Expensive queries, apply the necessary changes, and do not go with all the things suggested.<br />
<br />At this moment of time you should be able to have fair amount of increase in performance and decision on further troubleshooting and performance tuning.</div>
ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com1tag:blogger.com,1999:blog-7635781011424397642.post-40137830611044163022013-12-03T17:27:00.001+05:302013-12-03T17:27:43.612+05:30Edition Upgrade SQL Server Step by Step Instruction<p>I was wondering how can I upgrade my SQL Server Edition. Say I have a SQL Server Standard edition installed on one of my Server. Now due to some business needs, I need to upgrade my SQL Server Standard Edition to SQL Server BI Edition. I was able to achieve this and have documented these step by step instruction below. This is for Microsoft SQL Server Edition Upgrade from Standard Edition Installation to BI Edition Installation. This edition Upgrade Does not need any Re-Boot of server until there is any dependency.</p> <p>Note: Upgrade can be done only to Higher Edition. Ex, from Evaluation to Standard or Standard to Enterprise etc. </p> <p>Step1: Open the SQL Server Installation Media of BI Edition and run the setup.exe as below. SQL Server Setup processes.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUIMcqKRVhhxrtJ7ldwriEk6-WV7rmckctNDV2g2ntUN-bO29CULMWlhkNVfoXpV1M7kzjxPjrdjTzP0eLg19FnaRW0D72jw9Plul2m2JXuv50UobvjUllTAptTTL86-qaZJ_vSkYl_j0/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-ronYVvKrnFQ/Up3Gafe6mvI/AAAAAAAAAPg/8gW2B98f3DA/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="182" /></a></p> <p>Step2: SQL Server Installation Center appears, click on Maintenance Tab on Left and Click on Edition Upgrade Option as shown below.</p> <p><a href="http://lh4.ggpht.com/-9Cyxx_2hz9k/Up3GdqkQMKI/AAAAAAAAAPo/7kij7MCLWr0/s1600-h/image%25255B10%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhd-oo9XvnIedYWlmiIeSMVMJqM8ruBsbtc7AA4vWQo7yW4APIULKjV07s6YInTRAIvyT41EgQR48PSCEn38gg0Hl_8MGWRvmbNU4ycb1wisoJSaz0wP4sV4b-tbdZzwr1aSwSWG5R1oYs/?imgmax=800" width="347" height="270" /></a></p> <p>Step3: SQL Server Setup starts and setup support rules are run, once the operation is completed and the setup rules are passed, if any of them fail, check verify and re-run the rules and  click ok as shown below.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvXjpfsWrZoStXyMt4dxF9crZRoldyvwOebQ5H3X6zbeSQEELdqUhQJr1OnoMVhns6PDm9JX8dayVPSkOyfsMt23gDboL5UaO4NmzopgwVlilHi3PgbfH9Z3ZafwYvuG9d3hIVxRe4nzo/s1600-h/image%25255B14%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-SzQr2aTcgME/Up3GlwVuMzI/AAAAAAAAAQA/ee7niUtSdDc/image_thumb%25255B6%25255D.png?imgmax=800" width="347" height="267" /></a></p> <p>Step4: SQL Server Setup support rules for Edition Upgrade are run, once the operation is completed and the setup rules are passed, if any of them fail, check verify and re-run the rules and  click Next to continue as shown below.</p> <p><a href="http://lh6.ggpht.com/-VD_nZEyoAWg/Up3Gow6tqtI/AAAAAAAAAQI/V3aYbUYYJX8/s1600-h/image%25255B18%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-xaonBB67Y08/Up3Gq5XX_BI/AAAAAAAAAQQ/yst4AA-NaWQ/image_thumb%25255B8%25255D.png?imgmax=800" width="352" height="269" /></a></p> <p>Step5: Product Key window appears. Enter the BI Edition product key(sometimes this get populated automatically if you are taking this from some iso) and click Next as shown below.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjzyq3L4YRdgANeIt7dlUTFMTzl5gHF8yOvZjuJMn2alSLl0BbUwTjaiVjM0l0GyTFHlHaK1q5oRz1tEJqO5i1IkYm8MQ3uQ3up0vlt36k7jXEsR6oLEFziokFPRdAqJfgf48HVLBmzANQ/s1600-h/image%25255B22%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-5pTjmDtgT6o/Up3GvlzhbEI/AAAAAAAAAQg/NY-g9P4oWQc/image_thumb%25255B10%25255D.png?imgmax=800" width="352" height="272" /></a></p> <p>Step6: Accept the license terms and click next as shown below.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggSfOpuWdvbVMNIDJ6UKAyLhXkoDNQftQWN8ZCWt4rhEuhGYm_DBmzpsTJjltWnTOUBzZJ005gHilX5zhu9t-JEkYRbwZBD8UullRCaMC3M0Q0UQKN6n8RT0ZlcC_BmdZd1Oj1BdKbg3A/s1600-h/image%25255B26%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsPeDh762ezAHR5gEJlpwTL3cyGvB7BVfGkvM5WiBJvyxAScwDgjN_tmAIMi6GM8AUhf30AmzPFCNtMSzn6_uiq9-Bl1do7_A9lt914gYHGY7LV-AlynF8AYi0LpRnJyVGJHv1IX9DY-s/?imgmax=800" width="356" height="272" /></a></p> <p>Step7: Select the instance which you want to upgrade(Only if multiple instances are installed on the machine else MSSQLSERVER default instance will appear) and click Next as shown below.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrQuN7QNpNRGl76Q0sLGaocxzg_P79vxtvdzwdkwHUGPsmDBjRjzCLFt2D4Kkcu4MyVPFezxYN1B4i4Rh-B11n89Eo_M4sl_fBt9o0zJitf7KrsAIV-38xCotwT-oIQ1VKVXgtyfGgDGw/s1600-h/image%25255B30%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwWZYp4-fx4MzbBsDsY1PhwApNYdBU2JpqRRoaPo3yWSaZcyy1Gc4SEDYiHK5vFhHVtrnlN9rEp0AC8w1IZcCOD6m7r1xvQxyCXMgy-uEu4qHHw9Zg6DbFTQyJ445D-FCYXNI2AMo2yd8/?imgmax=800" width="360" height="277" /></a></p> <p>Step8: Edition Upgrade Rules are run, once the operation is completed and Edition upgrade rules are passed, if any of them fail, check verify and re-run the rules and  click Next as shown below.</p> <p><a href="http://lh3.ggpht.com/-nSuH3cFVzog/Up3G97se2sI/AAAAAAAAARI/-djyJVDTDvo/s1600-h/image%25255B34%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhu1x09_cMqWope-df0VJa3h7djV389AvUuKLV25ozgw-LIDJxA80EDBy04etrpIgs7L2qliKLk0eynoDJBg0QzePgu3x0x6K1wrC6udLQIHSq5BPSwi61IFtjMyrCeXETxsa-aZ6Jvt1E/?imgmax=800" width="365" height="282" /></a></p> <p>Step9: Your installation is now ready to upgrade. Review the changes to your standard edition. Make a Note of Path for configuration File Path. This file can be used to automate the things in future if you want to run bulk upgrade. Click Next to Upgrade as shown below.</p> <p><a href="http://lh6.ggpht.com/-AQdh_klF4v4/Up3HCu32t-I/AAAAAAAAARY/5QsGIxwgvP8/s1600-h/image%25255B38%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTR4O-iGApYSZjwrSFaKOsvnszxswEpscBVbff7LzBrNKu59YX1rfhTfpgvdJSKpRXGtMziT5N1l4bB129Pn0oJHZ4-I90HfTEbxYU85-mlZtx_R4ipcs_-ms-CiZk9mWuNOxSVYp04t0/?imgmax=800" width="366" height="282" /></a></p> <p>Step10: Once the upgrade is completed successfully review the information to see all the feature status is succeeded. Check the summary log for any more information. Click Close to exit Installation.</p> <p><a href="http://lh5.ggpht.com/-0C2YuJfXjIQ/Up3HHiC9n7I/AAAAAAAAARo/bfBMbKaDpGk/s1600-h/image%25255B42%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSdBq2DWs0jD4dRIsYM9XDvplWDfowLuG2ProgVBBjoSSfh17MVtggOJc3eXw_QBx_6KdeHGzsz-K0TAv1_JYloTzTlZnJ3zTel7KpvEBFDX8WltI8uJxK4Q51468xwO6NUDd590AksgA/?imgmax=800" width="368" height="282" /></a></p> <p>Step11. Connect to SQL Server instance which you have just upgraded and run the query “Select SERVERPROPERTY(‘Edition’) to check you get the below output just to verify your Instance has been upgraded to BI edition.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIexW36bS_Yy9QOIBnlNdDrijs6YCgvd6oHpSSl-9CIxxcGM5qgnwn6S78Qjbm_4LPjTpgD1pkPKZ1jULFNns0IR6d0X8X1uGwYcrx6JZg8aJdXTpzD74YQzTDdiBB0UvYPgKxq1L4yPg/s1600-h/image%25255B45%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhntYMYycnrlUxRsmXze4FQyVEDVYKuBFF82P_3G0npURArSvxvWquO6lEqLSEFYoVcthqluPRSbV1lIs9Ki-P3XKihfy9B5i2trofLAsnSAEHvHBIKiYpDaN_QWhDIpl9W5fzPqaJ_a7U/?imgmax=800" width="244" height="106" /></a></p> <p>That’s it. Done. You have now successfully upgraded your Existing SQL Server Standard Edition Installation to SQL Server BI Edition Installation.</p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:1393fe05-7d95-4518-b241-5d08cc89b7f2" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/SQL+Server+Edition+Upgrade" rel="tag">SQL Server Edition Upgrade</a>,<a href="http://technorati.com/tags/Upgrade" rel="tag">Upgrade</a>,<a href="http://technorati.com/tags/Edition+Upgrade" rel="tag">Edition Upgrade</a>,<a href="http://technorati.com/tags/SQL+Server+Upgrade" rel="tag">SQL Server Upgrade</a></div> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-42730312720953948042013-11-28T16:02:00.001+05:302013-11-28T16:11:53.288+05:30T-SQL to get Database backup details<p>Below is a simple yet powerful query to get the details about your database backup. This query can be customised as per your needs as this is simple join to two tables in MSDB Database.</p> <p> <pre class="brush:sql"><br /> use msdb<br /> go<br /> select bus.name,bus.[user_name],bus.backup_finish_date,<br /> ((((bus.backup_size)/1024)/1024)/1024) as 'backup_size in GB',bus.database_name,<br /> CASE<br /> WHEN bus.[type] = 'D' Then 'Full Backup'<br /> WHEN bus.[type] = 'I' Then 'Differential Database Backup' <br /> WHEN bus.[type] = 'L' Then 'Log Backup' <br /> WHEN bus.[type] = 'F' Then 'File or filegroup Backup' <br /> WHEN bus.[type] = 'G' Then 'Differential file Backup' <br /> WHEN bus.[type] = 'P' Then 'Partial Backup'<br /> WHEN bus.[type] = 'Q' Then 'Differential partial Backup'<br /> End AS 'backup_Type',<br /> bumf.physical_device_name,<br /> CASE <br /> WHEN bumf.device_type = '2' Then 'Disk'<br /> WHEN bumf.device_type = '5' Then 'Tape'<br /> WHEN bumf.device_type = '7' Then 'Virtual device'<br /> WHEN bumf.device_type = '105' Then 'permanent backup device'<br /> End AS 'Backup_Device_Type'<br /> from backupset bus<br /> left outer Join backupmediafamily bumf<br /> on bus.media_set_id = bumf.media_set_id<br /> where bus.database_name = 'YourDatabaseName'<br /> order by bus.backup_finish_date desc<br /></pre><br /></p><br /><div class="csharpcode"><br /> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:2b53f893-b62c-4d16-a8b6-f9648b543bc8" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/Database+backup" rel="tag">Database backup</a>,<a href="http://technorati.com/tags/backup" rel="tag">backup</a>,<a href="http://technorati.com/tags/backup+details" rel="tag">backup details</a></div><br /></div> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-36121076775278462172013-11-28T13:20:00.001+05:302013-11-28T13:20:48.654+05:30The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.[]<p>There can be many reasons for a Login Failure to SQL Server Instance.</p> <p>Out of this you may get one as “The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.[]”</p> <p>This error may occur when The SQL Server computer was unable to process the client login packet. Inside the square braces, will be the IP of the client from which they are trying to login.</p> <p>When the user is a member of many groups or has many policies, the token may grow larger than normal to list them all. If the token grows larger than the MaxTokenSize value of the server computer, the client fails to connect with a General Network Error (GNE) and error 17832 can occur. This problem may affect only some users: users with many groups or policies.</p> <p>To Resolve this the MaxTokenSize value of the server computer needs to be increased.</p> <p>To change the MaxTokenSize on the server computer, You need to have administrator rights on the server to perform this action.</p> <ol> <li> <p>On the Start menu, click Run.</p> </li> <li> <p>Type regedit, and then click OK. </p> </li> <li> <p>Navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters.</p> </li> <li> <p>Right-click MaxTokenSize, and then click Modify.</p> </li> <li> <p>In the Value data box type the desired MaxTokenSize value.</p> </li> <ol> <li>Note: Hexadecimal value ffff (decimal value 65535) is the maximum recommended token size. Providing this value would probably solve the problem, but could have negative computer-wide effects with regard to performance. We recommend that you establish the minimum MaxTokenSize value that allows for the largest token of any user in your organization and enter that value.</li> </ol> <li> <p>Click OK. </p> </li> <li> <p>Close Registry Editor.</p> </li> <li> <p>Restart the computer.</p> </li> </ol> <p><strong>Changing registry value incorrectly may hamper the system. Please backup the registry and have this done by a System Administrator to be sure what is done.</strong></p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:d1cb6ad6-5f86-416a-a66d-ff43f3889a83" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/MaxTokenSize" rel="tag">MaxTokenSize</a>,<a href="http://technorati.com/tags/login+packet" rel="tag">login packet</a>,<a href="http://technorati.com/tags/Login+Error" rel="tag">Login Error</a></div> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-56879857535810805012013-11-25T16:07:00.001+05:302013-11-25T16:07:59.927+05:30ReportServer: Deleting Encryption Key in SQL Server Reporting Services Configuration Manager<p>Below are the steps to Delete the Encryption Key in SQL Server Reporting Services Configuration.</p> <p>Before Deleting make sure you have taken a backup of your Current Encryption Keys. Click <a href="http://manjunathcbhat.blogspot.com/2013/11/reportserver-procedure-to-backup.html" target="_blank">Here</a> to see the procedure to take a backup if not already.</p> <p>Step1: Go to Your Source Server Reporting Services where your Reporting Services are running, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.</p> <p><img title="Untitled1" border="0" alt="Untitled1" src="http://farm6.staticflickr.com/5486/10999327313_7bd7b0451c_o.png" /></p> <p>Step2: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click Delete to delete your current Encryption Keys .</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUa-Plw59UJbVsBxxdRh9k6FOvDj-7hB0IpLF6uncNBP2jZiiBQon9N9J2qV2zoF7T7BfC9vDrdz7IDU7lJdP-yPtJtyHLV3pErmLtKyx3sqj0ID78iJIbmzrdvyLFVVp9p6K2McQt3gg/s1600-h/image%25255B10%25255D.png"><img title="image" border="0" alt="image" src="http://lh5.ggpht.com/-AebCMCXIAsY/UpMh4qDbSOI/AAAAAAAAANU/ijcrmyUJIPI/image_thumb%25255B4%25255D.png?imgmax=800" width="223" height="48" /></a></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbS0ty7CD8NP9hbappMuuzuscOKy5SSRRhLtaP5u7RZ0OyzxLPUAEsrB0U2HCycGSIxeYT8iB4Xk4kcOTX6Xep9PXh8Uo22HYj4Ktcd7OX-mZ__dkUJwi-7yhgxPjvHaqT70BtHZRnsnA/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIBpYYQRQn7XEE_z5DsWx0XWzykDQ8EeaCRyc-iHaUf5ugxRmJgnAO-gDhWAPnVMIitpswuJaH5ZlHeIbUgDKYZ15JlVE1N5cjhH7ctUAUPBt5fAxLYIxaGWvZjfZJM4hPHdqMj-2KrOE/?imgmax=800" width="366" height="52" /></a></p> <p>Step3. You will get a below Pop Up. Click yes, if you want to delete the Keys,</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhs30jFkyyz0KiutmHTG0XJYvJttpF6eapKDC1-tnhTkFRHws6wz4rO7TxaroCuYfstH0NvIt_auf7GL7MYHcp-oI4lTpc-67I0U_AhHuJ3ctTxijSCtjP9-x26n9LoAMHKboUzqQo3B-o/s1600-h/image%25255B6%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKhtZHb1IX2bB4gZ5ex653s1IMW2Ehorfg7Y6pL74DUoFY0NWKwpHyZdV5pR301puBzIuWBP3O8PUWIHS1Aps6sAiSTLprRP2NnnzmlW3XbddQET1m2Dh3m412sd9YSuzeQhdaKRrpnIE/?imgmax=800" width="244" height="150" /></a></p> <p>Close the Reporting Services Configuration Manager and Restart the Reporting Services.</p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:29631245-4b0f-4c27-8fb2-5c701511e2fb" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/Delete+Encryption+Keys" rel="tag">Delete Encryption Keys</a>,<a href="http://technorati.com/tags/Report+Server" rel="tag">Report Server</a></div> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-87167119983883390412013-11-25T15:50:00.001+05:302013-11-25T15:50:45.524+05:30ReportServer: Procedure to Backup the Encryption Keys<p>We would needing to backup the Encryption Keys used in Reporting Services for various purpose. Below mentioned are the simple steps to backup you current Encryption Keys. Note that the reporting services should be running to backup the Encryption Keys.</p> <p>Step1: Go to Your Source Server Reporting Services where your Reporting Services are running, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.</p> <p><img title="Untitled1" border="0" alt="Untitled1" src="http://farm6.staticflickr.com/5486/10999327313_7bd7b0451c_o.png" /></p> <p>Step2: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys . </p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUa-Plw59UJbVsBxxdRh9k6FOvDj-7hB0IpLF6uncNBP2jZiiBQon9N9J2qV2zoF7T7BfC9vDrdz7IDU7lJdP-yPtJtyHLV3pErmLtKyx3sqj0ID78iJIbmzrdvyLFVVp9p6K2McQt3gg/s1600-h/image%25255B10%25255D.png"><img title="image" border="0" alt="image" src="http://lh5.ggpht.com/-AebCMCXIAsY/UpMh4qDbSOI/AAAAAAAAANU/ijcrmyUJIPI/image_thumb%25255B4%25255D.png?imgmax=800" width="223" height="48" /></a></p> <p><img title="Untitled2" border="0" alt="Untitled2" src="http://farm4.staticflickr.com/3718/10999109705_3eb1849e81_o.png" /></p> <p>Ste3: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed if you want to restore this encryption key back. This encryption key holds all credentials required for reports, subscriptions etc.</p> <p><img title="Untitled5" border="0" alt="Untitled5" src="http://farm4.staticflickr.com/3824/10999110235_c1c2cf75be_o.png" /></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2ATSgOPtQIR92H-et4tiPu8JO8jxs6w4fwq1vpb-hAoS-dHUx2cEzqeXQ_-BieOnzbe2HfLDt13f6kuTPTaLx0fNomI5WgEiVvTumDfJ419UFKTiF3dXqNjxz2SXlusT-Iqf8ADlugek/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-FS4YVkShYj0/UpMkerVfSGI/AAAAAAAAAOY/EKy4ajBfQGw/image_thumb%25255B1%25255D.png?imgmax=800" width="385" height="99" /></a></p> <p>Done, You have backed up your Encryption keys. You can now use this to Restore.</p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:9e00919b-ba8d-4549-9a05-2cc6557176c4" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/Backup+Encryption+Keys" rel="tag">Backup Encryption Keys</a>,<a href="http://technorati.com/tags/Reporting+Services" rel="tag">Reporting Services</a></div> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-27158916950460889282013-11-25T15:40:00.001+05:302013-11-25T16:10:46.024+05:30Reporting Server Error: The report server installation is not initialized<p>This is another Error related to Reporting Services.</p> <p>You might come across this error after a fresh New installation of SQL Server Reporting Services or You have restored/Migrated a ReportServer Database as i described in my previous post.</p> <p>Click <a href="http://manjunathcbhat.blogspot.nl/2013/11/migrationrestoration-of-microsoft-sql.html">Here</a> to Check out the Migration/restoration of ReportServer Databases.</p> <p>Once you are done with the Migration/Restoration of your ReportServer Databases you might come across this errors.</p> <p>To Resolve this below are the two ways which i found out and worked for me.</p> <p>Generally what happens is, whenever you Restore/Migrate Report Server Databases from other server, there would be a Entry Added in you keys Table in ReportServer Databases.</p> <p>Firstly Run the below query to get the list of keys available in the Restored ReportServer Database on New Server</p> <div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; width: 97.5%; background-color: #f4f4f4"> <div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"> <pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: white"><span id="lnum1" style="color: #606060"> 1:</span> <span style="color: #0000ff">Use</span> [YourReportServerDBName] --Name <span style="color: #0000ff">of</span> the ReportServer <span style="color: #0000ff">Database</span> which you have restored</pre><br /><!--CRLF--><br /><br /> <pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span id="lnum2" style="color: #606060"> 2:</span> <span style="color: #0000ff">GO</span></pre><br /><!--CRLF--><br /><br /> <pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: white"><span id="lnum3" style="color: #606060"> 3:</span> <span style="color: #0000ff">select</span> * <span style="color: #0000ff">from</span> dbo.keys</pre><br /><!--CRLF--></div><br /></div><br /><br /><p>You will get results like below. Along with a Entry for Your Current New Server Say 1NewMachine, you will be having a Entry for your Old server as well from where you did the Restore say 1OldMachine.</p><br /><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIwsTmR8_Cd__3NrBRos2uclI2asXFJhtUtOLzekxIgufeddb7x1KqQLTmbHv7T9l1aGsfJdQYUn-xC08by2TvT7ls_kwVBNHu2e_UDXTNJ63bPs8zDWRU7W-AlhVsdTqTWoma_RLeyjI/s1600-h/image%25255B7%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-rTBzvymHo8I/UpMh1mYib-I/AAAAAAAAANE/nuOG_h3zUcg/image_thumb%25255B3%25255D.png?imgmax=800" width="551" height="59" /></a></p><br /><br /><p>Make sure you delete the Entry for your OldServer by running below query. Replace the word <1OldMachine> with your Old Machine Name from where you did the restore.</p><br /><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; width: 97.5%; background-color: #f4f4f4"><br /> <div id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><br /> <pre style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: white"><span id="lnum1" style="color: #606060"> 1:</span> <span style="color: #0000ff">Delete</span> <span style="color: #0000ff">from</span> dbo.keys <span style="color: #0000ff">where</span> MachineName = <span style="color: #006080">'1OldMachine'</span></pre><br /><!--CRLF--></div><br /></div><br />Now Go to Destination Server where your SQL Server Reporting Services are running from which you have Migrated/Restore ReportServer Database to New Destination. Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect. <br /><br /><p><img title="Untitled1" border="0" alt="Untitled1" src="http://farm6.staticflickr.com/5486/10999327313_7bd7b0451c_o.png" /></p><br /><br /><p>Go to Encryption keys Tab as shown below and click on Restore.</p><br /><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUa-Plw59UJbVsBxxdRh9k6FOvDj-7hB0IpLF6uncNBP2jZiiBQon9N9J2qV2zoF7T7BfC9vDrdz7IDU7lJdP-yPtJtyHLV3pErmLtKyx3sqj0ID78iJIbmzrdvyLFVVp9p6K2McQt3gg/s1600-h/image%25255B10%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-AebCMCXIAsY/UpMh4qDbSOI/AAAAAAAAANU/ijcrmyUJIPI/image_thumb%25255B4%25255D.png?imgmax=800" width="223" height="48" /></a></p><br /><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7715zlEMQbSZED6CenHlmRWpCsjvYRH38vXjIyk5c-8IDkjKh5FcvsCMgS_lN3cfoUmVrq6lLKmLtxwLskLlob72OgGr_GAuGr6hkvlXyylZcdDAzYR_ylNH7DNmlF8J8YHxWgy088m4/s1600-h/image%25255B15%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_GU1dfQk9W71WYkvfSv5Tv1gK8iTOqDuXLS1lZ5GX8N95Z_LyVleSyyjzaxJhFjHtFmsUfERG79vrxGnmvXz8L8cjdf-qMFrNlzBXScuBwKDYdfS_9ieskJKJSmtcOo6wXb1OeQ1r3rs/?imgmax=800" width="360" height="53" /></a></p><br /><br /><p>Select the source where you have kept the key which you have backed up from you source Server(Old Server) from where you have restored the database, provide the password which you had given during backing up of Key, and click ok. Your Key will be restored</p><br /><br /><p>Click <a href="http://manjunathcbhat.blogspot.nl/2013/11/reportserver-procedure-to-backup.html" target="_blank">Here</a> to Backup the Encryption Key and Restore, if you have not backed it up earlier.</p><br /><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6LmprotwDlOLU_rRrAgKHFEjfCfrEbneMJOrDIlYHPS0Y8q4vFth2Fli3mn6SCZe8LAdg0B3GJmSUunxQ2mfY6H1AEPZLFlQHbZuDsD3TKO0e8C1Le8enok7PGCK4Vp-TxJYCB40qfe0/s1600-h/image%25255B19%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkwUp7c7jX-LG_46Q3PLcTr6c3A208ChNLSF_oNy5BSOXW5Np-Q-4CGrkoSPJfFZG4NPq6u-FUoBym3MMj5QSRWrYqFGH6bT6x-kOOh3B2fK_YR6uANTx1BfRrJT7_BGoCZgw6FovcsJ0/?imgmax=800" width="294" height="189" /></a></p><br /><br /><p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinhUKb4qCJgi5AeivdUyndPcTz3Mab1ryToLzkHmfgRRqN1kzN48IKiJESmACIKuTlc98LfxNrlz3zh25F4Z6BVSo6fEFbVp4_TjnL0HveVmxXkrpFV62ITGzxh2YiHKs26rbdR8qZBxA/s1600-h/image%25255B23%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-I_AINymrcgs/UpMiB_aWMtI/AAAAAAAAAOE/Kbp49WWC0bQ/image_thumb%25255B11%25255D.png?imgmax=800" width="473" height="61" /></a></p><br /><br /><p>Close the Reporting Services Configuration Manager, Restart your Reporting Services and Try again. It should work.</p><br /><br /><p>If you get a Error: “An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.Invalid object name ''. Could not use view or function 'ExtendedCatalog' because of binding errors”, Not to Worry, Click <a href="http://manjunathcbhat.blogspot.com/2013/11/reportservererror-error-occurred-within.html">Here</a> to Resolve this Error.</p><br /><br /><p>If Not, Delete the Encryption and Restart the SQL Server Reporting Services. It should work.</p><br /><br /><p>Click <a href="http://manjunathcbhat.blogspot.com/2013/11/reportserver-deleting-encryption-key-in.html" target="_blank">Here</a> to get the procedure of deleting the Encryption Keys.</p><br /><br /><div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:6ec152f6-c48f-4149-8443-8f94a7cd28f6" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/ReportServer" rel="tag">ReportServer</a>,<a href="http://technorati.com/tags/SQL+Server+Reporting+Services" rel="tag">SQL Server Reporting Services</a>,<a href="http://technorati.com/tags/Report+Server+Not+Initialized" rel="tag">Report Server Not Initialized</a></div> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-73579635716359080992013-11-23T02:15:00.000+05:302013-11-25T18:13:02.275+05:30Migration/Restoration of Microsoft SQL Server ReportServer Database<p>Microsoft SQL Server Reporting Services is one of the most powerful reporting tools available in the Market. Many of the small scale to Large scale enterprise organisation use Microsoft SQL Server Reporting Services. I am not going to describe more on reporting services and its features as tons of resources are available online.</p> <p>However for those who are using Reporting Services There will be often Business Needs to Have your Report Server Migrated Moved or Restored onto Different Environment for various purpose such as custom reporting, backup reporting, Domain Migration, upgradation etc.</p> <p>Here i am going to Describe one such ReportServer Database Migration/ReportServer Database Restoration on to Different Server with Different Name. Below are the step by step instructions to have a smooth ReportServer Database Migrated/Restored.</p> <p>Step1: Go to Your Destination Server Reporting Services where you want to have Migrated/Restored ReportServer Databases configured, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.</p> <p><img title="Untitled1" border="0" alt="Untitled1" src="http://farm6.staticflickr.com/5486/10999327313_7bd7b0451c_o.png" /></p> <p>Step2: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys . This is very much needed incase you want to Rollback the Migration/Restoration and Revert back to Old configuration. </p> <p><img title="Untitled2" border="0" alt="Untitled2" src="http://farm4.staticflickr.com/3718/10999109705_3eb1849e81_o.png" /></p> <p>Ste3: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed if you want to restore this encrytion key back. This encryption key holds all credentials required for reports, subscriptions etc.</p> <p><img title="Untitled5" border="0" alt="Untitled5" src="http://farm4.staticflickr.com/3824/10999110235_c1c2cf75be_o.png" /></p> <p>Step4: Go to SQL Server Configuration Manager, CLick on SQL Services, Right Click Reporting Services and Stop SQL Server Reporting Services. This can be done from Services.Msc as well.</p> <p>Step5: This step is needed only if you are overwriting any existing ReportServer Databases. If you are Restoring/Migrating the ReportServer Databases on to New Server, then need not worry. Connect to SQL Server Instance where the ReportServer and ReportServerTempDB Databases are hosted. Backup both ReportServer and ReportServerTempDB Databases and keep it in safe location, where it does not get overwritten or deleted. This is needed if incase you want to Rollback the Migration/Restoration and Revert back to Old configuration. </p> <p>Step6: Go to Source Server where your SQL Server Reporting Services are running from which you want to Migrate/Restore ReportServer Database to New Destination. Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.</p> <p><img title="Untitled1" border="0" alt="Untitled1" src="http://farm6.staticflickr.com/5486/10999327313_7bd7b0451c_o.png" /></p> <p>Step7: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys . This is needed in later stage to restore on destination server incase needed. </p> <p><img title="Untitled2" border="0" alt="Untitled2" src="http://farm4.staticflickr.com/3718/10999109705_3eb1849e81_o.png" /></p> <p>Step8: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed when you are restoring this encrytion key back on destination environment. This encryption key holds all credentials required for reports, subscriptions etc.</p> <p><img title="Untitled5" border="0" alt="Untitled5" src="http://farm4.staticflickr.com/3824/10999110235_c1c2cf75be_o.png" /></p> <p>Step9: Connect to Source SQL Server Instance where your current ReportServer and ReportServerTempDB are hosted. Take a copy Only Backup so the LSN wont get affected of both ReportServer and ReportServerTempDB Databases and have this two backup files copied onto Destination Server or Any Network Share folder accessible from destination Server.</p> <p>Step10: Connect to Destination SQL Server Intance where you want the Migrated/Restored ReportServer and ReportServerTempDB Databases to be hosted. Backup the Users,Permissions,Roles,securables etc which needs to be applied back once you restore the Database in Next Steps.</p> <p>Step11: Connect to Destination SQL Server Intance where you want the Migrated/Restored ReportServer and ReportServerTempDB Databases to be hosted. Restore the ReportServer database from the backup file copied in previous step. Now restore the ReportServerTempDB from the backup file copied in previous step. Drop the orphaned users and assigned New security users or have the security replicated which was existing before Restoration which you may have backed up using previous step. Best Practice is to Retain both ReportServer and ReportServerTempDB name which is coming from source server. That is if the Source ReportServer and ReportServerTempDB Names and Destination ReportServer and ReportServerTempDB are same then no worries. If the Source ReportServer and ReportServerTempDB Names and Destination ReportServer and ReportServerTempDB names are not same, then this will be handled in later steps.</p> <p>Ex: Source DB Details</p> <p>MyReportServerDB and MyReportServerTempDB</p> <p>Ex: Destination DB Details</p> <p>MyReportServerDB and MyReportServerTempDB</p> <p>If this is case nothing to worry much.</p> <p>Step12: Go to SQL Server Configuration Manager on Destination Server, CLick on SQL Services, Right Click Reporting Services and Start SQL Server Reporting Services. This can be done from Services.Msc as well.</p> <p>Step13: Go to Your Destination Server Reporting Services where you want to have Migrated/Restored ReportServer Databases configured, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.</p> <p><img title="Untitled1" border="0" alt="Untitled1" src="http://farm6.staticflickr.com/5486/10999327313_7bd7b0451c_o.png" /></p> <p>Step14: Go to Encryption Keys Tab, Click on Restore button, select the Encryption Key which you have backed up on Source Server in Step8. Provide the password and click ok and close Reporting Services Configuration Manager.</p> <p>Step15: Go to SQL Server Configuration Manager on Destination Server, CLick on SQL Services, Right Click Reporting Services and ReStart SQL Server Reporting Services. This can be done from Services.Msc as well.</p> <p>Step16: Go to Internet Explorer and try to Open Reporting Services Manager GUI. Ideally it will be <a href="http://destinationserver/Reports">http://DestinationServer:80/Reports</a> unless you have configured a different virtual Directory. You should be now able to See the Reporting Services Manger GUI with all the reports which were on source server.</p> <p>Congrats. You have now migrated your ReportServer Databases to a New Server.</p> <p>Now if you have encountered a error stating: The report server installation is not initialized. Not to Worry. Please follow the below link to resolve this issue.</p> <p><a href="http://manjunathcbhat.blogspot.com/2013/11/reporting-server-error-report-server.html" target="_blank">Click Here to Resolve The report server installation is not initialized Error</a></p> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-65187741588551787622013-11-22T18:02:00.001+05:302013-11-23T02:43:46.689+05:30ReportServerError: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.Invalid object name ''. Could not use view or function 'ExtendedCatalog' because of binding errors.<div dir="ltr" style="text-align: left;" trbidi="on">
There will be often Business Needs to Have your Report Server Migrated Moved or Restored onto Different Environment. You May come up with Different Errors Post Restoration. Here i am going to describe one such Error. You May get the below error when you have your ReportServer Databases Restored<br />
“An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.Invalid object name ''. Could not use view or function 'ExtendedCatalog' because of binding errors.”<br />
This is because there is a function in Your ReportServer Database by Name 'ExtendedCatalog' . This will be holding the entry to corresponding ReportServerTempDB.<br />
The Old Names may be different and New Names might be different. Since this catalog will still be pointing to Old ReportServer Temp Database you will get this error.<br />
Resolution:<br />
To resolve this error, you need to Modify the Function and Change the Old ReportServerTempDB Name to New one<br />
Old Code Snippet:<br />
[OldReportServerTempDB].dbo.TempCatalog TC<br />
New Code Snippet:<br />
[NewReportServerTempDB].dbo.TempCatalog TC<br />
Done, now you will be able to run your Reports.<br />
I will write a Separate Topic on Clean and Neat ReportServer Database Restoration.<br />
<div class="wlWriterEditableSmartContent" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:0c03032d-4706-443a-84b9-5b9af7799d2d" style="display: inline; float: none; margin: 0px; padding: 0px;">
Technorati Tags: <a href="http://technorati.com/tags/ReportServerTempDB" rel="tag">ReportServerTempDB</a>,<a href="http://technorati.com/tags/ReportServer" rel="tag">ReportServer</a>,<a href="http://technorati.com/tags/ExtendedCatalog" rel="tag">ExtendedCatalog</a></div>
</div>
ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-67195936042369469492013-11-19T20:01:00.001+05:302013-11-19T20:01:47.008+05:30Backup Database Error SQL Server 2012: This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally<p>Microsoft SQL Server 2012 has introduced a new feature called AlwaysOn High Availability. There are numerous resources online to get information on AlwaysOn High Availability. So here i am not going in depth with features of this. </p> <p>Will be coming up with some interesting post on this topic later. But for those you have already configured this feature, you might run into below error while running a backup command.</p> <p>"This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally"</p> <p>This is because AlwaysOn is configured to only allow backups on the primary replica.</p> <p>So avoid this if you are running backup from Some SSIS Package, make sure you include below phrase in your backup command</p> <pre class="csharpcode">sys.fn_hadr_backup_is_preferred_replica ( name ) = 1</pre><br /><style type="text/css"><br />.csharpcode, .csharpcode pre<br />{<br /> font-size: small;<br /> color: black;<br /> font-family: consolas, "Courier New", courier, monospace;<br /> background-color: #ffffff;<br /> /*white-space: pre;*/<br />}<br />.csharpcode pre { margin: 0em; }<br />.csharpcode .rem { color: #008000; }<br />.csharpcode .kwrd { color: #0000ff; }<br />.csharpcode .str { color: #006080; }<br />.csharpcode .op { color: #0000c0; }<br />.csharpcode .preproc { color: #cc6633; }<br />.csharpcode .asp { background-color: #ffff00; }<br />.csharpcode .html { color: #800000; }<br />.csharpcode .attr { color: #ff0000; }<br />.csharpcode .alt <br />{<br /> background-color: #f4f4f4;<br /> width: 100%;<br /> margin: 0em;<br />}<br />.csharpcode .lnum { color: #606060; }</style><br /><br /><p>So this will run backup on databases which are only Primary replica.</p><br /><br /><div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:eac9f673-a018-4477-84af-038b733946cc" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/AlwaysOn+High+Availability" rel="tag">AlwaysOn High Availability</a>,<a href="http://technorati.com/tags/database+mirror" rel="tag">database mirror</a>,<a href="http://technorati.com/tags/secondary+replica" rel="tag">secondary replica</a>,<a href="http://technorati.com/tags/primary+replica" rel="tag">primary replica</a>,<a href="http://technorati.com/tags/Microsoft+SQL+Server+2012" rel="tag">Microsoft SQL Server 2012</a></div><br /><br /><p>Hope this helps.</p> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-6226643988878625112013-11-15T16:21:00.001+05:302013-11-15T16:21:07.028+05:30"dbcc checkdb('') with no_infomsgs" failed with the following error: "The In-row data USED page count for object "", index ID , partition ID , alloc unit ID (type In-row data) is incorrect. Run DBCC UPDATEUSAGE<p>As most of us are aware that DBCC CheckDB is one of the most important DBCC Command which is used to check for database integrity and corruption and consistency.</p> <p>DBCC CheckDB may come up with many Error but today i will be going through one such error.</p> <p>The error is as mentioned below.</p> <p>"dbcc checkdb('') with no_infomsgs" failed with the following error: "The In-row data USED page count for object "", index ID , partition ID , alloc unit ID  (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.</p> <p>Below mentioned are the some of the possibilities for this error.</p> <p>1.This error generally occurs when inaccuracies are found in rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index.  </p> <p>2. This error occurs because DBCC CheckDB has found an incorrect value on page for the USED space. </p> <p>3. This might have happened because the database on which you are running DBCC CheckDB is upgraded from SQL Serer 2000 version and in SQL 2000 it was possible for the row and page counts for a table or index to be incorrect, even negative. </p> <p>4. This error also might occur if there are more DDL statements being issues more frequently on a database.</p> <p>The resolution to this is very simple as stated in Error itself. </p> <p>Run DBCC UPDATEUSAGE(‘DatabaseName’) </p> <p>If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.</p> <p>Make sure, you run this command during off peak hours as it might take a long time to run some times.</p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:2881c332-9f9a-4e6f-9c06-6dae0ad6561b" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/DBCC" rel="tag">DBCC</a>,<a href="http://technorati.com/tags/DBCC+UPDATEUSAGE" rel="tag">DBCC UPDATEUSAGE</a>,<a href="http://technorati.com/tags/DBCC+CHECKDB" rel="tag">DBCC CHECKDB</a>,<a href="http://technorati.com/tags/In-row+data" rel="tag">In-row data</a>,<a href="http://technorati.com/tags/type+In-row+data" rel="tag">type In-row data</a></div> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-55131590264960568912013-11-13T20:50:00.001+05:302013-11-13T20:50:01.298+05:30SSAS Deployment Failed - Error 3 The project could not be deployed to the '' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0<p>In one of my Old Post, I posted a SSAS Deployment failed because of insufficient rights for a User to deploy the cube/project to SSAS Server.</p> <p>Now is this Post we will see another scenario where the SSAS Deployment Fails.</p> <p>When you are done with developing your Cube/project you provide all your server setting, user credentials etc. and when you click on deploy bang.. You get this error.</p> <p>Error 3 The project could not be deployed to the '' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0</p> <p>Now there could be couple of reason for this error to pop up.</p> <p>1. The SSAS Service on the Server on which you are trying to Deploy the project is down. To resolve this you need to go to that server and start the SQL Server Analysis Services.</p> <p>2. There might be Network Issues or no connectivity between the Development Server and Analysis Server. You need to have contact you network Team and resolve this issue.</p> <p>3. This is the one which i am going to explain now and steps to resolve this. You are developing your project on some remote Machine and your SSAS is hosted on some other Machine.  By Default when you Install a New Installation of SQL Server Analysis Services on a Server, the TCP/IP port 2383 will be blocked by Firewall on that server to access to this Port. To Resolve this, you need to follow below mentioned step by step instruction to have that port Opened and provide access to that Port.</p> <blockquote> <p>Step 1.</p> <p>Connect to Windows Server on which the SQL Server Analysis Services are installed.</p> <p>Step 2.</p> <p>Point your mouse pointer to lower left corner of the screen if Windows 2012 as shown below and click Run or for other go to Run and type WF.msc as shown below.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXEiNHqiZTHnrxAWiEWpKdy_wzEMZEGAi2hf7T99X9VWHCvJJd_8LYjvqpRSIk_SAkNAeLgKgVtqG1LA2ClQ8VhobYG0KtbjekoLHtm6avpZenvQkHOjZ-xiL7NaYxLfiEmRTBaGUB9Mc/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrhXb3X0thQ02M2mqdbdzGKLVeDGWMFv3l3fQvVw3v8oOVf3rG7kZ_MrJrnzTLvFDsDJ6-PfWsmK_Hib3-9AHmSWR9kUigxct6kKsZ4NwjfHGTgkuxnvB9seQfgDvJ_YpN9OTmfsBz_5Q/?imgmax=800" width="244" height="109" /></a></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVEtnKmNaQvGr4tgzu2u01FRwvA9eoUWOeqkVFRbBZjv6orriHk6pLUQ5u9IZ4vdD4GnK-cJfFfNqktTdT3ntYZFEmNWu5tSVbYlZlsGCSgnA-vx5BUhRvmm-KV48Lkd6bl34ePQFq_eY/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-ngZOQLiYOjw/UoOYN5Kt1oI/AAAAAAAAAKQ/3YzYCQ00n8M/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="138" /></a></p> <p>Step 3.</p> <p>On the Windows Firewall Screen which Pops Up, Right Click on InBound Rules and Click New Rule as shown below.</p> <p><a href="http://lh5.ggpht.com/-ppK8Ev6qqaQ/UoOYPOEtNDI/AAAAAAAAAKY/UwNukv43TfI/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKJEN51ciu7uUmyH_X9hC-ME8ICo_4aYL_uq8we0Q4-GxlzvNoyVO60_WvAayZ-IGeALGcwtenGJQUuvmr4-t25e5biNBkunotU-Oc_iWoiWia4XlW1vjt_uzeNF77X8EzXO9W4qDB5mE/?imgmax=800" width="217" height="244" /></a></p> <p>Step 4.</p> <p>The New InBound Rule wizard appears, click on Port Radio Button in What type of Rule would you like to create section and click Next.</p> <p><a href="http://lh4.ggpht.com/-6-pD_8785fQ/UoOYStxmd6I/AAAAAAAAAKo/0A419EpgWc4/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdf556rRg7siRDRb2rLonBTGPnQmCgI4v3Zud0h92CMD4Jtyf1B55nmq-BndFkuFQYZRfgbFJgX8oJClKe7JuFx3jbgT07K1rwTjNsmTiEPieDefyajtXubJpm84Irm_2th6KLw2KzDHs/?imgmax=800" width="244" height="199" /></a></p> <p>Step 5.</p> <p>In Protocol and Ports Screen, select TCP in Does this rule apply to TCP or UDP and provide the Default Port Number for SSAS 2383 in Specific local Ports area in Does this rule apply to all local ports or specific local ports section and click next.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiKQtw9A3cSJVsDRewqjiWttbHEGA4gBDdqQg5senu-ioP9audVMxC4yw9gUKLqXgQGckIUq4kaPK07CKq3acu-emt3XZb_QsxoM2g0ueggjs0V3eSLMypIoSp2iaTILrhRdnW-9fdEzM/s1600-h/image%25255B14%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1YBTPRxc55ZR4SBbxe4iXpcoil47IC0-a2RH65zXHapIBnlu9SdA0VoYmTzxiSysUPCwU9oAHZOKWhYEgYgC2Z8Rajbhg2ZDAaPQU8K7shQbNzkccuWMwfVUKZX0MMJefYmHJ_jULC9A/?imgmax=800" width="244" height="198" /></a></p> <p>Step 6.</p> <p>Select “Allow the connection” Radio Button in Action Screen and click next</p> <p><a href="http://lh5.ggpht.com/-U2juzWfoQeY/UoOYb6X28_I/AAAAAAAAALI/mXx7HRK6gCs/s1600-h/image%25255B20%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-4BQ9Rlqt_p8/UoOYd_42KeI/AAAAAAAAALQ/UDx6nL54MPc/image_thumb%25255B6%25255D.png?imgmax=800" width="244" height="198" /></a></p> <p>Step 7.</p> <p>Check the required profiles where you want your rule to apply. I have selected Domain only, because i dont want anyone to access this when they are not connected to Domain. This should be per your Needs. Click Next.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVkLoLjmX2FrVh6pEfGKbe0qj0_efKIN3XxR9Ql-cUYYrlOv5YGRiUoD7nVhqlfxNQjSapY8I1PX4Nb8AJYunRR9e-oBlWNgt-LbzH9QyQ64GJTGsTzQSPMqaQnR1q0y5DaVAMVlCcd-g/s1600-h/image%25255B23%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-8o4mpGmXp10/UoOYhvyozRI/AAAAAAAAALg/GAzdAFL8v70/image_thumb%25255B7%25255D.png?imgmax=800" width="244" height="198" /></a></p> <p>Step 8.</p> <p>Provide a Meaning Full Name such as “SQL Analysis Services” or anything which you prefer and a Optional Description and click Finish.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyPphlefqA9KL4AZVZcVyy_KN7GKEPrFiAPlpqim2Thf97dCP1Oe5RErql_1x454pe8Mn2u1wJDnMzydh-mov3G59NtiIlLQlDNN18_GB8uVzo4umPtymRhV8U3_LTtMWewvbj2tA50Kk/s1600-h/image%25255B26%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5ym-rGktaAbKXrEMXHfxDGW7wRkFoTAftdl5mg9WU0NjDHDVZFw5LYVpYxJ3KtarthCnKlK_iH75yiw2XRzLk9t-zHMCOx2mvfc27XDulcuMhyyEeZt4gBG6y1ev67jX8fyC9WGXneco/?imgmax=800" width="244" height="199" /></a></p> <p>That’s it. Done. You have allowed your firewall to allow connection to SSAS Port 2383 and you can now connect to SSAS remotely.</p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:197f42f3-ad69-4efb-8aa0-472dd90681bf" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/SSAS+TCP%2fIP+Port" rel="tag">SSAS TCP/IP Port</a>,<a href="http://technorati.com/tags/TCP%2fIP+port+2382" rel="tag">TCP/IP port 2382</a>,<a href="http://technorati.com/tags/Remote+Access+to+SSAS" rel="tag">Remote Access to SSAS</a></div></blockquote> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-76539395356264707622013-11-13T20:23:00.001+05:302013-11-13T20:23:15.903+05:30SSAS Deployment Failed - Error 3 Either the '' user does not have permission to create a new object in '', or the object does not exist. 0 0<p>This is one of the Most common error a user might face during deploying a SSAS(SQL Server Analysis Services) Cube.</p> <p>“Error 3 Either the '' user does not have permission to create a new object in '', or the object does not exist. 0 0”</p> <p>This error occurs because the user who is deploying a cube is not having access to SQL Server Analysis Services(SSAS)</p> <p>You can follow the below step by step Guide to provide access to SSAS to a User.</p> <p>Step 1.</p> <p>Connect to SSAS Server using SQL Server Management Studio.</p> <p>Step 2.</p> <p>Once you are connected to SSAS Server, Right click on Server and Click on Properties as shown below.</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0V5zpt6Si3OYE2EK8O3Zg2UTbN2Xd4MW89F1XaL7tn8p4UZ-43dWvIK5ItHOHYXkW94OSiAPiFOYgwwiauNm7KJKXuLPfbAVnYTxXCMw1V7ECouUBv8XYTtyPujHJVG4rwWUU0ep9l8M/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi85tr2uC6p53PLcBv3hcp4gUw27MwoAqizk8WzJuZAliiD2U3M2c24Zuf8y5lvBTel9Df6s7XUAWTk5YMVbHTAnhOBiAw4sDSRpYlLYqlIhKCdU341_xbBlSVrzc8nMhdfv2IF9TFZD-k/?imgmax=800" width="227" height="244" /></a></p> <p>Step 3.</p> <p>On the properties window of SSAS server properties, click on security Tab and click on Add as shown below </p> <p><a href="http://lh3.ggpht.com/-_jB4CRJV3j8/UoOSRarutVI/AAAAAAAAAJQ/p1-6IL8uHp8/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9vbN7WNpPgcefsjKLqKEKL04JDmJDk9B2I9dSbUJgKSso4CRtMfP24K1NrzUgYQmxBHJEa47nrrjXMRlMfk6j3rvDM-5f6JiyVPjr0QmBn7_-bINSsZu-e6Rz4Mq4NOd-aEWad5Y7g3Y/?imgmax=800" width="244" height="220" /></a></p> <p>Step 4.</p> <p>provide the user name/group in the Enter the Object Name section and click check Name. Make sure you have selected Groups in Object Types if you want to give access to group. Select Entire Directory in Location.</p> <p><a href="http://lh3.ggpht.com/-NdTB55zaVIk/UoOSUp7ZqoI/AAAAAAAAAJg/bUUK0GGgYhQ/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-fLxTpKLxC9Q/UoOSWYDa-eI/AAAAAAAAAJo/sN3S7Hh7sZs/image_thumb%25255B2%25255D.png?imgmax=800" width="244" height="136" /></a></p> <p>Step 5.</p> <p>Once the name is resolved, click ok, click ok. Done Now your user should be able to deploy cube without any issues.</p> <p>Thank you.</p> <p> <div id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:090d7436-e4ae-4966-aacb-f490574ec6d3" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px">Technorati Tags: <a href="http://technorati.com/tags/SSAS" rel="tag">SSAS</a>,<a href="http://technorati.com/tags/Deployment" rel="tag">Deployment</a>,<a href="http://technorati.com/tags/SSAS+Error" rel="tag">SSAS Error</a>,<a href="http://technorati.com/tags/SSAS+deployment+error" rel="tag">SSAS deployment error</a>,<a href="http://technorati.com/tags/Error+3" rel="tag">Error 3</a>,<a href="http://technorati.com/tags/SSAS+Deployment+Error" rel="tag">SSAS Deployment Error</a>,<a href="http://technorati.com/tags/SSAS+Access" rel="tag">SSAS Access</a></div></p> ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-18820537142463542572013-11-06T18:59:00.001+05:302013-11-06T18:59:33.752+05:30Upgrade SQL Server 2000 Database to SQL Server 2012<div dir="ltr" style="text-align: left;" trbidi="on">
Even though we are now into SQL Server 2012, 2008R2 etc, but still many organizations have their SQL Server running on 2000 and may want to Move some databases to SQL Server 2012.<br />
<br />
This may seem a bit normal, yes but there is a little Trick.<br />
<br />
When you Try to Take a Backup of a Database which is running on SQL Server 2000 version and try to Restore on to SQL Server 2012 Version you may encounter below error.<br />
<br />
<pre class="brush:sql">
The database was backed up on a server running version 8.XX.XXXX. That version is incompatible
with this server, which is running version 11.XX.XXXX. Either restore the
database on a server that supports the backup, or use a backup that is compatible with this server.</pre>
<br />
</div>
<br />
This is not allowed as compatibility 80 is not supported on SQL Server 2012<br />
<br />
But its not the end.<br />
A quick solution to this is, <br />
<br />
1. Backup the SQL Server 2000 Database ex. DB2000<br />
2. Restore the SQL Server 2000 Database DB2000 on SQL Server 2005, 2008 or 2008R2 Server DB200X(X can be 5,8,8R2)<br />
3. Backup the Newly Restored SQL Server DB200X(X can be 5,8,8R2) database<br />
4. Now Restore the DB200X(X can be 5,8,8R2) database on SQL Server 2012.<br />
<br />
Note: Any deprecated feature existing on the database may not work.<br />
<br />
This works. But its always good to run Upgrade Advisor as this will highlight any Deprecated Features.ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-88986299443957340392013-10-15T15:01:00.000+05:302013-10-15T18:27:48.912+05:30TSQL to Get all details of who created the SQL Server Login on a SQL server Instance<div dir="ltr" style="text-align: left;" trbidi="on">
As a DBA, One of the most important task is to audit the security. <br />
<br />
Very Often, we may come across situation where logins are created on SQL server Instance but we are not aware of any details on who created it, when it was created etc.<br />
<br />
So here a small simple query to all such details.<br />
<br />
Firstly we need to get the path of default Trace which is running on SQL Server Instance,<br />
The below query will assist you in getting that path.<br />
<br />
<pre class="brush:sql">select value from ::fn_trace_getinfo(0)</pre>
<br />
<br />
Now you will get values something like below. The Path may vary how you have configured it to be and it will show you the default trace which are currently running.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMd2y1Tf0bS4ByHa62lh7koDC_2bJQ6p6xVVpduqB-kzvvwGEGiqIx-yNT2_905MeazvOzrQp1Bd5-4L6yo5euVnISMkLBDSjsE-ebZlT4SZ9FnrNKUMoDBTL1TjysRFJrzJdpiEvEURo/s1600/getLoginCreationDetails1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="85" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiMd2y1Tf0bS4ByHa62lh7koDC_2bJQ6p6xVVpduqB-kzvvwGEGiqIx-yNT2_905MeazvOzrQp1Bd5-4L6yo5euVnISMkLBDSjsE-ebZlT4SZ9FnrNKUMoDBTL1TjysRFJrzJdpiEvEURo/s320/getLoginCreationDetails1.PNG" width="320" /></a></div>
Now copy the path which you have found out in above query and replace "<span style="color: red; font-family: Consolas; font-size: x-small;">PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere" </span>with the path which you have found in the below query to get the required details.<br />
<pre class="brush:sql">SELECT ste.name
,sftgt.DatabaseName
,sftgt.NTDomainName
,sftgt.ApplicationName
,sftgt.LoginName
,sftgt.StartTime
,sftgt.TargetLoginName
,sftgt.SessionLoginName
FROM sys.fn_trace_gettable('PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere', DEFAULT) sftgt
JOIN sys.trace_events ste ON sftgt.EventClass = ste.trace_event_id
JOIN sys.trace_subclass_values stsv ON stsv.trace_event_id = ste.trace_event_id
AND stsv.subclass_value = sftgt.EventSubClass
WHERE ste.name = 'Audit Addlogin Event'
AND stsv.subclass_name = 'add'
GO</pre>
<br />
<br />
You can customize this query. <br />
Note: This feature is available from SQL Server 2005 onwards only.<br />
<br />
You get many more information from default trace. Will cover more in further posts.</div>
ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-85741551084733779182013-10-08T17:36:00.002+05:302013-10-08T17:36:35.577+05:30T-SQL to backup all databases or selected databases to specific location<div dir="ltr" style="text-align: left;" trbidi="on">
Very often we come across situation where we need to backup database in bulk. Sometime when you are migrating a server or upgrading to different version or applying patch and to be on safer side you tend to take backup of all database. imagine you have more than 100 databases on server and backing up each database would be tedious task</div>
<pre class="brush:sql">
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
--Provide the path where all the databases needs to be backed up
SET @path = 'MyBackupFilePath'
--used to suffix the current date at the end of backup filename
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
--Use this for all database except the system databases and any exclusion you can make
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
--Uncomment and use this for only specific databases.
--Those database names you can provide under IN clause
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('MyDB1','MyDB2')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH STATS = 1
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
</pre>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-90064531216274656102013-10-08T17:26:00.000+05:302013-10-08T17:26:38.690+05:30Script to change collation of multiple columns in multiple table for all tables in database<div dir="ltr" style="text-align: left;" trbidi="on">
Various time we come across situation where in lower environment the collation will be different and in production environment the collation will be different. This might hamper you work in many ways. Your development work may come to halt or slowdown making these changes.<br />
<br />
So here is a small script to change collation of various columns for all table. If you want for some columns and some table the select statement in cursor needs to tweaked.</div>
<pre class="brush:sql">
Declare
@TableName varchar(300),
@ColumnName varchar(300),
@SQLText varchar(max),
@CharacterMaxLen varchar(max),
@CollationName varchar(max),
@IsNullable varchar(max),
@DataType varchar(max)
SET @CollationName = 'RequiredCollation'
Declare MyTableCursor cursor for
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name
OPEN MyTableCursor
FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE MyColumnCursor Cursor
FOR
SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
IS_NULLABLE from information_schema.columns
WHERE table_name = @TableName AND (Data_Type LIKE '%char%'
OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
ORDER BY ordinal_position
Open MyColumnCursor
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
@DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END +
') COLLATE ' + @CollationName + ' ' +
CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
PRINT @SQLText
FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
@CharacterMaxLen, @IsNullable
END
CLOSE MyColumnCursor
DEALLOCATE MyColumnCursor
--Print @SQLText
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor
</pre>
ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-45697968799950514932013-10-04T20:14:00.000+05:302013-10-04T20:17:15.080+05:30Types of Backups and the background process of database Backup methodology.<div dir="ltr" style="text-align: left;" trbidi="on">
Types of Backups and the background process of database Backup methodology.<br />
<br />
• Full Backup: It’s just full backup of database, it backs up all data and objects that reside in the database but it will not backup objects like jobs and SQL Server logins which don’t reside in database. With the versions prior to the SQL Server 2005, if you execute a full database backup, you could not make any other backups (such as transactional log backup), until the full database backup is completed. But SQL Server 2005 allows you to do two concurrent backups at the same time. If you exceed two the third backup will fail. It’s advised that not to use more than one backup at a time as it will use high disk I/O. Full backup dose not truncate or backup transactional log. Full backup process:<br />
1. When backup command is executed, SQL server locks the database, blocking all transactions and check points is issued on the database, which writes all deity pages to disk. This ensures that all committed transactions up to the point of backup command will be part of the full backup.<br />
2. SQL Server makes a mark in the transactional log to point where the full back begins. This is important in the recovery process; SQL Server will use this mark to help validate what combination of files can or cannot be used to restore the database from the full backup, releases lock on the database.<br />
3. The data is then read out of the database files to the backup files, which is plain text file that is create on disk or tape.<br />
4. Lock the database, blocking all transactions, and issues checkpoint. <br />
5. When full backup is completed SQL Server makes another mark in the transaction log.<br />
6. Release database lock.<br />
7. Extracts all transactions between the two log marks and append to the backup, by maintaining the consistency of backup for e.g., if someone modifies the data on the pages while backup process, and if he restores the backup he might lose the modified data.<br />
• Differential database backup: A differential database backup contains only the changes made to the database since the last full backup. The main purpose of differential backup is to reduce the number of transactional log backups that needed to be restored at the time of recovery. A differential database backup is possible because SQL Server keeps track of the extents that have changed, in an internal bitmap contained within the data file. When the full backup is made bitmap is reset. One bit is used per extent, which is eight physical contiguous 8kb pages (i.e., extent is 8X8 = 64kb), so single 8kb page can map to about 4GB of data. SQL Server creates one of these mapping pages for every 8,192 extents.<br />
• Transactional log backup: it is a backup of your current transactional log; you can take transactional log back up only if you have taken full backup of the database at least once in its life time. Log backup starts at the log sequence (LSN) number at which previous log backup completed or if it is 1st log backup, it will start at which the full backup was completed. SQL Server then backs up all the subsequent transactions until the backup encounters an open transaction. Once SQL Server encounters the open transaction the log backup completes. Any LSN’s that are backed up will be removed from the transactional log, which enables system to reuse the log space. From recovery standpoint no backup is critical than transactional log backup because it allows you to recover more granular point in time. To apply a transactional log, your database must be restored WITH NORECOVERY or WITH STANDBY. WITH NORECOVERY puts your database in pure loading state where it cannot be used for client connection, but WITH STANDBY puts your database in loading state where clients can access your database in read-only mode, but when a transaction is loaded, the database needs exclusive access to load the log, so it would be unavailable for clients use. <br />
• File and File group backup: File and file group backups are based on the full and differential backups, if your database architecture uses multiple files and file groups, although you can do a standard full or differential backup, you now have the option of doing backups on files or file groups which will make recovery much easier. Although you can do file or file group backup from full or differential backup, the advantage of file or file group backup is when we are doing backup of multi terabyte databases.<br />
• Partial backup: It possible that some of the file groups in SQL Server database as read only and some of them both (read/write). In previous versions of SQL Server, a full backup captures all extents even if the file group is marker as read only, which meant that there were no changes to the data. SQL Server 2005 introduces a new parameter to the backup command to handle this situation. The READ_WRITE_FILEGROUPS clause causes the backup engine to skip any file groups that are marked as read only, saving time and space in the backup by having the backup engine gather only the set of extents that could change. E.g., BACKUP DATABASE PUBS READ_WRITE_FILEGROUPS TO DISK='C:\DEMO\BACKUP\PUBS1.BAK'<br />
• Mirror Backup: this is a new feature from SQL Server 2005 onwards, it is the ability to mirror your backup media.<br />
• Media Retention: It an instance level option where we can set how long SQL Server can retain backup set. Retaining means within SQL Server you cannot delete it (but it will not prevent doing anything physically on your media) this option is media retention and is considered an advanced configuration option within SQL Server. It is set in days, and the values can be from 0 (the default) to 365.</div>
ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-77369338080966311522013-10-04T20:11:00.000+05:302013-10-04T20:11:14.891+05:30TSQL Script to create Default Database EmailRecently we did a installation of some bulk SQL Server for various purpose. Now we needed database Email to be configured on each server and there had to be some standard.
I worked out this my Making a generic script which can be used and run on any machine and which maintain symmetry
<pre class="brush:sql">
USE master
Go
sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
USE msdb
GO
DECLARE
@profile VARCHAR(100),
@display VARCHAR(100),
@emailaddr VARCHAR(100),
@accountname Varchar(100)
SELECT
@profile = 'MyProfileName_DB_Mail_Profile_' + @@ServerName,
@display = 'MyProfileName_DB_Mail_Account_' + @@ServerName,
@emailaddr = 'MyRequiredEmailAddress' + @@servername + '@myDomain.com',
@accountname = 'MyAlertName_Alert_Account';
-- Create Database Mail account
EXECUTE dbo.sysmail_add_account_sp
@account_name = @accountname,
@description = 'MyDatabaseAccountName Alert Database Account',
@email_address = @emailaddr,
@display_name = @display,
@mailserver_name = 'MyMailserver.com';
-- Create a Database Mail profile
EXECUTE dbo.sysmail_add_profile_sp
@profile_name = @profile,
@description = 'MyDatabaseEmailProfileName Alert Database Profile';
-- Add multiple Database Mail accounts to Mail profile
EXECUTE dbo.sysmail_add_profileaccount_sp
@profile_name = @profile,
@account_name = @accountname,
@sequence_number = 1;
-- Grant the mail profile access to msdb public role & make it a default
EXECUTE dbo.sysmail_add_principalprofile_sp
@profile_name = @profile,
@principal_name = 'public',
@is_default = 1;
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
REG_SZ,
@profile;
GO
</pre>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-35873836936993401692012-01-27T17:15:00.000+05:302013-10-08T17:18:48.984+05:30Stored Procedure to Get Expensive Queries<div dir="ltr" style="text-align: left;" trbidi="on">
As a DBA we often need to get information on various Queries, One of them is how expensive it is. A query can be judged as expensive on various criteria’s like Long Running, Memory Utilization, CPU utilization etc.<br />
I came up with this Stored Procedure which gives you Expensive Queries based on criteria’s like Duration, Memory, CPU, Read etc.<br />
<br />
<pre class="brush:sql">
<br />
USE [master] <br />GO <br />IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[usp_GetExpensiveQueries]‘) AND type in (N’P', N’PC’)) <br />DROP PROCEDURE [dbo].[usp_GetExpensiveQueries] <br />GO <br />USE [master] <br />GO <br />SET ANSI_NULLS ON <br />GO <br />SET QUOTED_IDENTIFIER ON <br />GO <br />CREATE PROCEDURE [dbo].[usp_GetExpensiveQueries] <br />@Limit AS INT, <br />@Database_Name AS VARCHAR(255), <br />@Expense_Counter INT <br />AS <br />/*Variables Used <br />1. @Limit — No of Recoreds to be Retrieved in Int <br />2. @Database_Name — Database Name for which the Expense queries needs to be Retrieved in Varchar <br />3. @Expense_Counter — Criteria on Which the Query Expense needs to judged <br />Refer Below for Expense Counter <br />End Variables Used*/ <br />/*Expense Counter <br />DurTimeAvgMin 0 <br />CPUTimeAvgMin 1 <br />TotalCPUTime 2 <br />TotalDurTime 3 <br />NoPhysicalReads 4 <br />AvgNoPhyscialReads 5 <br />NoLogicalReads 6 <br />AvgNoLogicalReads 7 <br />*/ <br />SELECT Database_name,QueryText,ProcBatTest,PlanGenerationNumber,ExecutionCount,DurTimeAvgMin, <br />CPUTimeAvgMin,TotalCPUTime,TotalDurTime,NoPhysicalReads,AvgNoPhyscialReads,NoLogicalReads,AvgNoLogicalReads <br />FROM <br />( <br />SELECT TOP (@Limit) DB_NAME(CONVERT (INT, epa.value)) AS [Database_Name], <br />SUBSTRING(est.text, (eqs.statement_start_offset/2)+1, <br /> ((CASE eqs.statement_end_offset <br /> WHEN -1 THEN DATALENGTH(est.text) <br /> ELSE eqs.statement_end_offset <br /> END – eqs.statement_start_offset)/2) + 1) AS QueryText, <br />est.text AS ProcBatTest, <br />eqs.plan_generation_num AS PlanGenerationNumber, <br />eqs.execution_count AS ExecutionCount, <br />(eqs.total_worker_time/1000) AS TotalCPUTime, <br />(((eqs.total_worker_time/1000)/eqs.execution_count)/3600) AS CPUTimeAvgMin, <br />(eqs.total_elapsed_time/1000) AS TotalDurTime, <br />(((eqs.total_elapsed_time/1000)/eqs.execution_count)/3600) AS DurTimeAvgMin, <br />eqs.total_physical_reads AS NoPhysicalReads, <br />(eqs.total_physical_reads/eqs.execution_count) AS AvgNoPhyscialReads, <br />eqs.total_logical_reads AS NoLogicalReads, <br />(eqs.total_logical_reads/eqs.execution_count) AS AvgNoLogicalReads, <br />eqs.last_execution_time AS LastExecutionTime <br />FROM SYS.DM_EXEC_QUERY_STATS eqs <br />CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sql_handle) est <br />CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(plan_handle) eqp <br />CROSS APPLY SYS.DM_EXEC_PLAN_ATTRIBUTES(eqs.plan_handle) epa <br />WHERE attribute = ‘dbid’ <br />AND DB_NAME(CONVERT (INT, epa.value)) = @Database_Name) x <br />–and qs.last_execution_time > ’2011-08-09 17:29:33.750′ <br />–If we want to get queries executed greater than some time <br />–and (((qs.total_elapsed_time/1000)/qs.execution_count)/3600) >= 2 <br />ORDER BY <br />–Seems to be Problem with Order By working on the same <br />–Order By Fixed <br />CASE <br />WHEN @Expense_Counter = 0 THEN DurTimeAvgMin <br />WHEN @Expense_Counter = 1 THEN CPUTimeAvgMin <br />WHEN @Expense_Counter = 2 THEN TotalCPUTime <br />WHEN @Expense_Counter = 3 THEN TotalDurTime <br />WHEN @Expense_Counter = 4 THEN NoPhysicalReads <br />WHEN @Expense_Counter = 5 THEN AvgNoPhyscialReads <br />WHEN @Expense_Counter = 6 THEN NoLogicalReads <br />WHEN @Expense_Counter = 7 THEN AvgNoLogicalReads <br />END DESC <br />GO<br />
</div>
<br />
</pre>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-34117767019707894582011-11-17T06:46:00.000+05:302012-05-08T12:46:47.467+05:30T-SQL to get the SQL Server Service Start time and Up time of SQL Server service<div dir="ltr" style="text-align: left;" trbidi="on">
There are number of occasions where we need to get to know the SQL Server
service start date time and also from what time the SQL Services are up and
running generally termed as Uptime. There are many ways to do it like check for
temp DB Creation time, using DMF sys.dm_io_virtual_file_stats etc. Below written
is small T-SQL script but very useful which provides you this information. Hope
this is useful. Comments and suggestions are always welcome. .<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">USE</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">MASTER</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">GO<br />
<br />
SET</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">NOCOUNT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">GO<br />
<br />
SELECT</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'SQL server started at '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;"></span></span><br />
<span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CONVERT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DATETIME</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> sqlserver_start_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> 126</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">20</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' and is up and running from '</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;"></span></span><br />
<span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DATEDIFF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">MINUTE</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">sqlserver_start_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">GETDATE</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">()))/</span></span><span style="font-size: x-small;">60 </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">5</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' hours and '</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">RIGHT(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'0'</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(((</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DATEDIFF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">MINUTE</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">sqlserver_start_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">GETDATE</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">()))%</span></span><span style="font-size: x-small;">60</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">2</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)),</span></span><span style="font-size: x-small;">2</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' minutes'</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> [Start_Time_Up_Time] </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">dm_os_sys_info</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">GO<br />
<br />
SET</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">NOCOUNT</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">OFF</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">GO</span></span></div>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-82444420532862934602011-11-10T22:23:00.000+05:302012-05-08T12:44:08.384+05:30T-SQL to get various details on currently running Active processes on SQL Server Instance<div dir="ltr" style="text-align: left;" trbidi="on">
Couple of day back i was working on something very important which will run
some stored procedures to accomplish the task. Now there can be many
transactions or different piece of codes in a Stored Procedure. Now just think
that you come across a weird situation where you are not getting your results on
time or may be its taking more time than expected. Now you can determine which
is session and spid your procedure is running, but what if you want to know
which piece of code is running which might me causing the issue, since how long
it is running etc these kind of various questions comes to mind and we are
stuck. Now thanks to DMV's and DMF's which makes our task easier by giving
various piece of information which will be very useful for us. So i came with a
small piece of TSQL which will answer these kind of questions and gives us the
vital information.<br />
<br />
This also provides information on estimated completion time, completed
percentage for various operations like Backup,Database Integrity Check, Index
Re-Organise etc.<br />
<br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">USE</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">MASTER</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">
</span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">GO<br />
<br />
SELECT</span></span><span style="font-size: x-small;"> SPID</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">ER</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">percent_complete</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;"></span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* This piece of code has been taken from article. Nice code to get time criteria's<br />
<br />
http://beyondrelational.com/blogs/geniiius/archive/2011/11/01/backup-restore-checkdb-shrinkfile-progress.aspx<br />
<br />
*/</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(((</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DATEDIFF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">s</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">start_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">GetDate</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">()))/</span></span><span style="font-size: x-small;">3600</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">varchar</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' hour(s), '</span></span><br />
<span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DATEDIFF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">s</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">start_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">GetDate</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">())%</span></span><span style="font-size: x-small;">3600</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)/</span></span><span style="font-size: x-small;">60 </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">varchar</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'min, '</span></span><br />
<span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DATEDIFF</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">s</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">start_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">GetDate</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">())%</span></span><span style="font-size: x-small;">60</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">varchar</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' sec'</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> running_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="font-size: x-small;">estimated_completion_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">/</span></span><span style="font-size: x-small;">3600000</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">varchar</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' hour(s), '</span></span><br />
<span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="font-size: x-small;">estimated_completion_time </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">%</span></span><span style="font-size: x-small;">3600000</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)/</span></span><span style="font-size: x-small;">60000 </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">varchar</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'min, '</span></span><br />
<span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="font-size: x-small;">estimated_completion_time </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">%</span></span><span style="font-size: x-small;">60000</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)/</span></span><span style="font-size: x-small;">1000 </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">varchar</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">' sec'</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> est_time_to_go</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DATEADD</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">second</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">estimated_completion_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">/</span></span><span style="font-size: x-small;">1000</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">getdate</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">())</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">as</span></span><span style="font-size: x-small;"> est_completion_time</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">/* End of Article Code */</span><br />
</span><span style="font-size: x-small;">ER</span><br />
<span style="font-size: x-small;"></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">command</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">ER</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">blocking_session_id</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> SP</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DBID</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">LASTWAITTYPE</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;"></span></span><br />
<span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DB_NAME</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">SP</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DBID</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> DBNAME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;"></span></span><br />
<span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SUBSTRING</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">est</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">text</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">ER</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">statement_start_offset</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">/</span></span><span style="font-size: x-small;">2</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)+</span></span><span style="font-size: x-small;">1</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">((</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CASE</span></span><span style="font-size: x-small;"> ER</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">statement_end_offset</span><br />
<span style="font-size: x-small;">
</span><br />
<span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHEN</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">-</span></span><span style="font-size: x-small;">1 </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">THEN</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">DATALENGTH</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">est</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">text</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ELSE</span></span><span style="font-size: x-small;"> ER</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">statement_end_offset</span><br />
<span style="font-size: x-small;">
</span><br />
<span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">END</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">-</span></span><span style="font-size: x-small;"> ER</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">statement_start_offset</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)/</span></span><span style="font-size: x-small;">2</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="font-size: x-small;"> 1</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> QueryText</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">TEXT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">CPU</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">HOSTNAME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">LOGIN_TIME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">LOGINAME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;">SP</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">status</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">PROGRAM_NAME</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;">NT_DOMAIN</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> NT_USERNAME</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYSPROCESSES</span></span><span style="font-size: x-small;"> SP</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">sys</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">dm_exec_requests</span></span><span style="font-size: x-small;"> ER</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> sp</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">spid </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> ER</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">session_id</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">CROSS</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">APPLY</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">DM_EXEC_SQL_TEXT</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">er</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">sql_handle</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><span style="font-size: x-small;"> EST</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ORDER</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">BY</span></span><span style="font-size: x-small;"> CPU </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">DESC</span></span><br />
</div>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-42859503311900993592011-10-04T04:09:00.000+05:302012-05-08T12:32:28.924+05:30TSQL to get All Primary Key Columns and its relevant details<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"><span style="color: black; font-family: Arial, Helvetica, sans-serif;">I was working on database design stuff and came across a situation where in i
had to get all the primary keys existing in database for all tables and the
primary key columns and column details. It would be very had and insane to go
into each table and search for PK and its data. So i came up with this small
script which gives you all information on Primary Keys. This can be modified as
per your needs to get more information or for foreign keys ets. Hope this is
useful for some guys looking for it. It was very helpful for me though :)</span><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">SELECT</span></span></span><span style="font-size: x-small;"> SS</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">NAME </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> [TABLE_SCHEMA]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> ST</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">NAME </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> [TABLE_NAME]</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> SKC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">NAME </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> [CONSTRAINT_NAME]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> SC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">NAME </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> [CONSTRAINT_COLUMN_NAME]</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">STY</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">NAME </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">20</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'('</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">CAST</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">CASE</span></span><span style="font-size: x-small;"> ST</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">NAME</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">WHEN</span></span><span style="font-size: x-small;"> </span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'NVARCHAR'</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">THEN </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> SC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">MAX_LENGTH</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">/</span></span><span style="font-size: x-small;">2</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ELSE </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">SELECT</span></span><span style="font-size: x-small;"> SC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">MAX_LENGTH</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">END</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">VARCHAR</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="font-size: x-small;">20</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">))</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">+</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">')'</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> [DATA_TYPE]</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">KEY_CONSTRAINTS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> SKC</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">TABLES</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> ST</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> ST</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> SKC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">PARENT_OBJECT_ID</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SCHEMAS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> SS</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> SS</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SCHEMA_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> ST</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">SCHEMA_ID</span></span><br />
<span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">INDEX_COLUMNS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> SIC</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> SIC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> ST</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><br />
<span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> SIC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">INDEX_ID </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> SKC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">UNIQUE_INDEX_ID</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">COLUMNS</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> SC</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> SC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> ST</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">OBJECT_ID</span></span><br />
<span style="color: magenta; font-size: x-small;"><span style="color: magenta; font-size: x-small;">
</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">AND</span></span><span style="font-size: x-small;"> SC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">COLUMN_ID </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> SIC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">COLUMN_ID</span><br />
<span style="font-size: x-small;">
</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">INNER</span></span><span style="font-size: x-small;"> </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">JOIN</span></span><span style="font-size: x-small;"> </span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">SYS</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="color: green; font-size: x-small;"><span style="color: green; font-size: x-small;">TYPES</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">AS</span></span><span style="font-size: x-small;"> STY</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ON</span></span><span style="font-size: x-small;"> SC</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">USER_TYPE_ID </span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">=</span></span><span style="font-size: x-small;"> STY</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">USER_TYPE_ID</span><br />
<span style="font-size: x-small;">
</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">ORDER</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">BY</span></span><span style="font-size: x-small;"> TABLE_SCHEMA</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> TABLE_NAME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="font-size: x-small;"> CONSTRAINT_NAME</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">;</span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span></div>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-50144593096164672602011-08-10T03:53:00.000+05:302011-08-10T03:53:32.566+05:30SQL Script to Create Roles,Schemas, Add users and Associate Schema to Role<div dir="ltr" style="text-align: left;" trbidi="on"><div class="WordSection1"><div class="MsoNormal"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">/* SQL Script to Create Roles, Schemas and users to the same */</span></div><div class="MsoNormal"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">--Creation of Database Role and Adding User's begin</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">USE</span><span style="font-family: "Courier New"; font-size: 10pt;"> [YOURDATABASENAME]</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">CREATE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">ROLE</span> [YOURROLENAME]</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: green;">--Adding Users Begin</span></span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">EXEC</span> <span style="color: maroon;">sp_addrolemember</span><span style="color: blue;"> </span><span style="color: red;">N'YOURROLENAME'</span><span style="color: grey;">,</span> <span style="color: red;">N'USERNAME1'</span></span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">GO</span></span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">EXEC</span> <span style="color: maroon;">sp_addrolemember</span><span style="color: blue;"> </span><span style="color: red;">N'YOURROLENAME'</span><span style="color: grey;">,</span> <span style="color: red;">N'USERNAME2'</span></span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">GO</span></span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: green;">--Adding Users End</span></span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: green;">--Similarly Multiple users can be added using EXEC sp_addrolemember</span></span></div><div class="MsoNormal"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">--Creation of Database Role and Adding User's End</span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">--Creation of Schema Starts</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">USE</span><span style="font-family: "Courier New"; font-size: 10pt;"> [YOURDATABASENAME]</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">CREATE</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">SCHEMA</span> [YOURSCHEMANAME] <span style="color: blue;"> AUTHORIZATION</span> [YOURSCHEMANAME]</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">--Creation of Schema End</span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">--Association of Created Schema to Role Starts</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">USE</span><span style="font-family: "Courier New"; font-size: 10pt;"> [YOURDATABASENAME]</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal"><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: blue;">ALTER</span> <span style="color: blue;">AUTHORIZATION</span> <span style="color: blue;">ON</span> <span style="color: blue;"> SCHEMA</span><span style="color: grey;">::</span>[YOURSCHEMANAME] <span style="color: blue;"> TO</span> [YOURROLENAME]</span></div><div class="MsoNormal"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">GO</span></div><div class="MsoNormal"><span style="color: green; font-family: "Courier New"; font-size: 10pt;">--Association of Created Schema to Role End</span> </div></div></div>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0tag:blogger.com,1999:blog-7635781011424397642.post-71552700492724924002011-08-08T06:44:00.002+05:302011-08-08T06:44:28.498+05:30Grouping data from multiple rows into single row<p>Often we come across situation where we need to group data from multiple rows into single row. E.g.: An Employee works for multiple projects, so in this case if columns are EMPLOYEE_NAME, PROJECT_DURATION, PROJECTS_WORKED and think that employee works for 10 projects. Then you would have 10 rows containing EMPLOYEE_NAME as same and then 10 different projects. If you now want to have a single row with Employee name and then 10 subsequent name delimited in single column, unfortunately we don’t have any function like GROUP_CONCAT as in MySQL for SQL server. But not to worry this can be achieved by using XML PATH. Below is the example .</p><p>Insert into EMPLOYEE_PROJECT(EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME)<br /><br />
SELECT EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME=<br /><br />
REPLACE(<br /><br />
REPLACE(<br /><br />
(SELECT REPLACE(PROJECT_NAME, ' ', '')<br /><br />
AS [data()] FROM EMPLOYEE_PROJECT_DETAILS<br /><br />
WHERE EMPLOYEE_NAME=a.EMPLOYEE_NAME<br /><br />
FOR XML PATH('')),' ',' ; '),'',' ')<br /><br />
FROM EMPLOYEE_PROJECT_DETAILS a WHERE EMPLOYEE_NAME IS NOT NULL GROUP BY EMPLOYEE_NAME,PROJECT_DURATION</p><p>Small query but very useful.</p><p>Happy Coding </p><p>Technorati Tags: Group,Grouping,XML PATH</p>ManjunathCBhathttp://www.blogger.com/profile/07145609253192986891noreply@blogger.com0