Attach database failed : "Unable to open the physical file "....". Operation system error 5: "5(Access is Denied)". Microsoft SQL Server, Error: 5120)

If you receive this follow error while you are trying to attach you old or back-up database mdf file to your Microsoft SQL Server 2008, you can fix it by using the methods mentioned below.

Attach database failed .................
"Unable to open the physical file "....". Operation system error 5: "5(Access is Denied)". Microsoft SQL Server, Error: 5120)

There are several ways of fixing it. I looked for the solutions and found these 3 methods from http://blog.sqlauthority.com. Here, I am going to re-share them again.


Method 1. ----- Tested
CREATE DATABASE ygnonline ON
(FILENAME = N'D:\MyProjects\myapps\database\mydb_Data.mdf')
FOR ATTACH_REBUILD_LOG
GO
***The log file (.ldf) will be recreated.

Method 2.

EXEC sp_attach_single_file_db @dbname='TestDb',
@physname=N'D:\MyProjects\myapps\database\mydb_Data.mdf'
GO


Method 3.

CREATE DATABASE ygnonline ON
(FILENAME = N'D:\MyProjects\myapps\database\mydb_Data.mdf')
FOR ATTACH
GO


I never try myself on method 2 and 3 since the issue I have is fixed with the method 1. Please kindly be advised to check with your database expert before using above methods in live or production environment.
You can also go and read TechNet library of Microsoft for more details in this.
Wish you guys can fix all the bugs you got before coming of the new year!!! Good Luck!! Enjoy!!! All the best!!!!!!

Remove or delete duplicate records or rows from SQL Server Database.

You can delete or remove the duplicate records or rows from your SQL Database Table without needing identical column with following piece of code. I used single field or column in this example. If you have better solution, please kindly leave a comment. 



SELECT * INTO #TempTable FROM DTABLE GROUP BY DRECORD HAVING COUNT(DRecord)>1
DELETE FROM DTABLE
INSERT INTO DTABLE SELECT * FROM #TEMPTABLE
DROP TABLE #TEMPTABLE
SELECT * FROM DTABLE

All the best!!!!

How to fix: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path.

When you are getting the following error on your shared hosting server.


Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.


You can fix it by following steps

1. Open your local data file "ASPNETDB.MDF" from you visual studio's solution explorer.
2. Right click on "ASPNETDB.MDF" and click "Publish to Provider"
3. "Script to file"
4. Click "next" or "finish" to create a script file.

5. Open the generated script file in your database's query window.

6. Make your the selected database is your main database to use.

7. Execute the script.

8. You will see asp.net application services tables in your database which are the tables with "aspnet_" prefix.

9. Change your connection string "ApplicationServices" as mention below
<add name="ApplicationServices" connectionString="Data Source=XXPC\sqlexpress;Initial Catalog=yourdb;Persist Security Info=True;User ID=sa;Password=1234;" providerName="System.Data.SqlClient" />


Now your application's membership services and user profile will be working with the database from your sql server and no more with local data file.

SQL Management 2008 Error: "Saving changes is not permitted."

Are you getting this error message when you are trying to change any of the information on your table(s) in you database??

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.


You can fix this by following action.



Go to #Tools# -> #Options# -> #Designers# -> #Table and Database Designers# and uncheck the Prevent saving changes that require table re-creation checkbox.