IF OBJECT_ID(‘tempdb..#TEMP_AP_INVOICE’) IS NOT NULL DROP TABLE #TEMP_AP_INVOICE
Category DBA
Cannot resolve the collation conflict between “Latin1_General_CS_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
If this error happens in join, use this solution:
SELECT *
FROM Table1
LEFT OUTER JOIN Table2 ON Table2.ID = Table1.ID collate SQL_Latin1_General_CP1_CI_AS
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
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:
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/
Monir's Blog 