CASE is an expression – it returns a single scalar value (per row). It can’t return a complex part of the parse tree of something else, like an ORDER BY clause of a SELECT statement. It looks like you just need: ORDER BY CASE WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount END desc, CASE WHEN TblList.HighCallAlertCount … Read more
I also had an issue with this problem simply copy and pasting DATETIME fields from SQL Management Studio to Excel for manipulation. Excel has the correct value of the DATETIME (even if the formatting is applied after the paste), but by default doesn’t have a built in format to show the SQL DATETIME. Here’s the … Read more
You’ll need to Enable identity insert. There’s a check box when you click the Edit Mappings.
In the SQL Server Management Studio, to find out details of the active transaction, execute following command DBCC opentran() You will get the detail of the active transaction, then from the SPID of the active transaction, get the detail about the SPID using following commands exec sp_who2 <SPID> exec sp_lock <SPID> For example, if SPID … Read more
You could declare a variable as a temporary table like this: declare @myList table (Id int) Which means you can use the insert statement to populate it with values: insert into @myList values (1), (2), (5), (7), (10) Then your select statement can use either the in statement: select * from DBTable where id in … Read more
It depends on whether default path is set for data and log files or not. If the path is set explicitly at Properties => Database Settings => Database default locations then SQL server stores it at Software\Microsoft\MSSQLServer\MSSQLServer in DefaultData and DefaultLog values. However, if these parameters aren’t set explicitly, SQL server uses Data and Log … Read more
I’ve been operating under the assumption that a single statement in SQL Server is consistent That assumption is wrong. The following two transactions have identical locking semantics: STATEMENT BEGIN TRAN; STATEMENT; COMMIT No difference at all. Single statements and auto-commits do not change anything. So merging all logic into one statement does not help (if … Read more
Try the following script: Exec sp_msforeachtable ‘SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD’ GO Also I prefer(After a long search) to use the following script, it contains @fillfactor determines how much percentage of the space on each leaf-level page is filled with data. DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET … Read more
See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example: DECLARE @textval NVARCHAR(30) SET @textval=”AB ABCDE # 123″ SELECT LEFT(SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000), PATINDEX(‘%[^0-9.-]%’, SUBSTRING(@textval, PATINDEX(‘%[0-9.-]%’, @textval), 8000) + ‘X’) -1)
Visibility Warning: Don’t the other answer. It will give incorrect values. Read on for why it’s wrong. Given the kludge needed to make UPDATE with OUTPUT work in SQL Server 2008 R2, I changed my query from: UPDATE BatchReports SET IsProcessed = 1 OUTPUT inserted.BatchFileXml, inserted.ResponseFileXml, deleted.ProcessedDate WHERE BatchReports.BatchReportGUID = @someGuid to: SELECT BatchFileXml, ResponseFileXml, … Read more