Auto increment Column in Oracle

To day I will show you how to create Auto increment column in oracle which may be use as Primary Key .

In My Sql and Sql Server Data bases it very easy to create auto increment column but in oracle you must have to follow some step to Create an Auto increment column.

Read More

Log/Audit Table in Oracle and SQL SERVER

Today i will explain you Why we  create Log/Audit table, How it help us in various situations and How to create Log table and audit table.

In this tutorial we will Learn  How to create Trigger  in Oracle and Sql server.

In this article i uses both data base Oracle and SQL Server.

By creating log tables, data base automatically logging of changes to several of our database tables.

Read More

Get Client IP Address , Host name and Current User in Oracle and SQL Server

Today I show how to get Client IP Address, Host name and Current Data base user in Sql Server and Oracle.

It very Use full security purposes and in case of any illegal or unauthorized activity is perform on your Database so you can Track and Trace how, when  and who is performed that  illegal or unauthorized activity.

Read More

SQL Server 2008:How To Create User Defined Functions in SQL

To day i will Show you how to Create User defined Function SQL Server and how to Use it.
I've already shared  how create Store Procedures and How to Get a Return Value From Store Procedures in SQL Server.
Store Procedure ,User Define Function and User Define or User Created Views are Always Saved in the Data Base (MDF file). They are all Fast and Pre-Compiled which means we call these Functions or Store Procedures  From Our business logic layer or (Simply from Coding ) where they are Directly Executed and thus which Saves time.
Difference of time saving is noticible in enviorments where you have 25-30 tables and Each table has 1,000-50,000 rows, in these Situations we Feel the Difference between In-Line Queries and Pre-Compiled SQL Statements(Store Procedure ,User Define Function and User Define or Created Views  etc).

Read More

Generate Data Base Script in Sql Server

 Today I will show you how to Generate Full Data Base Script in SQL Server 2008 or 2005.
By Generating SQL Data Base script basically we copy complete database schema and all of its objects such as Stored Procedures, Functions, Triggers, Tables, Views, Constraints etc. You can review your schema, backup for reference or use it to compare with previous backup.
SQL Server Management Studio provides an efficient  wizard to generate scripts to transfer objects along with their dependencies as well as the data.
In this tutorial, let's firstly generate a migration script for the following objects from the AdventureWorks database:

Read More

Free Download SQL assistant Full Version

Now you can Download Free SQL ASSISTANT Full Version or Crack Version.
Let Me Introduce What is SQL ASSISTANCE?
 It is an intellisense tool for Data Base Developer ,Data Designer and DBAs to increase Coding,query and Sql Programming
  accuracy ,Productivity and improve code quality.
It support and Provide Facility for  code snippets, automatic word completion, and attribute browsing into the SQL query or coding window.
 It help a lot you to in writing long or complex store procedures through its automatic word completion facility.

Read More

How to Make Back and Restore Data base

To day I show You how To

HOW TO MAKE DATA BASE BACKUP AND HOW TO RESTORE IT IN SQL SERVER Management Studio.

Normally Data back can be Done very easily but there is some Confusion in restoring the data base.
In previous post I show you how to connect Manage studio with Visual studio .

Following Step is Involve to take Back up of data base

First, you need to configure the Microsoft SQL Server Management Studio on your local machine. If you don’t have it, you can download it from the following location.

LINK

Step 1: Open your Microsoft SQL Server Management Studio, whichever you prefer, standard or express edition.

Step 2: Using your Database Username and Password, simply login to your MS SQL server database.

Step 3: Select the database >> Right-click >> Tasks >> Back Up [as shown in the image below]:

Once you click on the “Backup” the following Backup Database window will appear [as shown in the image below]:

Step 4: Select the following options:

1. Backup type: Full
2. Under Destination, Backup to: Disk

Step 5: Now, by clicking on the “Add” button the following window will appear to select the path and file name for the database backup file [as shown in the image below]:

Step 6: Select the destination folder for the backup file and enter the “File name” with .bak extension [as shown in the image below]:

Make sure you place your MS SQL database .bak file under the MSSQL backup folder.

Step 7: Hit the OK button to finish the backup of your MS SQL Server 2008 Database. Upon the successful completion of database backup, the following confirmation window will appear with a message “The backup of database “yourdatabasename” completed successfully. [as shown in the image below]:

Following the above shown steps, you will be able to create a successful backup of your MS SQL Server 2008 Database into the desired folder.

How to Restore MS SQL Server 2008 Database Backup File ?

In order to restore a database from a backup file, follow the steps shown below:

Step 1: Open your Microsoft SQL Server Management Studio Express and connect to your database.

Step 2: Select the database >> Right-click >> Tasks >> Restore >> Database [as shown in the image below]:

Step 3:  The following “Restore Database“ windows will appear. Select “From device” mentioned under the “Source for restore” and click the button infront of that to specify the file location [as shown in the image below]:

Step 4: Select the option “Backup media as File” and click on the Add button to add the backup file location [as shown in the image below]:

Step 5: Select the backup file you wish to restore and Hit the OK button [as shown in the image below]:

That’s it! You will get the confirmation windows with a message “The restoration of database “yourdatabasename” completed successfully.” Now you know the procedure of backing up and restoring MS SQL Server 2008 Database.

VIDEO

Read More

Connect Management Studio Data Base from Server Explorer of Visual Studio

To day I show You how To

connect SQL Management Studio from Server Explorer of Visual studio.

I am always very Much Confuse to Connect Local DB in Visual Studio 2010 from Sql Management studio.

But Now I search Alot and clear My Concepts and I also Want to Show these Concepts to you So can Also understand .

STEPS
First You Have to Open SQL Management Studio

 












It should be look like this

Now Open Visual Studio And Create New Project (Asp.net Site or Windows Form Application)

Open Server Explorer Window

• • Server Explorer is known as Database Explorer in Visual Studio Express
  • MSDN How to: Connect to a Database from Server Explorer
• Visual Studio 2010 comes with Server Explorer - which allows you to connect to a remote SQL Server database.
• View → Server Explorer → click the Connect to Database button on the top of the Server Explorer panel 
• From the View menu, choose Server Explorer.
• In Server Explorer right-click the Data Connections node and choose Add Connection.
• 
• Use the following godaddy connect parameters
  • 
• click the Test Connection button
• 

So Here We Successfully Connect to Management Studio data  base .
Please Watch the video

Read More

How TO Get return value from stored procedure

Recently we changed our DAC layer from using inline SQL to stored procedures in the database. On some of these SQL, we did record deletion (usually only 1 record), and we just execute it via IDBCommand.ExecuteNonQuery() and then check the return value to see how many records were affected (which should be 1) for verification that the query actually does something. With the change to stored procedure, we just return 1 in the stored procedure if the delete is successful. However, the calling code then started to show these deletions as errors. Apparently ExecuteNonQuery only returns the number of affected rows on SELECT, INSERT and DELETE statements; for everything else it returns -1.  So I tried to figure out how to get a return value from a stored procedure. Let's assume a simplistic stored procedure as follows:

ALTER PROC ReturnOnly AS BEGIN       RETURN 5 END

You can't use ExecuteScalar to get the returned value, and ExecuteNonQuery will always return -1.  To get the value back, you need to add a return value parameter to the command.  The name of the parameter is not important.  The code to get the value returned by that procedure will be as follows:

private int ExecuteStoredProcedure(string storedProcedureName) {    SqlConnection connection = new SqlConnection(connectionString);    // Command - specify as StoredProcedure    SqlCommand command = new SqlCommand(storedProcedureName, connection);    command.CommandType = CommandType.StoredProcedure;    // Return value as parameter    SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);    returnValue.Direction = ParameterDirection.ReturnValue;    command.Parameters.Add(returnValue);     // Execute the stored procedure    connection.Open();    command.ExecuteNonQuery();    connection.Close();    return Convert.ToInt32(returnValue.Value); }

Read More

BrowseUpload Sign Out Part 2 of 2 - How to: Connect to SQL Server using ADO.NET and VB.Net

Part 2 - I'll take you through setting up a SQL server database and table, then moving into Visual Studio and creating an application in code that retrieves and saves data to SQL Server.

Read More

Part 1 of 2 - How to: Connect to SQL Server using ADO.NET and VB.Net

Part 1 - I'll take you through setting up a SQL server database and table, then moving into Visual Studio and creating an application in code that retrieves and saves data to SQL Server.

Read More

SQL Server Tutorials

Video Sub Category

NOTE:-  To view a video, click on the title. To update time watched, please login. (The Microsoft videos are totally free, but they  do require that you register with them to view the videos and you also have to use Internet Explorer browser, instead of Firefox, Safari, etc.)

	Series Title Date Presenter Min SQL Server 2005 Express What is a Database? 3/29/2006 Bob Taylor 28 SQL Server 2005 Express Understanding Database Tables and Records 3/29/2006 Bob Taylor 25 SQL Server 2005 Express More about Column Data Types and Other Properties 2/22/2006 Bob Taylor 22 SQL Server 2005 Express Designing Relational Database Tables 3/29/2006 Bob Taylor 34 SQL Server 2005 Express Manipulating Database Data 3/29/2006 Bob  Taylor 40 SQL Server 2005 Express More Structured Query Language 3/29/2006 Bob Taylor 23 SQL Server 2005 Express Understanding Security and Network Connectivity 3/29/2006 Bob  Taylor 41 SQL Server 2005 Express Connecting your Web Application to SQL Server 2005 Express Edition 3/26/2006 Bob Taylor 66 SQL Server 2005 Express Using SQL Server Management Studio 10/23/2006 Bob Taylor 40 SQL Server 2005 Express Getting Started with Reporting Services 10/21/2006 Bob Taylor 33 SQL Server 2005 Express Building and Customizing Reports in Business Intelligence Development Studio 10/22/2006 Bob Taylor 45 SQL Server 2005 Express Creating and Using Stored Procedures 11/14/2006 Bob Taylor 43 SQL Server 2005 Express Enabling Full-Text Search in your Text Data 11/14/2006 Bob Taylor 38 Total 478

Read More