DIFFERENCES BETWEEN TINYINT , SMALLINT , INT , BIGINT IN SQL SERVER

DIFFERENCES BETWEEN TINYINT , SMALLINT & INT , BIGINT IN SQL SERVER
Similarities and Differences.

1.TINYINT

Minimum Value : 0
Maximum Value : 255
Storage size : 1 byte

2.SMALLINT

Minimum Value : -32768
Maximum Value : 32767
Storage size : 2 bytes

3.INT

Minimum Value : -2^31
Maximum Value : 2^31 – 1
Storage size : 4 bytes

4.BIGINT

Minimum Value : -2^63
Maximum Value : 2^63 – 1
Storage size : 8 Bytes.

SECURITY QUESTIONS: GRANT PERMISSIONS TO ALL STORED PROCEDURES / Allow user to EXEC and EXCUTE command in sqlserver

--Listing 1. Grant exec to all stored procedures in a database
GRANT EXEC TO [User]
GO

--Listing 2. Permissions based on executing stored procedures in a schema

GRANT EXEC ON SCHEMA::SomeSchema TO [User]

For Eg:

USE EmpDb
GRANT EXEC TO Selva;
GRANT EXECUTE TO Selva;

Unused Index Script

SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

Add new article in transactional replication in SQL Server 2012

Add new article in transactional replication in SQL Server 2012
Creating replication is one thing and maintaining it is a different yet equally important thing. Over the time, you might add or remove some objects in your primary database and hope the same for your replication database and this article comes into the picture when you need to add some more object from primary (Publication) database to replicated (subscriber) database.



Step 1 :













Step 2:






step 3 :

Copy And Paste From SQL Server Management Studio 2012 New Line Issue Into Excel

select REPLACE(REPLACE(col, CHAR(13), ''), CHAR(10), ' ')




for eg : 

OrderStatus=REPLACE(REPLACE(cast(OrderTxtStatus as varchar(500)), CHAR(13), ''), CHAR(10), ' ')

sql server Replication and Move Database Files mdf and ldf to Another Location

Step 1 :  Stop log agent in primary DB replication located

Step 2 : Open Read or secondary DB , new query window --->USE MASTER;

Step 3 :  Run 

-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE db_read
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Step 4 : -- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'db_read'
GO

Step 5 : Manually move the mdf and ldf Files from Loc1 to Loc 2

Step 6 : -- Re-Attached DB
CREATE DATABASE [db_read] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read_log.ldf' )
FOR ATTACH
GO

Step 7 : start the log agent in primary DB

Configure Database Mail – Send Email From SQL Database

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GORECONFIGUREGOsp_CONFIGURE 'Database Mail XPs', 1
GORECONFIGUREGO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GOEXEC sp_send_dbmail @profile_name='PinalProfile',@recipients='test@Example.com',@subject='Test message',@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at SQL SERVER – Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in thesysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field  value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT *FROM sysmail_mailitems
GOSELECT *FROM sysmail_log
GO

Status can be verified using sysmail_sentitems table.

After sending mail you can check the mail received in your inbox, just as I received as shown below.

Refer : http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/