T-sql :: Change just DATE part of a DATETIME

DECLARE @dt DATETIME SET @dt = GETDATE() SELECT DATEADD(dd, DATEDIFF(dd, @dt, '2015-06-01'), @dt)

T-sql :: Change just DATE part of a DATETIME

Set date to first day of the month and end day of the month

Set date to first day of the month:

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0)

Set date to end day of the month:

SELECT EOMONTH(GETDATE())

Set date to last day of the previous month:

SELECT EOMONTH(GETDATE(), -1)

Remove milliseconds from datetime:

DECLARE @now DATETIME SET @now = GETDATE() SELECT @now SELECT DATEADD(ms, -DATEPART(ms, @now), @now)

Set date to first day of the month and end day of the month

Visual Studio Error List and Output Window shortcuts

To view the Error List window: CTRL + \,E inside the IDE.
To show the Output Window: CTRL + ALT + O.

Visual Studio Error List and Output Window shortcuts

Visual Studio Immediate Window shortcut

To get in the immediate window in Visual studio ::
Debug --> Windows --> Immediate
Ctrl + Alt + I

Visual Studio Immediate Window shortcut

Unable to update the EntitySet ‘Table’ because it has a DefiningQuery and no element exists in the element to support the current operation

Entity Framework tried to add a record to a table which had Clustered Index but it didn't have Primary Key.

Simple solution was adding the Primary Key.

the reason is treating the table as View, then the EDMX file keep the table as a view in StorageModel\EntitySet\DefiningQuery element. When there is a DefiningQuery the Entity becomes readonly unless you add modification functions, the other solution can be adding modification functions like Stored Procedures for Inserting, Updating, and Deleting.

Unable to update the EntitySet ‘Table’ because it has a DefiningQuery and no element exists in the element to support the current operation

VS :: Peek Definition

Everyday I enjoy the Peek definition in Visual Studio 2013 !

VS :: Peek Definition

SQL - Break execution of a SQL script

SET NOEXEC ON

SET NOEXEC ON skips the rest of the script but it does not terminate the connection.
To execute any other command you need to turn noexec off again.

OR

RAISERROR('Your error message', 20, -1) WITH log

This will terminate the connection, thereby stopping the rest of the script from running.
If you are logged in SQL as [admin] ('sysadmin' role), the RAISERROR will terminate the connection and stops the script from running the rest of code.
(If you are not logged in as [admin], otherwise the RAISEERROR() will fail and the script will continue executing.) Note that it requires both severity 20 (or higher) and "WITH LOG".

SQL - Break execution of a SQL script

Recursive SQL CTE to split CSV string to table rows

DECLARE @strCSV VARCHAR(8000) , @delimiter CHAR(1) SET @delimiter = ',' SET @strCSV = '001002330 ,001012657 ,001046228 ,001105047 ,001138189' DECLARE @TableVariable TABLE ( ID INT, Item VARCHAR(12) ) ;WITH RegNumStartEndIndexes AS( SELECT StartIndex = 0 , EndIndex = CHARINDEX(@delimiter,@strCSV) UNION ALL SELECT EndIndex+1 , CHARINDEX(@delimiter,@strCSV,EndIndex+1) FROM RegNumStartEndIndexes WHERE EndIndex>0 ) INSERT INTO @TableVariable (ID, Item) SELECT ROW_NUMBER() OVER(ORDER BY(SELECT 1)) AS ID , REPLACE( REPLACE( SUBSTRING(@strCSV, StartIndex, COALESCE(NULLIF(EndIndex,0), LEN(@strCSV)+1)-StartIndex), CHAR(13), ''), CHAR(10), '') AS Item FROM RegNumStartEndIndexes option (maxrecursion 0) -- [option (maxrecursion 0)] added To avoid following error when number of recursions gets high -- The maximum recursion 100 has been exhausted before statement completion SELECT * FROM @TableVariable

Originally grabbed the idea from here

Recursive SQL CTE to split CSV string to table rows

Query sp_Who2

sp_Who by MSDN - "Provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session."

DECLARE @Table TABLE( SPID smallint, Status nchar(30), LOGIN nchar(128), HostName nchar(128), BlkBy char(5), DBName nchar(128), Command nchar(16), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT ) INSERT INTO @Table EXEC sp_who2 SELECT * FROM @Table WHERE DBName = 'DB_NAME'

Query sp_Who2

Global Assembly Cache Tool - Gacutil.exe

Gacutil.exe can help to view and manipulate the global assembly cache and download cache. Visual Studio installs the tool and it needs to run using the Developer Command Prompt.

gacutil [options] [assemblyName]

Global Assembly Cache Tool - Gacutil.exe