Delete duplicate records in SQL Server?

Use this script at you own risk. I am not responsible for any accidental mass amount of your data get deleted. Always use begin tran and rollback tran to make sure you know what are you deleting.

 

 

BEGIN TRAN

DELETE x

FROM (  SELECT *, rn=row_number() over   (   partition by  [DELETED]               ,[NETBIMDER]      ,[PG_DEPT]      ,[EMAILNOTIFICATION]    order by ID  )  from ND_LOG ) xwhere rn > 1

ROLLBACK TRAN

 

 

TO see what will be deleted, use this:

SELECT *SELECT *FROM (  SELECT *, rn=row_number() over   (   partition by  [DELETED]               ,[NETBIMDER]      ,[PG_DEPT]      ,[EMAILNOTIFICATION]    order by ID  )  from ND_LOG ) xwhere rn > 1

 

ID = Primary key.

7If you don’t have a primary key, you can use ORDER BY (SELECT NULL)

 

Reff: https://stackoverflow.com/questions/3317433/delete-duplicate-records-in-sql-server

Advertisements

Rebuilding Indexes using the SSMS Database Maintenance Wizard

Usually I do all 3 job under one single task but with separate schedule for each of them:

 

Reorganize_Index

Rebuild_Index

Update_Statistics

 

Details can be found here:

https://www.red-gate.com/simple-talk/sql/database-administration/rebuilding-indexes-using-the-ssms-database-maintenance-wizard/

 

 

VS2013 An exception occurred while initializing the database

I’m doing a code first Entity database

{“An exception occurred while initializing the database. See the InnerException for details.”}

It was working fine, but I decided to delete the physical database to see it recreate it, but it just fails every time.

I get the error

Cannot attach the file ‘C:\ASP\OdeToFood\OdeToFood\App_Data\OdeToFoodDb.mdf’ as database ‘OdeToFoodDb’.”}

Its code first so I don’t understand why its not recreating the database.

 

 

So deleting the file from Visual Studio was a dumb idea.

SQL still has it registered.

Fire up MS Sql Management Tool, connect to the server in my case

(localdb)\v11.0

You will see the database, still exists, delete it, you will get an error. The file doesn’t exist. Refresh and its gone from SQL. Now run your code again and all is good.

So delete the DB from the management tool not visual studio

Allow remote connections to your MySQL server

nano /etc/my.cnf

Now that the file is backed up let’s open this baby up for editing. When you have the file open you are going to want to look for this line:

bind-address = 127.0.0.1

What the above line does is limit connections to the localhost and only the localhost. You want to comment this line out by changing it to:

#bind-address = 127.0.0.1

Now save that file and restart MySQL with the command:

service mysqld restart

mysql -u root -p

Where root is the MySQL administrative user (most like it is root).

You will be prompted for the MySQL administrators password. After you have successfully authenticated you will have a new prompt that looks like:

mysql>

You are now at the MySQL prompt. You only have one command to enter for this to work. You will want to enter this command carefully:

GRANT ALL PRIVILEGES ON *.* TO username@address IDENTIFIED BY “password”;

Where username is the username on the remote machine that will be connecting, address is the IP address of the remote machine, andpassword is the password that will be used by the remote user.

When that command is issued successfully you should see something like:

Query OK, 0 rows affected (0.00 sec)

As long as you get Query OK, you should be good to go.

 

Full Reff: http://www.ghacks.net/2009/12/27/allow-remote-connections-to-your-mysql-server/