Exchange: Using XBL block list to filter incoming email.

Spam spam spam.. Let's block them using XBL black list.
Exploits Block List (XBL) is a database which has been frequently updated for any known spam mail sender. You can configure your Exchange server on its Connection Filter to use any or multiple XBL lists to filter spam messages.

Open Exchange System Manager,  First Organization, right click Message Delivery, left click on Connection Filter tab.then enter any known XBL FQDN

 

For more information regarding XBL block list, please check out in Wiki:

http://en.wikipedia.org/wiki/The_Spamhaus_Project

SQL: Display attributes of a table.

When I need to create a table, I usually look for a existing table as a reference. We can use a query to pull only the attributes such as data type, column name and other information from a table:

For a full list of information you could pull from a table, please check out this link:
http://msdn.microsoft.com/en-us/library/ms188348.aspx

     USE AdventureWorks2008;
     GO
     SELECT COLUMN_NAME 'Column Name',
     DATA_TYPE 'Data Type',
     CHARACTER_MAXIMUM_LENGTH 'Maximum Length',
     IS_NULLABLE 'Nullable'
     FROM AdventureWorks2008.INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_NAME = 'Person';
     GO

SQL: Look for duplicated record within a table.

How to spot a duplicated record in a table? One way to slide it is to let SQL query to group the identical records then count how many identical record exist in a table:  

       SELECT * FROM(
       SELECT COUNT(*) num,  PARTSNNU, PARTMADEIN, PARTNAME
       FROM [DB-DWH].[dbo].[PART]
       GROUP BY  PARTSNNU, PARTMADEIN, PARTNAME ) a
       WHERE  num>1

If the table has no duplicated record, there will be no record returns on the result set.

Exchange: testing SMTP email function using TELNET

One way to troubleshoot a email server is back to basic: By sending test message through telnet and SMTP alone. No GUI component involve and more meaningful connection message log to look at.  

In this situation, I have a email server emailhost.hostname.com. which its outgoing message had been queue up. I wonder where is the cause of the problem? the email server act up? firewall blocking the outgoing mail? or DNS problem?

From my email address which is admin@hostname.com I am trying to send a test message to sender@hostname.com (assume that sender is a external email address. like hotmail, gmail etc.)

From DOS prompt, type the exchange server host name with the SMTP port 25: (command line input as underline text. )

telnet emailhost.hostname.com 25

220 emailhost.hostname.com Microsoft ESMTP MAIL Service, Version: 6.0.3790.3959 read

y at  Thu, 29 Jan 2009 01:09:30 +0000

ehlo

250-emailhost.hostname.com Hello [219.123.123.11]

250-TURN

250-SIZE

250-ETRN

250-PIPELINING

250-DSN

250-ENHANCEDSTATUSCODES

250-8bitmime

250-BINARYMIME

250-CHUNKING

250-VRFY

250-X-EXPS GSSAPI NTLM LOGIN

250-X-EXPS=LOGIN

250-AUTH GSSAPI NTLM LOGIN

250-AUTH=LOGIN

250-X-LINK2STATE

250-XEXCH50

250 OK

mail from:admin@hostname.com

250 2.1.0 admin@hostname.com....Sender OK

rcpt to:sender@hostname.com

250 2.1.5 sender@hostname.com

data

354 Start mail input; end with <CRLF>.<CRLF>

subject:test email from admin to sender with rDNS ON

now we will see....

.

250 2.6.0 <admin@hostname.com> Queued mail for deliv

ery

If everything goes, the sender will receive a email from admin@hostname.com subject: test email from admin to sender with rDNS ON.. with a body of message as "now we will see."

If the message does not go through it will display additional line of message from the telnet window to tell me where this test message has been stopped.

SQL: Compare tables 2: Distinct Value

Say the row count from both tables are matched. It doesn't mean your original table is same as the copied table.One way to be sure is run a query which look for the difference by compare both tables. Row by row.

   SELECT
         [PARTSNNU],
         [PARTMADEIN],
         [PARTNAME]    

   FROM DB-PRO.dbo.PART

     EXCEPT

   SELECT
      [PARTSNNU],
      [PARTMADEIN],
      [PARTNAME]    
     
  FROM DB-DWH.dbo.PART

So the query select few columns from both tables in different databases then look for any exception, which is a distinct value exist on the left table only. If both the data value on both tables are the same, there should be nothing to return on the result set.

SQL: compare tables 1: Row Count

Say you have two databases: one is a production DB-PRO and the other is a data warehouse DB-DWH. You made a copy of table dbo.PART from production to data warehouse. Now you want to make sure both tables are the same.

There are two factors to compare tables: Row count and Distinct data value. If table A is EXACTLY like table B, then the number of rows of A should equal B, and there should be no distinct data in either table.

Lets do the row count first:

             SELECT COUNT  (*) FROM DB-PRO.dbo.[PART]
             SELECT COUNT (*) FROM DB-DWH.dbo.[PART]

After you run this on SQL, it should give you a row count on both tables:

SQL : to add a column into a existing table

 Say you have a table called PART in SQL database to store all the machine parts by name, by quantity etc. Later on you realize you need additional columns to store part's serial number, and the countries they made from.
This script should do :)

ALTER TABLE dbo.PART
ADD
    [PARTSNNUM] [int] NULL,
    [PARTMADEIN][nvarchar](10) NULL