Changing the font color of a row dynamically using sp_send_dbmail

declare @tableHTML nvarchar(max)
SET @tableHTML =
N'<H1>Test Data</H1>' +
N'<H4>TEST_sub_header</H4>' +
N'<H4>TEST_header_1</H4>' +
N'<table border="1">' +
N'<tr><th>place</th><th>Temperature</th>' +
N'</tr>' +
CAST ( ( select case when Temperature>40 then 'red' else 'black' end as "font/@color",
place as "font/td",'',
case when Temperature>40 then 'red' else 'black' end as "font/@color",
Temperature as "font/td"
from test_fontcolor
order by place
for xml path('tr')
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@profile_name='test',
@recipients='test@microsoft.com',
@from_address = 'storenote@microsoft.com',
@subject = 'Test Font Color',
@body = @tableHTML,
@body_format = 'HTML' ;

Enable or Disable Logins with Stored Procedure

create procedure toggle_login (@login_name nvarchar(125), @set_enable bit)
as
begin
declare @sql_command nvarchar(500)
set @sql_command = 'ALTER LOGIN [' + @login_name + '] ' + case when @set_enable = 1 then 'ENABLE' else 'DISABLE' end
print @sql_command
exec sp_executesql @sql_command
end

 
--usage of above stored procedure

exec toggle_login 'domain\loginname,0

SSIS CUSTOM LOGGING

Credits to Keshav.

New Database Request form

Name

Owner

Collation

Database

• Initial size
• Growth increment
• Max size
• Drive\Volume

Transaction log

• Initial size
• Growth increment
• Max size
• Drive\Volume

Security

• Initial security needed

Backup schedule

• Days of week
• Time of day
• Type (Full, Differential, Transaction Log)
• Location (Volume or Drive or Tape)

High Availability related

• Include in log shipping
• Include in database mirroring

Business related data

• Purpose
• Related application
• Business Owner
• Priority
• User location
• Operating hours
• Maintenance hours
• Development Point of Contact
• DBA Point of Contact

Reference: http://sqlserverquestions.mssqltips.com/7211/new-database-request-form/

Script to find Duplicate Values in SQL Server

Find Duplicate Values in SQL Server
SELECT COLUMN_NAME, COUNT(COLUMN_NAME) AS NumOccurrences
FROM  [TABLE_NAME]GROUP

BY COLUMN_NAME
HAVING ( COUNT(COLUMN_NAME) > 1 )

Peer to Peer Transactional Replication

"Microsoft.SqlServer.ConnectionInfo
Cannot insert the value NULL into column 'article_id'.table'distribution.dbo.MSrepl_commands'; the column does not allow NULLLS. INSERT fails. The subscription does not exist. The subscription could not be found. (Microsoft SQL Server, Error 515)


Solution: Dont back and restore database on Second node without creating Publicatiion on first Node,
Normally you should back up after creating the publication on the first node.

It solved my issue.

Error[21355] SQL-DMO: The new DB file size must be larger than the current size

Error[21355] SQL-DMO: The new DB file size must be larger than the current size.

Reduce the initial size of mdf file in sql server.



-- get the file#s / names of all the db's files
USE pubs
EXEC sp_helpfile



--Then choose the file# or filename you want to shrink. For example, to shrink the primary file to 2GB:


DBCC SHRINKFILE ( 1, 20000)