I found a solution for people who get an exception: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were …
Tag: sql-server
Finding a Primary Key Constraint on the fly in SQL Server 2005
I have the following SQL: Since I have multiple environments, that PK_PS_userVariables constraint name is different on my different databases. How do I write a script that gets that name then adds it into my script? Answer While the typical best practice is to always explicitly name your constraints, you can …
How to delete all duplicate records from SQL Table?
Hello I have table name FriendsData that contains duplicate records as shown below I want to remove duplicate combinations rows using MS SQL? Remove latest duplicate records from MS SQL FriendsData table. here I attached image which highlights duplicate column combinations. How I can removed all duplicate com…
How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?
I would like to write a single SQL command to drop multiple columns from a single table in one ALTER TABLE statement. From MSDN’s ALTER TABLE documentation… Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earli…
Simple DateTime sql query
How do I query DateTime database field within a certain range? I am using SQL SERVER 2005 Error code below Note that I need to get rows within a certain time range. Example, 10 mins time range. Currently SQL return with Incorrect syntax near ’12’.” Answer You missed single quote sign: Also, …
Using a conditional UPDATE statement in SQL
I would like to have an UPDATE statement like this: SELECT * FROM Employee WHERE age = CASE WHEN (age < 20) THEN age=15 WHEN (age > 20) THEN age= 20 Is this not possible in SQL Server / …
Syntax of for-loop in SQL Server
What is the syntax of a for loop in TSQL?
SQL Server 2008 – How to convert GMT(UTC) datetime to local datetime?
I have an insert proc that passes in GETDATE() as one of the values because each insert also stores when it was inserted. This is hosted on SQL Azure – which uses GMT. Now, when I am receiving messages, I have the GMT date stored for each of them in their timestamp columns, how do I convert this to the
How can I group by date time column without taking time into consideration
I have a bunch of product orders and I’m trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration? 3/8/2010 7:42:00 should be grouped with 3/8/2010 4:15:00 Answer Cast/Convert the values to a Date type for your …
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
I am getting following error in my SQL server 2008 R2 database: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘tblArmy’ because it is not full-text indexed.