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.

drivestoredirect:s:*

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.

https://blogs.msdn.microsoft.com/paullou/2013/04/29/copy-files-2gb-to-your-cloud-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))

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

 

12.0
1
1 SQLBINARY 8 0 “” 1 ABinaryCol “”

Change to:

12.0
1
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

 

http://stackoverflow.com/questions/27301763/sql-server-bcp-export-corrupted-file

 


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(m.name+','+t.name 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
 
 

 

https://social.msdn.microsoft.com/Forums/en-US/23be9d8c-83eb-4271-a1bc-aed514161458/convert-multiple-columns-data-in-to-single-row-with-comma-as-a-delimiter?forum=transactsql


Generate Create View Script for All User Tables

  
 ;with mycte as (
SELECT c.[TABLE_NAME],[COLUMN_NAME],[Ordinal_position]      
  FROM [INFORMATION_SCHEMA].[COLUMNS] c
   join [INFORMATION_SCHEMA].[TABLES] t ON    c.[TABLE_SCHEMA]= t.[TABLE_SCHEMA]  AND c.[TABLE_NAME]=t.[TABLE_NAME]
  WHERE t.[TABLE_TYPE]='BASE TABLE'   
  )
 

  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
 GROUP BY t1.[TABLE_NAME];

 
 

 

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)+')'
FROM  TempDB.[INFORMATION_SCHEMA].[COLUMNS]
  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 

 

https://social.msdn.microsoft.com/Forums/en-US/beafb6f3-f162-4424-bec8-bd6bd6280459/dynamic-unpivot-with-unknown-number-of-rowscolumns?forum=transactsql


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
(
  SELECT
     ItemID,Name,[Type],path
    ,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
(
  SELECT
     ItemID,Name,[Type],TypeDescription,path
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         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
(
  SELECT
     ItemID,Name,[Type],TypeDescription,path
    ,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 (
SELECT
     ItemID,Name,[Type],TypeDescription,path,ContentXML
    ,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

  

 ----****Decrypt
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
EXEC(@SQL) 

Select * from #backupdetails

Drop table #backupdetails