Copy Large File(>2G) to Azure VM

When you copy a file large than 2 G, it will fail with the default RD connection.

You need to turn on drive redirection to allow you see local drives.

Open the downloaded .rdp file from a text editor and add this line to the end of the file and save the file.


You can double click the file to remote in to your Azure VM with your credentials and you should be able to copy and paste large files from your local drives to your VM.

Issue with BCP Exported File and How to Fix It.

Using bcp to export binary data from column varbinary(max) to file system is easy enough.
But when you try to open these files, you may run into problems.
The problem is that bcp added 8 bytes to the exported file when you use -n flag.
To change this behavior, you need to use format file to rescue.

Create a dummy table with one varbinary(max) column and use this table to create a format file.
You can use either fmt or xml format as your format file.
Modify this generated format file and change the 8 in the third column to 0 and save it.
The final step is to use this format file for your bcp export.


 if object_id('mytemp1','U') is not null
drop table mytemp1 

create table mytemp1 ( ABinaryCol varbinary(max))

set @cmd = 'BCP  [mydb1].[dbo].[mytemp1] format nul -f "C:\temp\myFormatFile1.xml" -n -T -S'+ @@servername
exec master..xp_cmdshell @cmd


1 SQLBINARY 8 0 “” 1 ABinaryCol “”

Change to:

1 SQLBINARY 0 0 “” 1 ABinaryCol “”
The final bcp:


declare @name varchar(300)='myfile.doc'
declare @Command VARCHAR(4000) 

SET @Command = 'bcp "select datacol from  db.[dbo].[datatable] where id =1 queryout "c:\temp\' + @name + '" -f "C:\temp\myFormatFile1.xml" -T  -S '+ @@servername  ;

   -- PRINT @Command
EXEC xp_cmdshell @Command,NO_OUTPUT


Column Data to a Row with Recursive

Besides the For XML path way, we can use recursive CTE to concatenate column data within a group to a single data point.


if object_id('test','U') is not null
drop table test 

create table test 
	(Item_hierarchy_id int identity,
 	 parent_item_hierarchy_id int null, 
	 name varchar(10) not null)

insert into test (parent_item_hierarchy_id, name) values
(null,	'item1'), (1, 'child1'), (1, 'child2'), 
(null, 'item2'), (4, 'child3'), (4, 'child4'), (4, 'child5'),(4, 'child6')

;with mycte as (
Select Item_hierarchy_id, Item_hierarchy_id parent_item_hierarchy_id,Cast(name  as varchar(1000)) name,  0 as lvl 
from test where parent_item_hierarchy_id is null
Union all
Select t.Item_hierarchy_id, m.parent_item_hierarchy_id, Cast(',' as varchar(1000)) name ,lvl+1 as lvl 
from test t join  mycte m  
on t.parent_item_hierarchy_id  = m.parent_item_hierarchy_id 
and t.Item_hierarchy_id > m.Item_hierarchy_id  ) 

,mycte1 as (
select name ,row_number()Over(Partition by parent_item_hierarchy_id Order by lvl desc) rn  from mycte


Select name from mycte1 where rn=1

Generate Create View Script for All User Tables

 ;with mycte as (
SELECT c.[TABLE_NAME],[COLUMN_NAME],[Ordinal_position]      

  SELECT  'IF Object_Id(''View_'  +t1.[TABLE_NAME] + ''',''V'')  is not null  Drop  View View_' +t1.[TABLE_NAME] + char(10)+ char(13)+' GO'+ char(10)+ char(13)+ '  Create View View_'   +t1.[TABLE_NAME] +' as Select '+ Stuff(( SELECT ',' + quotename([COLUMN_NAME])
           FROM mycte t2
          WHERE t2.[TABLE_NAME] = t1.[TABLE_NAME]
          ORDER BY [TABLE_NAME] ,[Ordinal_position]
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  
			+ ' FROM ' +  quotename(t1.[TABLE_NAME]) +';'+ char(10)+ char(13)+' GO' AS statements
  FROM mycte t1



Dynamic UNPIVOT Gloabal Temp Table -T-SQL


 create table ##tempMSRCache (CompanyName varchar(50)
, Standard_A_Status varchar(50)
,Standard_A_Solution varchar(50)
, Standard_B_Status varchar(50)
, Standard_B_Solution varchar(50)

Insert into ##tempMSRCache values
('CompanyA','in spec','has backups','out of spec','pop mail'),
('CompanyB ','in spec','has backups','inspec','pop mail') 

Declare @sql as NVarchar(4000)
Declare @Cols as NVarchar(4000)

Set @Cols=null

Select @Cols =  COALESCE(@Cols + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
  WHERE TABLE_NAME='##tempMSRCache' and COLUMN_NAME<>'CompanyName'

   --print @Cols

Select @sql='Select
CompanyName, Standard,Answer
FROM ##tempMSRCache t
CROSS APPLY (Values ' + @Cols + ' )  d(Standard,Answer) '

--Print @sql;

exec (@sql)

 Select * from ##tempMSRCache 

drop table ##tempMSRCache

Query SSRS Report Content

Bret Stateham has a detailed post about how to query SSRS RDL definition.

Here is the the link to the post:

Extracting SSRS Report RDL (XML) from the ReportServer database


Here is code snippet I copied from the post for how to query the content for a search word:


--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
;WITH ItemContentBinaries AS
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM myReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
     END AS Content
  FROM ItemContentBinaries
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
--now use the XML data type to extract the queries, and their command types and text....
,finalCTEForQuery as (
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

Select * from finalCTEForQuery
Where CommandText Like '%searchWord%'


SQL Server 2016: Check Encryped Database with RESTORE FILELISTONLY


OPEN Master Key Decryption  BY PASSWORD = 'thisisMySecretKey$'

declare @SQL NVARCHAR(1000),  @fullbackup NVARCHAR(2000)
Set  @fullbackup   = N'C:\Backup\myEncryptedDBbackup.bak'

CREATE TABLE #backupdetails
     LogicalName          NVARCHAR(255),
     PhysicalName         NVARCHAR(255),
     Type                 NVARCHAR(1),
     FileGroupName        NVARCHAR(255),
     Size                 BIGINT,
     MaxSize              BIGINT,
     FileId               INT NULL,
     CreateLSN            NUMERIC(25, 0) NULL,
     DropLSN              NUMERIC(25, 0) NULL,
     UniqueFileId         UNIQUEIDENTIFIER NULL,
     readonlyLSN          NUMERIC(25, 0) NULL,
     readwriteLSN         NUMERIC(25, 0) NULL,
     BackupSizeInBytes    BIGINT NULL,
     SourceBlkSize        INT NULL,
     FileGroupId          INT NULL,
     LogGroupGuid         UNIQUEIDENTIFIER NULL,
     DifferentialBaseLsn  NUMERIC(25, 0) NULL,
     DifferentialBaseGuid UNIQUEIDENTIFIER NULL,
     IsReadOnly           BIT NULL,
     IsPresent            BIT NULL,
     --SQL Server 2008, 2012,2014 Only
     TDEThumbprint        VARBINARY(32) NULL

	 --  --SQL Server 2016 Only
	 ,SnapshotUrl  NVARCHAR(360)

SET @SQL ='RESTORE FILELISTONLY FROM DISK=''' + @fullbackup + ''''

INSERT #backupdetails

Select * from #backupdetails

Drop table #backupdetails


Dynamic Pivot Sample with Two Column Lists

Create table #MAIN (SAPID char(10), Col1 varchar(20), Col2 varchar(20), Col3 varchar(20), SAP1 varchar(10), City varchar(20))
Insert into #MAIN values ('SADRT','4000','8000','120000','2892999','IL')
Insert into #MAIN values ('HSGYD','4000','8000','100000','2892999','PL')
Insert into #MAIN values ('JUHYD','6000','8000','120000','3837737','SL')

Create table #Meaning (SID int, Col_Name varchar(20), Col_Desc varchar(40))
Insert into #Meaning values (1,'Col1','Toolprice')
Insert into #Meaning values (2,'Col2','Machineprice')
Insert into #Meaning values (3,'Col3','Microscopeprice')
Insert into #Meaning values (4,'Col4','Furnaceprice')
Insert into #Meaning values (5,'Col5','Velcrotapeprice')
Insert into #Meaning values (6,'Col6','Packingprice')
Insert into #Meaning values (7,'SAP1','Wrappingprice')

DECLARE @cols1 AS NVARCHAR(2000),  @cols2 AS NVARCHAR(2000), @sql AS NVARCHAR(4000)
select @cols1 = STUFF((select DISTINCT ', '
+ quotename(rtrim(col_name)+'_'+rtrim(COl_Desc))
FROM  #Meaning 
where Col_name in ('Col1','Col2','Col3','SAP1')
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')

--Print @cols1

select @cols2 = STUFF((select DISTINCT ', '
+ 'Max('+quotename(rtrim(col_name)+'_'+rtrim(COl_Desc))+') as ' +quotename(rtrim(col_name)+'_'+rtrim(COl_Desc))
FROM  #Meaning 
where Col_name in ('Col1','Col2','Col3','SAP1')
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')
--Print @cols2
Set @sql=N';with mycte as (
select SAPID,Col,Val,City from #MAIN
cross apply (values(''Col1'',Col1),(''Col2'',Col2),(''Col3'',Col3),(''SAP1'',SAP1) ) d(col,val)

SELECT SAPID, '+ @cols2 + ' ,City FROM (
Select (rtrim(col_name)+''_''+rtrim(COl_Desc)) as cols,SAPID,Col,Val,City
FROM mycte m join #Meaning me on m.Col=me.Col_Name
) src
PIVOT (Max(val) for Cols IN ('+ @cols1 +')) pvt
Group by SAPID,City'
 --print @sql
exec sp_executesql @sql;

drop table #Meaning,#MAIN

Search a Date String in All Columns in a Database


  DECLARE @SQL nvarchar(max)

declare @dtstring varchar(10)='2020-12-31'

 Select @SQL=Stuff((
SELECT  ' UNION ALL '+ char(13) + char(10)  + 'Select  ''' + c.COLUMN_NAME  +''' as '+  quotename(c.COLUMN_NAME)    +' , ''' + quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME] ) 
+ ''' as tname, sum(case when ' + quotename(c.COLUMN_NAME)+   ' = '''+@dtstring+'''   then 1 else 0 End)  cnt      FROM   ' 
+ quotename(t.[TABLE_SCHEMA]) + '.' + quotename(t.[TABLE_NAME]) + ' WHERE 1=0 OR ' + quotename(c.COLUMN_NAME) + '= '''+@dtstring+''' '  
 + char(13) + char(10)  +' Group by  '+   quotename(c.COLUMN_NAME)+ char(13) + char(10) 
 +' Having sum(case when ' + quotename(c.COLUMN_NAME)+   ' = '''+@dtstring+''' then 1 else null End)&gt;0 ' + char(13) + char(10)             

  WHERE TABLE_TYPE='BASE TABLE' and [DATA_TYPE] Like '%date%' --and t.[TABLE_NAME] like'Event%'
FOR XML PATH(''), type ).value('.', 'varchar(max)'),1,10,'')

 Print @SQL

New Table Valued Function STRING_SPLIT in SQL Server 2016

New Table Valued Function STRING_SPLIT in SQL Server 2016

STRING_SPLIT ( string , separator )

The Separator is length 1 single character or space.

Returns single column table with the column name: value.