How to test linkedserver's connectivity using SQL query

declare @Lnksrvr nvarchar(128), @returnvalue int;
set @Lnksrvr = 'LinkedServer_ConnectionName';
begin try
    exec @returnvalue = sys.sp_testlinkedserver @Lnksrvr;
end try
begin catch
    set @returnvalue = sign(@@error);
end catch;
if @returnvalue <> 0
  raiserror('Unable to connect to server. Please try later!', 16, 2 );
  

SQL Server Function for GetLastDayOfMonth

CREATE FUNCTION [dbo].[fn_GetLastDayOfMonth]     
(    
@InputDate datetime    
)    
RETURNS datetime    
BEGIN    
    RETURN DATEADD(day, -1, DATEADD(month, 1, CAST(CAST(YEAR(@InputDate) AS char(4)) + '/' + CAST(MONTH(@InputDate) AS char(2)) + '/01' AS datetime)))    
END    
  

SQL Server Function for GetFirstDayOfMonth

CREATE FUNCTION [dbo].[fn_GetFirstDayOfMonth]     
(    
@InputDate datetime    
)    
RETURNS datetime    
BEGIN    
    RETURN CAST(CAST(YEAR(@InputDate) AS char(4)) + '/' + CAST(MONTH(@InputDate) AS char(2)) + '/01' AS datetime)    
END    

How to take SharePoint Site into Offline Mode

How do you quiesce the farm?

                1. From Central Administration, Operations, select "Quiesce Farm."

                2. Enter the number of minutes in which you want the farm to be fully quiesced and click "Start Quiescing."

How do you "un-quiesce" (reset) a farm:

                1. From Central Administration, Operations, select "Quiesce Farm."

                2. Click "Close Quiescing."

Turn off SharePoint Services

This will stop the connections to some of the databases. If you try to detach any of the databases you will find that some will have connections active.

In case of the content databases you will probably find that the connections to the have already been taken care of

Either way you will need to stop some of the SharePoint services in order to get the search databases and config database totally disconnected.

You can either stop the services through the Central Administration, or you can do it at the Services Console.

To stop the connections to the existing databases stop the following services:

                1. Office SharePoint Search Service

                2. Windows SharePoint Services Administration

                3. Windows SharePoint Services Search

                4. Windows SharePoint Services Timer

                5. Windows SharePoint Services Tracing

                6. Windows SharePoint Services VSS Writer

                7. World Wide Web Publishing Service

After the services are stopped, you should be able to detach the databases that you are looking to move.

After detaching the databases navigate to where the data and log files are being stored and copy them to your destination.

Reattach your databases.

Restart your services.

Un-quiesce the farm

How to change Primary Key From Int to BigInt

ALTER TABLE [dbo].[TableName]
Drop CONSTRAINT PrimaryKeyConstraintName

--change data type
ALTER TABLE [dbo].[TableName]
ALTER COLUMN ColumnName BigInt

--add primary key
ALTER TABLE [dbo].[TableName]
ADD CONSTRAINT PrimaryKeyConstraintName PRIMARY KEY (ColumnName)

How to see PC details

Run command SystemInfo at command prompt


OR


Start --> ControlPanel -->Administrative Tools --> Event Viewer --> Windows Log --> System


Code 6005: Tells about when system got restarted
Code 6006: Tells about when system got shut down

How to See/Check Fixed Hard Drives Space Details using SQL Query

exec xp_fixeddrives
GO

How to see transaction log space usage statistics for all databases

dbcc sqlperf(logspace)
GO

How to See SQL Server Databases Health

Use master
GO


 IF  EXISTS (SELECT * FROM tempdb..sysobjects with (nolock) WHERE name like '%##drives%' AND type in (N'U'))
 DROP TABLE ##drives
 IF  EXISTS (SELECT * FROM tempdb..sysobjects with (nolock) WHERE name like '%##database%' AND type in (N'U'))
 DROP TABLE ##database
 IF  EXISTS (SELECT * FROM tempdb..sysobjects with (nolock) WHERE name like '%##db_space_info%' AND type in (N'U'))
 DROP TABLE ##db_space_info


 DECLARE @hr int
 declare @exec varchar(500)
 DECLARE @fso int
 DECLARE @drive nchar(1)
 DECLARE @odrive int
 DECLARE @TotalSize nvarchar(20)
 DECLARE @MB numeric(20,2) ; SET @MB = 1048576.00


 CREATE TABLE ##drives (
 drive nchar(1) collate database_default PRIMARY KEY,
 FreeSpace numeric(20,2) NULL,
 TotalSize numeric(20,2) NULL
 )


 INSERT ##drives(drive,FreeSpace)
 EXEC master.dbo.xp_fixeddrives


 CREATE TABLE ##database (dbname nvarchar(128) collate database_default)


 INSERT ##database(dbname)
 SELECT name FROM sysdatabases --with (nolock)
 where (512 & status) <> 512
 and (32 & status) <> 32
 and cmptlevel > 70


 DECLARE @dbname nvarchar(128)
 DECLARE @sql nvarchar(4000)
 CREATE TABLE ##db_space_info(
 dbname nvarchar(128) collate database_default,
 fgname nvarchar(50) collate database_default null,
 lname nvarchar(50) collate database_default,
 phname nvarchar(70) collate database_default,
 used_mb numeric(20,2),
 percent_used nvarchar(10) collate database_default,
 autogrow bit,growth_check bit
 )


 DECLARE dbinfocur CURSOR LOCAL FAST_FORWARD FOR SELECT dbname from ##database ORDER by dbname


 OPEN dbinfocur


 FETCH NEXT FROM dbinfocur
 INTO @dbname


 WHILE @@FETCH_STATUS=0
 BEGIN
 set @sql = 'use [' + @dbname +']'
 set @sql = @sql + ' insert into ##db_space_info
 select @dbname, sfg.groupname as fgname,lname=rtrim(left([name],50)), phname=rtrim(left(filename,70)),
 used_mb=convert(numeric(20,2),convert(numeric(20,2),FILEPROPERTY([name],''SpaceUsed''))/128),
 case when convert(numeric(20,2),convert(numeric(20,2),FILEPROPERTY([name],''SpaceUsed''))/128) != 0
 then convert(nvarchar(10),convert(numeric(20,2),convert(numeric(20,2),convert(numeric(20,2),FILEPROPERTY([name],''SpaceUsed''))/128) / convert(numeric(20,2),convert(numeric(20,2),[size])/128) * 100))
 else ''0'' end percent_used,
 case when growth = 0 then 0 else 1 end IsAutogrow,
 CASE WHEN (sf.status&0x100000) > 0 and [maxsize] > 0 and convert(numeric (20,2),convert(numeric(20,2),convert(numeric(20,2),[size])/128) * (str(growth))/100) + convert(numeric(20,2),convert(numeric(20,2),[size])/128) < [maxsize]/128.0 and convert(numeric (20,2),convert(numeric(20,2),convert(numeric(20,2),[size])/128) * (str(growth))/100) < FreeSpace
 THEN 1
 WHEN (sf.status&0x100000) > 0 and [maxsize] = -1 and convert(numeric (20,2),convert(numeric(20,2),convert(numeric(20,2),[size])/128) * (str(growth))/100) < FreeSpace
 THEN 1
 WHEN (sf.status&0x100000) < 1 and [maxsize] > 0 and convert(numeric(20,2),growth/128.0) + convert(numeric(20,2),convert(numeric(20,2),[size])/128) < [maxsize]/128.0 and convert(numeric(20,2),growth/128.0) < FreeSpace
 THEN 1
 WHEN (sf.status&0x100000) < 1 and [maxsize] = -1 and convert(numeric(20,2),growth/128.0) < FreeSpace
 THEN 1
 ELSE 0 end growth_check
 from sysfiles sf with (nolock)
 left outer join sysfilegroups sfg with (nolock) on sf.groupid=sfg.groupid,##drives
 where (drive  = upper(left(filename,1)) COLLATE DATABASE_DEFAULT
 or drive  = lower(left(filename,1)) COLLATE DATABASE_DEFAULT) order by 1'


 exec sp_executesql @sql,N'@dbname nvarchar(128) output', @dbname output


 FETCH NEXT FROM dbinfocur
 INTO @dbname
 END


 CLOSE dbinfocur
 DEALLOCATE dbinfocur

 create table #db_Files1
(name varchar(400),
fileid int,
filename varchar(4000),
Filegroup varchar(255),
Size varchar(300),
MaxSize varchar(100),
Growth varchar(100),
usage varchar(100))
create table #db_Files2
(Database_Name varchar(255),
Filegroup varchar(255),
Logical_Name varchar(400),
filename varchar(4000),
Size varchar(300),
MaxSize varchar(100),
Growth varchar(100))


declare dbcursor CURSOR for SELECT name FROM master..sysdatabases where (512 & status) <> 512 and (32 & status) <> 32
Open dbcursor


Fetch next from dbcursor
into @dbname


WHILE @@FETCH_STATUS = 0
BEGIN
set @exec = 'Use ['+@dbname+'] exec sp_helpfile'
insert into #db_Files1 exec (@exec)


insert into #db_Files2 select rtrim(@dbname), Filegroup, rtrim(name), filename,Size, MaxSize, Growth from #db_Files1
truncate table #db_Files1
Fetch next from dbcursor
into @dbname
END


CLOSE dbcursor
DEALLOCATE dbcursor

 update #db_Files2 set Filegroup = 'Log' where Filegroup is null
 update ##db_space_info set fgname ='Log' where fgname is null

 select b.Database_Name,b.Filegroup,b.Logical_Name,b.Size,b.MaxSize,a.used_mb,a.percent_used,b.Growth,a.autogrow,a.growth_check,b.filename from ##db_space_info a, #db_Files2 b
 where b.Database_Name = a.dbname and b.Filegroup = a.fgname and b.Logical_Name = a.lname
 order by 1,2,3

 --select * from ##db_space_info with (nolock) order by dbname, fgname,lname
 drop table ##drives drop table ##database drop table ##db_space_info
 drop table #db_Files2
 drop table #db_Files1
GO

SSRS - Change the Interactive Sort default to Descending

I believe as an SSRS developer you must be aware of Interactive sort option. This feature of the SSRS helps the end user to sort their report field/Column the way they want to sort. The default nature of this sort is an ascending and it is obvious that all users do not want their data to be sorted in an ascending order.

So if they want to sort their data in any of the way (ascending or descending) they need to click on up/down arrow button on the report column. In the first click data sorted in an ascending and on second click data gets sorted in descending order.

But here is question if user wants in reverse order I mean on first click data should be sorted in descending order and on second click in ascending order. This feature is not available in SSRS so we have to do some magic with code to get this done. This is possible on Numeric/Integer types not on character or date type fields. It may be possible but need to use cast or Convert.

So if you have any Numerical field then you have to multiply (*) that field with -1 like this

=Fields!Salary.Value*-1

Expand or Collapse All Grouped Items on SSRS Report

I found one more good topic on SQL Server Reporting Services so sharing here for your knowledge.

Expand or Collapse All Grouped Items on SSRS Report

I found the interesting topic on 

SSRS 2008 R2 Lookup - How to use multiple datasets in a tablix

So sharing this here for your knowledge.

How to set sp_addlinkedserver connection

After executing this script you can run SQL queries across the servers. But make sure the user what you will mention here must be a past of those servers as well.


Just mention the server name with whom you want to set link and execute this script on that system from where you want to connect the link. This link is one side so if you wand bi-directional then need to execute the script on both the servers.

EXEC master.dbo.sp_addlinkedserver @server = N'SqlServername', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SqlServername',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Username',@rmtpassword='Password!'

GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'SqlServername', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Alternative Color in the SSRS Report

=iif(rownumber(nothing) mod 2, "Gainsboro","Transparent")

Dynamic date in Connection string in the SSIS package

RIGHT((DT_STR,4,1252)YEAR(DATEADD("dd",0,getdate())),4) 
+""+ RIGHT("0"  +(DT_STR,4,1252)MONTH(DATEADD("dd",-1,getdate())),2)
+""+ RIGHT("0" +(DT_STR,4,1252)DAY(DATEADD("dd",0,getdate())) ,2) 

How to display Line nos in the SQL Server Report

=rownumber(nothing).ToString+"."

How to find out SQL Server Details

exec xp_msver
GO


exec xp_msver 'ProcessorCount'
GO


exec xp_msver 'ProductVersion', 'Platform'
GO

How to know Job History in SQL Server

USE MSDB
GO


DECLARE @JOBID VARCHAR (200)
SELECT @JOBID = 'CE2E392B-696E-4AF3-92D9-77F471CA424B'


SELECT J.JOB_ID, J.NAME,J.DESCRIPTION,J.DATE_CREATED AS CREATEDON, J.DATE_MODIFIED AS MODIFIEDON, 
JA.SESSION_ID AS SESSIONID, JA.RUN_REQUESTED_DATE AS [RUN REQUESTED DATE], JA.START_EXECUTION_DATE AS [EXECUTION DATE TIME], 
JA.LAST_EXECUTED_STEP_DATE AS [LAST EXECUTED DATE TIME], JA.STOP_EXECUTION_DATE AS [STOP EXECUTION DATE TIME], 
JA.NEXT_SCHEDULED_RUN_DATE AS [NEXT SCHEDULED],JH.INSTANCE_ID, JH.STEP_NAME AS [STEP NAME], JH.MESSAGE, 
JH.RUN_STATUS AS [STEP RUN STATUS], JH.RUN_DATE AS [STEP RUN DATE], JH.RUN_TIME AS [STEP RUN TIME], 
JH.RUN_DURATION, JH.SERVER AS [EXECUTED ON SERVER], JS.SCHEDULE_ID, JS.NEXT_RUN_DATE, JS.NEXT_RUN_TIME,
JSS.LAST_OUTCOME_MESSAGE, JSS.LAST_RUN_DURATION,JST.STEP_NAME, JST.DATABASE_NAME
FROM SYSJOBS J 
INNER JOIN SYSJOBACTIVITY JA ON J.JOB_ID= JA.JOB_ID
INNER JOIN SYSJOBHISTORY JH ON J.JOB_ID= JH.JOB_ID
INNER JOIN SYSJOBSCHEDULES JS ON J.JOB_ID= JS.JOB_ID
INNER JOIN SYSJOBSERVERS JSS ON J.JOB_ID= JSS.JOB_ID
INNER JOIN SYSJOBSTEPS JST ON J.JOB_ID= JST.JOB_ID
WHERE J.JOB_ID= @JOBID

SQL Query to find out Service Account details

DECLARE @ServiceaccountName varchar(250) 


EXECUTE master.dbo.xp_instance_regread 
N'HKEY_LOCAL_MACHINE', 
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', 
N'ObjectName', 
@ServiceAccountName OUTPUT, 
N'no_output'
  
SELECT @ServiceaccountName 

How to Shrink database in SQL Server

--Shrink Log


if object_id('tempdb.dbo.#file_info','u') is not null
drop table tempdb.dbo.#file_info


create table #file_info
(
dbname varchar(255),
logical_name varchar(255),
physical_name varchar(255)
)


insert into #file_info (dbname)
select '['+name+']' from master..sysdatabases where name not in ('Database1','Database2')


declare @sql varchar(2000), @dbname varchar(255)
declare c_1 cursor for select distinct dbname from #file_info
open c_1
fetch next from c_1 into @dbname
while @@fetch_status <> -1
begin
select @sql = 'update #file_info 
set physical_name = filename, logical_name = name
from '+@dbname+'..sysfiles 
where dbname = '''+@dbname+''' 
and filename like ''%.ldf'''


exec (@sql)


fetch next from c_1 into @dbname
end
close c_1
deallocate c_1


----------------------------------------------------------


declare @shrink_sql varchar(2000),
@shrink_dbname varchar(255),
@shrink_logical_name varchar(255)
declare c_2 cursor for select dbname,logical_name from #file_info
open c_2
fetch next from c_2 into @shrink_dbname,@shrink_logical_name
while @@fetch_status <> -1
begin
select @shrink_sql = '
use '+@shrink_dbname+'
dbcc shrinkfile ('+'['+@shrink_logical_name+']'+', notruncate)
dbcc shrinkfile ('+'['+@shrink_logical_name+']'+', truncateonly)
'
exec (@shrink_sql)
fetch next from c_2 into @shrink_dbname,@shrink_logical_name
end
close c_2
deallocate c_2
go

How to Check File Existance in the SSIS package

SET
--Read only Variables:
--User::SourceFileExtension,User::SourceFileName,User::SourceFilePath
--ReadWrite Variables:
--User::bolFileExists






' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO


_
_
 Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase


    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum




    Public Sub Main()
        Dim fileLoc, fileName, fileExtension, FileDate As String
        If Dts.Variables.Contains("User::SourceFilePath") = True AndAlso _
           Dts.Variables.Contains("User::SourceFileName") = True AndAlso _
            Dts.Variables.Contains("User::SourceFileExtension") = True Then
            fileLoc = CStr(Dts.Variables("User::SourceFilePath").Value)
            fileName = CStr(Dts.Variables("User::SourceFileName").Value)
            FileDate = Now.Date.ToString("yyyy") & Now.Date.ToString("MM") & Now.Date.ToString("dd")
            fileExtension = CStr(Dts.Variables("User::SourceFileExtension").Value)
            If File.Exists(fileLoc + fileName + FileDate + fileExtension) Then
                Dts.Variables.Item("User::bolFileExists").Value = True
                Dts.TaskResult = ScriptResults.Success
            Else
                Dts.Variables.Item("User::bolFileExists").Value = False
                Dts.TaskResult = ScriptResults.Failure
            End If
            'Dts.TaskResult = ScriptResults.Success
            ' Else
            ' Dts.TaskResult = ScriptResults.Failure
        End If
    End Sub


End Class

ReIndexing of all the tables in the selected SQL DB

DECLARE @SQL VARCHAR(500),
@TBLNAME VARCHAR(100)


DECLARE C_1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'
OPEN C_1
FETCH NEXT FROM C_1 INTO @TBLNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @SQL = 'DBCC DBREINDEX ('''+@TBLNAME+''','''',90)'
EXEC (@SQL)
INSERT INTO REINDEX_AUDIT (INDEX_NAME,START)
VALUES (@TBLNAME,GETDATE())


FETCH NEXT FROM C_1 INTO @TBLNAME
END
CLOSE C_1
DEALLOCATE C_1


GO

MDS - Master Data Services

Install and Configure:
http://www.youtube.com/watch?v=6PDbqBURyUo&noredirect=1

Create and Deploy MDS pacakges:
http://www.youtube.com/watch?v=g1fZez5AJHY&feature=related

Working with Versioning:
http://www.youtube.com/watch?v=lEcmmES9YaU&feature=related

Hierarchy:
http://www.youtube.com/watch?v=C8isL5SNd8E&feature=related

Mater Data Management:
http://www.youtube.com/watch?v=j-ZYbafWRF0&feature=related
http://www.youtube.com/watch?v=7Y25_4bs9DY&feature=related
http://www.youtube.com/watch?v=8HtBwm6JX70
Security:
http://www.youtube.com/watch?v=7aC7KLUW648

What is Master Data Management?
http://www.youtube.com/watch?v=h3LamD_K1vI&feature=related

Truth and Power of Master Data Management:
http://www.youtube.com/watch?v=p6VHpIlDfu4&feature=related

SPI model (SaaS, PaaS, IaaS)

What is SPI (SaaS, PaaS, IaaS)?

SPI is an acronym for the most common cloud computing service models,
Software as a Service,
Platform as a Service and
Infrastructure as a Service.

Software as a Service (SaaS) is a software distribution model in which applications are hosted by a vendor or service provider and made available to customers over a network, typically the Internet.

Platform as a Service (PaaS) is a paradigm for delivering operating systems and associated services over the Internet without downloads or installation.

Infrastructure as a Service (IaaS) involves outsourcing the equipment used to support operations, including storage, hardware, servers and networking components.
The increasing selection of services delivered over the Internet is sometimes referred to as XaaS.

Related glossary terms:XaaS (anything as a service), Infrastructure as a Service (IaaS), Software as a Service (SaaS)