Feeds:
Posts
Comments

Archive for February, 2008

         You may receive “Service Unavailable” error while connecting to Team Foundation Server(TFS). I was getting this error from yesterday whenever I was trying to connect to TFS. Finally today I found the reason and solution. I had changed the password of Administrator user which I had used to configure TFS Application Pool. This has stopped the application pool as the account which is running the TFS Application Pool has old password. So one has to change the password in TFS Application pool. To change password in application pool, click Start – Run and type inetmgr. This will open IIS. Now select the TFS application pool as shown below,

 Fig – (1) TFS Application Pool

        Right click on TFS Apppool and select properties. Select Identity tab and you will see the interface as shown in above figure. Here you have to enter a new password for this account and click ok. Restart the IIS and try to connect to TFS. This time you will not get the “Service Unavailable” error.

       You will get this same error if you have deleted the user which is used to configure TFS Application pool. Here is the good article from Microsoft.

Happy Programming !!

Read Full Post »

        Recently we had integrated Authorize.Net as a payment one of the payment gateway in application. Integrating Authorize.Net is a really simple task.

        First you need to register for test account. Here is the link for that. You will get a mail from Authorize.Net team regarding the approval of account.  You will receive your login id and transaction key in that email. Once you get all these information you are ready for development process.

        There are different integration methods are available, you can choose one which is most appropriate for you organization and development process. Below is the list of those methods,

     

(1) Advanced Integration Method (AIM) Implementation Guide

           AIM is Authorize.Net’s recommended connection method and offers the most secure and flexible integration. AIM allows merchants to host their own secure payment form and send transactions to the payment gateway using an end-to-end secure sockets layer (SSL) connection.

(2) Server Integration Method (SIM) Implementation Guide
           SIM uses scripting techniques to authenticate transactions with a unique transaction fingerprint. SIM provides a customizable, secure hosted payment form to make integration easy for Web merchants that do not have an SSL certificate.

(3) Automated Recurring Billing – Application Programming Interface (API) Guide
           For merchants enabled for the Automated Recurring Billing (ARB) service, the additional ARB API feature supports integration with a Web site payment form or a proprietary business application—allowing online customers or sales representatives using a business application to select and submit subscription- or installment-based payments.

(4) Customer Information Manager (CIM) – API Guide
          The Authorize.Net Customer Information Manager (CIM) allows merchants to create customer profiles that are stored on Authorize.Net’s secure servers.

 

     Here, you can get more detail and documentation about all.

 

        I have used SIM for integration. In my case, we dot want to transfer the user to Authorize.Net site. We will get all the required information on our site and than will call Authorize.Net to process, so that the user will remain on our site during the entire process.      

         Before we dive in to the integration of SIM one should check the minimum requirement for SIM.

SIM Minimum Requirements

(1) The merchant must have a U.S. based merchant bank account that allows Internet transactions.

(2) The merchant must have an e-commerce (Card Not Present ) Authorize.Net Payment Gateway account.

(3) The merchant’s Web site must be capable of performing an HTML Form POST to request the secure payment gateway hosted payment form.

(4) The merchant’s Web site or hosting provider must have server scripting or CGI capabilities such as ASP Classic, Cold Fusion, PHP or Perl.

(5) The merchant must be able to store payment gateway account data securely (for example, API Login ID , Transaction Key , Secret Answer ).

         In SIM we have to pass all the require data to API by submitting our form (asp.net or asp) using “POST” method to “https://test.authorize.net/gateway/transact.dll“.  Yes you read correct we have to write https://test.authorize.net/gateway/transact.dll in action method of our form. You can either do it by submitting the for to this URL or you can use WebRequest object to do that. In my code I have used WebRequest object. Below is the main few parameter that we are going to pass.

 

 

        Now we have test account and we have minimum requirement. Lets look in to the functionalities provided by SIM API. SIM provides five different methods,

(1) Authorization and Capture

           The amount is sent for authorization , and if approved, is automatically submitted for settlement .

The unique field requirement for an Authorization and Capture is:

<INPUT TYPE=HIDDEN NAME=”x_type” VALUE=”AUTH_CAPTURE”>

 

(2) Authorization Only

            This transaction type is sent for authorization only. The transaction will not be sent for settlement until the credit card transaction type Prior Authorization and Capture (see definition below) is submitted or the transaction is submitted for capture manually in the Merchant Interface. If action for the Authorization Only transaction is not taken on the payment gateway within 30 days, the authorization expires and is no longer available for capture. A new Authorization Only transaction would then have to be submitted to obtain a new authorization code.

The unique field requirement for an Authorization Only is:

<INPUT TYPE=HIDDEN NAME=”x_type” VALUE=”AUTH_ONLY”>

 

(3) Prior Authorization and Capture

            This transaction type is used to complete an Authorization Only transaction that was successfully authorized through the payment gateway.

  • The original Authorization Only transaction was submitted within the previous 30 days.

  • The transaction is submitted with the valid Transaction ID (x_trans_id) of an original, successfully authorized Authorization Only transaction.

  • The original transaction is not yet captured, expired or errored.

  • The amount being requested for capture is less than or equal to the original authorized amount.

 The unique field requirements for a Prior Authorization and Capture are:

<INPUT TYPE=HIDDEN NAME=”x_type” VALUE=”PRIOR_AUTH_CAPTURE”>

<INPUT TYPE=HIDDEN NAME=”x_trans_id” VALUE=”Transaction ID here”>

For this transaction type, the amount field (x_amount) is only required in the event that a Prior Authorization and Capture is submitted for an amount that is less than the amount of the original Authorization Only transaction. If no amount is submitted, the payment gateway will initiate settlement for the amount of the original authorized transaction.

 

(4) Credit

          This transaction type is used to refund a customer for a transaction that was originally processed and successfully settled through the payment gateway.

The payment gateway accepts Credits if the following conditions are met:

  • The transaction is submitted with the valid Transaction ID (x_trans_id) of an original, successfully settled transaction.

  • The amount being requested for refund is less than or equal to the original settled amount.

  • The sum amount of multiple Credit transactions submitted against the original transaction is less than or equal to the original settled amount.

  • The last four digits only of the credit card number (x_card_num) used for the original, successfully settled transaction are submitted. An expiration date is not required.

  • The transaction is submitted within 120 days of the settlement date of the original transaction.

The unique field requirements for a Credit are:

<INPUT TYPE=HIDDEN NAME=”x_type” VALUE=”CREDIT”>

<INPUT TYPE=HIDDEN NAME=”x_trans_id” VALUE=”Transaction ID here”>

<INPUT TYPE=HIDDEN NAME=”x_card_num” VALUE=”Only the last four digits of the credit card number here”>

 

(5) Void

          This transaction type is used to cancel an original transaction that is not yet settled and prevents it from being sent for settlement. A Void can be submitted against any other transaction type.

The payment gateway accepts Voids if the following conditions are met:

  • The transaction is submitted with the valid Transaction ID (x_trans_id) of an original, successfully authorized transaction.

  • The original transaction is not already settled, expired or errored.

The unique field requirements for a Void are:

<INPUT TYPE=HIDDEN NAME=”x_type” VALUE=”VOID”>

<INPUT TYPE=HIDDEN NAME=”x_trans_id” VALUE=”Transaction ID here”>

 

        Now look in to the piece of code. I have used three classes to generate framework for integration. 

   1: #region Namespaces
   2: using System;
   3: using System.Data;
   4: using System.Configuration;
   5: using System.Web;
   6: using System.Web.Security;
   7: using System.Web.UI;
   8: using System.Web.UI.WebControls;
   9: using System.Web.UI.WebControls.WebParts;
  10: using System.Web.UI.HtmlControls;
  11: using System.Text;
  12: using System.Net;
  13: using System.IO; 
  14: #endregion
  15:  
  16:  
  17:  
  18: /// <summary>
  19: /// Summary description for AuthorizeNet
  20: /// </summary>
  21: public static class AuthorizeNet
  22: {
  23:     #region Private Member(s)
  24:     private static string UNEXPECTED_ERROR = "Unexpected error";
  25:     private static string AUTHORIZENET_TEST_URL = "https://test.authorize.net/gateway/transact.dll";
  26:     private static string AUTHORIZENET_LIVE_URL = "https://authorize.net/gateway/transact.dll"; 
  27:     #endregion
  28:  
  29:  
  30:     #region Transaction Type Enum(s)
  31:  
  32:     // Transaction Type
  33:     public enum TransactionType
  34:     {
  35:         AUTH_CAPTURE = 0,
  36:         AUTH_ONLY,
  37:         PRIOR_AUTH_CAPTURE,
  38:         CREDIT,
  39:         VOID
  40:     }
  41:  
  42:     // Get string for Enum
  43:     public static string GetSringForEnum(TransactionType TransType)
  44:     {
  45:         switch (TransType)
  46:         {
  47:             case TransactionType.AUTH_CAPTURE:
  48:                 return "AUTH_CAPTURE";
  49:                 break;
  50:             case TransactionType.AUTH_ONLY:
  51:                 return "AUTH_ONLY";
  52:                 break;
  53:             case TransactionType.PRIOR_AUTH_CAPTURE:
  54:                 return "PRIOR_AUTH_CAPTURE";
  55:                 break;
  56:             case TransactionType.CREDIT:
  57:                 return "CREDIT";
  58:                 break;
  59:             case TransactionType.VOID:
  60:                 return "VOID";
  61:                 break;
  62:             default:
  63:                 return string.Empty;
  64:                 break;
  65:         }
  66:     } 
  67:  
  68:     #endregion
  69:  
  70:  
  71:     /// <summary>
  72:     /// Generate Rququest string
  73:     /// </summary>
  74:     /// <param name="objAuthorizeNetRequest"></param>
  75:     /// <returns></returns>
  76:     private static string GetRequestString(AuthorizeNetRequest objAuthorizeNetRequest,TransactionType TransType)
  77:     {
  78:         StringBuilder stbRequest = new StringBuilder(string.Empty);
  79:  
  80:         stbRequest.Append("x_login=");
  81:         stbRequest.Append(objAuthorizeNetRequest.Login);
  82:         stbRequest.Append("&x_type=");
  83:         stbRequest.Append(GetSringForEnum(TransType));
  84:         stbRequest.Append("&x_amount=");
  85:         stbRequest.Append(objAuthorizeNetRequest.Amount);
  86:         stbRequest.Append("&x_card_num=");
  87:         stbRequest.Append(objAuthorizeNetRequest.CardNumber);
  88:         stbRequest.Append("&x_exp_date=");
  89:         stbRequest.Append(objAuthorizeNetRequest.CardExpirationDate);
  90:         stbRequest.Append("&x_tran_key=");
  91:         stbRequest.Append(objAuthorizeNetRequest.TransactionKey);
  92:         stbRequest.Append("&x_relay_response=FALSE");
  93:         stbRequest.Append("&x_delim_data=TRUE");
  94:         stbRequest.Append("&x_delim_char=|");
  95:         stbRequest.Append("&x_email=chiragrdarji@yahoo.co.in");
  96:         // If x_test_request = FALSE, TransactionId is generated.
  97:         stbRequest.Append("&x_test_request=FALSE");
  98:  
  99:         // First name and Last will be displayed in Transaction report. (LastName,FirstName)
 100:         stbRequest.Append("&x_first_name=Chirag");
 101:         stbRequest.Append("&x_last_name=Darji");
 102:         stbRequest.Append("&x_company=XO Limited");
 103:  
 104:         
 105:  
 106:         if (TransType == TransactionType.CREDIT || TransType == TransactionType.PRIOR_AUTH_CAPTURE || TransType == TransactionType.VOID)
 107:         {
 108:             stbRequest.Append("&x_trans_id=");
 109:             stbRequest.Append(objAuthorizeNetRequest.TransactionId);
 110:         }        
 111:  
 112:         return stbRequest.ToString();
 113:     }
 114:  
 115:     
 116:     /// <summary>
 117:     /// Authorize the merchant detail and if merchant is valid process the credit card.
 118:     /// </summary>
 119:     /// <param name="objAuthorizeNetRequest"></param>
 120:     /// <returns></returns>
 121:     public static AuthorizeNetResponse CallAuthorizeNetMethod(AuthorizeNetRequest objAuthorizeNetRequest)
 122:     {
 123:         AuthorizeNetResponse objAuthorizeNetResponse = new AuthorizeNetResponse();
 124:         string strRequest = GetRequestString(objAuthorizeNetRequest, objAuthorizeNetRequest.TransactionType);
 125:         string strResponse = string.Empty;
 126:  
 127:         WebRequest objWebRequest = WebRequest.Create(AUTHORIZENET_TEST_URL);
 128:         objWebRequest.Method = "POST";
 129:         objWebRequest.ContentLength = strRequest.Length;
 130:         objWebRequest.ContentType = "application/x-www-form-urlencoded";
 131:  
 132:         // Add request parameters to memory stream before sending the web request.
 133:         using (StreamWriter objStreamWriter = new StreamWriter(objWebRequest.GetRequestStream()))
 134:         {
 135:             objStreamWriter.Write(strRequest);
 136:             objStreamWriter.Close();
 137:         }
 138:  
 139:  
 140:         // Get Response back.
 141:         WebResponse objWebResponse = objWebRequest.GetResponse();
 142:  
 143:         // Retrieve result parameter.
 144:         using (StreamReader objStreamReader = new StreamReader(objWebResponse.GetResponseStream()))
 145:         {
 146:             strResponse = objStreamReader.ReadToEnd();
 147:             objStreamReader.Close();
 148:         }
 149:  
 150:         HandleResponse(strResponse, objAuthorizeNetResponse);
 151:  
 152:         return objAuthorizeNetResponse;
 153:     }
 154:  
 155:  
 156:     /// <summary>
 157:     /// Set Response to indicate failure
 158:     /// </summary>
 159:     /// <param name="objAuthorizeNetResponse"></param>
 160:     /// <param name="ErrorMessage"></param>
 161:     private static void HandleError(AuthorizeNetResponse objAuthorizeNetResponse,string ErrorMessage)
 162:     {
 163:         objAuthorizeNetResponse.IsSuccess = false;
 164:         objAuthorizeNetResponse.Errors = ErrorMessage;
 165:     }
 166:  
 167:  
 168:     /// <summary>
 169:     /// Check the response and identify the result of the request.
 170:     /// </summary>
 171:     /// <param name="strResponse"></param>
 172:     /// <param name="objAuthorizeNetResponse"></param>
 173:     private static void HandleResponse(string strResponse, AuthorizeNetResponse objAuthorizeNetResponse)
 174:     {
 175:         /**********************************************************************************************************************************************************************************************************
 176:          * We will get the response srting as shown below, this is a sample string
 177:          * 
 178:          * 1|1|1|This transaction has been approved.|RmFjFi|Y|508252758|||100.00|CC|auth_capture||||||||||||chiragrdarji@yahoo.co.in||||||||||||||AAD0537178B11C0F1105614FD1774773||2||||||||||||||||||||||||||||
 179:          * 
 180:          * The first value before the first pipe(|) symbol indicates the result.
 181:          * Below is teh link that represents the different Response code
 182:          * http://www.authorize.net/support/Merchant/Transaction_Response/Response_Reason_Codes_and_Response_Reason_Text.htm
 183:          **********************************************************************************************************************************************************************************************************/
 184:         string[] strWebResponse = strResponse.Split(new char[] { '|' }, StringSplitOptions.None);
 185:  
 186:         if (strResponse != null)
 187:         {
 188:             // Check the response
 189:             if (strWebResponse[0] == "1")
 190:             {
 191:                 objAuthorizeNetResponse.IsSuccess = true;
 192:                 if (strWebResponse.Length > 3)
 193:                     objAuthorizeNetResponse.SuccessMessage = strWebResponse[3];
 194:                 // If x_test_request = FALSE, we will get transaction id else Transaction id = 0
 195:                 if (strWebResponse.Length > 6)
 196:                     objAuthorizeNetResponse.TransactionId = strWebResponse[6];
 197:             }
 198:             else
 199:             {
 200:                 if (strWebResponse.Length > 3)
 201:                     HandleError(objAuthorizeNetResponse, strWebResponse[3]);
 202:                 else
 203:                     HandleError(objAuthorizeNetResponse, UNEXPECTED_ERROR);
 204:             }
 205:         }
 206:         else
 207:         {
 208:             HandleError(objAuthorizeNetResponse, UNEXPECTED_ERROR);
 209:         }
 210:     }
 211: }

  Fig (1) AuthorizeNet.cs implements actual logic of integration.

   1: using System;
   2: using System.Data;
   3: using System.Configuration;
   4: using System.Web;
   5: using System.Web.Security;
   6: using System.Web.UI;
   7: using System.Web.UI.WebControls;
   8: using System.Web.UI.WebControls.WebParts;
   9: using System.Web.UI.HtmlControls;
  10:  
  11: /// <summary>
  12: /// Summary description for AuthorizeNetRequest
  13: /// </summary>
  14: public class AuthorizeNetRequest
  15: {
  16:     public AuthorizeNetRequest()
  17:     {
  18:         //
  19:         // TODO: Add constructor logic here
  20:         //
  21:     }
  22:  
  23:     #region Private Member(s)
  24:     private string mstrLogin;
  25:     private double mdblAmount;
  26:     private string mstrTransactionKey;    
  27:     private string mstrCardNumber;
  28:     private string mstrCardExpirationDate;
  29:     private AuthorizeNet.TransactionType enmTransactionType;    
  30:     private string mstrTransactionId; 
  31:     #endregion
  32:  
  33:     #region Public Propertie(s)
  34:     public string TransactionId
  35:     {
  36:         get { return mstrTransactionId; }
  37:         set { mstrTransactionId = value; }
  38:     }
  39:  
  40:  
  41:     public string CardNumber
  42:     {
  43:         get { return mstrCardNumber; }
  44:         set { mstrCardNumber = value; }
  45:     }
  46:  
  47:  
  48:     public double Amount
  49:     {
  50:         get { return mdblAmount; }
  51:         set { mdblAmount = value; }
  52:     }
  53:  
  54:  
  55:     public string Login
  56:     {
  57:         get { return mstrLogin; }
  58:         set { mstrLogin = value; }
  59:     }
  60:  
  61:     public string CardExpirationDate
  62:     {
  63:         get { return mstrCardExpirationDate; }
  64:         set { mstrCardExpirationDate = value; }
  65:     }
  66:  
  67:     public string TransactionKey
  68:     {
  69:         get { return mstrTransactionKey; }
  70:         set { mstrTransactionKey = value; }
  71:     }
  72:  
  73:     public AuthorizeNet.TransactionType TransactionType
  74:     {
  75:         get { return enmTransactionType; }
  76:         set { enmTransactionType = value; }
  77:     }
  78:     #endregion
  79:     
  80: }

Fig (2) AuthorizeNetRequest.cs implements Request class.

   1: using System;
   2: using System.Data;
   3: using System.Configuration;
   4: using System.Web;
   5: using System.Web.Security;
   6: using System.Web.UI;
   7: using System.Web.UI.WebControls;
   8: using System.Web.UI.WebControls.WebParts;
   9: using System.Web.UI.HtmlControls;
  10: using System.Collections.Generic;
  11:  
  12: /// <summary>
  13: /// Summary description for AuthorizeNetResponse
  14: /// </summary>
  15: public class AuthorizeNetResponse
  16: {
  17:     public AuthorizeNetResponse()
  18:     {
  19:         //
  20:         // TODO: Add constructor logic here
  21:         //
  22:     }
  23:  
  24:     private string mstrTransactionId;
  25:     private string mlstErrors;
  26:     private string mlstSuccessMessage;
  27:  
  28:     public string SuccessMessage
  29:     {
  30:         get { return mlstSuccessMessage; }
  31:         set { mlstSuccessMessage = value; }
  32:     }
  33:     
  34:     private bool mblnIsSuccess;
  35:  
  36:     public bool IsSuccess
  37:     {
  38:         get { return mblnIsSuccess; }
  39:         set { mblnIsSuccess = value; }
  40:     }
  41:  
  42:  
  43:     public string Errors
  44:     {
  45:         get { return mlstErrors; }
  46:         set { mlstErrors = value; }
  47:     }
  48:  
  49:  
  50:     public string TransactionId
  51:     {
  52:         get { return mstrTransactionId; }
  53:         set { mstrTransactionId = value; }
  54:     }
  55:     
  56: }

Fig (3) AuthorizeNetRespose.cs implements Response class.

 

Finally Default.aspx.cs page which call Authorize.Net method.

   1: public partial class _Default : System.Web.UI.Page 
   2: {
   3:     private const string TRANSACTION_KEY = "Transaction Key";
   4:     private const string CREDITCARD_NUMBER = "Card number";
   5:     private const string LOGINID = "Login id";
   6:     private const string CARD_EXPIRATION_DATE = "MM-YY";
   7:  
   8:     private string TransationID
   9:     {
  10:         get
  11:         {
  12:             if (ViewState["TransationID"] == null)
  13:                 ViewState["TransationID"] = string.Empty;
  14:  
  15:             return (string)ViewState["TransationID"];
  16:         }
  17:         set
  18:         {
  19:             ViewState["TransationID"] = value;
  20:         }
  21:     }
  22:  
  23:     protected void Page_Load(object sender, EventArgs e)
  24:     {
  25:         lblMessage.Text = string.Empty;
  26:         lblTransType.Text = string.Empty;
  27:     }
  28:  
  29:     private void DoAuthorizationAndPayment()
  30:     {
  31:         AuthorizeNetRequest objAuthorizeNetRequest = new AuthorizeNetRequest();
  32:         
  33:         // This is the account information for merchant account given by Authorize.Net people in email
  34:         // I can see transaction history here.
  35:         objAuthorizeNetRequest.Login = LOGINID; 
  36:         objAuthorizeNetRequest.Amount = 10.00;
  37:         objAuthorizeNetRequest.CardNumber = CREDITCARD_NUMBER; 
  38:         objAuthorizeNetRequest.CardExpirationDate = CARD_EXPIRATION_DATE;
  39:         objAuthorizeNetRequest.TransactionType = AuthorizeNet.TransactionType.AUTH_CAPTURE;
  40:         
  41:         // Below is the API created by me by registering for test account.
  42:         
  43:         objAuthorizeNetRequest.TransactionKey = TRANSACTION_KEY;
  44:  
  45:         AuthorizeNetResponse objAuthorizeNetResponse = AuthorizeNet.CallAuthorizeNetMethod(objAuthorizeNetRequest);
  46:  
  47:         lblTransType.Text = "Authorize And Capture";
  48:  
  49:         if (objAuthorizeNetResponse.IsSuccess)
  50:         {
  51:             TransationID = objAuthorizeNetResponse.TransactionId;            
  52:             lblMessage.Text =  "Success. Transaction ID : " + objAuthorizeNetResponse.TransactionId;
  53:         }
  54:         else
  55:         {
  56:             lblMessage.Text =  "Error : " + objAuthorizeNetResponse.Errors;
  57:         }
  58:  
  59:     }
  60: }

Here is thebest link I found for implementing SIM, Autorize.Net using ASP.NET.

 

Happy programming !!

Read Full Post »

      You get this error while restoring the database backup file in SQL Server 2005. This is because when you took the backup your backup file is divided in more than one part and at the time of restoring the database you have not added all the parts.

       Lets do this practically so that you can have better idea. Take the backup of any database you have. To take the backup right click on database and select the task and click on backup,

Fig – (1) How to take backup

         Once you click on Backup you have to select the location for .back file on database. If there are two paths in path selection box as shown in image below, the backup file is generated in two parts and saved at two different locations.

Fig – (2) Two paths in backup file path selection. 

        So in this case, we have two backup files generated for selected database. Now at the time of restoring if you are assigning only one file and try to restore the database, you will get “The media set has 2 media families but only 1 are provided.” error.

        To solve this issue you have to add all the files (in our case two files) in file selection box,

Fig – (3) Restoring database

        Once you select all the backup files and click on restore, the database will restore successfully.

For more detail check here.

Happy Programming !!

Read Full Post »

         As a developer, it is really important for us to understand database design and underlying tables used in application. Sometime we do not have direct access to database server so that we can not open the server console and look in to the database.

         In this case we can take help of SysObjects, SysColumns, SysTypes tables of SQL Server 2005. These tables stores the information about each tables and columns and their data types. Using this tables you can write the query to find out all the tables and columns in selected database. Below is the query that gives you all the table and columns for those tables with data types and length.

   1: SELECT
   2:     SysObjects.[Name] as TableName,
   3:     SysColumns.[Name] as ColumnName,
   4:     SysTypes.[Name] As DataType,
   5:     SysColumns.[Length] As Length
   6: FROM
   7:     SysObjects INNER JOIN SysColumns
   8:     ON SysObjects.[Id] = SysColumns.[Id]
   9:     INNER JOIN SysTypes
  10:     ON SysTypes.[xtype] = SysColumns.[xtype]
  11: WHERE
  12:     SysObjects.[type] = 'U'
  13: ORDER BY
  14:     SysObjects.[Name]

Fig – (1) Query to find all the tables and columns for those tables.

           “Type” columns of SysObjects table represent the different objects available in database (like Table,Trigger,Stored Procedures etc.). Below list explains the different values of “Type” columns.

   1: C = CHECK constraint
   2: D = Default or DEFAULT constraint
   3: F = FOREIGN KEY constraint
   4: FN = Scalar function
   5: IF = Inlined table-function
   6: K = PRIMARY KEY or UNIQUE constraint
   7: L = Log
   8: P = Stored procedure
   9: R = Rule
  10: RF = Replication filter stored procedure
  11: S = System table
  12: TF = Table function
  13: TR = Trigger
  14: U = User table
  15: V = View
  16: X = Extended stored procedure

Fig – (2) Explanation of different values of “Type” columns in SysObjects table.

          For more detail refer SysObjects and SysColumns.  and So in fig – (1) query uses [type] = ‘U’. This means query displays al the user tables. You can change the condition in WHERE clause to get different objects. The query shown below displays all the triggers in selected database.

   1: SELECT
   2:     b.[Name] as [Table Name],
   3:     a.[Name] as [Trigger Name],
   4:     a.[crdate] as [Created Date]
   5: FROM
   6:     SysObjects a
   7:     INNER JOIN Sysobjects b
   8:     ON a.[parent_obj] = b.[id]
   9: WHERE
  10:     a.[type] = 'TR'
  11: ORDER BY
  12:     b.[Name]

Fig – (3) Query to display all the Triggers for selected database.

Happy Programming !!

Read Full Post »