Last Server Started Time

 Powershell:

Get-CimInstance -ClassName win32_operatingsystem | select csname, lastbootuptime

https://serverfault.com/questions/159612/windows-server-last-reboot-time

Advertisements

msdb.dbo.sp_send_dbmail : Query Results Seperator Setting —@query_result_separator How to remove space between columns.


Solution:  @query_result_separator =0x00


---Sample for test

create table test (
 ID varchar(50), col1 varchar(50)
 ,Cube varchar(50),col2 varchar(50),col3 varchar(50))
 insert into test values('aa','123','dd1','Pieces','www1')
,('aa2','223','dd2','Pieces2','www2')
,('aa3','323','dd3','Pieces3','www3')
,('aa4','423','dd4','Pieces4','www4')

Declare @Qry nvarchar(max)
Set @Qry = ' Select ID,ID,'+'''    ''' +'as EmptyFill1,col1,col2,col3 from test'

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'myprofilename',
 @recipients = 'mygmailaccount@gamil.com',
 @query = @Qry,
 --@query_result_separator =' ', --default
 --@query_result_separator =',',
 @query_result_separator =0x00, -- no space between columns!!!***************
-- @query_result_separator =0x09, --tab
 -- @query_result_separator =0x0B, --vertical tab

 @subject = 'Subject here',
 @body = 'This is body',
 @query_result_header = 1,
@query_no_truncate = 1,
@query_result_no_padding = 0 

 drop table  test

  

You can also define a variable to hold delimiter:

declare @separator char(1)=char(9)–tab or 0x09 or a literal tab inside single quotes.

Or
—*************This is not documented in any other place************
declare @separator char(1) =char(0) — or 0x00 –no space

….
@query_result_separator =@separator

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/99483c5f-8be4-4c92-a656-2c7b0086f236/query-results-seperator?forum=transactsql


Remove Leading Zeros in a Varchar Column but keep inside spaces (with SQL Server 2017 solution)

  

 create table test (col varchar(100))
insert into test values('00876H873 '),('00876 876500'),('0000HUJ8 9IU877 8UJH')

select * ,
RTRIM(LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' '))) AS NumSerie 

,len(RTRIM(LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')))
)
 ,datalength(RTRIM(LTRIM(Replace(REPLACE(LTRIM(REPLACE(Replace(col, ' ','*'),'0', ' ')), ' ', '0'),'*',' ')))
)

from test

drop table test

---SQL Server 2017
create table test (col varchar(100))
insert into test values('00876H873 '),('00876 876500'),('0000HUJ8 9IU877 8UJH')

select *
,Rtrim(Trim('0' from col+' ')) ,
 len(Rtrim(trim('0' from col+' ')))
 ,datalength(Rtrim(trim('0' from col+' ')))
from test

drop table test

-- Syntax for SQL Server and Azure SQL Database
TRIM ( [ characters FROM ] string )

  

https://jingyangli.wordpress.com/2016/08/25/remove-leading-zeros-in-a-varchar-column-but-keep-inside-spaces/

 

https://docs.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-2017


Teams Login Error: login_hint duplicated

Clear everything for teams in your credential manager.

Restart Team.


Get a Parameter Value from Linkedserver Query Sample (EXEC (‘….’) AT linkerservername )

  

declare @cnt int
EXECUTE (' Use Projects;
SELECT
?= SUM(p.row_count) 
FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id <2 AND o.type = ''U''
AND s.name = ''dbo''
 AND o.name = ''Reports''
',@cnt output

  ) AT  [RemoteServer]

 select @cnt

  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/141016f1-84d9-4c84-9006-431bdb91fea8/exec-at-query-syntax-question?forum=transactsql
Relevant documentation part:

[?]
Indicates parameters for which values are supplied in the <arg-list> of pass-through commands that are used in an EXEC(‘…’, <arg-list>) AT <linkedsrv> statement.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-2017



Download File with Start-BitsTransfer

  

$url = "https://www.myfilesource,com/datafile.xls"
$output = "C:\SourceDataDownload\mynewdatafile.xls"

Start-BitsTransfer -Source $url -Destination $output

 

https://docs.microsoft.com/en-us/powershell/module/bitstransfer/start-bitstransfer?view=win10-ps


View Definition of Stored Procedure

  

SELECT   [object_id]
      ,[definition] 
  FROM myDatabase.[sys].[all_sql_modules] asm
  where exists(
  select * from sys.all_objects ao
where type='P'  and asm.[object_id]= ao.[object_id])
 and asm.[object_id]>0