Creating Linked Server through Query in SQL Server

Creating Linked Server through Query in SQL Server

-- DROPPING IF EXISTS
IF  EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0
                                                                                      AND srv.name =@ServerName)
BEGIN
            EXEC master.dbo.sp_dropserver @server=@ServerName, @droplogins='droplogins'
END

-- CREATING LINKED SERVERS
EXEC master.dbo.sp_addlinkedserver @server = @ServerName, @srvproduct=N'SQL Server'

--The linked server remote logins
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@ServerName,@useself=N'False',@locallogin=NULL,@rmtuser=@UserID,@rmtpassword=@Pwd
--GO

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

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

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

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

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

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

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

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

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

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

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

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

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

Closing Cursor by Checking if exists in SQL Server

Closing Cursor by Checking if exists in SQL Server

IF (SELECT CURSOR_STATUS('global','Cur_Name')) >=0
    BEGIN
        DEALLOCATE    Cur_UDD
    END

Ex :

BEGIN TRANSACTION
   
BEGIN TRY

DECLARE Cur_Name CURSOR FOR SELECT COL1,COL2 FROM TABLE

OPEN Cur_Name

FETCH NEXT FROM Cur_Name INTO @Col1,@Col2
WHILE @@FETCH_STATUS=0
BEGIN


    FETCH NEXT FROM Cur_Name INTO @Col1,@Col2

END

CLOSE        Cur_Name
DEALLOCATE    Cur_Name


COMMIT TRANSACTION
   
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
       
    IF (SELECT CURSOR_STATUS('global','Cur_UDD')) >=0
    BEGIN
        DEALLOCATE    Cur_UDD
    END
   
    DECLARE @Err_Msg NVARCHAR(MAX)
    DECLARE @Err_Sev INT
    SELECT  @Err_Msg=ERROR_MESSAGE(),@Err_Sev=ERROR_SEVERITY()   
    RAISERROR(@Err_Msg,16,1)
END CATCH

Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

CREATE PROCEDURE  Change_UDD_SP
(
    @Find_UDD_Name        NVARCHAR(MAX),
    @Replace_UDD_Type    NVARCHAR(MAX)
)
AS
BEGIN
   
    SET NOCOUNT ON

    DECLARE @Replace_UDD_Name    NVARCHAR(MAX)
    DECLARE @Udd_Count            INT
    DECLARE @CreateUDDQuery        NVARCHAR(MAX)

    SELECT    @Udd_Count            = 1
    SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)

   
    SELECT @Find_UDD_Name=LTRIM(RTRIM(@Find_UDD_Name))
    SELECT @Replace_UDD_Type=LTRIM(RTRIM(@Replace_UDD_Type))
   
    -- TO VALIDATE DATA
    IF ISNULL(@Find_UDD_Name,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Find_UDD_Name',16,1)
        RETURN
    END
   
    IF ISNULL(@Replace_UDD_Type,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Replace_UDD_Type',16,1)
        RETURN
    END

    -- TO CHECK FIND UDD IS EXISTS
    IF NOT EXISTS(SELECT 'X' FROM sys.types where name=@Find_UDD_Name)
    BEGIN
        RAISERROR('NO SUCH UDD : %s',16,1,@Find_UDD_Name)
        RETURN
    END
   
   
    --IF NOT EXISTS(SELECT    'X'    FROM    SYS.OBJECTS O,
    --                                    SYS.ALL_COLUMNS C,
    --                                    SYS.TYPES T
    --                            WHERE    o.object_id        =    c.object_id 
    --                            AND        c.user_type_id    =    t.user_type_id 
    --                            AND        t.name            =    @Find_UDD_Name)
    --BEGIN
    --    RAISERROR('NO REFERENCES TO THIS UDD : %s',16,1,@Find_UDD_Name)
    --    RETURN
    --END
   

    -- FIND RENAMING UDDs
    WHILE EXISTS(SELECT 'X' FROM SYS.TYPES WHERE name=@Replace_UDD_Name)
    BEGIN
        SELECT    @Udd_Count            = @Udd_Count +1
        SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)   
    END
   
   
    BEGIN TRANSACTION
   
    BEGIN TRY

        -- RENAMING UDDs
        EXEC SP_RENAME @Find_UDD_Name,@Replace_UDD_Name
       
        -- CREATING NEW UDD
        SELECT @CreateUddQuery    = N'CREATE TYPE ' + @Find_UDD_Name + ' FROM ' + @Replace_UDD_Type
        EXEC SP_EXECUTESQL @CreateUDDQuery
        --PRINT @CreateUDDQuery


        DECLARE @Table_Name        NVARCHAR(MAX)
        DECLARE @Table_Type        NVARCHAR(MAX)
        DECLARE @Column_Name    NVARCHAR(MAX)
        DECLARE @Is_Nullable    INT
        DECLARE @Is_Identity    INT
        DECLARE @Type_Name        NVARCHAR(MAX)
        DECLARE @Alter_Qry        NVARCHAR(MAX)
        DECLARE @Condition_Qry    NVARCHAR(MAX)
        DECLARE @Slno            INT
        DECLARE @Old_Table_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Cols    NVARCHAR(MAX)
        DECLARE @Constrn_Qry    NVARCHAR(MAX)
        SELECT    @Old_Table_Name    =    ''
        SELECT    @Constrn_Name    =    ''
        SELECT    @Constrn_Cols    =    ''

        DECLARE Cur_UDD CURSOR FOR    SELECT    o.name,o.type ,c.name,c.is_nullable,c.is_identity,t.name,CASE WHEN o.type='U' THEN 1 ELSE 2 END AS Slno 
                                    FROM    SYS.OBJECTS O,
                                            SYS.ALL_COLUMNS C,
                                            SYS.TYPES T
                                    WHERE    o.object_id        =    c.object_id 
                                    AND        c.user_type_id    =    t.user_type_id 
                                    AND        t.name            =    @Replace_UDD_Name
                                    ORDER BY Slno,o.type,o.name 
                                   

        OPEN Cur_UDD

        FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
        WHILE @@FETCH_STATUS=0
        BEGIN
           
            IF @Table_Name<>@Old_Table_Name AND UPPER(@Table_Type)='U'
            BEGIN
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
                IF EXISTS(SELECT 'X'    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                                        WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                                        AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                                        AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                                        AND        k.TABLE_NAME            =    c.TABLE_NAME
                                        AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                                        AND        k.TABLE_NAME            =    @Table_Name
                                        AND        k.COLUMN_NAME            =    @Column_Name)
                BEGIN
                    SELECT    @Constrn_Name            =    k.CONSTRAINT_NAME
                    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,
                            INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                    WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                    AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                    AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                    AND        k.TABLE_NAME            =    c.TABLE_NAME
                    AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                    AND        k.TABLE_NAME            =    @Table_Name
                    AND        k.COLUMN_NAME            =    @Column_Name
                   
                    SELECT    @Constrn_Cols    =    STUFF(CONVERT(NVARCHAR(MAX),(    SELECT    ','+COLUMN_NAME  
                                                                                FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                                                WHERE    CONSTRAINT_NAME    =    @Constrn_Name
                                                                                FOR        XML PATH(''))),1,1,'')
                    SELECT    @Constrn_Qry=N'ALTER TABLE ' +      @Table_Name + ' DROP CONSTRAINT ' + @Constrn_Name                                           
                    --PRINT @Constrn_Qry
                    EXEC    SP_EXECUTESQL @Constrn_Qry
                    SELECT    @Old_Table_Name    =    @Table_Name
                END
            END
           
            SELECT @Alter_Qry        =''
            SELECT @Condition_Qry    =''
           
            IF UPPER(@Table_Type)='U'
            BEGIN
                IF @Is_Nullable=0
                BEGIN
                    SELECT @Condition_Qry = ' NOT NULL '
                END
                SELECT @Alter_Qry=N'ALTER TABLE ' +  @Table_Name + ' ALTER COLUMN ' +  @Column_Name + ' ' + @Find_UDD_Name + ' ' + @Condition_Qry
               
                --PRINT @Alter_Qry
                EXEC SP_EXECUTESQL @Alter_Qry
               
            END
            ELSE
            BEGIN
                -- TO REFRESH TABLE FUNCTION AND VIEWS
                EXEC sp_refreshsqlmodule @Table_Name
               
            END
           
           
            FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
            -- CHECKING NEXT RECORD
            IF (@Table_Name<>@Old_Table_Name OR @@FETCH_STATUS<>0)  AND @Constrn_Name<>''
            BEGIN
                SELECT    @Constrn_Qry=N'ALTER TABLE ' + @Old_Table_Name + ' ADD CONSTRAINT ' + @Constrn_Name + ' PRIMARY KEY('+@Constrn_Cols+')'
                --PRINT @Constrn_Qry
                EXEC SP_EXECUTESQL @Constrn_Qry
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
            END
        END

        CLOSE        Cur_UDD
        DEALLOCATE    Cur_UDD
       
        -- CHECKING OTHER REF FOR THIS UDD
        DECLARE @Ref_Name    NVARCHAR(MAX)
        DECLARE Cur_Ref CURSOR FOR SELECT DISTINCT SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS WHERE USER_DEFINED_TYPE_NAME    =    @Replace_UDD_Name
        OPEN    Cur_Ref
        FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        WHILE @@FETCH_STATUS=0
        BEGIN
            EXEC sp_refreshsqlmodule @Ref_Name
            FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        END
        CLOSE        Cur_Ref
        DEALLOCATE    Cur_Ref
       
       
        -- DROPPING DUMMY UDD
        SELECT @CreateUddQuery    = N'DROP TYPE ' + @Replace_UDD_Name
        EXEC SP_EXECUTESQL @CreateUDDQuery
        COMMIT TRANSACTION
   
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
       
        IF (SELECT CURSOR_STATUS('global','Cur_UDD')) >=0
        BEGIN
            DEALLOCATE    Cur_UDD
        END
       
        IF(SELECT CURSOR_STATUS('global','Cur_Ref'))>=0
        BEGIN
            DEALLOCATE Cur_Ref
        END
       
        DECLARE @Err_Msg NVARCHAR(MAX)
        DECLARE @Err_Sev INT
        SELECT  @Err_Msg=ERROR_MESSAGE(),@Err_Sev=ERROR_SEVERITY()   
        RAISERROR(@Err_Msg,16,1)
    END CATCH
   
    SET NOCOUNT OFF

END

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2

Comparing 2 databases to find Objects existing in Database1 and not existing in Database2.

To Get UDD Differences :

-- NEW UDDs
select t.name,t.max_length,t.precision,t.scale,t.is_nullable,t1.name from Database1.sys.types t,Database1.sys.types t1 where t.is_user_defined=1 and t.name not in (select ti.name from Database2.sys.types ti where ti.is_user_defined=1)
AND t.system_type_id=t1.user_type_id

-- CHANGED UDDs
select t.name,tsys.name,t.max_length,t.precision,t.scale,t.is_nullable,t.default_object_id
,ttsys.name,tt.max_length,tt.precision,tt.scale,tt.is_nullable,tt.default_object_id
from Database1.sys.types t,Database2.sys.types tt,Database1.sys.types tsys,Database2.sys.types ttsys
where t.is_user_defined=1 and t.name=tt.name and t.system_type_id= tsys.user_type_id and tt.system_type_id=ttsys.user_type_id   
and t.name+'_'+CONVERT(NVARCHAR,t.system_type_id)+'_'+CONVERT(NVARCHAR,t.max_length)+'_'+CONVERT(NVARCHAR,t.precision)+'_'+CONVERT(NVARCHAR,t.scale)+'_'+CONVERT(NVARCHAR,t.is_nullable)+'_'+CONVERT(NVARCHAR,t.default_object_id)
not in (select ti.name+'_'+CONVERT(NVARCHAR,ti.system_type_id)+'_'+CONVERT(NVARCHAR,ti.max_length)+'_'+CONVERT(NVARCHAR,ti.precision)+'_'+CONVERT(NVARCHAR,ti.scale)+'_'+CONVERT(NVARCHAR,ti.is_nullable)+'_'+CONVERT(NVARCHAR,ti.default_object_id) from Database2.sys.types ti where ti.is_user_defined=1)
and t.name  not in (select n.name from Database1.sys.types n where n.is_user_defined=1 and n.name not in (select ni.name from Database2.sys.types ni where ni.is_user_defined=1) )

To Get Table Differences :

-- NEW TABLES
SELECT o.name FROM Database1.sys.objects o
WHERE o.type='U' and o.name not in (SELECT i.name FROM Database2.sys.objects i WHERE i.type='U')  order by o.name 

-- CHANGED COLUMNS
SELECT o.object_id,o.name,c.column_id,c.name,o.type,c.is_nullable,c.is_identity,t.name  
FROM Database1.sys.objects o,Database1.sys.columns c,Database1.sys.types t WHERE o.type ='U' and o.object_id=c.object_id and c.user_type_id=t.user_type_id 
AND o.name+'_'+  c.name NOT IN(SELECT uo.name+'_'+  uc.name FROM Database2.sys.objects uo,Database2.sys.columns uc WHERE uo.type ='U' and uo.object_id=uc.object_id) 
AND o.name not in (SELECT distinct n.name FROM Database1.sys.objects n
WHERE n.type='U' and n.name not in (SELECT ni.name FROM Database2.sys.objects ni WHERE ni.type='U'))
ORDER BY o.name,c.column_id  

To Get Constraint Differences :

-- DEFAULT CONSTRAINTS
select ob.name,o.name,c.name from Database1.sys.objects o,Database1.sys.columns c,Database1.sys.objects ob where o.type='D' and o.object_id=c.default_object_id and o.parent_object_id=ob.object_id  
and  ob.name+'_'+o.name+'_'+c.name not in (select obi.name+'_'+oi.name+'_'+ci.name from Database2.sys.objects oi,Database2.sys.columns ci,Database2.sys.objects obi where oi.type='D' and oi.object_id=ci.default_object_id and oi.parent_object_id=obi.object_id)  


-- PRIMARY KEY
select * from Database1.sys.objects where type='PK' and name not in(select name from Database2.sys.objects where type='PK')

Backup all databases in a Server except system DBs in Sql Server

Backup all databases in a Server except system DBs in Sql Server

Please compile this SP on Sql Server and whenever you want to take the backup of all existing databases, plz Execute this SP once.  It will automatically take backup and it will place in specified location.  

CREATE PROCEDURE [dbo].[DataBase_BackUp_SP](@OutputPath NVARCHAR(4000)='\\Server1\Shared Docs\Others\')   
AS   
BEGIN   
 SET NOCOUNT ON   
    
 DECLARE @BackFileName NVARCHAR(4000)    
 DECLARE @BackPath  NVARCHAR(4000)    
 DECLARE @BackFullPath NVARCHAR(4000)    
 DECLARE @DBName   NVARCHAR(4000)    
 DECLARE @ServerName  NVARCHAR(4000)    
   
   
 -- GETTING SERVER NAME   
 SELECT @ServerName = Name    
 FROM SYS.SERVERS    
 WHERE Server_id = 0    
   
    
 DECLARE DB_Cur CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE Database_Id>4   
 OPEN DB_Cur   
 FETCH NEXT FROM DB_Cur INTO @DBName   
 WHILE @@FETCH_STATUS=0   
 BEGIN   
     
  SELECT @BackPath=@OutputPath   
  SELECT @BackFileName='Backup_'+ @DBName +'_'+  @ServerName +'_' + CONVERT(NVARCHAR(10),GetDate(),105) + '.bak'   
  SELECT @BackFullPath =@BackPath +  @BackFileName   
     
  BACKUP DATABASE @DBName   
  TO DISK=@BackFullPath   
  WITH INIT   
     
  FETCH NEXT FROM DB_Cur INTO @DBName   
 END   
    
 CLOSE  DB_Cur   
 DEALLOCATE DB_Cur   
    
 SET NOCOUNT OFF   
END

Finding Missing Numbers in SQL Server

Finding Missing/Skipped Numbers in table  in SQL Server  

Declare @tbl table(slno int)

insert into @tbl(slno) values(1)
insert into @tbl(slno) values(3)
insert into @tbl(slno) values(4)
insert into @tbl(slno) values(5)
insert into @tbl(slno) values(11)
insert into @tbl(slno) values(13)
insert into @tbl(slno) values(18)
insert into @tbl(slno) values(8008)

select * from @tbl

select c.slno from
(select a.slno,(select b.slno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) b where b.ActSlNo=a.ActSlNo+1) as nextSlno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) a) c
where ISNULL(c.nextSlno,0)-ISNULL(c.slno,0)>1

Avoid Flickering of Datagridview in .NET

Avoid Flickering of Datagridview in .NET

Private Sub SetDoubleBuffering(ByVal pDataGridview As DataGridView)

        GetType(Control).InvokeMember("DoubleBuffered", Reflection.BindingFlags.SetProperty Or 

        Reflection.BindingFlags.Instance Or Reflection.BindingFlags.NonPublic, Nothing, 

        pDataGridview, New Object() {True})

End Sub