How to Get SQl Tables Information in DataBase


Information







Get Column Length:
SELECT LEN(Name) AS MyLength FROM Tbl_Test






Get Table Wise Column Size:

select DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, DATETIME_PRECISION,
       IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS where Table_Name='TableName'


Get List Of Tables in DataBase:

SELECT *
FROM sys.Tables

Get List Table Columns: 

SELECT *
FROM sys.Columns 
0 comments

send SQl DataBase Mail Out put in HTML Formate


Information 

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
   N'<head>' +
    N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:11px; border-collapse:collapse;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>' +
   N'<h2><font color="#0000ff" size="4">Work Order Report</font></h2>' +   
   N'</head>' +
N'<body>' +
N' <hr> ' +
N' ' +
   
    
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th>
    
    </tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks2008R2.Production.WorkOrder as wo
              JOIN AdventureWorks2008R2.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2006-04-30'
                AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='xyz@abc.com',
    @profile_name = 'SQl',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;
0 comments

Email Notification SQl Server Jobs


Information
A business critical SQL Server job failed during the weekend without anyone knowing about it. When some errors are not predictable, a good system should at least send notifications when things go wrong. In this case, it would have been appropriate if an email was sent to the relevant person with as much details possible about the error.
In SQL Server, we need to setup the following two features:
  1. Firstly, Database Mail; it should be enabled and configured. This has been covered in detail here.
  2. Secondly, an Operator that will be used by SQL Agent jobs to notify the relevant person or group.
Let’s create a new Operator:
Right click on Operators and click New Operator...
Right click on Operators and click New Operator...
In the next step, all we need is specify the email address of the person or group to receive notifications
Specify an email address
Specify an email address
Now that the Operator is ready,  any sql agent job can use it
Job property - notification tab
Job property - notification tab
Last but not least, let’s make sure that SQL Server Agent is able to use the Database Mail profile
SQL Server Agent properties
SQL Server Agent properties
Alert System - choose the mail profile to use for sending notifications
Alert System - choose the mail profile to use for sending notifications
Restart SQL Server Agent
0 comments

Send Mails In SQl Jobs


Information
Today in this article I would discuss about the Database Mail which is used to send the Email using SQL Server.  Previously I had discussed about SQL SERVER – Difference Between Database Mail and SQLMail. Database mail is the replacement of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail. Special thanks to Software Developer Monica, who helped with all the images and extensive testing of subject matter of this article.
In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out. 1) Create Profile and Account 2) Configure Email 3) Send Email.
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
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO
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
GO
EXEC 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 the sysmail_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
GO
SELECT *
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.
0 comments
Subscribe to: Posts (Atom)