The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.

You might get the following error when trying to connect to an newly installed SQL Server 2014 instance.
The error says "The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement."

This is because the server on which SQL server is installed, is denying the client connections to SQL Server.
To fix this error, allow connections to the SQL Server Port in the firewall.

Import data from SharePoint Online

It's all cloud could and cloud now-a-days. Everyone are moving towards the cloud for making life easier.
In this post I will be explaining on how to extract/Import data from SharePoint Lists which is hosted on SharePoint Online of Office 365 using SSIS.
There is no direct way to connect to the SharePoint Online using SSIS.
For connecting to SharePoint Online using SSIS, we need the below components installed before stating.

1. Microsoft OData Source for Microsoft SQL Server 2012
   Download from http://www.microsoft.com/en-in/download/details.aspx?id=42280
2. SharePoint Server 2013 Client Components SDK
   Download from http://www.microsoft.com/en-in/download/details.aspx?id=35585 

Microsoft OData Source for Microsoft SQL Server 2012 is the SSIS component which we use as source connection to the Sharepoint Online Lists and SharePoint Server 2013 Client Components SDK allows us to connect to Sharepoint Online.

Once you download and Install both the components, 

1. Open the Visual Studio and create a new Integration Services Package
2. Drag and Drop "Data Flow Task"
3. In the "Data Flow Task" Drag and Drop "OData Source"
4. Right-Click on "OData Source" and Click Edit
5. Enter the "Connection Manager Name" and "Service Document Location" For Service Document Location, append "/_vti_bin/listdata.svc" to the SharePoint site
   For Office 365, we need to use "User this user name and password"
   
   
6. Click "All" and change the "Microsoft Online Services Authentication" to True
7. Click "Test Connection"
8. Click "OK"
9. Then Choose "Collection" in "User Collection or resource path"
10. In the "Collection" choose the list you want to import
11. When you click on "Preview..." you will be able to see the data from the list
12. Now add the destination connection and run the package to import data from the SharePoint Online list

Error while connecting to SQL Server Instance on a Windows Azure Machine from local SQL Server Management Studio

When you try to connect to an SQL Server Instance hosted on Windows Azure, it works perfectly fine from within the server or through Azure management portal.
However, when you try to connect to the same instance from an local SQL Server Management studio, you get the below error which says "The connection string is not valid. The parameter is incorrect"

This is because, the name of the server specified will be wrong. For connecting to the instance hosted on azure, the server name we specify is slightly different from what we specify regularly.

Make sure the connection details you specify is as below.

Server type:  Database Engine
Server name: tcp:<servername>.cloudapp.net,<DB Port Number> OR tcp:<servername>.database.windows.net
Authentication:  SQL Server Authentication
Login: SQL User Name
Password: Password

Review on SysTools Master SQL Server Database Recovery Tool - A Guest Post by Andrew Jackson

Many might be aware of SysTools Software Company, which is a brand name in data recovery industry. Since its start in the year 2007 it has developed multiple software applications and served people all over the world with its highly sophisticated tools by online marketing. One of its key and highly talked about utility is SQL Recovery that is said to recover corrupt MDF and NDF files of MS SQL Server database (DB) and then export it into the database of server. It has been designed and redesigned quite a few times to provide users with new characteristics with its each new release.
Each edition of the SQL Server database recovery tool comes equipped with more enhanced and advanced features as compared to the previous one. The current version of the recovery tool that is being offered is 6.0. It is said to possess quite a number of amenities so as to facilitate people in recovering database and the compatible scripts too. However, the characteristics of the application need to be discussed in detail so as get a complete and overall understanding. The product review in fact is a step forward towards the same purpose. So, here goes a description of the key facilities and are mentioned in the section below:

Key Characteristics

This section of the review contains a detailed explanation of the multiple features of the SQL Server database recovery tool:   

1. SQL Server DB File Recovery - The database of Structured Query Language server stores Tables, Rules, Stored Procedure, Functions, Views, Triggers and its associated Primary Key, Unique Key and many others  components. Each and every part is said to be recoverable on corruption be it regardless of the fact whether it is minor or major corruption.
2. Primary & Secondary File Recovery – The primary files are the ones with .mdf as the extension while the secondary with .ndf file extension and both of them form the crucial part of server database in which data items are stored. Even when they get damaged the utility recovers it successfully without any difficulty.
3. Recovery of Permanently Deleted Data - Sometimes data items of the server database gets removed permanently, knowingly or unknowingly, from the files. With Advance scan option both .mdf as well as .ndf file gets deeply scanned and with this even the permanently deleted data gets retrieved.
4. Ability to Fix Major SQL Errors Also – When the level of corruption that affects the files of SQL server database is very high, then the damage caused to the primary and secondary files are also severe in nature and major errors prompt up. Even in such cases of rigorous damage, the tool fixes the errors while easily recovering both the file and its data. 
5. Supports SQL Server 2012 and All below Versions – The tool is devised to recover corrupted files of almost all editions of the SQL server like 2012, 2008, 2008 R2, 2005 and also 2000 database files. However, the latest release is 2014 but it is not supported by the recovery application.
6. Supported on All Releases of Windows Operating System – The most new version of MS Windows operating system is 8.1 and many users possess it. Therefore, the utility should be and is supported on all editions like Windows 8, 7, Vista, etc. including the latest one. 

Other Significant Properties of the Recovery Tool  

• Multiple secondary files (with .ndf extension) can be scanned and recovered at a time. This helps in reducing time for recovery.  
• Can export and save recovered file into both SQL Server database and also as SQL Server Compatible Scripts which is indeed a good feature. 
• Offers choice of selection to export and save file with either only Schema or both schema and data as well. This is a highly advantageous as it gives user the power to follow his/ her needs.
• Quick scan is provided to recover files from minor corruption issues. This is important because this form of scan takes less time to be executed as compared to Advance Scan mode of recovery.
• Has the power to detect the version of SQL server automatically. This is indeed a great feature as it recovers damaged file even when the user is unaware of the edition of SQL server.
• Minimum system requirements in terms of hard disk space, RAM and processor to install the software.
• Secured by two powerful anti-malware technologies that are Norton and McAfee that protects the machine on which the tool is run.

Overall Performance Review 

As per the discussion on the characteristics of the SQL Recovery version 6.0 that functions as SQL Server database recovery tool, it can be concluded that the application is an appropriate selection to recover any kind of corruption issue in either the primary or its associated secondary files. With its extremely high tech design, the functionalities that it renders are matchless as compared to the ones developed by other companies. But the point where it lacks is that it does not support the most new release of SQL Server by Microsoft i.e. edition 2014. This drawback will restrict some users in making use of the utility but sidelining this point the software proves to be an apt solution.

About Andrew Jackson

Andrew is a SQL Server DBA at SysTools and he is based out of New York City.

He has nearly 7 years of rich experience on SQL Server Database administration skills.

T-SQL to find Job Name of Subscribed Reports

Here is an handy T-SQL script which gives information about your subscribed reports in SSRS.

USE ReportServer
GO
SELECT 
   SJ.name AS JobName
  ,Cat.Name AS ReportName
  ,Sub.Description AS SubscriptionDescription
  ,Cat.Path AS ReportPath
  ,Sub.DeliveryExtension AS SubscriptionDeliveryType
  ,Sub.LastStatus
  ,Sub.EventType
  ,SJ.date_created AS SubscriptionCreatedDate
  ,SJ.date_modified AS SubscriptionModifiedDate
FROM msdb.dbo.sysjobs SJ
INNER JOIN ReportSchedule RS ON SJ.name=CAST(RS.ScheduleID AS VARCHAR(max))
INNER JOIN Subscriptions Sub ON Sub.SubscriptionID=RS.SubscriptionID
INNER JOIN Catalog Cat ON Cat.ItemID=Sub.Report_OID

T-SQL to get running sessions and its progress details

Here is a handy T-SQL to get running sessions and its progress details.

SELECT 
  ExecSessions.session_id AS SessionID
 ,ExecRequests.blocking_session_id AS BlockingSessionID
 ,db_name(ExecRequests.database_id) AS DatabaseName
 ,ExecSessions.login_name AS LoginName
 ,ExecSessions.memory_usage AS MemoryUsage
 ,CONVERT(dec(6,3), (ExecRequests.wait_time/60000.00)) AS [WaitTime (mins)]
 ,ExecRequests.[status] AS [Status]
 ,ExecRequests.percent_complete AS PercentComplete
 ,[Text]
FROM sys.dm_exec_sessions AS ExecSessions INNER JOIN sys.dm_exec_requests AS ExecRequests 
ON ExecSessions.session_id = ExecRequests.session_id
CROSS APPLY sys.dm_exec_sql_text (sql_handle) 
WHERE ExecSessions.status = 'running' AND ExecSessions.session_id <> @@SPID
ORDER BY ExecSessions.session_id

A connection was successfully established with the server, but then an error occurred during the login process.

Recently I came across an issue when connecting to SQL Server instance using SQL Authentication.
The error says,

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error:0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error:233)

To fix the issue, Disable "Shared Memory" protocol in the SQL Server Configuration Manager.

To find the status of SQL Server Protocols using T-SQL, refer to the below link
http://www.sanssql.com/2011/04/t-sql-query-to-find-sql-server.html

The connected user is not an Analysis Services server administrator

Consider a situation where you have installed the SQL Server Analysis Services and during installation, you forget to add the Analysis Services server administrator.
When you connect to the analysis services and try to do some administrative tasks then you are presented with the error "The connected user is not an Analysis Services server administrator."

So now you do not have administrative privilege and no one is added into this rights and how to fix it?

• Start SQL Server Management Studio as administrator >> "Run as Administrator" 
• Go to "Security" Page
• Add user to the "Server Administrators" group

Reporting Services Error - The report server installation is not initialized. (rsReportServerNotActivated)

Recently, I had a task for restoring the Reporting Services database from a live environment to a test environment.
Right after the successful restore, I tried opening the Report Manager website and I was presented with the error "The report server installation is not initialized. (rsReportServerNotActivated)"

This is because of the mismatch in the encryption key.
To fix this, either restore the encryption key from the live environment or delete the encryption key from the restored reporting services database.
The restoration or deletion of encryption key can be done by using the Reporting Services Configuration Manager.

T-SQL to find backup location

Here is a T-SQL query to find the location of the backup files.

SELECT database_name AS DBName
    ,physical_device_name AS BackupLocation
    ,CASE WHEN [TYPE]='D' THEN 'FULL'  
    WHEN [TYPE]='I' THEN 'DIFFERENTIAL' 
    WHEN [TYPE]='L' THEN 'LOG'
    WHEN [TYPE]='F' THEN 'FILE / FILEGROUP'
    WHEN [TYPE]='G'  THEN 'DIFFERENTIAL FILE'
    WHEN [TYPE]='P' THEN 'PARTIAL'
    WHEN [TYPE]='Q' THEN 'DIFFERENTIAL PARTIAL'
  END AS BackupType
    ,backup_finish_date AS BackupFinishDate
FROM msdb.dbo.backupset JOIN msdb.dbo.backupmediafamily
ON(backupset.media_set_id=backupmediafamily.media_set_id)
ORDER BY backup_finish_date DESC

T-SQL to find the SQL Server Installation date

Here is an handy T-SQL to find the installation date of SQL Server.

DECLARE @date AS VARCHAR(50), @ServerName AS VARCHAR(100)

SELECT @ServerName = CAST(SERVERPROPERTY('SERVERNAME') AS VARCHAR)

SELECT @date = convert(varchar(50),create_date,107) 
FROM sys.server_principals
WHERE name='NT AUTHORITY\SYSTEM'

PRINT 'This SQL Server Instance "' + @ServerName + '" was insalled on '+ @date 

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

When trying to load an excel spread sheet into SQL Server you may be presented with the below error.

This occurs because the Office System Driver and Data Connectivity Components are not installed on the server and SQL Server is unable to interact with the MS Office document. For the SQL Server to interact, open and load the data from excel spreadsheet, it requires the Office System Driver and Data Connectivity Components to be installed.

To fix this issue, download and install the Office System Driver and Data Connectivity Components from the below link.
http://www.microsoft.com/en-us/download/details.aspx?id=23734

This will install a set of components that can be used to facilitate transfer of data between Microsoft Office System files and non-Microsoft Office applications.

Do you have a desire to learn SQL?

Many of us have users that show a desire to learn SQL, and that would be awesome, but who has the time to train them?

Well I have a solution I think you’ll like.  I recently ran across a site for beginners, EssentialSQL, that provides easy to understand SQL lessons in plain English.  The site is targeted towards the beginner, though I suspect a couple of developers could use their guidance as well!

All the examples are easy to follow.  The site recommends using SQLite, since it is a snap to install.  The good news is SQLite is SQL-92 compliant, so the training applies to basic SQL Server DML.

I really liked that the training comes with videos, so if you don’t get what Kris, essential SQL’s creator, has written, you can always watch him “live.”

In additional to the training posts, the site has several posts on database concepts.  Some are basic, such as explaining ACID; where as others, delve into the details of a B-Tree indexes.  Kris mentioned he plans on digging into other topic such as Hash Indexes and Normalization.  No doubt, everyone can use a refresher with these topics.

If you know of anyone having a desire to learn SQL, I would recommend they visit this site.

Mirrored Databases disconnect and goes in-recovery state after a server restart

Recently, I had a situation where a production server was restarted and soon after the restart we found that the database mirroring was disconnected.
And as a bonus, one of the mirrored database was hung in recovery state.

When I investigated further, I found the below message in the SQL Server Logs.

Bypassing recovery for database '<DB Name>' because it is marked as an inaccessible database mirroring database. A problem exists with the mirroring session. The session either lacks a quorum or the communications links are broken because of problems with links, endpoint configuration, or permissions (for the server account or security certificate). To gain access to the database, figure out what has changed in the session configuration and undo the change.

This clearly says the connection between the principal and  the mirror is not established properly and the combination of a Mirrored Database with a big log file and a server restart caused the "In Recovery State" of the mirrored database.
To fix this error, I had to Restart the Database Mirroring Endpoint.

--To Stop the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STOPPED

--To Start the Endpoint
ALTER ENDPOINT <EndPoint Name> STATE=STARTED

Create an Encrypted Backup in SQL Server 2014

Encryption for Backups is a new feature introduced in SQL Server 2014 and the benefits of this option are

1. Encrypting the database backups helps secure the data.
2. Encryption can also be used for databases that are encrypted using TDE.
3. Encryption is supported for backups done by SQL Server Managed Backup to Windows Azure, which provides additional security for off-site backups.
4. This feature supports multiple encryption algorithms including AES 128, AES 192, AES 256, and Triple DES
5. You can integrate encryption keys with Extended Key Management (EKM) providers. 

The following are pre-requisites for encrypting a backup:

1. Create a Database Master Key for the master database.
   

   USE master;
   GO
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepass@word123';
   GO
   

2. Create a certificate or asymmetric Key to use for backup encryption.
   

   Use Master
   GO
   CREATE CERTIFICATE CertforBackupEncryption
      WITH SUBJECT = 'Certificate for Backup Encryption ';
   GO
   

Backup the database with encryption:

BACKUP DATABASE [SansSQL]
TO DISK = N'C:\Backup\SansSQL.bak'
WITH
  INIT,
  COMPRESSION,
  ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = CertforBackupEncryption
   ),
  STATS = 10
GO

Restoring the encrypted backup:
SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key. If you are restoring the encrypted backup to a different instance, you must make sure that the certificate is available on that instance.

Reference: http://msdn.microsoft.com/en-us/library/dn449489(v=sql.120).aspx

Page Level Restoration now has GUI

In one of my previous posts "Page Level Restoration", I had explained how to perform a page level restoration for a database. The whole procedure was using the T-SQL commands.
From SQL Server 2012 onward, we have an GUI to perform Page level restoration which makes life easy.

Backup a database to Windows Azure storage and restore a database from Windows Azure storage

The evaluation of SQL 2014 continues and here is what's new in Backup and Restore of SQL Server 2014.

In SQL Server 2014, we will be able to backup the database to Windows Azure storage and restore the database backup directly from the Windows Azure storage. 

To backup a database to Windows Azure storage, Choose the "Back up To:" to "URL" instead of "Disk"

To restore a database from Windows Azure storage, Choose the "Backup media type:" to "URL" instead of "File"

And here is where you connect to the Windows Azure storage during restore operation

Re-assign F5 key to refresh action in SQL Server 2014 Management Studio

During my evaluation of SQL Server 2014, I happened to face a problem, when I hit the F5 key in the Object Explorer, the SSMS was starting the Debug action instead of Refresh.
Usually most of them will be happy for F5=Refresh and F5=Query Execution.

Here is the procedure to Re-assign F5 key to refresh action in SQL Server Management Studio.

1. Open Management Studio
2. Go to Tools >> Options
   
3. Expand Environment >> Keyboard >> Keyboard
   
4. In the "Show commands containing:", type "View.Refresh" and here you can observe that "Shortcuts for selected command:" will be grayed out
   
5. Now choose "Global" in the "Use new shortcut in:" and press F5 button in "Press shortcut keys:"
   
6. Click Assign
7. Click Ok

From now on, when you press F5 in the Object explorer, the window will be refreshed instead of initiating debug option.

T-SQL query to find the timezone offset of the SQL server

Here is a short T-SQL query to find the timezone offset of the SQL server.

SELECT 'UTC'+RIGHT(SYSDATETIMEOFFSET(),6) AS 'TimeZone Offset'

T-SQL query to find if a Report Server Database is configured in Native or Sharepoint Integration mode

Here is a T-SQL Query to find if a Report Server Database is configured in Native or Sharepoint Integration mode.

USE ReportServer$SQL2008R2 --Change the Database Name
GO
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = 'ConfigurationInfo')
BEGIN

 DECLARE @Result nvarchar(max)
 SELECT @Result = Value FROM ConfigurationInfo
 WHERE NAME = 'SharePointIntegrated'
 PRINT CASE WHEN @Result= 'False' THEN 'The specified Report Server Database "'+ DB_NAME() + '" is configured in "NATIVE" mode'
      WHEN @Result = 'True' THEN 'The specified Report Server Database "'+ DB_NAME() + '" is configured in "SHAREPOINT INTEGRATION" mode'
    END
END
ELSE
BEGIN
DECLARE @DBName nvarchar(100)
SELECT @DBName = DB_NAME()
RAISERROR('The database %s is not a report server database',16,1,@DBName)
END

Unable to connect to SQL Server '(local)'. The step failed.

One fine morning you go to your office as usual and in a happy mood, you start looking into your routine tasks. Till this time everything is fine and suddenly you come across an Job failure alert which says,

Date 4/14/2014 11:47:53 PM Log Job History (SimpleJob) Step ID 1 Server SANSLAB\SQL2008R2 Job Name SimpleJob Step Name SimpleStep Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Unable to connect to SQL Server 'SANSLAB\SQL2008R2'.  The step failed.

The first thing you look at is, the connectivity to the server (because the message says so) and you find the server is contactable and also all the databases are up and online.

So now the question is from where the error is popping?
Open the job and its step and see what it is doing and you find every thing is fine including the syntax.
But the section "Database" is blank which is supposed to have a value.
That triggers something in your mind and a possible cause for the job failure

What next?
The database section should have a value and this is the cause for the job failure.
This usually happens if a user database is used within this section and it has been renamed or deleted.

So, the best practice is to

• Choose a system database name for this section and use the name of user database in your syntax. It will help avoid the failures of this kind.
• Before renaming or deleting a database, make sure it is not referenced anywhere.

By doing so, even if the job fails it will give some meaningful error for us to troubleshoot further.

Location of SSRS config files - Sharepoint integration mode

When SSRS is configured in Sharepoint Integration Mode then the config files reside in a different location when compared to the normal installation.

The location where these files can be found are
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

The below link provides a description of configuration files used for a SharePoint mode report server.
http://technet.microsoft.com/en-us/library/ms155866.aspx

Check AutoClose Status of a Database

For Setting up Database Mirroring, one of the important pre-requisite is to turn off AutoClose option on the database.
This script helps to check the status of the autoclose option on a database and will generate the script to disable autoclose if it is enabled.

DECLARE @DBName NVARCHAR(100)
SET @DBName ='SansSQL'
PRINT CASE WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 1
   THEN 'AUTO Close is enabled. Run the below statement to Disable Auto Close '+ 
     char(10) + '----------------------------------------------------------'+ char(10)+
     'ALTER DATABASE '+ @DBName +' SET AUTO_CLOSE OFF'
   WHEN DATABASEPROPERTY ( @DBName, 'IsAutoClose') = 0
   THEN 'AUTO Close is Disabled'
  END

Error while Provisioning Reporting Services Subscriptions in sharepoint

When trying to create subscriptions for SQL Server Reporting Services when hosted in SharePoint Integration mode , you might get an error which says

“The EXECUTE permission was denied on the object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.”

To Fix this issue,

1. Login to Sharepoint central administration and navigate to the “Manage Reporting Services Application” page
   
   
2. Click “Provision Subscriptions and Alerts” 
3. Click “Download Script” and this will download a file named “SSRS-GrantRights.sql”
4. Execute the script on your SharePoint database server

Configure e-Mail for a Reporting Services Service Application – Sharepoint Integration mode

Here are the steps to configure STMP/e-mail for SSRS Service application in SharePoint integration mode.

1. In SharePoint Central Administration, click the Application Management
   
2. In the Application Management page, under Service Applications group, click Manage service applications.
   
3. In the list of configured applications, click on the name of your Reporting Services service application (SSRS).
   
4. Click E-mail Settings on the Manage Reporting Services Application page.
   
5. Select Use SMTP server In the Outbound SMTP server box, type the name of an SMTP server.In the From address box, type an e-mail address. This e-Mail address will be used as the sender of the e-mail alert.
   
6. Click OK

T-SQL to get Machine name from SQL Server Instance Name

How many of you use Central Management Server?
How many of you use CMS for just storing the list of servers?
How many of you use CMS for other activities other thank just storing the server names?

The list of servers registered under the CMS can be queried using the view "sysmanagement_shared_registered_servers" present in msdb database.

This will give the list of SQL Server Instance names, but when we require to get the Machine name from the SQL Server instance name, we can use this query to achieve it.

SELECT DISTINCT CASE WHEN CHARINDEX('\',server_name) = 0 THEN server_name
      ELSE SUBSTRING(server_name,1,CHARINDEX('\',server_name)-1) 
      END AS MachineName
FROM msdb.dbo.sysmanagement_shared_registered_servers

T-SQL query to get the list of currently executing queries in particular session

This query can be used as a alternative for using Profiler.

SELECT Sess.session_id, 
requests.status, 
requests.blocking_session_id 'Blocked By', 
requests.wait_type, 
wait_resource, 
requests.wait_time / (1000.0) 'Wait Time (sec)', 
requests.cpu_time, 
requests.logical_reads, 
requests.reads, 
requests.writes, 
requests.total_elapsed_time / (1000.0) 'Total Elapsed Time (sec)', 
SUBSTRING(st.TEXT,(requests.statement_start_offset / 2) + 1, 
((CASE requests.statement_end_offset 
WHEN -1 
THEN DATALENGTH(st.TEXT) 
ELSE requests.statement_end_offset 
END - requests.statement_start_offset) / 2) + 1) AS statement_text, 
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid,st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid,st.dbid)), 
'') AS command_text, 
requests.command, 
Sess.login_name, 
Sess.HOST_NAME, 
Sess.PROGRAM_NAME, 
Sess.last_request_end_time, 
Sess.login_time, 
requests.open_transaction_count 
FROM sys.dm_exec_sessions AS Sess
JOIN sys.dm_exec_requests AS requests
ON requests.session_id = Sess.session_id 
CROSS APPLY sys.Dm_exec_sql_text(requests.sql_handle) AS st 
WHERE requests.session_id <> @@SPID 
ORDER BY requests.cpu_time desc, requests.status, 
requests.blocking_session_id, 
Sess.session_id

Difference between ORIGINAL_LOGIN() and SUSER_SNAME()

The function ORIGINAL_LOGIN() , returns the name of the login that was initially connected to the SQL Server instance. We can use this function to return the identity of the original login in sessions in which there are many explicit or implicit context/connection switches.

The function SUSER_SNAME() , returns the name of the login that is currently connected to the SQL Server instance.

Here is small demo on working of ORIGINAL_LOGIN() and SUSER_SNAME()

-- Initial Details
SELECT ORIGINAL_LOGIN() AS [OriginalLoginSession], SUSER_SNAME() AS [CurrentLoginSession]
GO
-- Execute as another Login
EXECUTE AS LOGIN = 'Test'
GO
SELECT ORIGINAL_LOGIN() AS [OriginalLoginSession], SUSER_SNAME() AS [CurrentLoginSession]
GO
-- Revert the Execute as 
REVERT
GO
-- Later Details
SELECT ORIGINAL_LOGIN() AS [OriginalLoginSession], SUSER_SNAME() AS [CurrentLoginSession]
GO

Get list of users from a local group

This post is slightly deviating from my routine SQL scripts. :)

Here is the command to get the list of users present in a local group.
This is command shell script and the results can be directly written to a output file.

To get the list of users from local administrator group, run the below command from the command prompt.
net localgroup Administrators >C:\LocalAdministrators.txt

The same can be executed from SQL server using the xp_cmdshell extended stored procedure.

EXEC xp_cmdshell 'net localgroup Administrators'

Moving database objects between schemas

Sometimes database objects gets created under different schema names.
This can be intentional or accidental. In either of the cases, if you decide to move an database object between schema then you can make use of the below script.

To move a database object to between schema

ALTER SCHEMA [Target Schema Name] TRANSFER [SchemaName].[ObjectName]

To move a database object to dbo schema

ALTER SCHEMA [dbo] TRANSFER [SchemaName].[ObjectName]

To move multiple database objects between schema, execute the result set generated by the below query

SELECT 'ALTER SCHEMA [Target Schema Name] TRANSFER ['+SCHEMA_NAME([schema_id])+'].['+[name]+']' 
FROM sys.objects WHERE SCHEMA_NAME([schema_id]) NOT IN ('dbo', 'sys')