MSBI Tutor

Step 1: Open report server web URL like : http//:localhost/reports Step 2: Open data sources folder Step 3: Click on (…) and  select manage Step 4: Go to credentials session à select using the following credentials option à provide proper user name and password  à click on apply

Procedure 1: 1.Start Management Studio and connect to Report Server Instance. 2.Right click on the ReportServer Instance and Select Properties 3.Click Advanced 4.In EnableRemoteErrors, select True. 5.Click OK. Procedure 2: 1. Create a text file and copy the following script into the file. Public Sub Main()    Dim P As New [Property]()    P.Name = "EnableRemoteErrors"    P.Value = True    Dim Properties(0) As [Property]    Properties(0) = P    Try      rs.SetSystemProperties(Properties)      Console.WriteLine("Remote errors enabled.")    Catch SE As SoapException      Console.WriteLine(SE.Detail.OuterXml)    End Try  End Sub 2. Save the file as EnableRemoteErrors.rss. 3. Click Start, point to Run, type cmd, and click OK to open a command prompt window. 4. Navigate to the directory that contains the .rss file you just created. 5. Type the following command line, replacing servername with the actual name of your server: rs -i EnableRemoteErrors.rss -s h

TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore of database 'DataBase_Name' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Procedure 1: 1. Right click Databases 2. Restore Database... 3. [General] Source from Device, select the bak file 4. [General] Change the Destination database name to a new name 5. [Files] Change mdf and ldf Restore As paths to a different, new path 6. [Options] (unnecessary on the other computer) Check Overwrite the existing database (WITH_REPLACE) 7. [Options] (unnecessary on the other computer)

USE [msdb] ; GO SELECT DISTINCT        j . name 'job_name' ,        count ( h . run_date ) 'failed_runs_last_7' --h.run_duration FROM        sysjobs j             JOIN sysjobhistory h on j . job_id = h . job_id WHERE        j . enabled = '1'        and h . run_status = '0'        and h . step_id = '0'        and h . run_date >= CONVERT ( VARCHAR ( 8 ), GETDATE (), 112 ) - 7 -- past 7 days GROUP BY        j . name ORDER BY        j . name

STORED PROCEDURES Ownership a)      The developer writing the Stored Procedure must provide his / her Full Name, Create Date, and brief Description of the Stored Procedure. This commented information should be the first part of the Stored Procedure. -- Author          : Author Name           -- Create date     : Date       -- Description     : Brief Description       SET Options b)      The Following options should be set in Each Stored Procedure, set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON   Naming Conventions a)      Stored Procedure name should start with ‘usp’. The next alphabet after ‘usp’ should be CAPITAL and each recognizable word in the name of Stored Procedure should start with CAPITAL letter. For example ‘uspInsertProcedure’   b)      INPUT parameters of Stored Procedure should start with ‘@p’ and end with ‘_IN’. For Example, @pInputVariable_IN.   The next alphabet after ‘@p’ should be

--Top 10 total CPU consuming queries SELECT TOP 10        QT . TEXT AS STATEMENT_TEXT ,        QP . QUERY_PLAN ,        QS . TOTAL_WORKER_TIME AS CPU_TIME FROM SYS . DM_EXEC_QUERY_STATS QS CROSS APPLY SYS . DM_EXEC_SQL_TEXT ( QS . SQL_HANDLE ) AS QT CROSS APPLY SYS . DM_EXEC_QUERY_PLAN ( QS . PLAN_HANDLE ) AS QP ORDER BY TOTAL_WORKER_TIME DESC --Top 10 average CPU consuming queries SELECT TOP 10        TOTAL_WORKER_TIME ,        EXECUTION_COUNT ,        TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] , QT . TEXT AS QUERYTEXT FROM SYS . DM_EXEC_QUERY_STATS QS CROSS APPLY SYS . DM_EXEC_SQL_TEXT ( QS . PLAN_HANDLE ) AS QT ORDER BY QS . TOTAL_WORKER_TIME DESC ; --Top 10 I/O intensive queries SELECT TOP 10        TOTAL_LOGICAL_READS ,        TOTAL_LOGICAL_WRITES ,        EXECUTION_COUNT ,        TOTAL_LOGICAL_READS + TOTAL_LOGICAL_WRITES AS [IO_TOTAL] ,        QT . TEXT AS