Execute permissions to Integration services on SQL Server

follow the below process to grant access for Integration services:

GO: MSDB --> Security --> Roles --> db_ssisltduser --> add the user/group name

Give SQLAgentOperatorRole --> if they want them to schedule it.

For more on integration securities please refer the link:

http://msdn.microsoft.com/en-us/library/ms141053.aspx

Thanks,
Satish Kumar.

send an email if the query returns any data

Hi All,
Some times we have requirement to send an e-mail, if the query returns data. please use the below code:
==============================================
use DATABASE_NAME
go
if exists(SELECT DstDesc, DstDT, COUNT(*) as [Count] from Distributions where
DstDT > '25-Aug-2011' group by DstDesc, DstDT having COUNT(*) > 1)

begin
--print 'test'
exec msdb..sp_send_dbmail @profile_name = 'SQL_MAIL_Profile_NAME',
@recipients = 'user_name@mail.com',
@subject = 'Duplicate Distribution',
@body = 'Please find below the duplicate entries in Distributions table',
@query = 'set nocount on
SELECT DstDesc, DstDT, COUNT(*) as [Count] from Distributions where
DstDT > ''25-Aug-2011'' group by DstDesc, DstDT having COUNT(*) > 1
set nocount off',
@execute_query_database = 'DATABASE_NAME',
@query_result_header = 1,
@query_result_width = 256
end
==============================================
Thanks,
Satish Kumar.

shrink database failed with PAGE granularity

Msg 651, Level 16, State 1, Line 1Cannot use the PAGE granularity hint on the table "dbo.table_name" because locking at the specified granularity is inhibited.The statement has been terminated.

The issue will error occurs when you have one or more non-clustered indexes created on table without a clustered index on table. One should always create a clustered index on table.

Resolution for above error is just to create a Clustered Index on the table AND re-run the shrink database

Thanks,
Satish Kumar.

Query to pull the users created in a database

SELECT
SU.Name AS UserName,
SU.createdate As createdate,
SR.Name AS RoleName
FROM sysUsers AS SU
INNER JOIN
sysUsers AS SR ON SU.GID = SR.UID WHERE
SU.GID <> SU.UID
ORDER BY RoleName, UserName

Enabling Service Broker on MSDB

Sometimes SQL Server jobs will be failed due to the following issue:

Msg 14650, Level 16, State 1, Procedure sp_send_dbmail, Line 72
Service Broker message delivery is not enabled in this database. Use the ALTER DATABASE statement to enable Service Broker message delivery.

Solution
Enable Service Broker on MSDB …
To check Service Broker is enabled on MSDB, please execute the given query:
select is_broker_enabled,* from sys.databases where name = 'MSDB'

If the output value of “is_broker_enabled” is 0 -> Service Broker NOT enabled
Note: A value of 0 indicates that Service Broker message delivery is not activated in the MSDB database

If the output value of is_broker_enabled is 1 -> Service Broker IS enabled

To enable the Service Broker on MSDB database is as follows:

1) Connect to SQL Server
2) Stop the SQL Agent Services
3) Run the query "ALTER DATABASE MSDB SET ENABLE_BROKER"
4) Check the service broker is enabled or not by executing the below query:
4.1) select is_broker_enabled from sys.databases where name = 'MSDB'
4.2) The output value “1” means service broker is successfully enabled
5) Start the SQL Agent Services

Reason to stop SQL Agent services: Enabling Service Broker is not allowed when the database is in USE. Obviously SQL Agent will use MSDB database.

After the execution of the above, please exeute the below scripts to confirm that we are receving mails:

EXEC msdb.dbo.sp_send_dbmail
@recipients = user_name@mail.com',
@body = 'TEST.',
@subject = 'TEST' ;

SELECT @@SERVERNAME, it may return NULL / Wrong Server name

when you run SELECT @@SERVERNAME, it may return NULL / Wrong Server name.

Whenever you change the Network Name of Server Name, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

To confirm do the following:
SELECT @@SERVERNAME
select SERVERPROPERTY('MACHINENAME')
If the two above outputs shown different name means do the following:

To make SELECT @@SERVERNAME reflect the new changed Network Name perform the below task.

USE master
GO
EXEC sp_dropserver 'OLDSERVERNAME'
GO
EXEC SP_addserver 'NEWNETWORKNAME','LOCAL'
GO
Once done please restart the SQL Services and then Query SELECT @@SERVERNAME.

enabling DeadLoack trace on SQL Server

BEGIN
DBCC TRACEON (1204, 3605, -1)
WAITFOR TIME '23:30';
DBCC TRACEOFF (1204, 3605, -1)
END