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%'

 

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
 

 

https://social.msdn.microsoft.com/Forums/en-US/3914a400-417d-43d2-b19c-2da598823dd6/help-in-query-please?forum=transactsql

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)>0 ' + char(13) + char(10)             

  FROM [INFORMATION_SCHEMA].[TABLES]  t join [INFORMATION_SCHEMA].[COLUMNS] c 
  on t.[TABLE_SCHEMA] =c.[TABLE_SCHEMA] and t.[TABLE_NAME]=c.[TABLE_NAME]  
  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
 Exec(@SQL) 

 

https://social.msdn.microsoft.com/Forums/en-US/42e2d4ac-e325-4e2d-b70e-a7f5843ec90e/finding-count-of-the-particular-value-in-all-the-columns-of-the-database?forum=transactsql