SQL Server Nuggets

 -- Find the Subscription ID based on the Description -- Subscription ID: 8BD6138A-9859-4F79-9394-41C50ED2FC22 (DEV) SELECT * FROM ReportServer.dbo.Subscriptions WHERE [Description] = 'Exception Policy Correction Plan Email'; -- Find the job created to run the data-driven subscription -- Look for the job step that contains the subscription ID -- job_id = '5A25474E-7014-4533-8D58-4C5971BF9776' SELECT * FROM msdb.dbo.sysjobsteps WHERE command LIKE '%8bd6138a-9859-4f79-9394-41c50ed2fc22%' /* Job Step command: exec [ReportServer].dbo.AddEvent  @EventType='TimedSubscription' , @EventData='8bd6138a-9859-4f79-9394-41c50ed2fc22' --<<< Subscription ID */

 DECLARE     @JSON    NVARCHAR(MAX) =         N'{"value":[{"AccountNumber":1003123456},{"AccountNumber":1004654321}]}'; SELECT * FROM OPENJSON(@JSON, '$.value')     WITH (         AccountNumber    BIGINT ); DECLARE     @JSON    NVARCHAR(MAX) =         N'[{"AccountNumber":1003123456},{"AccountNumber":1004654321}]'; SELECT * FROM OPENJSON(@JSON)     WITH (         AccountNumber    BIGINT );

https://www.brentozar.com/archive/2020/12/how-to-batch-updates-a-few-thousand-rows-at-a-time/  

https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/  

  https://www.red-gate.com/simple-talk/sql/database-devops-sql/feature-branches-and-pull-requests-with-git-to-manage-conflicts

RESTORE FILELISTONLY FROM DISK =  'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Backup\AdventureWorksLT2012.bak' RESTORE DATABASE AdventureWorksLT2012 FROM DISK =  'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Backup\AdventureWorksLT2012.bak' WITH MOVE 'AdventureWorksLT2008_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Data\AdventureWorksLT2012.mdf' , MOVE 'AdventureWorksLT2008_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Log\AdventureWorksLT2012.ldf'

  Full Backup: WideWorldImporters-Standard.bak Backup Folder: C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL Check the data and log file paths: RESTORE FILELISTONLY FROM DISK =  'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Backup\WideWorldImporters-Standard.bak' Restore to my data and log file paths: RESTORE DATABASE WideWorldImporters FROM DISK =  'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Backup\WideWorldImporters-Standard.bak' WITH MOVE 'WWI_Primary' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Data\WideWorldImporters.mdf' , MOVE 'WWI_UserData' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Data\WideWorldImporters_UserData.ndf' , MOVE 'WWI_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019DEV\MSSQL\Log\WideWorldImporters.ldf'

       DECLARE         @TSQL                                VARCHAR ( 8000 )     ,   @BRANCH_NUMBER                       VARCHAR ( 4 ) =              CONVERT ( VARCHAR ( 4 ), @P_BRANCH_NUMBER)     ,   @DATA_AS_OF                          CHAR ( 8 )      SET @TSQL =          'SELECT '      +     ' [Acctnum], [DataAs...

https://docs.microsoft.com/en-us/troubleshoot/sql/admin/pass-variable-linked-server-query  

  https://www.mssqltips.com/sqlservertip/5878/getting-started-with-powershell-file-properties-and-methods/

  https://www.mssqltips.com/sqlservertip/5802/powershell-module-to-read-directory-contents-and-store-in-a-sql-server-table/

https://www.mssqltips.com/sqlservertip/3207/rename-and-move-a-file-with-powershell-in-a-sql-server-agent-job/   The code can easily be put into a SQL Agent job step.

  http://bradsruminations.blogspot.com/2009/10/viva-la-famiglia.html

https://medium.com/swlh/recursion-in-sql-explained-graphically-679f6a0f143b