ProgrammingOcean

1.create the Database with same Name,MDF Name,LDF Name. 2.Stop the Sql Server and then Replace the only new MDF file by old database (Corrupted database) MDF file and delete the LDF File of newly created database. 3.Now Start the Sql Server again. 4.you can notice that database status became 'Suspect' as expected. 5.Then run the given script to know the current status of your newly created datatbase. (Better you note it down the current status) SELECT * FROM sysdatabases WHERE name = 'yourDB' 6.Normally sql server would not allow you update anything in the system database.SO run the given script to enable the update to system database. sp_CONFIGURE 'allow updates', 1 RECONFIGURE WITH OVERRIDE 7.After run the above script, update the status of your newly database as shown below. once you updated the status, database status become 'Emergency/Suspect'. UPDATE sysdatabases SET status = 32768 WHERE name = 'yourDB' 8.Restart SQL Server (This is must, i...

After changed the name or add new column in the table, that changes would not reflect in the view if that field used in that view. For that you just run this system stored procedure with view name as parameter rather open the view and update it. Sp_refreshview yourviewname For an Example: I am using Table_A, Table_B and View_C In the View C I have used Table A and Table B. After created the View C I added one more column call Status in the Table A and run the View C, you would not see that newly added column as view have not been refreshed yet as shown below. For this you can simply update the view just using the above stored procedure as shown below, Sp_refreshview View_C After run the script you can able to see that added column in the view as shown below,

In Oracle you can retreive the field names as shown below, DESC Table_Name

For this there is a function call CHARINDEX(). This is very similar to InStr function of VB.NET and IndexOf in Java. This function returns the position of the first occurrence of the first argument in the record. SELECT CHARINDEX('r','server') This would return 3 as it is start from 1.

Cursors are useful thing in SQL as it is enable you to work with a subset of data on a row-by-row basis. All cursor functions are non-deterministic because the results might not always be consistent. A user might delete a row while you are working with your cursor. Here after a few functions that work with cursors. When you work with Cursor , you will Have to follow these steps . 1. Declare Cursor 2. Open Cursor 3. Run through the cursor 4. Close Cursor 5. Deallocate the Cursor 1. Declare cursor Declare Emp_Cur Cursor For Select Emp_Code From Employee_Details Declare the cursor with select query for a Table/View as shown above. 2. Open Cursor Open Emp_Cur Fetch Next From Emp_Cur Into @mCardNo Open the Declared cursor and Fetch them into declared local variables for row-by-row basis. In given example, open cursor Emp_Cur and Fetch the Emp_Code records and assigned into a local Variable called @mCardNo. 3. Run through the Cursor For this there is a Cursor function called @@FETCH_STATUs w...

Stored procedures are stored in SQL Server databases. The simplest implication of stored procedures is to save complicated queries to the database and call them by name, so that users won’t have to enter the SQL statements more once. As you see, stored procedures have many more applications, and you can even use them to build business rules into the database. How to create a Stored Procedure, As shown given below, created a Stored Procedure for Inserting records into Table call Holiday_Details, which has Code and Description fields. In this Stored Procedure, passing two parametrs as INPUT Parameters and one OUTPUT parameter. Normally in Stored Procedure we can pass parameters as Input / Output Stored parameters. When you define output parameters, we have to implicitly specify the OUTPUT Keyword. Here I have shown the simple stored procedure. CREATE PROCEDURE [dbo].[SP_Holiday] @Code char(3), @Desc varchar(100), @flag bit, @Err Varchar(MAX)=Null OUTPUT AS Begin Transaction if @flag=0...

The SQL Server provides very useful feature which is Begin, Commit, Rollback Transaction. When we use Begin Transaction before we use DML Queries, we can Commit or Rollback that Transaction after the confirmation. This is very useful if you update anything wrongly then you can rollback that transaction. For example,As shown below, I am trying to update the NodeID column data to 5 from 1. begin transaction update DownLoad_Data set NodeID=5 But after I updated, You can check whether you have been updated properly. But here, I realised I did not mention the Where clause. select * from DownLoad_Data So I have to rollback this transaction. For that I can use Rollback Transaction since I used Begin Transaction. Rollback transaction So again I changed the Query and run it. Begin transaction update DownLoad_Data set NodeID=5 where RecNo=1 Still you can check whether have been updated properly. If it is updated correctly then, Run the Commit Transaction to make all updates permanently. Commit...

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.

In sql server there are 2 built-in stored procedures for drop the already existing backup device and create the new device in the user defined path. Before create the backup device, must drop the device. Because when you create a backup device, if backup device had already been created, sql server throw a error. So very first time have to create a backup device manually. Afterwards you can use this script. This is very use ful as user can take backup where user wants it since this procedure takes the path as parameter. Create Backup device manually in Sql Server 2008 Go to Server Object where right click on Backup Device, Then choose New Backup Device. if you choose that, sql server let you to create the New Backup Device. (Please refer the figures as shown below) Figure 1 Figure 2 Figure 3 Figure 4 Figure 5 Script for Drop the Backup Device EXEC sp_dropdevice 'Time_Attendance' Script for Create the Backup Device EXEC sp_addumpdevice 'disk', 'Time_Attendance', @...

This very frequent needful thing for developers as they need to create so many reports based on this concept. For this you will have to use one of the window functions in oracle which is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This would add each and every vale with previous value and give like Running Total. Query for this, SELECT PRODUCT_NO,PL_NO, UNRESTRICTED_QTY, SUM(UNRESTRICTED_QTY) OVER (ORDER BY PRODUCT_NO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RUNNING_TOTAL FROM PRODUCT_LOCATION WHERE CLIENT_C=UPPER(‘MSWG’) AND UNRESTRICTED_QTY>0 AND LOCATION_NO=’RECEIPT_BAY‘ row in the result set and adding up the values with currently reading value which is specified by CURRENT ROW up to last record of the record set. And ordering results by PRODUCT_NO The result of the above query shown below.

Here it s the query for retrieve the table structure in sql server, SELECT Ordinal_Position,Column_Name,Data_Type,Is_Nullable,Character_Maximum_Length INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='table Name'

drop table ##temp create table ##temp (id char(3) ,marks int ) create table ##temp2 (id char(3) ,marks int ) insert into ##temp(id,marks) ----Here we are inserting duplicate select '001',50 ----records for each ID union all select '001',60 union all select '002',66 union all select '002',88 union all select '003',92 union all select '003',64 union all select '004',44 union all select '005',67 ----Here we are getting the distinct records and insert then into another Tempory table insert into ##temp2 select distinct id,max(marks) from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) group by id ---And delete those duplicate records from original Table delete from ##temp where id in( select a.id from (select id,count(id) cnt from ##temp group by id having count(id)>1) a) ---And again inser the inserted reocrds from temporary Table insert into ##t...

Blogger Buzz: Blogger integrates with Amazon Associates

We can use this clause to sum/count/avg/max/min so on. This clause will give you the output as detail and summary which is based on the fields you want to summarize. select * from #temp order by student compute sum(marks) by student in above compute by clause, you must specify the field you want to sum in Compute clause and specify the field in By clause based on which field you need to compute. Very important thing is you must specify the Order By clause in which specify the fileds whatever you specify in By clause in Compute clause. The output of above query is, When we try with max,min,avg, the query and output would be as shown below, Using Max() select * from #temp order by student compute max ( marks ) by student Using Min () select * from #temp order by student compute min ( marks ) by student Using Avg() select * from #temp order by student compute avg ( marks ) by student

We can use rowcoun t sql property to set the number of rows to be shown in the output. for an example, lets say there are 10 records in a table, if we set the rowcount to 5 then when retrieve records from that table, only 5 records will be shown. if you rowcount to 0 then all records will be retrieved and shown in output. SET ROWCOUNT 5 SELECT ref_num FROM tbl_po_master in above example only 5 rows have been retrieved and shown in output as we set the rowcount to 5.

In SQL Server you can retreive the field names as shown below, SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name='Table_Name')

There is way find what are the parameter list for a storedprocedure in sql server rather find them by open individually. SELECT PARAMETER_NAME,DATA_TYPE,PARAMETER_MODE FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME='AddDefaultPropertyDefinitions' Hope this would be very useful for developers who are working with database.

Here it is the procedure to Split the words using comma seperator. still you can use different character for split instead of comma(','). Here i m using 'E,l,e,p,h,a,n,t' as word with comma characters. so the output should be 'E','l','e','p','h','a','n','t'. Declare @name as varchar(20) Declare @i as int Declare @char as char Declare @word as varchar(20) select @name='E,l,e,p,h,a,n,t' set @word='' set @i=1 while @i ',' begin set @word=@word+@char end else if (@char=',' and @i len(@name)) begin print @word set @word='' end ---Print the last word if @i=len(@name) begin print @word end set @i=@i+1 end Output of this query would be,

In SQL Server there is a built-in function called Datepart() which is takes 2 paramaters which are return date option and date value. for the 1st paramater pass the date option as 'dw' and for second parameter pass the date value as shown below, SET dateformat dmy Select DATENAME(dw,'09/03/2010') Day_Name,datepart(dw,'09/03/2010') which_day_ofWeek if you execute this query, output will be, in SQL Server, by default the week start with 'Monday' which is 1. so in this example, the week is Tuesday. So the number of the Tuesday is 2. You can check, what is default start week number by using @@DATEFIRST. Select @@DATEFIRST Since SQL Server default start week number is 1(Monday), it is giving 1 in output. Default Value for Week in SQL Server, Monday - 1 Tuesday - 2 Wednesday - 3 Thursday ...

There is a built-in function call DateName() in SQL Server to get the Weekday Name. This function takes 2 parameters in which first is return date option whereas second one date value from which you want to get the weekday name. To get the weekday name you have to specify the date option as 'dw' . for example, set dateformat dmy SELECT DATENAME(dw,'09/03/2010') Weekday in this example '09' is day of march. so if you use this sql function as i given above, it will return the exact name of the weekday. in output, it is give you week day name.