Search This Blog

Loading...

Monday, July 21, 2014

Unable to open the physical file in SQL

                Unable to open the physical file in SQL

Issue-Issue in SQL while Attaching MDF File or Restoring Backup file from some location.

Error1
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\ DB \AdventureWorksDW2012_Data (1).mdf". Operating system error 2: "2(The system cannot find the file specified.)".

Error 2
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\ DB \AdventureWorksDW2012_Data.mdf". Operating system error 32: "32(failed to retrieve text for this error. Reason: 15105)".


Issue Description-While attaching the Database  mdf file in sql server we were getting these above errors.

--Attaching MDF File without ldf file
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'C:\DB\AdventureWorksDW2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

Error

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "C:\DB\AdventureWorksDW2012_Data (1).mdf". Operating system error 2: "2(The system cannot find the file specified.)".

Issue Solution

-Run SSMS as an Administrator
-Restart SQL Server

Now you will be able to attach the DB Successfully.

Run your query again 

--Attaching MDF File without ldf file
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'C:\DB\AdventureWorksDW2012_Data.mdf')
FOR ATTACH_REBUILD_LOg

Output.It will attach the mdf file and will create a new ldf (log file) in same location 
with the message below.

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2012_log.ldf" may be incorrect.
New log file 'C:\DB\AdventureWorksDW2012_log.ldf' was created.

NOTE: If after Running SQL as Administrator and Restarting the sql server won’t work for you then check for permission in your folder where you have kept your mdf or back file then go to “Sharing and Security of that folder and Allow-Full control.

Tuesday, July 8, 2014

T-SQL Script to perform DML Operations (Select, Insert, Update and Delete) based on DB Name

T-SQL script that will Perform DML Operations (Select, Insert, Update and Delete) based on DB Name


Requirement-We were working with Different customers DB where different customers were having Different DB Names but the table name and Structure of Tables were same.

So our requirement was through same web application we need to perform DML Operations (Select, Insert, Update and Delete) based on DB Name passed from front end (i.e. web application).
For Eg

DB-Customer-X
Tables-Employee, Products, Product_Description, Customers……….

DB-Customer-Y
Tables-Employee, Products, Product_Description, Customers……….

DB-Customer-Z
Tables-Employee, Products, Product_Description, Customers……….

So below I created a Stored Procedure Script that used to perform DML in different db based on DB Name passed from Front end

So Below is the Script with two test DB and one Employee Table

--Creating two test DB
Create database Test1
Create Database Test2

--Run Table Script in both the Database
CREATE TABLE [dbo].[Employee1](
     [Empid] [int] IDENTITY(1,1) NOT NULL,
     [EmpName] [nvarchar](50) NULL,
     [EmpSalary] [float] NULL,
 CONSTRAINT [PK_Employee1] PRIMARY KEY CLUSTERED
(
     [Empid] 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

Stored Procedure Script that will will Perform DML Operations (Select, Insert, Update and Delete) based on DB Name Passed.

 --Script Code
--Run this Procedure in DB with which your web application is connected
Create PROCEDURE [dbo].[proc_db_test]
 (
 --Passing DB Name
@dbname varchar(100)=null,
--For Passing Operation insert update delete select
@Operation varchar(100)=null,
--Passing table values
@EmpId int =null,
@EmpName nvarchar(200)=null,
@EmpSalary float=null
 )
 AS
 BEGIN
IF db_id(@dbname) IS NULL
 BEGIN
RAISERROR ('Database %s does not exists!', 16,1, @dbname)
RETURN
END

if @Operation='INSERT'
begin

  EXEC('INSERT INTO '+@dbname+' .dbo.Employee1(EmpName,EmpSalary)
        VALUES('''+@EmpName+''','''+@EmpSalary+''')')

END
else if @Operation='UPDATE'
begin
 exec('update ' +@dbname+'.dbo. Employee1
set EmpName= '''+@EmpName+'''
,EmpSalary= '''+@EmpSalary+''' where EmpId='''+@EmpId+'''')
END
else if  @Operation='DELETE' 
begin
exec('Delete  from  ' +@dbname+'.dbo.Employee1  where EmpId='''+@EmpId+'''')
end
else  if @Operation='SELECTDETAILS'
begin
exec('select * from   ' +@dbname+'.dbo.Employee1  where EmpId='''+@EmpId+'''')
end
else if @Operation='SELECTALL'
begin
exec('select * from ' +@dbname+'.dbo.Employee1 order by Empid')
end
End


Test the Script

Insert Operation in both DB
--Procedure will have following inputs
--Proj_DB_Test DBName OPeration,EmpID,EMPName,EmpSalary

Exec  [dbo].[proc_db_test] 'Test1','Insert',1,'RK','15000'

--Test1 DB Test
Select * from test1.dbo.Employee1

Output
Empid  EmpName       EmpSalary
1          RK        15000

--Procedure will have following inputs
--Proj_DB_Test DBName OPeration,EmpID,EMPName,EmpSalary

Exec  [dbo].[proc_db_test] 'Test2','Insert',1,'RK1','20000'

--Test2 DB Test
Select * from test2.dbo.Employee1


Output
Empid EmpName    EmpSalary
1    RK1  20000


Test Update Operation in both DB

--Updating Name in Test1 DB
Exec  [dbo].[proc_db_test] 'Test1','UPdate',1,'RK_new','15000'

Output
Empid EmpName    EmpSalary
1    RK_new     15000



Test Delete Operation in both DB
--For Testing Delete Operation we will insert one more record in any of DB

--Insert
Exec  [dbo].[proc_db_test] 'Test1','Insert',2,'RK_1','10000'

Output

Empid EmpName    EmpSalary
1    RK_new     15000
2    RK_1 10000

--Deleting above newely added Record
Exec  [dbo].[proc_db_test] 'Test1','Delete',2

Output

Empid EmpName    EmpSalary
1    RK_new     15000


Test Select Operations in both DB
--Test Specific Records of Both DB Employee Table by passing Employee ID

Exec  [dbo].[proc_db_test] 'Test1','SELECTDETAILS',2

Output

Empid EmpName    EmpSalary
1    RK_new     15000



Exec  [dbo].[proc_db_test] 'Test2','SELECTDETAILS',1

Output 
Empid EmpName    EmpSalary
1    RK1  20000

Test Select all Operation in both DB
--Test all Records of Both DB Employee Table

Exec  [dbo].[proc_db_test] 'Test1','SELECTALL'

Output

Empid EmpName    EmpSalary
1    RK_new     15000

Exec  [dbo].[proc_db_test] 'Test2','SELECTALL'

Output

Empid EmpName    EmpSalary
1    RK1  20000


Testing by Passing DB Name that doesn't exist in server-Will Raise the below error

Exec  [dbo].[proc_db_test] 'Test3','SELECTALL'

Msg 50000, Level 16, State 1, Procedure proc_db_test, Line 16
Database Test3 does not exists!

NOTE:The best use of this script when you have multiple customers having different Databases having same table Names and Same table Structure.




Friday, June 27, 2014

Issue:Unclosed quotation mark after the character string ''.

Unclosed quotation mark after the character string in SQL


Issue:Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.


Issue Reason: Apostrophe (')  is used in sql to start and end the string.So including  Apostrophe (')  in any string or varchar values will throw the above error.
for eg: if you run below query it will thrown the above error
Select 'Indian's'

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.


Solution:To fix this issue you need to add one more Apostrophe (')
Select 'Indian''s'

Output:Indian's

Note :when Inserting the values containing   Apostrophe (') from your web application or system applications into SQL you can replace the Apostrophe (') with double Apostrophe ('') by using Replace Function.

Tuesday, June 24, 2014

Script -Deleting data of all tables of a Database in SQL

Script -Deleting data of all tables of a Database in SQL

The below Script will delete data from all tables of a Database.

Don't test the script in your Primary Database  although for data safety i have the script inside transaction block  and it will rollback all changes.
For tables having Referential Integrity truncate command will not work.So for that we are we are using Delete command.

--Tested and verified in SQL 2008 R2 and SQL2012

Begin tran

-- disable all referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else
  TRUNCATE TABLE ?
'
GO
-- enable all  referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Rollback

Truncate will not Delete the Data if the table have referential Integrity(Foreign key) 
So for table having referential Integrity or foreign key Relation we are using Delete .
Test it by Removing the If condition that is deleting Data of all tables.

Begin tran

-- disable all referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO
-- enable all  referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Rollback

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.Employee' because it is being referenced by a FOREIGN KEY constraint.

Note :Truncate will reset the Identity column but Delete command doesn't  'So in order to Reset the Identity column for tables having Foreign key Relation ship in the above script
Use the below command


DBCC CHECKIDENT('TableName', RESEED, 0)--0 new reseed starting value

Thursday, June 19, 2014

Issue-This workbook has a Powerpivot data model created using a previous version of the Powerpivot add-in. You’ll need to upgrade this data model with Powerpivot in Microsoft Excel 2013

Powerpivot Excel 2010 Issue in Excel power pivot 2013


Issue-Excel 2010 Powerpivot Report Not working in excel 2013
Error-This workbook has a Powerpivot data model created using a previous version of the Powerpivot add-in. You’ll need to upgrade this data model with Power Pivot in Microsoft Excel 2013
,


Issue Description-We were having Some of Power Pivot Reports which we created using Excel 2010 Powerpivot when we edited those Report in Excel 2013 those were showing below errors.
This workbook has a Powerpivot data model created using a previous version of the Power Pivot add-in. You'll need to upgrade this data model with Powerpivot in Microsoft Excel 2013

Issue Solution-Click on
 PowerPivot tab
Manage
It will show the error message again
click ok
This workbook has a Powerpivot data model created using a previous version of the Power Pivot add-in. You’ll need to upgrade this data model with Powerpivot in Microsoft Excel 2013
click Ok  as below Screenshot.



Next it will ask to upgrade the excel book
Click ok

It will upgrade the excel and restart the excel file again
Now the Report has been converted to excel 2013 and you can edit it in excel 2013.

Thursday, June 12, 2014

Project Server Issue-The lookup table could not be saved due to the following reason(s):•One or more code values in the lookup table contain a code mask separator.

Project Server 2010\2013 Issue-The lookup table could not be saved due to the following reason(s):•One or more code values in the lookup table contain a code mask separator.

Issue-The lookup table could not be saved due to the following reason(s):•One or more code values in the lookup table contain a code mask separator.

Issue Description -While Creating a LUT(Look Up table) in project Server the LUT Fields were having Values which contains . eg Test.Testing
So adding these type of values in the LUT and saving the LUT was Throwing Error as below Screenshot.






Issue Solution-In Code mask Separator section change the Separator . to * and then save the LUT .The LUT Will get saved .


Note:If your LUT Values contains comma so again you will face the above error changing separator to * will not work you have to Remove comma from your LUT Values.

Wednesday, June 11, 2014

Running Sum in SQL Server

Running Sum in SQL Server


As a Programmer\developer we are aware of Fibonacci Sequence where the  next number is found by adding up the two numbers before it.
for e.g.-0, 1, 1, 2, 3, 5, 8, 13, 21, 34, ...

In SQL we termed it as Running Total or RunningSum where we will add a new Column that will calculate the Running total.

CREATE TABLE RunningTotal (
   Product nvarchar(100),
   P_Cost int not null
)


INSERT INTO RunningTotal (Product,P_Cost) VALUES ('X',100),('X',150),('X',200),('X',250),('X',300);
INSERT INTO RunningTotal (Product,P_Cost) VALUES ('Y',1000),('Y',1500),('Y',2000),('Y',2500),('Y',3000);
INSERT INTO RunningTotal (Product,P_Cost) VALUES ('Z',10),('Z',15),('Z',20),('Z',25),('Z',30);


So below is the Script calculating Running Sum (Fibonacci Sequence in a New Column ) and  Running Sum Per Product.

Below Script will calculate two below Columns
  1. Total Running Sum-Running Total of  P_cost for all Product irrespective of Product
  2. Running Sum Per Product-Running Total of each Product Calculating Running Sum for Each Product
with cte as
(
Select Product,P_cost,row_number()over(order by (Select 0))as RowNum
 from RunningTotal b
 )
 select Product,P_cost ,(Select Sum(P_cost) as TotalRunningSum from cte a where a.RowNum<=b.rownum    ) as TotalRunningSum,
(Select Sum(P_cost) as TotalRunningSum from cte a where a.RowNum<=b.rownum and a.Product=b.product    ) as RunningSumPerProduct
from cte b
order by Product

Output


NOTE: I have Used row_number()over(order by (Select 0))as RowNum to get row_number of each Row if your table has an identity column then you can use it Replacing this.