Database Attached failed, Unable to open physical file, operating system error 5: "5 (Access is denied.)" And Version Error

Some times when you try to attach database files (.mdf, . ldf) to sql server , you might get the errors
I     Permission Error
II   Version Error

I     Permission Error

"Unable to open physical file, operating system error" 

This is because of you don't have the full permission for (.mdf) file from where you are accessing. For this reason you need to give the full permission for that file.

1. Right click on file
2  Go to Properties
3. Select Security Pane.
4. Click Edit
5.  Select the Appropriate User Name
6. Give Full control 
7. Apply and Ok

Now you try to Attach the file to Database.

II   Version Error
Some times we can't open mdf file due to lower version of SQL Server. The file will be higher version than the Server version. In that case we need to add supported version where you are loading the file.

read more

Multiple CTEs Join with Other Tables

Multiple CTEs Join with Other Tables

1. Create  1st CTE : cte_year

2.Create 2nd CTE: cte_date

3. Use Joining Table at Left Side :Job_Plan

Example

WITH cte_year

AS (
        SELECT year(getdate()) AS current_year,1 AS cnt

        UNION ALL

        SELECT current_year + 1 AS current_year,cnt + 1 AS cnt FROM cte_year WHERE cnt < 5

        )

,cte_date

AS (

        SELECT CASE
                       WHEN month(getdate()) >= 7  THEN year(getdate())
                       ELSE year(dateadd(yy, - 1, getdate()))
                       END start_dt

               ,CASE
                       WHEN month(getdate()) <= 6 THEN year(getdate())
                       ELSE year(dateadd(yy, 1, getdate()))
                       END end_dt
               ,1 AS cnt
   

        UNION ALL

        SELECT start_dt + 1 start_dt ,end_dt + 1 end_dt ,cnt + 1 FROM cte_date  WHERE cnt < 5

        )

SELECT DISTINCT A.* FROM Job_Plan(NOLOCK) a

INNER JOIN (

        SELECT cast(current_year AS VARCHAR(100)) AS Plan_Name  FROM cte_year

        UNION ALL

        SELECT cast(right(current_year, 2) + 'FS' AS VARCHAR(100)) AS Plan_Name  FROM cte_year

        UNION ALL

        SELECT cast(right(current_year, 2) + 'PS' AS VARCHAR(100)) AS Plan_Name  FROM cte_year

        UNION ALL

        SELECT cast(right(start_dt, 2) + '-' + right(end_dt, 2) AS VARCHAR(100)) AS Plan_Name   FROM cte_date

        UNION ALL

        SELECT cast(right(start_dt, 2) + ' - ' + right(end_dt, 2) AS VARCHAR(100)) AS Plan_Name   FROM cte_date

        ) F ON ltrim(rtrim(a.Plan_Name_Long)) LIKE '%' + ltrim(rtrim(F.Plan_Name)) + '%'

        AND a.Plan_Event_Name_Long NOT LIKE '%' + cast(year(dateadd(yy, - 1, getdate())) AS VARCHAR(100)) + '%'

        AND Plan_ID NOT IN (  SELECT Plan_ID    FROM Job_In_Plan(NOLOCK)  )

ORDER BY a.Plan_ID


output of cte_year
current_year cnt
2013        1
2014        2
2015        3
2016        4

2017        5


output of cte_date
start_dt end_dt cnt
2013 2014 1
2014 2015 2
2015 2016 3
2016 2017 4

2017 2018 5

read more

How to Import XML file Data into SQLServer Table

Sample XML file error_1.xml

<?xml version="1.0" encoding="UTF-8"?>
<api:response type="failure" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:api="https://audience.job.com/services/flow/ext-register">-<errors>-<error field="passwordCriteria.password" code="regex">
<![CDATA[Password may not contain spaces ]]>
</error>-<error field="emails[0].value" code="emailInvalidFormat">
<![CDATA[Bad email format]]>
</error></errors></api:response>

Query To view the Data from XML file

SELECT  xCol FROM    (SELECT * FROM OPENROWSET (BULK 'E:\error_1.xml',SINGLE_CLOB)  AS xCol) AS R(xCol)

The output  will be as same as xml format file.
--------------------------------------------
<?xml version="1.0" encoding="utf-8"?>  <api:response xmlns:api="https://audience.job.com/services/flow/ext-register" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" type="failure">    <errors>      <error code="regex" field="passwordCriteria.password"><![CDATA[Password may not contain spaces ]]></error>      <error code="emailInvalidFormat" field="emails[0].value"><![CDATA[Bad email format]]></error>    </errors>  </api:response>

If we are converting  the xCol  into XML data type then It will strip or remove the DTD from XML and stored in Table.

I) SELECT CONVERT(xml, BulkColumn)FROM OPENROWSET(Bulk 'E:\error_1.xml', SINGLE_BLOB) [rowsetresults]

II) SELECT  convert(xml,xCol) FROM 
(SELECT * FROM OPENROWSET (BULK 'E:\error_1.xml',SINGLE_CLOB)  AS xCol)
 AS R(xCol)

Output

<api:response xmlns:api="https://audience.job.com/services/flow/ext-register"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" type="failure">
  <errors>
    <error code="regex" field="passwordCriteria.password">Password may not contain spaces </error>
    <error code="emailInvalidFormat" field="emails[0].value">Bad email format</error>
  </errors>

</api:response>

read more

How we can create Identity and to set seed and reseed Identity Column In SQL

1)Create Identity Column in a Table

Create Table Emp_Master(Emp_ID int Identity(1,1),Emp_Name Varchar(100))
Identity(1,1) In this first 1 is Identity Seed and second 1 is identity Increment.

Identity Seed : Exposes the Initial row value for an identity column.
Identity Increment: Exposes the value added to the maximum existing row identity value when  generating the next identity value.

2)How to find the Identity value of a table
  IDENT_CURRENT('Tablename') 

select * from Emp_Master
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo
5 Jose
6 John

Select IDENT_CURRENT ('Emp_Master') As Value
Value
6

3)How to Reseed the identity value

DBCC CheckIdent(Tablename,Reseed, your desired value)

If we delete data from Emp_Master table, our seed value will not to set back. In that purpose we need to reseed the identity value.
select * from Emp_Master
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo

In this example  we deleted empid 5 and 6.  Now our current identity value will be 6
Select IDENT_CURRENT ('Emp_Master') As Value
Value
6

And if we insert another record into Emp_Master table  the value of Emp_ID will be 7 not be 5
select * from Emp_Master
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo
7 John

In this case we need to Reseed the identity column to 4. we deleted where empid is 7
Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo

DBCC CheckIdent(Emp_Master,Reseed,4)

Now our seed value is 4 . And if we insert empid into emp_master table the value of Emp_id will be 5.
Select IDENT_CURRENT ('Emp_Master') As Value
Value
4

Emp_ID Emp_Name
1 Job
2 Joby
3 Jijo
4 Jojo
5 John

read more

Dynamic Query For Truncate Tables when it has Foreign key relationships

Set NoCount on
Declare @FK_Table Varchar(100)
Declare @PK_Table Varchar(100)
Declare @FK_Constraint Varchar(100)
Declare @FK_Cnt int
Declare @FK_Columns Varchar(max)
Declare @PK_Columns Varchar(max)
Declare @Query varchar(max)
Declare @FK_Cursor Cursor

Select * Into Test_FroeignKey From
(Select
Name As Constraint_Name,OBJECT_NAME(A.parent_object_id) Foreign_Table,
OBJECT_NAME(A.referenced_object_id ) Primary_Table,
COL_NAME (B.parent_object_id,parent_column_id ) As Foreign_Key,
COL_NAME(B.referenced_object_id ,referenced_column_id) As Primary_Key
From sys.foreign_keys A Inner join sys.foreign_key_columns B
On A.object_id =B.constraint_object_id   where A.name Like'%ConstraintName%'
)AA

Set @FK_Cursor = Cursor For Select Distinct Constraint_Name From Test_FroeignKey
Open @FK_Cursor
Fetch Next From @FK_Cursor Into @FK_Constraint
 --Drop the foreign keys
 While @@FETCH_STATUS =0
 Begin
 
   Select @FK_Table= Max(Foreign_Table) From Test_FroeignKey
   Where Constraint_Name in(
                            Select Distinct Constraint_Name
                            From
                                (Select  Name As Constraint_Name
                                 From sys.foreign_keys  A
                                 Inner join sys.foreign_key_columns  B
                                 On A.object_id =B.constraint_object_id
                                 Where A.name Like'%ConstraintName%'
                                 )AA
                             ) And Constraint_Name =@FK_Constraint
   Set @Query = 'Alter Table Test.dbo.'+@FK_Table+' Drop '+ @FK_Constraint
   Exec (@Query)
 
 Fetch Next From @FK_Cursor Into @FK_Constraint
 End

Close @FK_Cursor

---Truncate Tables-------
Truncate Table Test.dbo.Tablename

Open @FK_Cursor
Fetch Next From @FK_Cursor Into @FK_Constraint

 While @@FETCH_STATUS =0
 Begin

 Select @FK_Cnt=COUNT(*)  From Test_FroeignKey  Where Constraint_Name =@FK_Constraint
 If @FK_Cnt =1
   Begin
   Select
       @FK_Table= Foreign_Table,@FK_Columns=Foreign_Key,
       @PK_Table = Primary_Table,@PK_Columns =Primary_Key
   From Test_FroeignKey
   Where Constraint_Name Not In(
  Select Distinct Constraint_Name
  From (
Select  Name As Constraint_Name
From sys.foreign_keys  A
Inner join sys.foreign_key_columns  B
On A.object_id = B.constraint_object_id
Where A.name Like'%ConstraintName%'
)AA
                               ) And Constraint_Name = @FK_Constraint
                             
   Set @Query = 'Alter Table Test.dbo.'+ @FK_Table +
                ' ADD  CONSTRAINT '+ @FK_Constraint+' FOREIGN KEY('+ @FK_Columns +
                ')REFERENCES Test.dbo.'+@PK_Table+'('+ @PK_Columns +')'
 
   Exec (@Query)
   End
 
   Else
   Begin

Set @FK_Columns = Stuff((Select ', ' + Convert(Varchar(100),Foreign_Key)
    From
  (Select Foreign_Key From Test_FroeignKey
   Where Constraint_Name =@FK_Constraint
  )X
    FOR XML PATH('')
    ),1,2,'')
                         
   Set @PK_Columns = Stuff((Select ', ' + Convert(Varchar(100),Primary_Key)
                            From
                              (Select Primary_Key From Test_FroeignKey
                               Where Constraint_Name =@FK_Constraint
                              )X
                            FOR XML PATH('')
                            ),1,2,'')                        


   Select
       @FK_Table = Max(Foreign_Table),
       @PK_Table = Max(Primary_Table)
   From Test_FroeignKey
   Where Constraint_Name Not In(
  Select Distinct Constraint_Name
  From (
Select  Name As Constraint_Name
From sys.foreign_keys  A
Inner join sys.foreign_key_columns  B
On A.object_id = B.constraint_object_id
Where A.name Like'%ConstraintName%'
)AA
                               ) And Constraint_Name = @FK_Constraint
     
   Set @Query = 'Alter Table Test.dbo.'+ @FK_Table +
                ' ADD  CONSTRAINT '+ @FK_Constraint+' FOREIGN KEY('+ @FK_Columns +
                ')REFERENCES Test.dbo.'+@PK_Table+'('+ @PK_Columns +')'
 
   Exec (@Query)
   End

 Fetch Next From @FK_Cursor Into @FK_Constraint
 End
Close @FK_Cursor
Deallocate  @FK_Cursor
Drop Table Test_FroeignKey

read more

Gmail SMTP, POP3 & IMAP Settings

Gmail SMTP Settings

 The Gmail SMTP server settings for sending mail through Gmail from any email program are:

    Gmail SMTP server address: smtp.gmail.com
    Gmail SMTP user name: Your full Gmail address (e.g. example@gmail.com)
    Gmail SMTP password: Your Gmail password
    Gmail SMTP port: 465
    Gmail SMTP TLS/SSL required: yes

 Gmail POP3 Settings

The Gmail POP3 server settings for accessing incoming messages in any email program are:

    Gmail POP server address: pop.gmail.com
    Gmail POP user name: Your full Gmail address (example@gmail.com)
    Gmail POP password: Your Gmail password
    Gmail POP port: 995
    Gmail POP TLS/SSL required: yes

 Gmail IMAP Settings

 The Gmail IMAP server settings for accessing incoming messages and online folders in any email program are:

    Gmail IMAP server address: imap.gmail.com
    Gmail IMAP user name: Your full Gmail address (example@gmail.com)
    Gmail IMAP password: Your Gmail password
    Gmail IMAP port: 993
    Gmail IMAP TLS/SSL required: yes

read more

Primary Key And Foreign Key In SQL

Primary Key:
Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.

Foreign Key: 
 A foreign key is a field  that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

Creating  And Dropping Primary Key In SQL Tables:

1)Add Primary key during the creation of Table

Create Table Employee

(Emp_Id Int Constraint PK_Emp_Id Primary Key Clustered,

 Emp_Name Varchar(50))

Primary key with Clustered

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50),

 Constraint PK_Emp_Id Primary Key Clustered (Emp_Id  )

 )

Insert Into Employee  Values (200,'Johny'),(100,'Job')

Select * From Employee

Emp_Id	Emp_Name
100	Job
200	Johny

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50),

 Constraint PK_Emp_Id Primary Key Clustered(Emp_Id Desc)

 )

Insert Into Employee  Values (100,'Job'),(200,'Johny')

Select * From Employee

Emp_Id	Emp_Name
200	Johny
100	Job

Primary key with Nonclustered

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50),

 Constraint PK_Emp_Id Primary Key NonClustered(Emp_Id)

 )

Insert Into Employee  Values (200,'Johny'),(100,'Job')

Select * From Employee

Emp_Id	Emp_Name
200	Johny
100	Job

2)Add Primary key After the creation of Table

Create Table Employee

(Emp_Id Int  Not Null,

 Emp_Name Varchar(50))

Alter Table Employee Add Constraint PK_Emp_Id Primary Key(Emp_ID)

Note : During the creation of Primary key  where Key column should be Not NULL

3) Drop Primary Key

Alter Table Employee Drop PK_Emp_Id

Alter Table Employee Drop Constraint PK_Emp_Id

Creating  And Dropping Foreign Key In SQL Tables:

1)Add Foreign key during the creation of Table

Create Table Employee

( Emp_Id Int Constraint PK_Emp_Id Primary Key Clustered,

 Emp_Name Varchar(50),

 Emp_Dep_Id Int Constraint FK_Emp_Dep_Id Foreign Key References Dept(Dep_Id)

 )

Create Table Employee

(Emp_Id Int Constraint PK_Emp_Id Primary Key Clustered,

 Emp_Dep_Id Int,

 Emp_Name Varchar(50),

 Constraint FK_Emp_Dep_Id Foreign Key (Emp_Dep_Id) References Dept(Dep_Id) )

2)Add Foreign key After the creation of Table

Alter Table Employee Add Constraint FK_Emp_Dep_Id Foreign Key (Emp_Dep_Id) References Dept(Dep_Id)

3) Drop Foreign Key

Alter Table Employee Drop FK_Emp_Dep_Id

Alter Table Employee Drop Constraint FK_Emp_Dep_Id

read more

How to Restart an Interrupted SQL Server Database Restore

The RESTORE DATABASE…WITH RESTART command is a very useful command which is available in SQL Server 2005 and higher versions. A Database Administrator can use this command to finish restoring an interrupted database restore operation.
In the below snippet you can see that ProductDB is in a (Restoring…) state once the SQL Server came online after the unexpected failure.

During such scenarios one can execute the RESTORE DATABASE…WITH RESTART command to successfully complete the database restore operation.
Below are two commands.  The first gets a list of the backups on the file and the second does the actual restore with the restart option.

-- get backup information from backup file

RESTORE FILELISTONLY
FROM DISK ='C:\DBBackups\ProductDB.bak'
GO

-- restore the database

RESTORE DATABASE ProductDB
FROM DISK ='C:\DBBackups\ProductDB.bak'
WITH RESTART
GO

Below you can see that after running the RESTORE DATABASE…WITH RESTART command the database was successfully restored allowing user connectivity.

 

read more

Defining a recursive common table expression(CTE) : How to show the hierarchical level of managers and the employees who report to them

How to show the hierarchical level of managers and the employees who report to them:

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees

(

      EmployeeID smallint NOT NULL,

      FirstName nvarchar(30)  NOT NULL,

      LastName  nvarchar(40) NOT NULL,

      Title nvarchar(50) NOT NULL,

      DeptID smallint NOT NULL,

      ManagerID int NULL,

 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)

);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES

 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)

,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)

,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)

,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)

,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)

,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)

,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)

,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

--Records in MyEmployees table

EmployeeID	FirstName	LastName	Title	DeptID	ManagerID
1	Ken	Sánchez	Chief Executive Officer	16	NULL
16	David	Bradley	Marketing Manager	4	273
23	Mary	Gibson	Marketing Specialist	4	16
273	Brian	Welcker	Vice President of Sales	3	1
274	Stephen	Jiang	North American Sales Manager	3	273
275	Michael	Blythe	Sales Representative	3	274
276	Linda	Mitchell	Sales Representative	3	274
285	Syed	Abbas	Pacific Sales Manager	3	273
286	Lynn	Tsoflias	Sales Representative	3	285

Query to show the hierarchical level of managers and the employees who report to them:

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS

(

    SELECT ManagerID, EmployeeID, Title, 1 AS EmployeeLevel

    FROM dbo.MyEmployees  WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1

    FROM dbo.MyEmployees AS e

        INNER JOIN DirectReports AS d

        ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, Title, EmployeeLevel

FROM DirectReports

ORDER BY EmployeeLevel ;

Output:

ManagerID	EmployeeID	Title	EmployeeLevel
NULL	1	Chief Executive Officer	1
1	273	Vice President of Sales	2
273	16	Marketing Manager	3
273	274	North American Sales Manager	3
273	285	Pacific Sales Manager	3
285	286	Sales Representative	4
274	275	Sales Representative	4
274	276	Sales Representative	4
16	23	Marketing Specialist	4

More details about recursive CTE
http://technet.microsoft.com/en-us/library/ms175972.aspx

read more