Retrieval of SQL database system Object details

How to get the names of Tables, Views, Stored Procedures and User Defined Functions of SQL Server database Using SQLDMO Class?

The following code VB.NET code snippet explains how to retrieve the names of tables, views, stored procedures and user defined functions of SQL Server database using SQLDMO class.

Private Sub cmdDisplayObjectDetails_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDisplayObjectDetails.Click

'Make the Database connection

Dim strSQL As String = ""

strSQL = "Provider=SQLOLEDB.1;data source=(local);user id='test';Password='test';database='TEST'"

Dim Conn As New OleDbConnection(strSQL)

Try

Conn.Open()

Conn.Close()

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Exit Sub

End Try

'Display the System Object Details

RetrieveAllObjects()

End Sub

Public Sub RetrieveAllObjects()

'Declaration of database

Public MYDatabase As SQLDMO.Database2

Dim MyDBServer As New SQLDMO.SQLServer2

'Connect to the Database –Server Name, User Name and Password

MyDBServer.Connect("(local)", "test", "test")

'Populate all the system object details

MyDatabase = Nothing

Try

'Database Name: TEST

MyDatabase = MyDBServer.Databases.Item("TEST")

MyDatabase.DBOption.SelectIntoBulkCopy = True

Catch ex As Exception

MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)

Exit Sub

End Try

'Get Table Details

Dim i As Integer

For i = 1 To MyDatabase.Tables.Count

If Not MyDatabase.Tables.Item(i).SystemObject Then

Console.Writeln(MyDatabase.Tables.Item(i).Name )

End If

Next

'Get View Details

For i = 1 To MyDatabase.Views.Count

If Not MyDatabase.Tables.Item(i).SystemObject Then

Console.Writeln(MyDatabase.Views.Item(i).Name )

End If

Next

'Get Stored procedure Details

For i = 1 To MyDatabase.StoredProcedures.Count

If Not MyDatabase.StoredProcedures.Item(i).SystemObject Then

Console.Writeln(MyDatabase.StoredProcedures.Item(i).Name )

End If

Next

'Get User Defined Function Details

For i = 1 To MyDatabase.UserDefinedFunctions.Count

If Not MyDatabase.UserDefinedFunctions.Item(i).SystemObject Then

Console.Writeln(MyDatabase.UserDefinedFunctions.Item(i).Name )

End If

Next

End Function

Code Explanation:

The connection to the SQL Server is made using OLEDB provider. After establishing the connection, the SQLDMO SQLServer2 and SQLDMO Database2 classes are used to retrieve the details of database.

Restrict .Net Window Forms resizing

How to restrict the user from moving or resizing the .NET Windows
Forms?

There are some situations that we might want to prevent the user from
resizing(Maximizing, minimizing or double clicking the title bar etc) the
VB.Net Windows forms.

For example, we might not want to resize the form when the user double
click on the title bar of the Windows Forms.

This will be accomplished by overriding the color="#008000">WndProc Method.

WndProc Method – A method which handles the
windows messages (Maximizing the window, minimizing the window etc) posted
by the Windows Forms.

Based on the windows messages, we need to write the handler to handle
the messages.

The following VB.NET Code Snippet explains how to prevent

1) Maximization or Restoration of Form (When click on the Title bar of
the Form)

2) Maximize button click event

3) Minimize button click event

4) Moving of Form

5) Resizing the Form (Double click on Title bar of the Window)

Protected Overrides Sub WndProc(ByRef WndMsg As
Message)

'various windows message codes

Const WM_NCLBUTTONDOWN As Integer = 161

Const WM_NCLBUTTONDBLCLK As Int32 = &HA3 'When
User Clicks on the Title Bar

Const WM_SYSCOMMAND As Integer = 274 'Windows
Message

Const HTCAPTION As Integer = 2 'Title bar of
Form

Const SC_MOVE As Integer = 61456 'When the Form is
moved

Const SC_MINIMIZE As System.Int32 =
&HF020& 'When a Form is Minimized

Const SC_MAXIMIZE As System.Int32 =
&HF030& 'When a Form is Maximized

Const SC_RESTORE As System.Int32 = 61728 'When a
Form is Restored

If (WndMsg.Msg = WM_NCLBUTTONDBLCLK) And
(m.WParam.ToInt32() = HTCAPTION) Then 'Handle the Title Bar Click event as
well, this would be either Restore or Maximized

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_MAXIMIZE) Then

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_MINIMIZE) Then

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_MOVE) Then

Return

End If

If (WndMsg.Msg = WM_SYSCOMMAND) And
(WndMsg.WParam.ToInt32() = SC_RESTORE) Then

Return

End If

If (WndMsg.Msg = WM_NCLBUTTONDOWN) And
(WndMsg.WParam.ToInt32() = HTCAPTION) Then

Return

End If

MyBase.WndProc(m)

End Sub

color="#400000">USAGE:

Place the above code snippet in the form’s
Coding area where we want to do the above activities.

Creating a Unit Test Plan to test the .NET application

Once the coding of the application is complete, the next phase is to ensure that it functions according to specification.

Although the compiler detects syntax errors, run-time errors and logical errors might not be revealed without thoroughly testing our program.

Hence there is a need to design an efficient test plan for testing of the component or application.

The Unit Test Plan

Most code contains errors on the first go. Thus, it is normal to  expect the code to have bugs when first written. However, a final application that is full of bugs or does not function according to specification is useless. We create a final version from a bug-laden first draft through testing.

Testing and debugging are separate but related activities. Debugging refers to the actual finding and correcting of code errors, whereas testing is the process by which errors are found. Testing is usually broken down by method. Individual methods are tested with a variety of inputs and execution parameters. This approach is called Unit Testing.

Designing Test Cases

As a minimal starting point when designing test cases, every line of code must be tested. Thus, if there are any decision structures in our method, we will need to define test cases that follow all possible branches of the code. Consider the following method:

Public Sub TestMethod (Byval bVal1 As Boolean, Byval bVal2 As Boolean)

If bVal1 = True Then

MsgBox (“Val1 is true”)

Else

MsgBox (“Val1 is False”)

End If

If bVal2 = True Then

MsgBox (“Val2 is true”)

Else

MsgBox (“Val2 is False”)

End If

End Sub

 

The number of test cases needed to run every line of code in this method is two: one case where bVal1 and bVal2 are true, and one case where bVal1 and bVal2 are false. Depending on the values of the parameters to be provided to the method in the code, the method execution could take paths not convered by the test case – for example, if bVal1 is True but bVal2 is false, the method execution will follow a path that was not convered in the test plan. To test all possible paths in this method requires two additional test cases: one where bVal 1 is True but bVal2 is false, and one where bVal1 is false but bVal2 is true. Therefore, at minimum our design for a unit test should include testing of all possible data paths.

Testing Data:

Testing the functionality of all possible data points is a good plan, but to make your application robust, you should also test whether it can handle different kinds of data. We want our application to behave normally and give expected results when data within normal parameters is provided, and it should gracefully and appropriately handle data that is outside of the specified bounds as well. Thus, to be thorough, we must test our application with a variety of data inputs ranging from normal to extraordinary. Specific types of data conditions used to create test cases are described as follows:

Normal Data:

It is important to test data that is within the normal bounds of program execution. Although it is usually impossible to test the entire range of normal data, your test cases should contain several examples of data that is normal for the program to process. This data should span the normal range of operation and should include the normal minimum and maximum values.

Boundary conditions:

Special considerations should be given to testing data on the boundaries of normal conditions. This includes the normal minimum and maximum values for program data, as well as values that are “off by one”. For example, to test the maximum boundary, we would include the maximum value, the maximum value minus one, and the maximum value plus one. This approach allows you to correct simple mistakes, such as using a > operator where a >= is required.

Example:

If e.RowIndex >= 0 Then

MsgBox DataGridView1(e.RowIndex).Cells(0).Value

End If

Bad Data

Using a variety of bad data in our test cases evaluates whether our program will crash in response to bad data, or, worse, function normally and return inappropriate results. We should test values that are well outside of the normal scope of operation, including zero for non-zero values, negative numbers for positive data, and so on.

Data Combinations

Our test plan should include a variety of combinations of the types of data. Errors might not be revealed until the correct data combination is used. For example, we might have a method that functions fine when each of its parameters is at its normal maximum, but fails when all of its parameters are at normal maximum. Similarly, combinations of known bad data can yield normal looking results. Consider the following method:

 

Public Function CalculateMySalary (ByVal Hours As Short, ByVal Weeks As Short) As Integer

Dim mySalary As Integer

mySalary = Hours * Weeks * WageConstant

End Function

 

Given that a normal range for Hours might be 0 to 40, and a normal range for weeks might be 0 to 52, this method functions normally with nominal input. However consider the following method call:

Dim result As Integer

Result = CalculateMySalary (-35, -50)

A combination of bad data returns a seemingly normal result. For this reason, our test cases should test a variety of possible data combinations.

To sum up the to do activities for an unit test plan

1. Begin by creating test cases that execute every line in the test unit

2. Add additional test cases until every possible path of data flow through the unit has been tested.

3. Add further cases to test variance in the data the unit will process. In addition to testing nominal data, we should test boundary conditions, bad data and different combinations of good and bad data

4. Determine the expected result of each test case in advance of the actual test.

5. Execute the tests and compare observed results with expected results.

String Functions in .NET

String manipulations are keenly important for many applications – converting strings to display formats, for instance, or extracting substrings from existing strings.

The String class exposes a variety of member methods for the manipulations of strings.

Some useful string methods in .NET Framework are:

Name                    Description

String.Insert             Inserts a specified string into the current instance

String.PadLeft          Adds characters to the left of the string

String.PadRight        Adds characters to the right of the string

String.Remove          Deletes a specified number of characters from the string, beginning at a specified character

String.Replace          Replaces all occurrences of a specified character in the string with another specified character

String.Split               Returns the array of substrings that are delimited by a specified character

String.Substring        Returns a substring from the specified instance

String.ToCharArray    Returns an array of the characters that make up the string

String.ToLower          Returns the string converted to all lower case

String.ToUpper          Returns the string converted to all upper case

String.TrimEnd          Removes trailing spaces from the string

String.TrimStart         Removes leading spaces from the string

String.Trim                Removes both trailing and leading spaces from the string

String.Compare         Compares two specified string objects

String.Concat            Returns a string that is a result of the concatenation of two or more strings

String.Format            Returns a string that has been formatted according to a specified format

String.Join                Returns a string that is the result of the concatenation of a specified array of strings, with a specified   separation string between each member of the array

Dynamically align the Form in a screen based on users screen resolution

 

The following VB.NET Code Snippet is used to dynamically align the form to the center of the window, based on the the end users screen resolution.

Sub Center_Form()

Dim maxwidth As Integer = Screen.PrimaryScreen.WorkingArea.Size.Width

Dim left As Integer

Dim top As Integer

'Remove 10% top part of window

Dim maxheight As Integer = Screen.PrimaryScreen.WorkingArea.Size.Height - Screen.PrimaryScreen.WorkingArea.Size.Height * 0.1

left = (maxwidth - Me.Width) / 2

top = ((maxheight - Me.Height) / 2)

Me.Location = New Point(left, top)

End Sub

Call the function Center_Form in the Form_Load event of the form.

Private Sub DataForm1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

‘Center display

Center_Form()

End Sub

The following VB.NET Code Snippet is used to maximize the form.

Private Sub Maximize()

Me.WindowState = FormWindowState.Maximized

End Sub

The following VB.NET Code Snippet is used to minimize the form.

Private Sub Minimize()

<B>Me.WindowState = FormWindowState.Minimized</B>

End Sub

Import Flat Files into DataSet

In many legacy applications, data is stored in text files, sometimes called flat files. It might be necessary to read data from these files into an ADO.NET Dataset

The System.IO Namespace facilitates file access, and the methods exposed by the System.String class enable parsing the data contained in text files.

When reading data from a text file, you must first determine how the data is stored. Data stored in text files is usually separated by a common character called a delimiter. A delimiter can be a comma(,), a colon(:), a semicolon(;), or any other character. Rows of data are often stored as lines of text with a carriage return signifying the beginning of a new row. The number of entries in each row should correspond to the number of columns in the table.

Once the basic structure of the stored data has been determined, you can begin to construct your Dataset around it. You must create a single DataTable for each table of data you want to represent. Next, you should add the appropriate number of columns to your DataSet. Sometimes, the data in the file’s first row is used for the column names. If this is the case, you should read the first line of the text file and name the columns programmatically.

A text file can be read with the System.IO.StreamReader class. This class can open a file and return the characters represented within it. Once the file has been opened, you can use the method of the System.String class to separate the data entries and add them to the new data rows.

Because every flat file conceivably could have a different format, you must determine the correct procedure for accessing the data on an individual basis. The following code example demonstrates how to access data from a simple, common scenario: data stored in a text file where the rows are represented by lines and the entries are delimited by commas, a common format known as comma-Separated values (CSV).

Imports System.IO

‘Creates a new dataset

Dim myDataSet As New DataSet()

‘Creates a new datatable and adds it to the Tables collection

Dim aTable As New DataTable(“MyTable”)

‘Creates and names seven columns and adds them to MyTable

Dim Counter As Integer

Dim aColumn As DataColumn

For Counter = 0 to 5

aColumn = new DataColumn(“Column “ & Counter.ToString())

myDataSet.Tables(“MyTable”).Columns.Add(aColumn)

Next

‘Creates the StreamReader to read the file and a string variable to hold the output of the StreamReader

Dim myReader As New System.IO.StreamReader(“C:\Viji\MyTestFile.txt”)

Dim myString As String

‘Checks to see if the reader has reached the end of the stream

While myreder.Peek <> -1

‘Reads a line of data from the text file

myString = myReader.ReadLine

‘Uses the String.Split method to create an array of strings that represents each entry in the line.

myDataSet.Tables(“MyTable”).Rows.Add(myString.Split(“,”c))

End While

Extender Provider Components in .NET Framework

 

Extender providers are components that impart additional properties to controls.

Take the ToolTipProvider for example.

When you place an instance of a ToolTipProvider on a form, every control on that form receives a new property. This property can be viewed an set in the Properties window, where it appears as ToolTip on MyTooltip, where MyTooltip is the name of the ToolTipProvider.At run time, the value of this property is displayed in a yellow box when the mouse hovers over a control.

Extender providers are usually used to provide information to the users at run time. As we have seen, the ToolTipProvider can be used to provide Tool Tips at run time.

Other extender providers include the

 

a) HelpProvider

b) ErrorProvider

 

To use an extender provider in your project

1. Add a component of the appropriate extender type (such as ErrorProvider) to your form. The component appears in the component tray.

2. In the Properties window, set appropriate values for the properties provided by the extender provider.

The properties provided by extender providers actually reside in the extender providers themselves, not within the controls they extend. Thus they are not true properties of the component and cannot be accessed in code at run time. The extender provider implements methods that can be used to access the properties it provides. By convention, these methods are always called Getprop and Setprop where prop is the name of the property provided.

Thus the ToolTipProvider implements methods named GetToolTip and SetTooltip, which can be used in code to access or dynamically change the value of the Tool Tip stored for a particular control. Both methods take a reference to the appropriate control as an argument, and theSet methods require a value to which the property is to be set.

To access the value of an extender property at run time

Use the Get method implemented for that property. You must supply a reference to the appropriate control.

Example:

Dim myToolTip As String

myToolTip = ToolTip1.GetToolTip(btnData)

To set the value of an extender property at run time

Use the Set method implemented for that property. You must supply a reference to the appropriate control and a new value for that property.

Example:

ToolTip1.SetToolTip(btnData, “Click me to display the data”

.NET Interview Questions - Part 9

After two weeks am back to blogging, to start of let me start the Part 9 of the .net Interview question.

1. Briefly describe the major components of the .NET Framework and describe what each component does. The .NET Framework consists of two primary parts: the Common language runtime, which manages application execution, enforces type safety, and manages memory reclamation, and the .NET base class library, which consists of thousands of predeveloped classes that can be used to build applications.

2. Briefly explains what is meant by a reference type and a value type.

A value type holds all of the data represented by the variable within the variable itself. A reference type contains a reference to a memory address that holds the data instead of the actual data itself.

3. How do you enable your application to use .NET base class library members without referencing their fully qualified names?

Use the Imports keyword (VB.NET) or the Using keyword(Visual c#) to make a .NET framework namespace visible to your application.

4. Briefly describe how the garbage collection works.

The garbage collector is a thread that runs in the background of managed .NET applications. It constantly traces the reference tree and attempts to find objects that are no longer referenced. When a non referenced object is found, its memory is reclaimed for later use.

5. Briefly describe what members are, and list the four types of members.

 Members are the part s of a class or a structure that hold data or implement functionality. The primary member types are fields, properties, methods and events.

6. Explain what constructors and destructors are and describe what they are used for.

The constructor is the method that initializes a class or structure and is run when a type is first instantiated. It is used to set default values and perform other tasks required by the class. A destructor is the method that is run as the object is being reclaimed by garbage collection. It contains any code that is required for cleanup of the object

7. Do you need to instantiate a class before accessing a shared (static) member> Why or Why not?

Because a Shared (static) member belongs to the type rather than to any instance of the type, you can access the member without first creating an instance of the type.

8. Briefly describe how a class is similar to a structure. How are they different?

Both classes and structures can have members such as methods, properties and fields both use a constructor for initialization, and both inherit from System.Object. Both classes and structures can be used to model real-world objects. Classes are reference types, and the memory that holds class instances is allocated on the heap. Structures are value types, and the memory that holds structure instances is allocated on the stack

9. You are writing an application that needs to display a common set of controls on several different forms. What is the fastest way to approach the problem?

Create a Single form that incorporates the common controls, and use visual inheritance to create derived forms

10. What is an extender provider, and what does one do?

Extender providers are components that provide additional properties to controls on a form. Examples include the ErrorProvider, HelpProvider and ToolTip components. They can be used to provide additional information about particular controls to the user or the user in the user interface.

Best forum supporter for the week - 5th July to 11th July, 2009

Following my contribution at dotnetspider, i was awarded the winner of the week award

http://www.dotnetspider.com/forum/214520-Best-forum-supporter-for-week--th-July-th-July.aspx

Hello DNS'ians,

We are glad to announce, the Best Forum Supporter Award for the week 5th July,09 to 11th July, 2009 which goes to the following member.


1. VijayaLakshmi Rajkumar


Vijayalakshmi is a Diamond Member who joined DNS recently in the month of June, 2009, but she has contributed a lot of resources to DNs and has actively participated in the forums, and provided very good answers in the forum. She has contributed actively in the forum and provided very good answers to the questions.

See the sample thread where she clearly explains about Tableless Web Design with an example of how to display tableless html code. Here are some more threads where he has provided very good answers.

Sample Thread1
Sample Thread2
Sample Thread3

Vijaya Lakshmi has provided lots of answer to the questions asked by different members.

These are only a few answers.

I invite all the members to join us and Congratulate VijayaLakshmi Rajkumar on this

great achievement.

We are awarding her with Cash credit of Rs.100 and a

Certificate of appreciation.

The winner is requested to send her shipping address and her contact numbers to send the

certificates.

You can catch the other Winners list at :-->>
href='http://www.dotnetspider.com/credits'>List of DNS Award Winners




Raghav
Web Master

What is SQL Injection

 

SQL Injection is an attack of non-valid inputs passed through web application for execution by a backend database , simply It is a trick to inject data to SQL query/command as an input possibly via web pages.

Best example for this, when a user login the web page that user name and password and make SQL query to the database to check if a user has valid name and password. With SQL Injection, it is possible for us to send crafted user name and/or password field that will change the SQL query and grant us.

SQL Query :

sqlquery = “ SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME = ‘ “ + strusername + “ ’ AND PASSWORD = ‘“ + strpwd + “ ’ ”;

sqlqueryresult = GetQueryresult(sqlquery);

if (sqlqueryresult = string.empty)

{

Response.write(“User login failed”);

}

Else

{

Response.redirect(“home.aspx”);

}

User passes ‘VIJI’ and ‘PASS’  as username and password respectively. If the user is a valid by executing the above SQL command, web page redirect to home page.

Look here, if user passes the below inputs Strusername as ‘ OR ‘ ‘ = ‘ and Strpwd as ‘ OR ‘ ‘ = ‘ then dynamic query will be

“ SELECT USERNAME FROM USERLOGINTABLE WHERE USERNAME = ‘ OR ‘ ‘ = ‘ AND PASSWORD = ‘ OR ‘ ‘ = ‘ “

Few judgment of this query:

• There is no syntax error
• There is no conflict between the operators.
• Inputs are not valid.

Web application will redirect the home page even input are invalid because result of the query will be true. The query compares the first single quotation and another quotation (means nothing) then OR is an operator. When comparing nothing to =, it returns true. Same execution is applied for password. These kind inputs are called vulnerable inputs to SQL commands.

Disadvantages

SQL injection provides a facility to the net hackers to pull the data from the backend database by supplying the vulnerable inputs.

Will Continue writing on the

Attacks of the SQL Injection

• Select Command.
• Insert Command.
• Using SQL Stored Procedures.

and how to prevent SQL injection in the upcoming articles.

SQL Editor Shortcut Keys

SQL Editor is a code editor and explorer for SQL Server databases.

The following is a list of Shortcut keys used in the SQL Editor.

Shortcut Key	Function
F1	Windows Help File
F2	Toggle Full screen Editor
F3	Find Next Occurrence
<SHIFT> <F3>	Find Previous Occurrence
F4	Describe Table, View, Procedure, Function, or Package in popup window
F5	Execute as Script
F6	Toggle between SQL Editor and Results panel
F7	Clear All Text
F8	Recall previous SQL statement
F9	Execute statement
<CTRL> F9	Verify statement without execution (parse)
<SHIFT> F9	Execute current statement at cursor
F10	Popup Menu
<CTRL> A	Select All Text
<CTRL> C	Copy
<CTRL> E	Execute Explain Plan on the Current Statement
<CTRL> F	Find Text
<CTRL> G	Goto Line
<CTRL> L	Converts Text to Lowercase
<CTRL> M	Make Code Statement
<CTRL> N	Recall Named SQL Statement
<CTRL> O	Opens a Text File
<CTRL> P	Strip Code Statement
<CTRL> R	Find and Replace
<CTRL> S	Save File
<SHIFT> <CTRL> S	Save File As
<CTRL> T	Columns Dropdown
<CTRL> U	Converts Text to Uppercase
<CTRL> V	Paste
<CTRL> X	Cut
<CTRL> Z	Undo Last Change
<CTRL>.	Display popup list of matching tablenames
<SHIFT> <CTRL> Z	Redo Last Undo
<ALT> <UP>	Display Previous Statement
<ALT> <DOWN>	Display Next Statement (after <ALT> <UP>)
<CTRL><HOME>	In the data grids, goes to the top of the recordset
<CTRL><END>	In the data grids, goes to the end of the recordset
<CTRL><SPACE>	Completely expand dependency tree views
<CTRL><TAB>	Cycles through the collection of MDI Child windows

DATA VALIDATION

Validation is a most important phase in a  data migration project, to ensure the data integrity between the legacy and the target systems.

Below is one case study  I've been involved to help the business with the validation process.

Client: A technology-based provider of power and automation products, systems, solutions, and services in the united States

History:

The Project is an  ERP(SAP) implementation project, and currently in the Go live phases, with 19 ERP system migrating in 6 phases.

Go live 1 through 3,the Data Migration team performed multiple loads into data migration specific SAP clients/systems in an attempt to have the businesses review, validate and cleanse their data. While the upside of this was that the data was in an SAP system for the businesses to review, the down side was that the turn-around time to correct errors and reload the SAP system was too long thus reducing the productivity of the Data Migration team.  Also no one could visually see the data that was to be uploaded.

 

Prior Model of Operation:

Cransoft is the tool used in tjis

To overcome this challenge, the Raj came along with the  data migration team came up with the new strategy called VLVP (Virtual load and validation process) during Go-Live 4. VLVP means that the legacy data, once extracted and transformed into SAP ready load formats, is exported to logical Excel spreadsheet formats and Access databases, also known as SIM (Simulation Sheets).

VLVP allowed the Data Migration team to provide, to the business, snapshots of the actual data to be loaded to SAP without actually loading the SAP system. It allows the data team to respond quicker to data object load specification changes presented by the businesses and shortens the load turn around time drastically.

 

Version1:

Version 1 was a simulated Excel sheets and access databases that were  interacting with the SQL server

Period of release: Go Live 4, Feb 2009

Designers:

1. RAJKUMAR (Excel,Access)
2. Ernie Blazek of  ABB had designed major of large volume data to be show via, Access

Model Of operation

	Pros of this version
1	Validate the data that is to be loaded into SAP , prior itself,
2	Office Tools part of every PC, so new installation requirement
3	Easier methodology to validate whether proper transformations are in place
4	Improved Data Quality
5	Higher Percentage of Success in load

	Cons of this version
1	Occupies huge space and high network on the Cransoft hosted network
2	Detail Reports are refreshed every 30 Min’s, while the Summary report is real time, creating confusion among users
3	Limitation of 65000 record with the Microsoft excel 2003
4	Access Databases - Non user friendly and occupies high volumes Runs as part of Cransoft process, increasing the processing time in Cransoft Takes longer time to view data.
5	Back up of data not possible
6	Numerous of number of process locks in SQL server

The biggest  cons from the User and Developer point of view was that  SIM sheets consisted of various Excel spreadsheets and Access databases that resided on a network drive and the division teams were provided access to them. It became a strenuous process for the data migration team to educate the division teams on how not only to access the network drive; but which SIM sheets applied to their site; what versions of the SIM sheets were relevant; which SIM sheet naming conventions were applicable; whether a SIM sheet for a particular data object was a Excel spreadsheet or an Access database and why that was.

It caused a lot of coordination at times, where the objective of the SIM sheet was to simplify the validation process.

Some of the positive client Feedbacks(With out any Edition)

• Simulation sheets (SIM) worked well with ability to re-load data when needed.
• Continue with the SIM sheets for future sites, definitely see future sites benefitting.
• The SIM helped identify mapping issues before data was loaded to SAP.
• The error log produced by the SIM helped identify areas of legacy data that needed to be cleansed prior to the actual load of data in SAP.
• This was my first Go-Live on the Power Products (PP) OsA team, and I honestly cannot imagine how difficult the cutover must have been for the previous go-lives.
• Without the SIM and error logs, I do not believe we would have been able to begin cutover 3 days earlier than schedule or for the controllers to sign off 2 days ahead of schedule.
• Excellent tool for the cutover teams to identify data issues. Examples - Do purchase parts have planned delivery time?
• Excellent tool to see if cross reference logic is working as expected.
• Excellent tool to come up with remediation rules.
• Excellent tool to check data counts and see any glaring issues.
• Excellent tool to perform analytical analysis of data and provide recommendations to business on fixing data issues.
• Eliminated the unknown of guessing if our changes are going to work or not.

Version 2:

Period of release: Go Live 4.5, May 2009

Though the Sim sheets received  a positive feedback, Raj heeded the constructive criticism and changed the way the SIM sheets are presented to the division site data validators with a new user Interface application to house all SIM sheets that is far simpler, intuitive and easier to use. There is no switching between Excel and Access. The user is free of the tool is behind the user interface

Designed by:

1. RAJKUMAR

Programmed by

1. VIJI
2. RAJKUMAR

Model Of operation

 

 

Points of comparison	excel/Access	DATA VALIDATION TOOL
Occupies huge space and high network on the Cransoft hosted network	Yes	No
Detail Reports are refreshed every 30 Min’s, while the Summary report is real time, creating confusion among users	Yes	Real time
Limitation of 65000 record with the Microsoft excel 2003	Yes	No limitation
Access Databases - Non user friendly and occupies high volumes Runs as part of Cransoft process, increasing the processing time in Cransoft Takes longer time to view data.	Yes	No. Its on vitual
Back up of data	Not possible	Possible

Result:

We are happy to report that the data migration team has not received one criticism of the new simulation sheet (SIM) application; in fact, numerous compliments; The new  SIM sheet application has allowed the users to focus  on validating the data and eliminated distractions in the validation process.

We heard a very  very positive feedback one of the business user saying, this is beyond the obvious.

 

 

Some of the Screen Shots of DATA VALIDATION Tool

The simple Home Page designed to access GO Live 5( 6 ERP systems ) and Go live 6( 3 ERP system) data

 

The list of  ERP sites sites on the left as a tree view

individual objects can  be displayed choosing  the appropriate node, you have options as filter and sort similar to Excel to work around other options are to view the data on a full screen as below

 

And  can export the data in the grid view to txt,csv or xls format. by clicking on the Export File option on the right hand screen or on the file menu. If you are more comfortable with Excel.

 

This is the xls File saved to my desktop

 

The Additional Feature of this tool is the error summary , which reports, the business object and the volume of records that would fail to make through into SAP when it is uploaded prior to even load, and the reason why they would not make through.This makes the tool a bit of artificial intelligence one too…

 

By Clicking on the Error Summary on the Main Menu, you can view the summary of the errors simulated  site wise with count, to view which all records would fail in detail, you can click on the View report.

Our next exploration  would be on  Extraction tool , Raj has the design  in place on the walls, i ve got to figure out a schedule to support him., more on this experience  i will blog once it comes to ready to use state. One thing is for sure, it is going to an interesting assignment. SAP ---> SQL ,JDE ----> SQL,MAPICS---->SQL, MACPAC---> SQL, SYTELINE ---> SQL, .. Oops there is a  lot

Creating Alias name for namespace

The namespaces in .NET can be very long and less obvious.

And to refer the class of the name space we need to include the entire path.

There is a way to simplify this by creating your own alias for the namespace.

How to do?

You can use the Imports statement (VB.NET) or using directive (C#) with an assignment operation.

Syntax:

VB

Imports < AliasName > = < NameSpace >

C#

Using < AliasName > = < NameSpace >


Example - VB


Imports MyaliasName = Microsoft.VisualBasic


Example - C#


Using MyaliasName = Microsoft.VisualBasic;


That way, you can refer to specific class elements within the class referred to using the variable you created in the assignment operation.


'VB

Private Sub TestFunction()

MyaliasName.MsgBox("I am Alias")

End Sub


// C#

void TestFunction()

{

MyaliasName.MsgBox("I am Alias");

}

Database Exception Handling Best Practices

This article would highlight on how to deal with exceptions in our code and the end result would be better code.

1. Validate data prior passing it to DB at client and server.

2. Left validation to DB and handle DB exceptions properly

3. Validate on both sides

4. Validate some obvious constraints in business logic and left complex
validation to DB

5. Strong type checking and validation are powerful tools to prevent unexpected
exceptions. So be ready to always check types, objects value etc before
passing to database.

6. Don't trust external data -External data is not reliable. It must be extensively checked.

7. Generic exception handling should be done in a central point in your
application. Each thread needs a separate try/catch block, or you'll lose
exceptions and you'll have problems hard to understand. When an application
starts several threads to do some background processing, often you create a
class for storing processing results. Don't forget to add a field for storing an
exception that could happen or you won't be able to communicate it to the
main thread. In "fire and forget" situations, you probably will need to duplicate the main application exception handler on the thread handler.

8. Log Exception.ToString(); never log only Exception.Message!

9. Cleanup code should be put in finally blocks

10. Don't use exception handling as means of returning information from a
method
11. Don't clear the stack trace when re-throwing an exception.

12. When in doubt, don't Assert, throw an Exception Don't forget that Debug.Assert is removed from release code. When checking and doing validation, it's often better to throw an Exception than to put an assertion in your code.
Save assertions for unit tests, for internal loop invariants, and for checks that should never fail due to runtime conditions (a very rare situation, if you think about it).

13. Use "using" everywhere -

Simply calling Dispose () on an object is not enough. The "using" keyword will
prevent resource leaks even on the presence of an exception.


14. Avoiding exception handling inside loops

Check if your code uses exceptions inside loops. This should be avoided. If you
need to catch an exception, place the try/catch block outside the loop for better performance.

DataTypes in C#

What is DataType?

A data type in a programming language is a set of data with values having predefined characteristics. It describes how the value will be stored for the type and how it will be processed.


DataTypes in c#:

The C# programming language is a strongly typed language. All variables must be declared with a valid data type.


Types of DataTypes:


There are two types of datatype:

1. Built In data type

2. User Defined data type


Built In data type:

The built in data types are provided by the c# .NET frameword. The data types are reserved keywords and cannot be used as a variable name.

Built in data types supported in c# are:

byte - 0 to 255 characters

sbyte(short byte) - (-128 to 127)

short - 32,768 to 32,767

ushort(unsigned short) - 0 to 65,535

int -2,147,483,648 to 2,147,483,647

uint(unsigned int) 0 to 4,294,967,295

long -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

ulong 0 to 18,446,744,073,709,551,615

float - (-3.402823e38 to 3.402823e38 )

double - ( -1.79769313486232e308 to 1.79769313486232e308 )

decimal ( -79228162514264337593543950335 to 79228162514264337593543950335 )

char - A Unicode character.

string - A string of Unicode characters.

bool - True or False.

object - An object.


User Defined data type


The user defined data type, will be defined by the user.

Example:

Class or Interface

Enumerations - user defined integer type


The data types can further be classified into

a) Value Types

b) Reference Types


Value Types:

The value type variables store the actual data in the variable. Each variable maintains its own copy of data.


Example:

int x = 10;

double y = 10.12;

char z = 's';


The following value types are supported in c#:

* int
* uint
* bool
* char
* decimal
* float
* double
* sbyte
* byte
* char
* short
* ushort
* ulong
* long
* decimal

* structs
* enumerations


Reference Types


Reference types variables store the reference of the actual data.


The following reference types are supported in c#.

* string - represents a string of unicode characters.

* object - Represents a general purpose type. In C#, all predefined and user-defined types inherit from the object type or System.Object class.

* class

* interface

* delegate


Example:

string s = "TestString";

Although string is a reference type, the equality operators (== and !=) are defined to compare the values of string objects, not references.

public delegate void TestDelegate(string message);

The delegate keyword is used to declare a reference type that can be used to encapsulate a named or an anonymous method


Data Type Conversions:

C# supports two types of conversions.

1. Implicit conversions

2. Explicit conversions.


Implicit conversions are direct conversion. It includes method invoking and assignment.


For example:

int iVal = 34;
long lVal = intValue;

Explicit conversions includes type casting. By using a cast expression for conversion.

For example:

long lVal = 123456;
int iVal = (int) lVal;

17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

The error Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’." usually happens when try to login to the system after dropping the database.


Possible Reason

1. Drop the database after creating the trigger without dropping the trigger and try to login.

Let me explain the scenario with script example.

Example:

Consider a database MyTempDB.

[CODE]

use myTempDB
GO

--Create a table Called MytempTable

CREATE TABLE myTempTable
(val1 VARCHAR(100),
val2 VARCHAR(100))

GO

--Create Trigger

CREATE TRIGGER Trg_Temp

ON ALL SERVER FOR LOGON

AS

BEGIN

INSERT INTO myTempDB.dbo.myTempTable ('XXX','xxx')

END
GO

[/CODE]

Dropping of myTempDB database


[CODE]

---Dropping of myTempDB database

USE master
GO
DROP DATABASE myTempDB --will throw Logon failed for login due to trigger execution

GO

[/CODE]


After executing the above code, we will get the error

Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’."

and we cannot login to the database.


Possible Solutions

1. Must use drop trigger before dropping the database

Example:

[CODE]

---Dropping of myTempDB database

USE master
GO

--Drop thhe Trigger

DROP TRIGGER Trg_Temp ON ALL SERVER

--Drop the database
DROP DATABASE myTempDB

GO

[/CODE]

2. If we don't give the Drop Trigger, we cannot login to the sql server database.

3. The fix is to use DAC (Using a Dedicated Administrator Connection to Kill Currently Running Query.)

4. Using windows authentication do the following

a. Connect to sql server using windows authentication

b. Goto command prompt

c. Type the following

C:\Documents and Settings\Manager> sqlcmd -S LocalHost -d master -A

DROP TRIGGER Trg_Temp ON ALL SERVER
GO

The -d option will enable us to directly login to the database

ASP.NET Interview Questions Part 2

Interview Questions ASP.NET

1. Describe the difference between inline and code behind.?

Inline code written along side the html in a page. Code-behind is code written in a separate file and referenced by the .aspx page.

2. Explain what a diffgram is, and a good use for one?

The DiffGram is one of the two XML formats that you can use to render DataSet object contents to XML. For reading database data to an XML file to be sent to a Web Service.


3. Whats MSIL, and why should my developers need an appreciation of it if at all?

MSIL is the Microsoft Intermediate Language. All .NET compatible languages will get converted to MSIL.

4. Which method do you invoke on the DataAdapter control to load your generated dataset with data?

The .Fill() method

5. Can you edit data in the Repeater control?

No, it just reads the information from its data source

6. Which template must you provide, in order to display data in a Repeater control?

ItemTemplate

7. How can you provide an alternating color scheme in a Repeater control?

Use the AlternatingItemTemplate

8. What property must you set, and what method must you call in your code, in order to bind the data from some data source to the Repeater control?

You must set the DataSource property and call the DataBind method.

9. What base class do all Web Forms inherit from?

The Page class.

10. Name two properties common in every validation control?

ControlToValidate property and Text property.

11. What tags do you need to add within the asp:datagrid tags to bind columns manually?

Set AutoGenerateColumns Property to false on the datagrid tag

12. What tag do you use to add a hyperlink column to the DataGrid?



13. What is the transport protocol you use to call a Web service?

SOAP is the preferred protocol.

14. True or False: A Web service can only be written in .NET?

False

15. What does WSDL stand for?

(Web Services Description Language)

16. Which property on a Combo Box do you set with a column name, prior to setting the DataSource, to display data in the combo box?

DataTextField property

17. Which control would you use if you needed to make sure the values in two different controls matched?

CompareValidator Control

18. True or False: To test a Web service you must create a windows application or Web application to consume this service?

False, the webservice comes with a test page and it provides HTTP-GET method to test.

19. How many classes can a single .NET DLL contain?

It can contain many classes.

'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral

The error 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed generally occurs when try to run the stored procedure.

This error often occurs when try to run the Stored Procedure from the web application.

Possible Reason

1. Not enough security permission for the stored Procedure

2. Anything that access database from SP requires at least the
WSS_Medium security policy in the web.config file.

3. If the security message is from the web part, it's usually the trust element in the web.config file.


How to Fix?

1. Open wss_mediumtrust.config & wss_minimaltrust.config files

2. The normal path is (C:\Program Files\Common Files\Microsoft Shared\Web Server
Extensions\12\config\)

3. We can find the exact path in web.config file


4. Find in the wss_mediumtrust.config File:





5. Copy and paste it in to the node of
wss_minimaltrust.config file.


6. In the PermissionSet section of this configuration file, add the
following:

Find in wss_mediumtrust.config:

Copy and paste it in to the a node of
wss_minimaltrust.config.


7. Goto Control Panel - Administrative Tools

8. Goto .NET Framework configuration settings

9.a) Expand the runtime security policy.

b) expand machine

c) expand code groups

d) right click all_code

e) Click on properties

f) Click on permission tab

g) Set Modified nothing to Full Trust

SQL Error: Disallowed implicit conversion from data type varchar to data type money

The Error Disallowed implicit conversion from data type varchar to data type money usually occurs when try to assign String Value to Money Data Type of Sql Server.


Possible Reasons:


1. Assign String(Varchar) Value to Sql Server Column value which is of Money data type.

2. Especially in Insert and Update Queries

Assume Account Table in Sql Server contains a Field Amount which is of Type "Money".


Example1:

Insert Query

strSQL = "Insert INTO AccountTable(Amount) Values (" & txtamount.text & ")"

MyConn.Execute(strSQL)


Example2:

Update Query

strSQL = "UPDATE AccountTable SET Amount = " & txtamount.text

MyConn.Execute(strSQL)



Solutions:


Need to Convert the Value of Varchar Type or String Type to Money.


How to convert Varchar to Money Type?

Need to Use CONVERT Function and Single Quotes.

Modified Query:

strSQL = "Insert INTO AccountTable(Amount) Values (CONVERT(money,'" & txtamount.text & "'))"

strSQL = "UPDATE AccountTable SET Amount = (CONVERT(money,'" & txtamount.text & "'))"


Important:

(Note the single-quote just after the word money, and the single-quote and end paren just prior to the final comma.)

Invalid length for a Base-64 char array

The error Invalid length for a Base-64 char array usually occurs when calling the function

Convert.FromBase64String() to convert the given string bytes array.


Possible reasons

1. The Length of the String is not a multiple of 4.

2. The String may contain ignorable characters such as White Space Characters.

Thus the invalid characters in the string are getting ignored. Thus the length of the string is not a multiple of 4.


Possible Solutions

1. Make Sure that the Length of a string is a multiple of 4.

2. Use the Modulus Operator (%) to check whether the length of the String % 4 is equal to Zero.

Example:


If (Str.Trim.Length % 4 == 0 ) Then

bitmapdate = Convert.FromBase64String(str_lbl)

End If



3. Replace the Ignorable characters with valid characters.


4. For example Replace White space characters in the character array with "+" Sign.




str = str.Replace(" ","+")

If (Str.Trim.Length % 4 == 0 ) Then

bitmapdate = Convert.FromBase64String(str_lbl)

End If

Ways to trigger windows form Control's event

In this article, we can see the various ways of triggering the Control's Event.

Some times we may want to perform the same functionality as a button control does, from another control, say another button or Checkbox etc.

In such scenarios, it will be useful to trigger the event handler from other controls.

There are Three ways to trigger Conrols Event


1. Perform the event by the Assigned Control itself.

2. Call the Event Handler by passing Null as parameters For both Sender and Args Parameter.

3. Calling the Event Handler by passing Control Name as parameters For Sender and Null For Args Parameter.


To illustrate this, let us consider the following Form and Controls.

a) A Form, and name it as Myform.

b) a button and name it as Button1

c) a button and name it as Button2

d) a ComboBox and name it as ComboBox1


and we will see how the Button1's Click Event Handler is going to be handled by all other controls and Button1.



Public Sub New()

'Assigning text property of the controls

me.Button1.Text = "I am Button 1"

me.Button2.Text = "I am Button 2"

me.ComboBox1.Items.Add ("1")
me.ComboBox1.Items.Add ("2")
me.ComboBox1.Items.Add ("3")


End Sub



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click

Dim myComboBox As ComboBox
Dim myButton As Button

'code to perform action when Button is clicked

If Sender Is Nothing Then

MessageBox.Show (Button1.Text)

End If

If TypeOf sender Is Button Then

myButton = CType(sender, Button)

'Just to display the text of the control as an illustration

MessageBox.Show (myButton.Text)

ElseIf TypeOf sender Is ComboBox Then

myComboBox = CType(Ctrl, ComboBox)

MessageBox.Show (myComboBox.SelectedItem.ToString)

End If


End Sub





Perform the event by the Assigned Control itself.(Button1)

In this case,

a. we can just click on the Button1. The Event handler will be invoked.

The Text "I am Button1" will be displayed

b. Call Button1's PerformClick() Method

Button1.PerformClick()

It will also invoke Button1's Event handler


passing Null as parameters For both Sender and Args Parameter.


1. Triggering the Button1's Click Event using Button2's Click

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click

'Calling Button1's Click Event by passing Sender as Button2 And
'Args As Nothing

Button1_Click (sender, Nothing)

End Sub


2. Triggering the Button1's Click Event using ComboBox1's selectedIndexChanged

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

Button1_Click (sender, Nothing)

End Sub



Calling the Event Handler by passing Control Name as parameters For Sender and Null For Args Parameter.


1. Triggering the Button1's Click Event using Button2's Click

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As_
System.EventArgs) Handles Button1.Click

'Calling Button1's Click Event by passing Sender And
'Args As Nothing

Button1_Click (Nothing, Nothing)

End Sub


2. Triggering the Button1's Click Event using ComboBox1's selectedIndexChanged

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

'Calling Button1's Click Event by passing Sender And
'Args As Nothing

Button1_Click (Nothing, Nothing)


End Sub

Find the Operating System Version

The System.Environment.OSVersion static (shared) property is used to find the operating system version of the system.

Prerequisites

Import System.Environment in the top of the Windows form.

Example


Imports System.Environment

Public Class Form1

Private Sub DisplayOSVersion

Dim os As OperatingSystem = Environment.OSVersion

MessageBox.Show("OS Version:" & os.Version.ToString())

MessageBox.Show("OS platform:" & os.Platform.ToString())

End Sub

End Class

Error While unpacking program, code LP5. please report to author

Generallt the Error Error While unpacking program, code LP5. please report to author comes wen booting the system or trying to download using some softwares.

Protected programs are packed to reduce space and prevent hacking.

And unpacked are unpacked each time we are running.

But the incorrect downloads prevents unpacking of the software properly.


Possible Reasons:


1. LP5 means the package is corrupted. This can happen mainly because of some bad download or obsolete package.

2. Obsolete Antivirus

3. Improper Download Software

4. Flash drive

5. Spyware

6. interference from other applications running on the computer

7. During installation of security sites


Possible Solutions:


1. Check For the version of Anti virus

2. Goto control panel /Add Remove Programs/

3. In the list of installed programs we can see the antivirus version.

4. If it is obsolete, uninstall it and reinstall it with the latest one

5. Check For any 3rd party tool that may interfere with the unpacking of software

6. If so un install it.

7. Run Restore disks (or built-in System Restore). It will reinstall the software automatically.

8. Instead of flash drive, use any download manager

Find the executable path of current application

There are two ways to find the path the current running application.


1. Using Application.ExecutablePath

2. System.Reflection.Assembly.GetExecutingAssembly()

Prerequisties:

Import the System.Reflection name space in the top of the windows Form

Example:

[CODE]

Imports System.Reflection

Public Class Form1

....

Private Sub FindPathOfExe()

Dim appPath as String

'Method 1

appPath = Application.ExecutablePath

Debug.Print "Application path: " & appPath

'Method 2
appPath = System.Reflection.Assembly.GetExecutingAssembly().Location


Debug.Print "Application path: " & appPath


End Sub

End Class

[/CODE]

Difference between UNION and UNION ALL in SQL

In this article we will see the difference between UNION and UNION ALL Sql Keywords.

Syntax:

[SQL Statement 1]


UNION [ALL]


[SQL Statement 2]

Where ALL statement is optional.

Both the Sql statements must consist of equal number of fields.


Similarties:


The UNION and UNION ALL Sql Keywords are used to combine two or more sql queries and return the result set consisting of a single set of all queries mentioned in there.


Differences:

The main difference between UNION and UNION ALL is that UNION retrieves distinct values (With out duplicates) where as UNION ALL retrieves all values from the result set including duplicates.


Example

Consider Employees Table of NorthWind database.


UNION ALL

The following query is used to display the EmployeeId and lastName.

SELECT EmployeeID, lastName
FROM [dbo].[Employees]

UNION ALL

SELECT EmployeeID, lastName
FROM [dbo].Employees


RESULT


EmployeeId LastName

5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth
5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth

The repetition of same records twice. Thus duplicate records exists in this result set.

UNION

The following query is used to display the EmployeeId and lastName.

SELECT EmployeeID, lastName
FROM [dbo].[Employees]

UNION

SELECT EmployeeID, lastName
FROM [dbo].Employees


RESULT

EmployeeId LastName

5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth


Here only distinct values are returned. Thus no duplicate records exists in this result set.


Same artcicle available underhttp://www.dotnetspider.com/resources/29704-Difference-between-UNION-UNION-ALL-SQL.aspx

Script to drop all stored procedures from the database

The following SQl stored procedure dropAllSpproc is used to drop all stored procedures from the database.

We need to mention the schema name in the stored procedure.

/*The procedure declares the cursor to iterate through all SP List*/

/*This procedure retrieves SP Name from sysobjects based on type 'p'

/*The type 'p' indicates - it is a stored procedure */





CREATE PROCEDURE [dbo].[dropAllSpProc] AS



--Declaration of Cursor type

DECLARE spDropCursor1 cursor for




--Retrieve the names of Stored Procedures




SELECT name

FROM sysobjects

WHERE type = 'P' -- 'P' indicates Stored Procedure



--Open the spDropCursor

OPEN spDropCursor1



--Declaraion of Variable to store Stored procedure name



DECLARE @spName varchar(100)

DECLARE @sqlStr varchar(100)



--Fetch the first name into SPName variable


FETCH NEXT FROM spDropCursor1 INTO @spname



--Start the Loop

WHILE @@fetch_status = 0


begin


SET @sqlStr = 'drop procedure ' + @spname


--Execute the drop Statement


EXEC @sqlStr



--Start Fetching other stored procedure Name

fetch next from spDropCursor1 into @spname



end



--Clean Up

close spDropCursor1



deallocate spDropCursor1



Go