Different Ways to Search for SQL Server Database Objects

In the course of any database development project, searching for database objects is something you will probably do.  It can be any kind of search which is done using some text as the search mechanism. Some of the examples of search that a developer / DBA may make within a database or database server are: searching for a database object, searching for occurance of particular text within database objects, searching within the schema of a database object, search within the results of query results or entire tables, etc..

In this tip, we look at different mechanisms that can be used to facilitate this type of searching.
Solution
Searching is generally required across database objects, across databases, and also across database servers. There are different mechanism that can be used for different kinds of search requirements.  For this tip I will be using SQL Server Management Studio (SSMS) 2008
.
1) SSMS Object Search
2) T-SQL Scripts
3) SQL Search Add-in
4) SSMS Tools Pack Add-in

We will look at four different ways for our search that cover various search requirements. 

1) SSMS Object Search:

As a part of the enhancements in SSMS 2008, object explorer details window has a nice object search toolbar which allows context sensitive searching of database objects. As show in the below screenshot, I attempted searching database object named "Address".  It fetched all the tables that it found with the same name. But there can be many database object having the text "Address" within it, or "Address" text can also appear in the definition of the database object. Also you can use '%' as a wild card character along with the text that you want to search in the same way as you use it with "like" for T-SQL.

2) T-SQL Scripts:

This one is the most flexible to search anything you would like to search within your database server. Using this mechanism, you have the flexibility to interrogate any schema or data level information that you would like, but the only down-side is that you need to create code and create a set of scripts to search across databases. You can create parameterized stored procedures to faciliate the search. But if a change is required, either you need to create your stored procedures with all the parameters based on how you would like to slice and dice your search or you will need to change the code. 

In the below screen-shot, I attempted searching the text "Address" within "AdventureWorks" database. I found a stored-procedure containing "Address" text within it's name. Then I created a query to search text "Address" within all the stored-procedures in this database. There is no limit to the kind of scripts that can be created for searching using this technique as long as you know which catalog views to use.

(SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id
WHERE lower(sc.TEXT) LIKE '%tUsers%')

3) SQL Search Add-in:

This is a free SSMS add-in from Red Gate Software and can be downloaded from here. Once installed, it becomes visible on the toolbar. This search requires no explanation and the benefit is that you just type the text and see the results. You can also limit the scope for a database or type of database objects as per your requirement. It also shows you if the text you are searching for appeared in the name of the database object and definition of the database object.

In the screenshot below, you can see that I attempted searching "Address" keyword within "AdventureWorks" database, and then tried locating the stored procedure that we looked at using the T-SQL Script. It shows this stored procedure twice in the search result, as "Address" is a part of the name of this stored procedure and also "Address" appears in the definition of the stored procedure. Not only does it list the object, but also highlights the places where the text appeared in the definition.

The only downside is that it won't can not copy the search results from the grid. I have requested this feature from Red Gate Software and I heard back that there is a good possibility that this feature will get added.

4) SSMS Tools Pack Add-in:

This is another free SSMS add-in and can be downloaded from here. After installation, it can be accessed from the object explorer by right-clicking as shown below.  Also at context specific areas, it is availalbe by right-clicking. If we extend our search to the next level, say we want to search the entire database including all tables and every column for a text that contains "Napa", and we need a report of the same. See the screen-shot below, where I did a right-click on database and did a search on the entire database data for any text that contains the word "Napa". This add-in has a lot of featuers, I leave it to the reader to browse the rest of these features.

Next Steps

Download and install both the add-ins mentioned above.

Try using all the options mentioned above to define a search strategy for your search requirements.

Here are some related tips:

http://www.mssqltips.com/sqlservertip/1525/scan-a-sql-server-database-for-objects-and-columns-containing-a-given-text-value/

http://www.mssqltips.com/sqlservertip/1935/using-object-explorer-details-and-object-search-feature-of-ssms-2008/

http://www.mssqltips.com/sqlservertip/1419/how-to-find-keywords-in-sql-server-stored-procs-and-functions/



Source From : http://www.mssqltips.com/sqlservertip/1962/different-ways-to-search-for-sql-server-database-objects/

Read More...

Checking Internet Connection

The Microsoft WinINet API enables applications to access standard Internet protocols, such as FTP and HTTP.

Many of you want to know if a computer has an active Internet connection before trying to connect to the internet using some communication interface. We can determine it by using one of WinINet function for check the status of internet connection in a computer.

  Private Declare Function InternetGetConnectedState Lib "wininet" _
  (ByRef conn As Long, ByVal val As Long) As Boolean

InternetGetConnectedState function retrieves the connected state of the local system. A return value of TRUE from InternetGetConnectedState indicates that at least one connection to the Internet is available.

Imports System.Runtime.InteropServices

Public Class Form1
   Private Declare Function InternetGetConnectedState Lib "wininet" (ByRef conn As Long, ByVal val As Long) As Boolean

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      Dim Out As Integer
      If InternetGetConnectedState(Out, 0) = True Then
          MsgBox("Connected !")
      Else
          MsgBox("Not Connected !")
      End If
   End Sub
End Class

Souce From : http://net-informations.com/vbprj/communications/internet-connection.htm

Read More...

How to send email from VB.NET (Old Style but work every where)

VB.NET using SMTP protocol for sending email . SMTP stands for Simple Mail Transfer Protocol . VB.NET using System.Net.Mail namespace for send mail . We can instantiate SmtpClient class and assign the Host and Port . The default port using SMTP is 25 , but it may vary different Mail Servers .In the following example shows how to send an email from a Gmail address.


create new class name MailMsg.vb and put bellow code in that class,create object of class like
dim objMail as new MailMsg

Send Mail using SendMail().

#Region "Import"

Imports System.Web.Mail
Imports System.Text
Imports System.IO

#End Region


Public Class MailMsg

#Region "Variable Declaration"

    Private m_strMailFrom As String
    Private m_strSmtpServer As String
    Private m_strSmtpUName As String
    Private m_strSmtpPwd As String
    Private m_strMailTo As String
    Private m_strMailCc As String
    Private m_strMailBcc As String
    Private m_strMailSubject As String
    Private m_strMailBody As String
#End Region



#Region "Method"

    ''' <summary>
    ''' Method to send mail using static detail mentioned in code
    ''' </summary>
    ''' <param name="strMailSubject">Mail subject</param>
    ''' <param name="strMailBody">Mail body text</param>
    ''' <param name="strMailAttachmentFileName">Attachment file path</param>
    ''' <param name="strMailAttachmentFileToBeDeleted">Attachment file path which are going to be deleted after attachment</param>
    ''' <remarks></remarks>
    Public Sub SendMail(ByVal strMailSubject As String, ByVal strMailBody As String, Optional ByVal strMailAttachmentFileName As String = "", Optional ByVal strMailAttachmentFileToBeDeleted As ArrayList = Nothing)

        Try

            m_strSmtpServer = "Mail ServerName"
            m_strSmtpUName = "UserName"
            m_strSmtpPwd = "Password"

            'Mail address
            m_strMailFrom = "FromEmailId"
            m_strMailCc = ""

            m_strMailTo = "To Email Id's (;seperated) "
            m_strMailBcc = "Bcc EmailId's (;seperated) "

            m_strMailSubject = strMailSubject
            m_strMailBody = strMailBody

            If fnCheckUserInput() = True Then

                Dim objMailMessage As New System.Web.Mail.MailMessage
                objMailMessage.Subject = m_strMailSubject
                objMailMessage.From = m_strMailFrom
                objMailMessage.To = m_strMailTo
                If m_strMailCc <> "" Then
                    objMailMessage.Cc = m_strMailCc
                End If
                If m_strMailBcc <> "" Then
                    objMailMessage.Bcc = m_strMailBcc
                End If

                objMailMessage.BodyFormat = MailFormat.Html
                objMailMessage.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", "1") 'Basic Authentication
                objMailMessage.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", m_strSmtpUName) 'user name
                objMailMessage.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", m_strSmtpPwd) 'password
                objMailMessage.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpserverpost", 25)
                SmtpMail.SmtpServer = m_strSmtpServer

                'Attach backup file in mail
                If strMailAttachmentFileName <> "" Then
                    If File.Exists(strMailAttachmentFileName) Then
                        Dim BackUpFileAttachment As New MailAttachment(strMailAttachmentFileName)
                        objMailMessage.Attachments.Add(BackUpFileAttachment)
                    End If
                End If

                If Not strMailAttachmentFileToBeDeleted Is Nothing Then
                    For Each strFileToBeDeleted As String In strMailAttachmentFileToBeDeleted
                        If strFileToBeDeleted <> "" Then
                            If File.Exists(strFileToBeDeleted) Then
                                Dim BackUpFileAttachment As New MailAttachment(strFileToBeDeleted)
                                objMailMessage.Attachments.Add(BackUpFileAttachment)
                            End If
                        End If
                    Next
                End If
                Dim MailBodyBuilder As New StringBuilder()
                MailBodyBuilder.AppendLine("<html><body style=' font-family: Verdana; font-size: 10pt;'>")
                MailBodyBuilder.AppendLine(m_strMailBody)
                MailBodyBuilder.AppendLine("</body></html>")
                objMailMessage.Body = MailBodyBuilder.ToString

                SmtpMail.Send(objMailMessage) ' Send Mail
                'Delete Backup file if it has been sent in mail
 
                If Not strMailAttachmentFileToBeDeleted Is Nothing Then
                    For Each strFileToBeDeleted As String In strMailAttachmentFileToBeDeleted
                        If strFileToBeDeleted <> "" Then
                            If File.Exists(strFileToBeDeleted) Then File.Delete(strFileToBeDeleted)
                        End If
                    Next
                End If
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub


    ''' <summary>
    ''' Functin to check user input
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function fnCheckUserInput() As Boolean
        If m_strSmtpServer = "" Then
            Dim exError As New Exception("SMTP server name is missing")
            Throw exError
        ElseIf m_strSmtpUName = "" Then
            Dim exError As New Exception("SMTP user name is missing")
            Throw exError
        ElseIf m_strSmtpPwd = "" Then
            Dim exError As New Exception("SMTP password is missing")
            Throw exError
        ElseIf m_strMailFrom = "" Then
            Dim exError As New Exception("From address is missing")
            Throw exError
        ElseIf m_strMailTo = "" Then
            Dim exError As New Exception("To address is missing")
            Throw exError
        ElseIf m_strMailSubject = "" Then
            Dim exError As New Exception("Subject is missing")
            Throw exError
        ElseIf m_strMailBody = "" Then
            Dim exError As New Exception("Mail body is missing")
            Throw exError
        End If
        Return True
    End Function

#End Region

End Class

Read More...

How to send email from VB.NET

VB.NET using SMTP protocol for sending email . SMTP stands for Simple Mail Transfer Protocol . VB.NET using System.Net.Mail namespace for send mail . We can instantiate SmtpClient class and assign the Host and Port . The default port using SMTP is 25 , but it may vary different Mail Servers .In the following example shows how to send an email from a Gmail address.

Imports System.Net.Mail

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, _

    ByVal e As System.EventArgs) Handles Button1.Click

        Try

            Dim SmtpServer As New SmtpClient()

            Dim mail As New MailMessage()

            SmtpServer.Credentials = New Net.NetworkCredential("username@gmail.com", "password")

            SmtpServer.Port = 587

            SmtpServer.Host = "smtp.gmail.com"

            mail = New MailMessage()

            mail.From = New MailAddress("YOURusername@gmail.com")

            mail.To.Add("TOADDRESS")

            mail.Subject = "Test Mail"

            mail.Body = "This is for testing SMTP mail from GMAIL"

            SmtpServer.Send(mail)

            MsgBox("mail send")

        Catch ex As Exception

            MsgBox(ex.ToString)

        End Try

    End Sub

End Class

Read More...

How to add Connection string in Config file (.Net)

Step 1:
    Right Click on project->Add->New Item->General
    Select “Application Configuration File” and click on “Add”

Step 2:
    Add bellow section in config file and change DatabaseName,Database,Username,Password according to connect server

</configuration>
    <appSettings>
        <add key="ConnectionString" value="Data Source=Persist Security Info=True;Initial Catalog=DatabaseName;Data Source=DatabasePath;User ID=UserName;Password=Password" /> 
    </appSettings>
</configuration>

Step 3:
    Add reference of System.Configuration.

Step 4:
    Add bellow code where you want to use connection string  

Configuration.ConfigurationManager.AppSettings("ConnectionString").ToString()

Read More...