Rename a table with sp_rename (Table name has square brackets in the name literal)

  
  
 CREATE TABLE [TEST].[[TEST]].[NewTableName](
 [StatusID] [int] NOT NULL,
 [Status] [nchar](10) NULL )
 
GO
 
--wrong table name:     [TEST].[NewTableName   


--code to fix the name issue with sp_rename

----*******************
--put the srong table name inside double quotes
sp_rename'TEST."[TEST].[NewTableName"','NewTableName'

----*******************


--or the way with escape like you did
sp_rename 'TEST.[[TEST]].[NewTableName]','NewTableName'

---The table name has been changed to  TableName with schema [TEST]
--Afyer rename
[TEST].[TableName] or ( TEST.TableName)

 

https://social.msdn.microsoft.com/Forums/en-US/d5f0caa9-2062-4df5-85c6-ac7f95524020/renaming-a-table?forum=transactsql

https://msdn.microsoft.com/en-us/library/ms188351%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


Move datetime to nearest 15 minute

  
  create table test (dt datetime)
insert into test values('2016-12-07 11:22:25.063')
,('2016-12-07 11:02:25.063')
,('2016-12-07 11:55:55.063')
,('2016-12-07 11:32:25.063')
,('2016-12-07 11:52:25.063') 
,('2016-12-07 11:15:00.000') 

Select 
datetimefromparts (Year(dt),Month(dt),day(dt), datepart(hour,dt), (datepart(minute,dt)/15 + (CASE WHEN datepart(minute,dt)%15 =0 Then 0 Else 1 End) )*15%60 ,0,0)  dt1, 
dt ,
Dateadd(minute, (datepart(minute,dt)/15 + (CASE WHEN datepart(minute,dt)%15 =0 Then 0 Else 1 End) )*15%60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) dt2

,Dateadd(minute, ceiling(datepart(minute,dt) /15.)*15 % 60, DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) dt3

,Dateadd(minute
,datepart(minute, convert(smalldatetime,(ceiling(cast(dt as float) * (24/.25)))/(24/.25))) /* rounded minute in 15 minute interval */
,DATEADD(hour, DATEDIFF(hour, 0,dt), 0)) /* beginning of the hour */
AS dt4

from test

   
drop table test
 

 

https://social.msdn.microsoft.com/Forums/en-US/28a41498-1617-4d05-ba0c-b410adf141a3/conversion-error-while-converting-a-time-and-formatting-it?forum=transactsql


Insert “dummy” record into each group

  
  
 create table manufacturingprocess ([Job#] int, Process varchar(30), Hours int, ProcessOrder int)

Insert into manufacturingprocess values(1,'Cut',10,50),(1,'Assemble',25,100)
,(2,'Paint',5,25),(2,'Cut',12,25),(2,'Assemble',12,25)

--Query 
merge manufacturingprocess tgt
using (select distinct [Job#] From manufacturingprocess ) src 
on src.[Job#]=tgt.[Job#] and ( 1=0 or /* avoid insert again if execute twice */ tgt.Process='Dummy r')
When Not matched then 
insert ([Job#],Process,Hours, ProcessOrder) values (src.[Job#],'Dummy r',0,1);

Select [Job#],Process,Hours, ProcessOrder from manufacturingprocess
Order by [Job#],Hours

drop table manufacturingprocess

 

https://social.msdn.microsoft.com/Forums/en-US/df3b593f-27c1-4ca1-aa6f-a8df71fe10a5/insert-dummy-record-into-each-group?forum=transactsql


Generate Code-128 Barcode Online

You can generate barcode from the below link online:

http://atozee.co.uk/barcodes/barcode.php?draw=1

ASCII Control Codes in Code 128 Barcodes:
http://www.makebarcode.com/info/appnote/app_022.html

^M (Key): CR carriage return

For example: 5^M with 35p(barcode height)x 2p minimum bar width.
You can download the generated barcode image for print.


OctopusDeploy Tentacle Service Automatic Startup not Working

When the service startup was set to automatic, the OctopusDeploy Tentacle service did not start as expected. I tried to change the startup type to Automatic (Delayed start) as a workaround(trick found from the below link).

http://help.octopusdeploy.com/discussions/problems/46643-octopusdeploy-tentacle-service-doesnt-auto-start-after-server-restart


Speed up Reports for MS SQL Server 2014 Reporting Services SSRS and Access Log Information

How to Modify a Reporting Services Configuration File (RSreportserver.config)
https://technet.microsoft.com/en-us/library/bb630448(v=sql.120).aspx

There are a few configuration values can be changed to improve long running reports.

SQLServer 2014: \Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

Updated to:

https://www.mssqltips.com/sqlservertip/2197/how-to-speed-up-ms-sql-server-reporting-services-ssrs-on-first-run/
https://msdn.microsoft.com/en-us/library/ms157273(v=sql.120).aspx

Check Trace log file :
The trace log files are ReportServerService_.log and are located in the following folder:
C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\LogFiles

Read log information from ExecutionLog3 view

–T-SQL
Use ReportServer
select * from ExecutionLog3
Order by TimeStart DESC

https://msdn.microsoft.com/en-us/library/ms159110(v=sql.120).aspx

–Some sample values
https://blogs.msdn.microsoft.com/mariae/2009/09/24/troubleshooting-timeout-errors-in-reporting-services/

—Configure Available Memory for Report Server Applications
https://technet.microsoft.com/en-us/library/ms159206(v=sql.120).aspx

Modify SessionTimeout for SSRS with rs.exe utility

https://blogs.msdn.microsoft.com/jgalla/2006/10/11/session-timeout-during-execution/


SSRS Format Negative Number

=Format(Fields!colFieldName.Value, “###,###.;(###,###.)”))

Result examples:
(7,146,711)
(429,999)
763,607
1,500
300