SQL SERVER 2005 Sps & Qurries /IIS Configs Issues and solutions

SqlServer Complex Qurries:.

Getting 3rd Max Unit Price value form table
---------------------------------
SELECT TOP 1 UnitPrice
FROM (
SELECT DISTINCT TOP 3
 UnitPrice
FROM Products
ORDER BY UnitPrice DESC) A
ORDER BY UnitPrice
----------------------------------

Create Table Named Marks.

CREATE TABLE [dbo].[Marks]([MarksID] [int] IDENTITY(1,1) NOT NULL,[Name] [varchar](50) NULL,[Marks] [numeric](18, 0) NULL,CONSTRAINT [PK_Marks] PRIMARY KEY CLUSTERED ([MarksID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
Getting 4th and Fifth max Values from a table Marks.

select top 2 (Marks) from (Select top 5(Marks) from Marks Order by Marks Desc)A order by Marks

Multiple Tables Insert using Stored Procedure

Create PROCEDURE dbo.InsertNewImage
 @ImageTitle nvarchar(30),
 @ImageDescription nvarchar(100),
 @ImageFilename nvarchar(50),
 @ImageThumbnail image,
 @AlbumID int
AS
 Begin

  Set Nocount On

  DECLARE @ImageID int

  INSERT INTO Images(ImageTitle, ImageDescription, ImageFilename, ImageThumbnail)
  VALUES (@ImageTitle, @ImageDescription, @ImageFilename, @ImageThumbnail)

  SELECT @ImageID=@@IDENTITY

  INSERT INTO ImageInAlbum(ImageId, AlbumId)
  VALUES (@ImageID, @AlbumID)

 End

OnDelete Cascade Clause

CREATE TABLE test_child
(SUB1 INT,
[NO] int,
FOREIGN KEY ([NO]) REFERENCES Test_parent
ON DELETE CASCADE)


SQL SERVER /2005 Configs Issues and solutions


<b>1-</b> Back Up a Database (SQL Server Management Studio)/Getting Location of any db file.

Error<>

Working in sql with attcahing any db file from any location the above attach file is enable to view in program files because it start overwritting on the attach file .U need make a back of that particular file while transporting it another system.

Solution

Show the Links
http://msdn.microsoft.com/en-us/library/ms187510.aspx
http://msdn.microsoft.com/en-us/library/ms188312.aspx


Creation Of User Login:. 

To Make any user we need to go and login through windows authentecation mode go in security click on logins to create any User Login we need give the users special rights

*SysAdmin
*DBCreator

'sa' is bydefault user of SqlServer if we need to change its password we need go to the login area and give new passwor...