DBA WORLD

07 November 2022

Restore encrypted databases

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0xE11A199C1059C6F1E0223B56581CDCF3F043DFE8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Solution

02 November 2022

Script to List the Version and Edition

SELECT

SERVERPROPERTY ('MachineName') AS PhysicalMachineName,

SERVERPROPERTY ('ServerName') AS SQLServerName,

SERVERPROPERTY ('Edition') AS ServerEdition,

CASE

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'

ELSE 'unknown'

END AS MajorVersion,

CASE

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN SERVERPROPERTY ('productlevel')

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN SERVERPROPERTY ('productlevel')

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN SERVERPROPERTY ('productlevel')

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN SERVERPROPERTY ('productlevel')

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN SERVERPROPERTY ('productlevel')

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN SERVERPROPERTY ('productlevel')

WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN SERVERPROPERTY ('productlevel')

else

SERVERPROPERTY('ProductUpdateLevel')

END as 'ProductUpdate Level',

SERVERPROPERTY ('ProductVersion') AS ProductVersion,

--RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],

SERVERPROPERTY ('Collation') AS Collation

25 February 2022

Query to list the SPID time and completion time

Here is the query i found on one of the blogs, we can use this to know the SPID duration along with wait type. Thanks to the blogger.

===================================================================

SELECT R.session_id

, R.percent_complete

, R.total_elapsed_time/1000 AS elapsed_seconds

, R.wait_type

, R.wait_time

, R.last_wait_type

, DATEADD(s,100/((R.percent_complete)/ (R.total_elapsed_time/1000)), R.start_time) AS est_complete_time

, ST.text AS batch_text

, CAST(SUBSTRING(ST.text, R.statement_start_offset / 2,

(

CASE WHEN R.statement_end_offset = -1 THEN DATALENGTH(ST.text)

ELSE R.statement_end_offset

END - R.statement_start_offset

) / 2

) AS varchar(1024)) AS statement_executing

FROM sys.dm_exec_requests AS R

CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST

WHERE R.percent_complete > 0

AND R.session_id <> @@spid;

=====================================================================

03 August 2021

SSIS- The version number in the package is not valid.

Package migration from version 8 to version 6 failed with error 0xC001700A "The version number in the package is not valid. The version number cannot be greater than current version number.".

Solution:

1. Go to properties

2. Change the Version and redeploy the package

3. Copy the DTSX or move it to source server and execute.

27 July 2021

Alter failed for Database 'DBNAME '. (Microsoft.SqlServer.Smo), It is in the middle of a restore. (Microsoft SQL Server, Error: 927)

First Solution

This error will appear when we configure DB Mirroring.

Solution 1:

Please ensure you restored the latest log backup on the mirrored server and then click on start mirroring again on principal.

Solution 2:

Set the endpoints on both mirror and principal servers by executing the below commands on each server.

On Mirror Server:

ALTER DATABASE DBNAME SET PARTNER = ‘TCP://PrincipalServername.com:5022’

On Principal Server:

ALTER DATABASE DBNAEM SET PARTNER = ‘TCP://MirrorServername.com:5022’

01 April 2021

The publisher of this content does not allow it to be displayed in a frame

This will issue will come when SQL Server has been upgraded to any versions after 2016, SSRS reports that are used to display in iFrame will fail to display.

Solution:

Go to URL and change the URL to reportviwer, if the URL is using reports then it will not dispay in the iframe.

Example:

Change From:

https://Server/Reports/browse

Change To:

https://Server/ReportServer/Pages/ReportViewer.aspx?&rs:embed=true

http://Servername/ReportServer/Pages/ReportViewer.aspx?%kjhkjs%khjkhkjhy%retre+uytts%2popiopoi+iuiuyiuy&rs:Command=Render&Rs:embed=true

Note: this will be your report name -> %kjhkjs%khjkhkjhy%retre+uytts%2popiopoi+iuiuyiuy

23 December 2020

SSRS Failed to Start - an error occurred when attempting to connect to the report server remote procedure call (RPC) end point.

ERROR:

report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.

---> System.Runtime.InteropServices.COMException: The RPC server is not listening. (Exception from HRESULT: 0x800706B3)

--- End of inner exception stack trace ---

at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)

at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.GenerateDatabaseScript(String databaseName, Int32 lcid, Boolean isSharePointIntegrated, String& script)

at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.GenerateDatabaseScript(String databaseName, Int32 lcid, Boolean isSharePointIntegrated, String& script)