Move Files using Command Line Interface

Command Prompt to move the files to another location using Windows Scheduler:
Move /Y d:\backup\*.bak file://10.0.0.140/d$/backup

Shrinking Database Data Files - MS SQL Server

This procedure is a handy tool for DBA to shrink database logical data file in the SQL Server Database: Tested in SQL Server 2005.

CREATE procedure P_LOGICAL_DATA_FILE_SHRINK
AS
Begin
DECLARE @DBNAME varchar(50)
DECLARE @LOGICALFILENAME varchar(50)
DECLARE CURSOR_LOGDB CURSOR for select db_name(database_id) as DBNAME,NAME AS LOGICALFILENAME from sys.master_files(NOLOCK) WHERE TYPE_DESC='ROWS'
OPEN CURSOR_LOGDB
FETCH NEXT FROM CURSOR_LOGDB INTO @DBNAME, @LOGICALFILENAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
Declare @SQL1 Varchar(MAX)
Declare @SQL2 Varchar(MAX)
SET @SQL1 = 'BACKUP LOG '+@DBNAME+' WITH TRUNCATE_ONLY'
PRINT @SQL1
EXEC (@SQL1)
SET @SQL2 = 'use ' + @Dbname + ' DBCC SHRINKFILE(' + @LOGICALFILENAME + ',0)'
PRINT @SQL2
EXEC (@SQL2)
FETCH NEXT FROM CURSOR_LOGDB INTO @Dbname, @LOGICALFILENAME
END
close CURSOR_LOGDB
Deallocate CURSOR_LOGDB
END

DATABASE LOG TRUNCATE OR LOG SHRINKING IN MSQL SQL SERVER

This procedure is a handy tool for DBA to truncate logs in the SQL Server Database: Tested in SQL Server 2005.

CREATE procedure P_LOGICAL_LOG_FILE_SHRINK
AS
Begin
DECLARE @DBNAME varchar(50)
DECLARE @LOGICALFILENAME varchar(50)
DECLARE CURSOR_LOGDB CURSOR for select db_name(database_id) as DBNAME,NAME AS LOGICALFILENAME from sys.master_files(NOLOCK) WHERE TYPE_DESC='LOG'
OPEN CURSOR_LOGDB
FETCH NEXT FROM CURSOR_LOGDB INTO @DBNAME, @LOGICALFILENAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
Declare @SQL1 Varchar(MAX)
Declare @SQL2 Varchar(MAX)
SET @SQL1 = 'BACKUP LOG '+@DBNAME+' WITH TRUNCATE_ONLY'
PRINT @SQL1
EXEC (@SQL1)
SET @SQL2 = 'use ' + @Dbname + ' DBCC SHRINKFILE(' + @LOGICALFILENAME + ',0)'
PRINT @SQL2
EXEC (@SQL2)
FETCH NEXT FROM CURSOR_LOGDB INTO @Dbname, @LOGICALFILENAME
END
close CURSOR_LOGDB
Deallocate CURSOR_LOGDB
END

How to check Entire Instance SQL Server Database, Logical and Physical Name

Below is the script to find Database, Logical and Physical Name

a) select db_name(database_id) as DBNAME,name AS LOGICALFILENAME,physical_name AS [PHYSICAL_NAME] from sys.master_files(NOLOCK) -- To find all Database Name, Logical and Physical Name

b) select db_name(database_id) as DBNAME,name AS LOGICALFILENAME,physical_name AS [PHYSICAL_NAME] from sys.master_files(NOLOCK) WHERE TYPE_DESC='LOG' -- -- To find all Database Name, Logical Log File

c) select db_name(database_id) as DBNAME,name AS LOGICALFILENAME,physical_name AS [PHYSICAL_NAME] from sys.master_files(NOLOCK) WHERE TYPE_DESC='ROWS' ---- To find all Database Name, Logical Data File

Rule "Previous releases of MS Visual Studio 2008" failed.

OBJECTIVE: TO AVOID MS SQL SERVER 2008 INSTALATION FAILURE, PLEASE INSTALL VISUAL STUDIO 2008 SP1 FOR YOUR VISUAL STUDIO 2008 PRIOR TO THE INSTALLATION OF MS SQL SERVER 2008

You may get the SP1 from here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=FBEE1648-7106-44A7-9649-6D9F6D58056E&displaylang=en
The error you may come across like below screenshot if you never install VS SP1

Column Value Restriction - Trigger

To restrict the column value change beyond the range, you may consider to use the below trigger in sql server.

CREATE TRIGGERTrig_column_value_Restriction on schedule for Insert, Update as
Begin
Declare @TimeType int, @day int, @HOUR int,@MINUTE int
select @TimeType = TimeType,@day=day, @HOUR=HOUR,@MINUTE=MINUTE from Inserted
if (@TimeType = 2 and ( @day NOT BETWEEN 0 and 31 OR @HOUR NOT BETWEEN 0 and 23 OR @MINUTE NOT BETWEEN 0 AND 59 ) )
Begin
RaisError('PLEAE TYPE DAY, HOUR, MINUTE WITHIN THE RANGE. PLEASE CONTACT ADMINISTRATOR IF YOU NEED ANY HELP', 18, 1)
Rollback Tran
End
End

To see the available trigger in particular table:
sp_helptrigger 'TableName'
To enable or disable trigger for particular table:
DISABLE TRIGGER TRIGGERNAME ON TABLENAME;
ENABLE TRIGGER TRIGGERNAME ON TABLENAME;
To alter trigger:
Replace 'Create' with 'Alter' in above Trigger statement. It will alter.
To drop trigger:
DROP TRIGGER TRIGGERNAME

Concatenate Column in Excel

Objective: How to Concatenate Column Data in Excel Sheet.
EXAMPLE: Concatenate column data with comma
=CONCATENATE(A2,",")
EXAMPLE: Concatenate column data with proper digits. Here the number is concatenated with 6 digits.
=CONCATENATE("Insert into T_Tablename (Column1,Column2,Column3) values (307, '", TEXT(A2, "000000"), "',1)")
EXAMPLE: Concatenate column data of Date Format
=CONCATENATE("insert into table (startdate,EndDate) Select '",TEXT(C2,"yyyy/mm/dd"),"','",TEXT(C2,"yyyy/mm/dd"),"'")


You can see the formula in the above screen for concatenate comma.

You can see the formula in the above screen for concatenate data with certain predefined digits.

You can see the formula in the above screen to concatenate column data with Date Format.

Microsoft Internet Explorer Access Problem

Objective: To remove the error while Accessing IE: The Operation has been cancelled due to restrictions in effect on this computer:
Please go to registry and change as per following:
[HKEY_LOCAL_MACHINE\Software\Polices\Microsoft\Internet Explorer\Restrictions\NoBrowserOptions
1. If you cannot find this option, you need to create Internet Explorer under [HKEY_LOCAL_MACHINE\Software\Polices\Microsoft\
2. Then Create Restrictions - Right Click Internet Explorer --> Select Key --> Then Type Restrictions
3. Right Click Restrictions --> Select New --> DWORD (32 Bit) Value.
4. Change the value as per you want
DWORD value for NoBrowserOptions should be 0. -- Access Permitted
DWORD value for NoBrowserOptions should be 1. -- Access Restricted


Error is like above

Change the default program of IE to Internet Explorer and Mail to Outlook (In case, if the permission denied to access IE from Outlook)

Follow the above steps to choose default program.

Go to Run --> Type Regedit -- > You will get the above

Change the DWORD value as per you like.

MS Outlook PST File Repair Tool

Sometimes, Email File (pst) from Outlook may corrupt due to various factors. So the handy tool to repair is available from MS Office 2007.

You may find that scan application file "SCANPST" from C:\Program Files\Microsoft Office\Office12

Then Right --> Run --> Choose the PST File from your email location --> Then Repair

Right Click --> Run

Select the PST file from the location.

Click the Start

Finally Repair the file by clicking Repair Button

How to Change the Table Column Structure Without Reinitialise - MS SQL Server

Consider below is the table structure replicating to other database:
USE TEST1
GO
CREATE TABLE [dbo].T_Replication
(
ID [int] NOT NULL identity(1,1) CONSTRAINT [PK_TT_Replication] PRIMARY KEY CLUSTERED,
[Name] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Company] [varchar](50) NULL,
[Submitdate] [datetime] NULL,
[EmailID] [varchar](100) NULL,
)
GO

Then once we set up the publication and subscription for the above table, we may alter the column name and data type:

exec sp_repladdcolumn @source_object = '[T_Replication]'
, @column = 'Email' -- New Column Name
, @typetext = 'varchar(75) NOT NULL' -- This is the new column type
, @publication_to_add = 'Test'
GO

UPDATE THE ADDED COLUMN WITH THE EXISTING COLUMN:

update [T_Replication] set Email = EmailID
GO

FINALLY DROP THE UNWANTED COLUMN:
exec sp_repldropcolumn @source_object = '[T_Replication]'
, @column = 'EmailID'
GO




TO ADD COLUMN AND DROP COLUMN WITHOUT DISTURBING THE EXISTING REPLICATION SET UP, ie, WITHOUT REINITIALIZING THE NEW SNAPSHOT:
ADD THE COLUMN AS YOU LIKE: (TESTED IN MS SQL Server 2000 and 2005)
GO

Also you can add constraint: Few scripts below may be useful while

Alter table dbo.Tablename drop constraint constraintname

GO

ALTER TABLE dbo.Tablename ADD DEFAULT ('UK') FOR [columnname]

GO

Exec sp_repldropcolumn @source_object = 'Tablename' , @column = 'columnname'

GO

Exec sp_repladdcolumn @source_object = 't_tablename'

, @column = 'columnname'

, @typetext ='varchar (10) not null default ''UK'' '

, @publication_to_add = 'All'

How to Reset the Identity to Specific Value

Sometimes, the identity values are much higher when you delete the huge records. So there may be occassion you may come across to reset the identity.
USE TEST1
GO
CREATE TABLE [dbo].T_Replication
(
ID [int] NOT NULL identity(1,1) CONSTRAINT [PK_TT_Replication] PRIMARY KEY CLUSTERED,
[Name] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Company] [varchar](50) NULL,
[Submitdate] [datetime] NULL,
[EmailID] [varchar](100) NULL,
)
GO
INSERT INTO [Test1].[dbo].[T_Replication]
([Name]
,[LastName]
,[Company]
,[Submitdate]
,[EmailID])
VALUES
('Adams'
,'Joe'
,'Joe Company'
,getdate()
,'Adams.Joe@yahoo.com')
GO
INSERT INTO [Test1].[dbo].[T_Replication]
([Name]
,[LastName]
,[Company]
,[Submitdate]
,[EmailID])
VALUES
('John'
,'Wilkinson'
,'ABC Company'
,getdate()
,'John.Wilkinson@yahoo.com')
GO
INSERT INTO [Test1].[dbo].[T_Replication] ([Name] ,[LastName] ,[Company] ,[Submitdate] ,[EmailID]) VALUES ('John' ,'Wilkinson' ,'ABC Company' ,getdate() ,'John.Wilkinson@yahoo.com')

DELETE FROM T_REPLICATION WHERE ID=3
Now in order to insert the deleted ID for the following records, you need to reset the identity to 3 by executing the below script. Otherwise, the insertion will follow with the identity value of 4.

Script to Reset:
DBCC CHECKIDENT(T_REPLICATION,RESEED,2)

Permissions in SQL Server - Users and Objects

USE master
GO
CREATE LOGIN JOHN WITH mailto:PASSWORD= - Create login
GO
USE REPORTSERVER - To create user in particular database. Please change the database to that particular database and then create user in that database.
GO
CREATE USER JOHN FOR LOGIN JOHN
GO
GRANT Select,Insert,Update on Batch to JOHN -- Grant permissions to particular
GO EXEC sp_helprotect @username='JOHN' - To see permissions in objects for the particular username
GO

REVOKE Select,Insert,Update on Batch to JOHN -- Revoke permissions to particular
GO
EXEC sp_helprotect @username='JOHN'
GO


DENY Select,Insert,Update on Batch to JOHN -- Deny permissions to particular user
GO
EXEC sp_helprotect @username='JOHN' -- Based on username
GO


EXEC sp_helprotect @name='Batch' -- Based on Object Name ie, table name etc
GO
EXEC sp_helprotect @GRANTorname='dbo' --It is the name of the principal that Granted permissions.
GO
EXEC sp_helprotect NULL, NULL, NULL, 's' --The following example lists all the statement permissions in the current database
GO
EXEC sp_helprotect NULL, NULL, NULL, 'o' -- Lists the object permissions