Server: Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing a query for execution against OLE DB provider 'ADsDSOObject'. OLE DB error trace [OLE/DB Provider 'ADsDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

Whenever you get this issue make sure your security context is correctly supplied.In that case you have to use domian user acount in sp_addlinkedserver stored procedure whihc has access rights to Active Directory.The domain name is required in the Security settings for the remote login. So in the Security settings I entered Remote login: domain\user; With password: password.Find below dummy example.

Step1:-EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services', @provider=N'ADsDSOObject', @datasrc=N'LINLDP2'

Output : Success

Step2: EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false', @rmtuser='Domainname\username', @rmtpassword='#Security@SWIM#'

Also,Make sure your SQL Server Service is running under the account which has access right to Active Directory.

How to get, when your db was restored last time?

Suppose you have to findout when you db was restored last time and by whihc user.To get this info you have to look into a system table 'restorehistory' whihc exist in system database msdb.Run below cmds to get this info:

use msdb
go
select Destination_database_name AS [DB Name],user_name AS [User] ,restore_date As [Last Restore Date]
from restorehistory
where Destination_database_name like ('qa%')

Once you will run this you will get below output.

To find out no of traces running on your db instance

Suppose you want to find out how many traces are running on your instance?
No idea???here is a solution to find out no of traces, also you can stop them by below script.

To Find out the number of traces running use the below query

SELECT * FROM fn_trace_getinfo(default);
GO
You will get below output:

You can find out the no of traces through trace ID coloumn/count.Now suppose you have to stop them then run below script:

DECLARE @TraceID int
SET @TraceID = 2
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

Replace the trace id value with one which has to be stopped.

Error Msg 1813, Level 16, State 2, Line 1, Could not open new database ‘yourdatabasename’. CREATE DATABASE is aborted.

Fix/Solution/Workaround:

Follow below steps:-
1. Move mdf and ndf files to another directory (Data_old)
2. Create a database with the same name and same file names and locations as the original databases. (this only applies to the mdf and ndf files the log file can go anywhere)
3. Stop the SQL Server service.
4. Overwrite new mdf and ndf files with the original ones.
5. Start SQL Server.
6. Run this script (Set the @DB variable to the name of your database before running):
Declare @DB sysname;
set @DB = ‘DBName’;
– Put the database in emergency mode
EXEC(‘ALTER DATABASE [' + @DB + '] SET EMERGENCY’);
– Set single user mode
exec sp_dboption @DB, ‘single user’, ‘TRUE’;
or
– Repair database
DBCC checkdb (@DB, repair_allow_data_loss);
– Turn off single user mode
exec sp_dboption @DB, ‘single user’, ‘FALSE’;

If you are not able to connect after single user then run below cmd in one go.

Alter database dbname set single_user with roll back immediate
go
Run your DBCC checkdb command
go
Alter database dbname set multi_user

I got an error stating that the log file did not match the data file. You can ignore this as we are rebuilding the log file.

BIDS is not working after SQL Server Installation or BIDS is not installed properly.

First point is to make sure that Visual Studio is installed or not. If not previously have Visual Studio installed then first install it from SQL Server setup or run below msi or exe file:

*.\Tools\Setup\vs_setup.msi

This will install the VS Shell. After this is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory:

start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS

If above cmd gives any error then run sqlRun_tools.msi (Root\tools\setup\sqlRun_tools.msi))setup before running above cmd.Once this will complete then proceed once again with above cmd.