SQL Linked servers

Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server.

Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:

• The ability to access data from outside of SQL Server.
  
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  
• The ability to address diverse data sources similarly.
  The following illustration shows the basics of a linked server configuration.
  
  
  
  Typically, linked servers are used to handle distributed queries. When a client application executes a distributed query through a linked server, SQL Server parses the command and sends requests to OLE DB.
  
  The rowset request may be in the form of executing a query against the provider or opening a base table from the provider.
  
  
  For a data source to return data through a linked server, the OLE DB provider (DLL) for that data source must be present on the same server as the instance of SQL Server.
  When a third-party OLE DB provider is used, the account under which the SQL Server service runs must have read and execute permissions for the directory, and all subdirectories, in which the provider is installed.
  
  

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "XXXX" was unable to begin a distributed transaction.

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "XXXX" was unable to begin a distributed transaction.

I have been able to resolve the issue after executing the following command on server A:

EXEC sp_serveroption @server = 'ServerB',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

=====================
DECLARE @ServerName SYSNAME
, @Message nvarchar(1000)
, @CMD1 nvarchar(max)
--
DECLARE @Server_List Table
( SrvID SMALLINT
, SrvName SYSNAME )
--
Set NoCount ON
--
-- Load up linked server list
--
BEGIN
INSERT INTO @Server_List (SrvID, SrvName)
SELECT SrvID
, SrvName
FROM [master].[SYS].sysservers
ORDER BY SrvID ASC
END
--
SELECT TOP 1 @ServerName = SrvName
FROM @Server_List
ORDER BY SrvID ASC
--
-- Loop through the Linked Server List
--
WHILE EXISTS ( SELECT * FROM @Server_List )
BEGIN
SELECT @Message = 'Server Name is '+ @ServerName
--
RAISERROR (@Message, 10,1) WITH NOWAIT
--
SET @CMD1 = 'EXEC master.dbo.sp_serveroption @server=N'''
+ @ServerName
+ ''', @optname=N''rpc'', @optvalue=N''true'''
Exec sp_executesql @cmd1
--
SET @CMD1 = 'EXEC master.dbo.sp_serveroption @server=N'''
+ @ServerName
+ ''', @optname=N''rpc out'', @optvalue=N''true'''
Exec sp_executesql @cmd1
--
set @cmd1 = 'EXEC master.dbo.sp_serveroption @server = '''
+ @ServerName
+ ''', @optname=N''remote proc transaction promotion'', @optvalue=N''false'''
Exec sp_executesql @stmt=@cmd1,@params=N''
--
DELETE FROM @Server_List WHERE SrvName = @ServerName
--
SELECT TOP 1 @ServerName = SrvName
FROM @Server_List
ORDER BY SrvID ASC
--
END
===============================

SSRS

What is SSRS

SQL Server 2005 Reporting Services is a server-based reporting platform that you can use to create and manage tabular, matrix, graphical, and free-form reports that contain data from relational and multidimensional data sources. The reports that you create can be viewed and managed over a World Wide Web-based connection. Reporting Services includes the following core components:

• A complete set of tools that you can use to create, manage, and view reports.

• A Report Server component that hosts and processes reports in a variety of formats. Output formats include HTML, PDF, TIFF, Excel, CSV, and more.

• An API that allows developers to integrate or extend data and report processing in custom applications, or create custom tools to build and manage reports.

The reports that you build can be based on relational or multidimensional data from SQL Server, Analysis Services, Oracle, or any Microsoft .NET data provider such as ODBC or OLE DB. You can create tabular, matrix, and free-form reports. You can also create ad hoc reports that use predefined models and data sources.

Visually and functionally, the reports that you build in Reporting Services surpass traditional reporting by including interactive and Web-based features. Some examples of these features include drill-down reports that enable navigation through layers of data, parameterized reports that support content filtering at run time, free-form reports that support content in vertical, nested, and side-by-side layouts, links to Web-based content or resources, and secure, centralized access to reports over remote or local Web connections.

Although Reporting Services integrates with other Microsoft technologies out-of-the-box, developers and third-party vendors can build components to support additional report output formats, delivery formats, authentication models, and data source types. The development and run-time architecture was purposely created in a modular design to support third-party extension and integration opportunities.

SSRS competes with Crystal Reports and other business intelligence tools, and is included in Express, Workgroup, Standard, and Enterprise editions of Microsoft SQL Server as an install option. Reporting Services was first released in 2004 as an add-on to SQL Server 2000. The second version was released as a part of SQL Server 2005 in November 2005. The latest version was released as part of SQL Server 2008 in August 2008.

Users can interact with the Report Server web service directly, or instead use Report Manager, a web-based application that interfaces with the Report Server web service. With Report Manager, users can view, subscribe to, and manage reports as well as manage and maintain data sources and security settings. Reports can be delivered via e-mail or placed on a file system. Security is role-based and can be assigned on an individual item, such as a report or data source, a folder of items, or site wide. Security roles and rights are inherited and can be overloaded.

In addition to using the standalone Report Server that comes with SQL Server, RDL reports can also be viewed using the ASP.NET ReportViewer web control or the ReportViewer Windows Forms control. This allows reports to be embedded directly into web pages or .NET Windows applications. The ReportViewer control processes reports in one of two ways: (a) server processing, where the report is rendered by and obtained from the Report Server; and (b) local processing, where the control renders the RDL file itself.

SQL Server Reporting Services also support ad hoc reports: the designer develops a report schema and deploys it on the reporting server, where the user can choose relevant fields/data and generate reports. Users can then download the reports locally.

Advantages Of Reporting Services (SSRS)

• 'Direct' and efficient reporting access to information residing in both Oracle and MS SQL Server databases.
• Faster (and therefore cheaper) production of reports on both relational and cube data.
• An easy to deploy centralised reporting infrastructure based on Microsoft Reporting Services.
• Faster delivery of information to the business, providing better decision support.
• Ability for the business to self-serve, edit and interact with information without having to rely on IT or IS resources.
• Simple pricing model tailored for both entry and enterprise level installations, allowing for inexpensive provision of Business Intelligence for the Masses and democratisation of information.
• No need for expensive specialist skills.
• The beauty is that the entire report and data source definition is stored as a simple XML file. This is the file the reporting engine uses to render reports. The elements and attributes required for defining a report format are fully documented. Further, you can even add your custom elements if you want to enrich available functionality. Most report writers available today never provided this functionality.
• XML based report definition allows you to directly design reports programmatically and render them. This was very difficult to achieve in currently available report writers.
• The default report designer is integrated with Visual Studio .NET so that you can create application and its reports in the same environment.
• The report designer eliminates the traditional bands very effectively. It provides three types of elements—Table, Matrix and List. Table is equivalent to the traditional report with header, footer, detail and groups. You can have multiple tables rendering different data arranged side by side!
• For each type of reporting element, you have to attach a dataset to it. Dataset is based upon data source.
• The matrix is like a pivot table. It has rows, columns and cells containing computed data. Very useful and very easy. I am sure all of you remember how much we have to struggle today to create a simple cross-tab report. Write complex queries, struggle with table formatting and so on. With this new tool, just drag the matrix on the report, specify row, column and data fields and that’s it.
• The list is free form data. It can be descriptive and cannot be represented as a structured table, somewhat like a data repeater. Whatever data elements you put in the list are repeated for each row in the base dataset. This allows you to create free form layout reports that still repeat for each data item.
• The report items can be recursive. For example, one list can contain another list. What’s more one report can be defined as a sub-report of the base report. This provides more than just drill down. The subreport also need not be hard coded. Parameters can be passed online to it based upon the area of base report clicked.
• Now, about rendering. This is the most sophisticated part. By default rendering is in HTML. But while you are viewing the report, you can simply click on the toolbar and render it in many different ways.
• The most important part is that all the reports are stored on the central SQL Server database. Usually, we have reports for each application stored and managed separately. This leads to a lot of confusion and administrative headaches.
• The reports are viewed and administered by using a Web-based implementation of the entire reporting engine. The default website provides a base structure which is folder based. Typically you will have folders created for each application or user functionality.
• How do you access reports usually? By instancing the report writer runtime. Here you don’t have to do that. Because all reports are accessible in one of the two ways:
  - By specifying the URL identifying the report on the reportserver or
  - By calling the Web service.
  
• The best part of the reporting server is that the entire functionality is exposed as a single Web service! You can create, manage, and view reports using various methods provided by the web service.
• The security is managed in a role-based manner and can be applied to folders as well as reports.
• User can manage their own reporting needs by accessing reports ad-hoc or by subscribing to the reports. Subscription based reports are automatically sent by mail to the users.
• All reports require parameters to be accepted from users. Here once parameters are defined, the UI for these parameters is automatically generated.

Export Options - SSRS Reports
Finally, you have many ways of rendering the reports:

• HTML (MHTML)
• Excel
• Acrobat
• Tiff (image)
• XML
• CSV

SQL Query to find CPU Utilization History for last 30 min

CPU Utilization History for last 30 min

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
ORDER BY record_id DESC;

How to Check Failed Jobs in SQL

Using Below SQL query to get failed job history in sql server


SELECT  'Job: ' + Job.[name] as failedjobs, Hst.[sql_message_id], Hst.[message] , Hst.[run_date], Hst.[run_time],'Hist', hst.*
FROM [msdb].dbo.sysjobhistory Hst 
INNER JOIN [msdb].dbo.sysjobs Job ON Hst.[job_id] = Job.[job_id]
where hst.run_status = '0'   -- 0 = FAILED
and  convert(varchar(8), GETDATE(),112) = Hst.[run_date]
ORDER BY Job.[name],Hst.[run_date] DESC, Hst.[run_time] DESC

How To Check SQL Recent Backups of all databases

The below sql query to get the backup details of all databases on SQL Instance.

use msdb
go

SET NOCOUNT ON
--select 'SERVER NAME : ' + @@servername
select  + @@servername as 'Server Name', SUBSTRING(s.name,1,40) AS 'Database Name',
CAST(b.backup_start_date AS char(25)) AS 'Last Backup Date',
CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())
THEN 'Backup Completed '
WHEN b.backup_start_date > DATEADD(dd,-7,getdate())
THEN 'Not taken '
ELSE 'Not taken '
END
AS 'Status',
substring(m.physical_device_name ,1,100) AS 'Backup File Name'

from master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date = (SELECT MAX(backup_start_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D' ) -- full database backups only, not log backups
left outer join msdb..backupmediafamily m
on m.media_set_id=b.media_set_id
and m.physical_device_name=(select max(physical_device_name) from msdb..backupmediafamily
where media_set_id=b.media_set_id)
WHERE s.name <> 'tempdb'
ORDER BY s.name

Top 10 SQL queries

1) The following sql query will show the HostName , SQL Instance Name , sql edition ,SQL procutLevel, Standlone or Clustered type and sql version info

SELECT

SERVERPROPERTY('MachineName') as HostName,

SERVERPROPERTY('InstanceName') as InstanceName,

SERVERPROPERTY('Edition') as Edition,

SERVERPROPERTY('ProductLevel') as ProductLevel

Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else

'STANDALONE' end as ServerType,

@@VERSION as SQLVersionDetails

2  The below SQL  query will show all of the information related to Server level configuration and sql performance related information .

SELECT * from sys.configurations order by NAME

3  Below sql query will show the info of sql security .

SELECT sl.name, sl.denylogin, sl.isntname,sl.isntgroup, sl.isntuser
  FROM master.dbo.syslogins sl
WHERE sl.sysadmin = 1 OR sl.securityadmin = 1

4  Below SQL query will show the infomation of database name , compatibility level ,database recovery model and database state , is it online or offline .

SELECT name,compatibility_level,recovery_model_desc,state_desc FROM sys.databases

5. This sql query will provides the logical name and the physical location of the data/log files of all the databases available in the current SQL Server instance

SELECT db_name(database_id) as DatabaseName,name,type_desc,physical_name FROM sys.master_files

6) The database may contain filegroups other than the primary file group. The following sql query  gets executed in each database on the server and displays the file groups related results

EXEC master.dbo.sp_MSforeachdb @command1 = 'USE [?] SELECT * FROM sys.filegroups'

7) The following SQL query lists all of the databases in the server and the last day the backup happened. This will help the database administrators to check the backup jobs and also to make sure backups are happening for all the databases

SELECT db.name, case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100), MAX(b.backup_finish_date)) end AS last_backup_finish_date FROM sys.databases db LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D' WHERE db.database_id NOT IN (2) GROUP BY db.name ORDER BY 2 DESC

8)The below sql query gets all the information related to the current backup location from the msdb database.

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily

9) The following sql query will list all of the trace flags that are enabled gloabally on the server.

DBCC TRACESTATUS(-1);

DBCC TRACESTATUS();

10) SQL Server database administrators used sp_who and sp_who2 to check the current users, process and session information. These statements also provided information related to cpu, memory and blocking information related to the sessions

sp_who

Sp_who2

11)  Using below query to find out the all databases file location on sql instance.


SELECT DB_NAME([dbid])AS [Database Name], fileid, [filename]
FROM sys.sysaltfiles
WHERE [dbid] > 0 AND [dbid] <> 32767
OR [dbid] = 2;