Problem in adding user in security page in Analysis Service

Recently we had a problem for adding users in Analysis service security option

But we got error while adding user

The following system error occurred:No mapping between account name and security IDs was done.

So we could not add or remove any user in analysis service.

 The error was there is user ID  S-1-5-21-3915353168-1605177054-2951684922-2242

 which is not in Domain or USERS. So it was failing for validation for it.
 So we removed S-1-5-21-3915353168-1605177054-2951684922-2242
it then successfully able to add user in security page.
 

Row count int sys.dm_exec_query_stats

SQL 2012 has added 4 new columns 

total_rows,

last_rows,

min_rows,

max_rows  in sys.dm_exec_query_stats DMV

This will help us to find the queries which are returning large number of results and we can optimized them 

SELECT QS.*,

       Substring(ST.text,

                        ( QS.statement_start_offset /2 ) + 1,

                        (( CASE statement_end_offset

                           WHEN –1 THEN Datalength(st.text)

                                                      ELSE QS.statement_end_offset

                           END – QS.statement_start_offset )/ 2 ) + 1) AS statement_text

FROM   sys.dm_exec_query_statsAS QS

       CROSS APPLY sys.Dm_exec_sql_text(QS.sql_handle) AS ST

ORDER  BY max_rows DESC

DMV to get SQL Server Registry information

SQL Server 2012 has new DMV sys.dm_server_srvices
This provides details in registry related to SQL server

This provides some interesting information like 
SQL Server Version/Startup Parameters/Ports/Service startup options

SELECT *

FROM   sys.dm_server_registry

Easy way to get status of SQL Server Services in SQL 2012

SQL 2012 has some new DMVs.
One of them is sys.dm_server_services
It gives us details about the SQL Server services (SQL Serve,SQL Server Agent, Full Text)

SELECT *

FROM   sys.dm_server_services

How to avoid MSDTC in linked query

As we all are aware of linked servers and how to use it
We can use it to query remote server as well as copy data from one server to other server
Till now if we want to execute procedure on remote server and insert data on to local server using linked server we need MSDTC enabled on the server

Let see with example

We are inserting data into table tran_test from linked server xxx.xxx.xxx.xxx

insert into tran_test

exec  [xxx.xxx.xxx.xxx].[master].[dbo].[exec_test]

In SQL 2008 onwards we have new option available to disable MSDTC

As we can see once we disable “Enable Promotion of Distributed Transaction”
we can insert data without getting problem of MSDTC