Report Server Configuration Manager: Unable to connect the the Report Server

Credit: Pedro Salinas-Ruiz - https://businesswintelligence.com/content/139/configuration-manager-ssrs-error 

Sometimes the WMI RS integration may become corrupted due to operating system updates or permission changes. If this happens, you may notice that Report Server Configuration Manager is unable to find the reporting instance on a server in which reporting services is DEFINITELY running in!

If this happens, we can rebuild the RS WMI component with a simple command. To do this, run the following command in an elevated command prompt. Make sure to change the path to the correct path relative to your installation directory and version.

mofcomp "C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\WMI\reportingservices.mof"

The default location for each version follows.

SSRS 2012

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\bin\reportingservices.mof

SSRS 2014

C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin\reportingservices.mof

SSRS 2016

C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\reportingservices.mof

SSRS 2017 and later

C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\WMI\reportingservices.mof

Power BI Report Server

C:\Program Files\Microsoft Power BI Report Server\PBIRS\WMI\reportingservices.mof

For those of you that have come up against the problem in SSIS packages where you cannot use an Execute Package Task to run a Package that is stored in the SSISDB then here is a solution.

Instead of using an Execute Package Task you will need to use an Execute SQL Task instead.

Firstly you will need to create the following Stored Procedure on whichever database you wish to that resides on the same SQL Server as the SSISDB.

Then in your controller parent SSIS Package use an Execute SQL Task and run the following SQL Statement: Amending the parameters as necessary.

Find Allocation Unit Size of Disk Using Powershell

Everything in SQL Server is stored on disk in 8K pages.  The Microsoft recommended best practice is to format using a 64K allocation unit size.  Processes such as the checkpoint and Lazy Writer try to write in extents when possible.  Extents are a collection of 8 pages and since each page is 8K we get 64K.  That is where the best practice recommendation originated.

You can check what Allocation Unit Size was used when the disks were originally formatted by using the following Powershell Command (Be sure to run Powershell with Administrative privileges)

PS C:\Windows\system32> Get-Volume | Format-List AllocationUnitSize, FileSystemLabel

SSMS: The Query Window Keyboard Shortcuts

 

Taken from https://www.red-gate.com/simple-talk/sql/sql-training/ssms-the-query-window-keyboard-shortcuts/

SSMS	…Toggle the full-screen display	Shift+Alt+Enter
	… Navigate to	Cntl+,
	…Exit	Alt+F4
Query Window	…Open with current connection	Ctrl+Q
	…Open with new connection	Ctrl+N
	…Save current	Ctrl+S
	…Save All	Ctrl+Shift+S
	…Close	Ctrl+F4
	…Print	Ctrl+P
	…Show or hide the results pane	Ctrl+R
	…Toggle query & results pane	F6
	Cycle Clipboard Ring	Ctrl+shift+V
	…Move to previous active window	Ctrl+Shift+F6
	…Move to the next active window	Ctrl+F6
	…trace query in SQL Server Profiler	Ctrl+Alt+P
	…Open Object Explorer if closed	F8
	…Open Server in Object Explorer	Alt+F8
	…Design Query in Editor	Ctrl+Shift+Q
	…Display Go To Line dialog box	Ctrl+G
View	…Object Explorer	F8
	…Object Explorer Details	F7
	…Registered Servers	Ctrl+Alt+G
	…Template Explorer	Ctrl+Alt+T
	…Solution Explorer	Ctrl+Alt+L
	…Bookmark Window	Ctrl+K, Ctri+W
	…Error List	Ctrl+V Ctri+E
	..Output	CtrkAlt+O
	…Task List	Ctrl+Alt+K
	…Toolbox	Ctrl+Alt+X
	…Full Screen	Shift+Alt+Enter
	…Navigate Backward	Ctrl-
	…Navigate Forward	Ctrl+Shift+-
	…Properties Window	F4
line indent	…Increase	TAB
	…Decrease	Shift+TAB
Make selected text	…upper case	Ctrl+Shift+U
	…lower case	Ctrl+Shift+L
	…a comment	Ctrl+K, Ctrl+C
	…Uncommented	Ctrl+K, Ctrl+U
Specify values	…for template parameters	Ctrl+Shift+M
Current Query, selected or all	…Execute	F5 (or Ctrl+E)
	…Parse	Ctrl+F5
	…Display estimated execution plan	Ctrl+L
	…Include Client Statistics	Shift+Alt+S
	…Include Actual Execution Plan	Ctrl+M
	…Cancel the executing query	Alt+Break
Debugging	…Start	Alt+F5
	…Stop	Shift+F5
	..Step Into	F11
	…Step Over	F10
	…Toggle Breakpoint	F9
	…Delete All Breakpoints	Ctrl+Shift+F9
Output results	…in a grid	Ctrl+D
	…in text format	Ctrl+T
	…to a file	Ctrl+Shift+F
Scroll text	… up one line	Ctrl* Up-arrow
	… below the cursor	Ctrl+Shift+Enter
Delete	…all text in the window	Ctrl+Shift+DEL
	…the word to the right of the cursor	Ctrl+Delete
	…the word to the left of the cursor	Ctrl+BACKSPACE
Move the cursor	…to the beginning of the line	Home
	…to the end of the line	End
	…the beginning of the document	Ctrl* Home
	…to the end of the document	Ctrl+End
	…Move the cursor up one screen	Page Up
	…Move the cursor down one screen	Page Down
	…one word to the right	Ctrl+Right-arrow
	…one word to the left	Ctrl+Left-arrow
Select text from the cursor	…to the beginning of the document	Ctrl+Shift+Home
	… to the end of the document	Ctrl+Shift+End
	…to the start of the current line	Shift+Home
	…down line by line	Shift+Down -arrow
	… up line by line	Shift+Up-arrow
Select	… the entire current document	Ctrl+A
	… the word containing the cursor, or the closest word	Ctrl+W
	… the current location in the editor, back to the previous location in the editor	Ctrl+=
Extend selection	…to the top of the current window	Ctrl+Shift+Page Up
	… one word to the right	Ctrl+Shift+Right-arrow
	… one word to the left	Ctrl+Shift+Left-arrow
	… one page up	Shift+Page Up
	… one page down	Shift+Page-Down
Move the cursor, Extending the selection	…To the right one word,	Ctrl+Shift+Alt+Right-arrow
	… to the left one word.	Ctrl+Shift+Alt+Left-arrow
	… to the last line in view.	Ctrl+Shift+Page Down
	… up one line	Shift+AIt+Up -arrow
	…down one line	Shift+Alt+Down-arrow
	…Block (column-wise) left	Alt+Shift +Left-arrow
	…Block right	Alt+Shift +Right-arrow
	…Block up	Alt +Shift +Up-arrow
	…Block down	Alt+Shift +Down-arrow
Search for text	…Display the Find dialog box	Ctrl+F
	…Display the Replace dialog box	Ctrl+H
	… the next occurrence of the previous search text	F3
	… the previous occurrence of the search text	Shift+F3
	…the next occurrence of the currently selected text	Ctrl+F3
	… the previous occurrence of the currently selected text	Ctrl+Shift+F3
(SQL Prompt) Format	…Rename variables/aliases	F2
	… Format SQL	Ctrl+K, Ctrl+Y
	… Uppercase keywords	Ctrl+B, Ctrl+U
	… Expand wildcards	Ctrl+B, Ctrl+W
	… Qualify object names	Ctrl+B, Ctrl+Q
	… Add/remove square brackets	Ctrl+B, Ctrl+B
	… Insert semicolons	Ctrl+B, Ctrl+C
	… Encapsulate as new stored procedure	Ctrl+B, Ctrl+E
(SQL Prompt) Navigate	… Script object as ALTER	F12
	… Execute current statement	Shift+F5
	… Code Snippets Manager	Ctrl+K,Ctrl+B
	… Select in Object Explorer	Ctrl+F12
	… Tab history	Ctrl+Q
	… Summarize script	Ctrl+B, Ctrl+S
	… Find unused variables and parameters	Ctrl+B, Ctrl+F
(SQL Prompt) Code-Complete	… Show suggestions	Ctrl+Space
	… Refresh suggestions	Ctrl+Shift+D
	… Code suggestions on/off	Ctrl+Shift+P
	… Switch to/from column picker	Ctrl+Left / Ctrl+Right
	… Move up/down suggestions box filters	Ctrl+Up / Ctrl+Down
Intellisense	…List members	Ctrl+Space or Ctrl+J
	…Complete word	Alt+Right Arrow
	… Parameter Information	Ctrl+Shift+Space
	… Refresh Local Cache	Ctrl+Shift+R
	… Jump between syntax pairs	CTRL+]
Bookmark	…go to Next one	Ctrl+K, Ctrl+N,
	…go to previous one	Ctrl+K, Ctrl+P
	…Toggle (set or remove)	Ctrl+K, Ctrl+K
	… Clear Bookmarks	Ctrl+K, Ctrl+H

SSMS Shortcut Key to fill in Parameters

 [Ctrl] + [Shift] + [M]

Create Table structure from another table in DB2 AS400

CREATE TABLE SCHEMA.NEW_TB AS (
    SELECT *
    FROM SCHEMA.OLD_TB
) WITH NO DATA

There is no way of copying the table structure and the data in the same way that

you can use SELECT INTO with MS SQL. 

SQL Agent Job Schedules

FIX: The request does not contain a certificate template extension or the CertificateTemplate request attribute

Scenario:

You are trying to generate an SSL Certificate from your Enterprise CA using the MMC Snapin using a CSR File and you get the error:

—————————
Certificate Request Processor
—————————
The request contains no certificate template information. 0x80094801 (-2146875391)
Denied by Policy Module 0x80094801, The request does not contain a certificate template extension or the CertificateTemplate request attribute.
—————————
OK
—————————

Fix:

Generate your CSR file as usual from IIS Management Console on your Web Server.
NB - You will now need to make sure that the Encryption is at least 2048 when generating the CSR...

Copy this csr file to a folder on your Enterprise CA Folder like C:\CertReq\

Log onto your Enterprise CA with Domain Administrator Account and run a command prompt with Administrative Privileges and type the following command:

C:\>certreq -submit -attrib "CertificateTemplate:WebServer" "C:\CertReq\CSR_File.txt" and press enter...

 You will then be prompted with a dialogue box to choose your Enterprise CA...

 Once you have clicked OK you will be prompted to save the Certificate File, once this is done you should see something like the following:

Now this is done copy your Certificate file to the Web Server and Complete Certificate Request in the IIS Management Snapin...

Check Articles on a Publication Database

This needs to be run against the Publication Database:

This script came from Stack Overflow User Davmos:

http://stackoverflow.com/users/493680/davmos

SELECT 
  msp.publication AS PublicationName,
  msa.publisher_db AS DatabaseName,
  msa.article AS ArticleName,
  msa.source_owner AS SchemaName,
  msa.source_object AS TableName
FROM distribution.dbo.MSarticles msa
JOIN distribution.dbo.MSpublications msp ON msa.publication_id = msp.publication_id
ORDER BY 
  msp.publication, 
  msa.article

List users with roles on a Database

SELECT

        [UserType] = CASE membprinc.[type]

                         WHEN 'S' THEN 'SQL User'

                         WHEN 'U' THEN 'Windows User'

                         WHEN 'G' THEN 'Windows Group'

                     END,

        [DatabaseUserName] = membprinc.[name],

        [LoginName]        = ulogin.[name],

        [Role]             = roleprinc.[name]

FROM

--Role/member associations

  sys.database_role_members AS members

--Roles

JOIN sys.database_principals AS roleprinc 
ON roleprinc.[principal_id] = members.[role_principal_id]

--Role members (database users)

JOIN sys.database_principals AS membprinc 

ON membprinc.[principal_id] = members.[member_principal_id]

--Login accounts

LEFT JOIN sys.server_principals AS ulogin    

ON ulogin.[sid] = membprinc.[sid]

WHERE membprinc.[type] IN ('S','U','G')

-- No need for these system accounts

AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')