Transact SQL – XQuery – Error – “XQuery [query()]: Attribute may not appear outside of an element”

Background

Stole one of Bob Beauchemin’s Code and had to understand it.

Introduction

  1. Author :- Bob Beauchemin
  2. Topic :- Move over developers! SQL Server XQuery is actually a DBA tool
  3. Link :- Link
  4. What does the code do
    • Looks for queries that have operators bearing a specific operator

 

Enhancement Envisioned

  1. Include the operator node in the list of projected records

Error

Textual


Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 30 [Batch Start Line 14]
XQuery [query()]: Attribute may not appear outside of an element


Image

 

Versions of SQL Server

  1. sql Server Version :- Microsoft SQL Server 2016 (SP1-CU3) (KB4019916) – 13.0.4435.0 (X64)
    Apr 27 2017 17:36:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Code

Original Code

Here is the original Code


use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Original]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Original] as ')

end
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Original]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    /*
        Author :- Bob Beauchemin
        Topic  :- Move over developers! SQL Server XQuery is actually a DBA tool
        Link   :-	https://www.sqlskills.com/blogs/bobb/move-over-developers-sql-server-xquery-is-actually-a-dba-tool/

    */

    SELECT 
            TOP
            (
                case
                    when  @maxNumberofRecords is  null then 10000
                    when  @maxNumberofRecords = 0 then 10000
                    else @maxNumberofRecords
                end
            )

            sql.text
            , qs.EXECUTION_COUNT
            , qs.*
            , p.* 
            --, relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
    CROSS APPLY p.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') relOp(node)
    WHERE query_plan.exist('
                            declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
                           ') = 1



end

GO

Revised Code

Here is the revised code – version 1

dbo.sp_LookForPhysicalOps.Revised01


&nbsp;
use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Revised01]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised01] as ')

end
go

print 'sql Server Version :- ' + @@VERSION
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised01]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    /*
        Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 24 [Batch Start Line 11]
        XQuery [query()]: Attribute may not appear outside of an element
    */

    ; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT 
            TOP
            (
                case
                    when  @maxNumberofRecords is  null then 10000
                    when  @maxNumberofRecords = 0 then 10000
                    else @maxNumberofRecords
                end
            )

            sql.text
            , qs.EXECUTION_COUNT
            , qs.*
            , p.* 
            , relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) p

    /*
        Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 32 [Batch Start Line 11]
        XQuery [query()]: Attribute may not appear outside of an element

    */
    CROSS APPLY p.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') relOp(node)

    WHERE query_plan.exist('
                            declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
                           ') = 1



end

GO



Compile Error


Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 30 [Batch Start Line 14]
XQuery [query()]: Attribute may not appear outside of an element


dbo.sp_LookForPhysicalOps.Revised02

Overview

  1. Added
  2. Modifies
    • Split into nodes
      • Original
        • CROSS APPLY p.query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable(“@op”)]’) relOp(node)
      • Revised
        • CROSS APPLY p.query_plan.nodes(‘//RelOp[@PhysicalOp = sql:variable(“@op”)]’) relOp(node)
      • Explanation
        • Please properly indicate attribute name
          • Change from /@PhysicalOp[.
          • To [@PhysicalOp

Code


use master
go

if object_id('[dbo].[sp_LookForPhysicalOps.Revised02]') is null
begin


    exec('CREATE PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised02] as ')

end
go

ALTER PROCEDURE [dbo].[sp_LookForPhysicalOps.Revised02]
(
      @op VARCHAR(30)
    , @maxNumberofRecords int = null
)
AS
begin

    ; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
    SELECT 
                TOP 
                (
                    case
                        when  @maxNumberofRecords is  null then 10000
                        when  @maxNumberofRecords = 0 then 10000
                        else @maxNumberofRecords
                    end
                )
              sql.[text]
            , qs.EXECUTION_COUNT
            --, qs.*
            , [node] = relOp.node.query('.')

    FROM sys.dm_exec_query_stats AS qs 

    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

    CROSS APPLY sys.dm_exec_query_plan(plan_handle) p

    /*
        Msg 2396, Level 16, State 1, Procedure sp_LookForPhysicalOps.Revised01, Line 32 [Batch Start Line 11]
        XQuery [query()]: Attribute may not appear outside of an element

    */
    --CROSS APPLY p.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]') relOp(node)

    CROSS APPLY p.query_plan.nodes('//RelOp[@PhysicalOp = sql:variable("@op")]') relOp(node)


    WHERE p.query_plan.exist('
                            declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
                            /ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
                            ') = 1



end

GO


Invoke



declare @op sysname
declare @maxNumberofRecords int = null

set @op = 'Hash Match'

set  @maxNumberofRecords = 5

EXECUTE [dbo].[sp_LookForPhysicalOps.Revised02]
          @op =  @op
        , @maxNumberofRecords = @maxNumberofRecords



Output

AWS / S3 – Management LifeCycle

Background

As one gets more comfortable with AWS RDS and S3,  discussions of backup files retention might arise.

That is how long are backup files kept.

 

Use Case

If you find yourself backing up files and using timestamps as part of your naming convention, you likely want to look into pruning files based on date.

Here is what our S3 bucket looks like:

 

Processing

Script

We can write scripts to prune backup files older than specific days.

Policy

Or we can manage retention via policies.

 

Overview

  1. We define life cycle rules at the bucket level
    • Keep in mind we do not go down to each folder
  2. Life-cycle Rule
    • Tab :- Name and Scope
      • Name
        • Offer a meaningful name
      • Scope
        • In our case we offer folder
          • full/
            • Full is the name of the folder
            • /
    • Tab :- Transitions
    • Tab :- Expiration
      • Permanently clean up after 3 days
    • Tab :- Review

 

 

Screen Shot

Amazon S3 – S3 Bucket [dbabackupmssql]

Tab – Overview

 

 

Tab – Management

 

 

Tab – Life-cycle rule – Name and scope

 

Tab – Life-cycle rule – Transitions

 

Tab – Life-cycle rule – Expiration

 

 

Tab – Life-cycle rule – Review

 

 

AWS/RDS – Backup/Restore – Error Messages

Background

Here are are some errors you might run into when you try to backup/restore a SQL Server database running on Amazon AWS RDS Instance.

Error Messages

Error Message Remediation
Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 350
Database backups can only be performed by members of db_owner or db_backupoperator roles in the source database
If you try to backup system databases ( master, msdb) or RDS Databases ( rdsadmin), please skip them
Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 287
A task has already been issued for database: AdminDB with task Id: 26, please try again later.
If you attempt to issue a new backup (rds_backup_database ) request while one is another running.
Msg 50000, Level 16, State 1, Procedure rds_backup_database, Line 58
Error executing procedure. Please provide appropriate backup type. Currently FULL and DIFFERENTIAL backup types are supported.
We can not log backup.  Only Full & Differential backups are supported.

 

AWS – RDS – SQL Server – Scheduling Database Backups

Background

Preparing for a meeting where we will be discussing our experience with Amazon AWS /RDS Deployment.

And, so it is time to document and share a bit more.

 

Code

  1. We will have a set of Stored Procedures
    •  dbo.usp_RDSDBBackupCore
      • The core SP
    • dbo.usp_RDSDBBackupFullAutomated
      • Manager
        • passes in targeted S3 folder and backup type

Stored Procedure

dbo.usp_RDSDBBackupCore



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


if object_id('[dbo].[usp_RDSDBBackupCore]') is null
begin

    exec('create procedure [dbo].[usp_RDSDBBackupCore] as ')

end
go

ALTER PROCEDURE [dbo].[usp_RDSDBBackupCore]
(
      @s3Bucket		varchar(100) 
    , @s3Folder		varchar(100) = null
    , @type			varchar(30) = 'FULL'
	, @addTS		bit         = 0
	, @dbName		sysname		= null
	, @scriptOnly	bit = 0

)
AS
BEGIN

	/*
		-- =============================================
		-- Author:		Daniel Adeniji
		-- =============================================
	*/
    SET NOCOUNT ON;
	SET XACT_ABORT ON;


	declare @tblDatabase TABLE
	(
		  [id] smallint not null
			identity(1,1)

		, [name] sysname not null
	)

	declare @tblDatabaseSkip TABLE
	(
		  [id] smallint not null
			identity(1,1)

		, [name] sysname not null

		, UNIQUE NONCLUSTERED 
			(
				[name]
			)
	)

	declare @id smallint
	declare @idMax smallint
	declare @db    sysname

	declare @s3arnBase			   varchar(30)
	declare @s3arn				   sysname
	declare @overwriteS3BackupFile int

	declare @CHAR_BACKSLASH        char(1)
	declare @CHAR_DASH			   char(1)
	declare @CHAR_COLON			   char(1)
	declare @CHAR_PERIOD		   char(1)
	
	declare @CHAR_EXT_BAK		   varchar(10)
	declare @CHAR_EXT_LOG		   varchar(10)
	declare @charExt               varchar(10)

	declare @backupTypeFULL		   varchar(15)
	declare @backupTypeLOG         varchar(15)

	declare @dtNow				   datetime
	declare @dateFormat			   smallint
	declare @TSAsString			   varchar(30)

	declare @log				   varchar(300)

	set    @s3arnBase = 'arn:aws:s3:::'


	set    @CHAR_EXT_BAK = '.bak'
	set    @CHAR_EXT_LOG = '.log'

	set    @backupTypeFULL = 'full';
	set    @backupTypeLOG = 'log';

	set @CHAR_DASH = '-'
	set @CHAR_BACKSLASH = '/'
	set @CHAR_COLON = ':'
	set @CHAR_PERIOD = '.'


	set @overwriteS3BackupFile = 1

	set @dateFormat = 126

	if (@type = @backupTypeLOG)
	begin

		set @charExt = @CHAR_EXT_LOG 

	end
	else if (@type = @backupTypeFULL)
	begin

		set @charExt = @CHAR_EXT_BAK 

	end
	else
	begin

		raiserror('Unsupported @type', 16,1)

		return
	end

	/*
		List databases that we will be skipping
			System Databases
				a) master
				b) tempdb
				c) model
				d) msdb
	*/
	 insert @tblDatabaseSkip
	 (
		[name]
	 )
	 select 'tempdb'
	 union 
	 select 'msdb'
	 union 
	 select 'master'
	 union 
	 select 'model'
	 union 
	 select 'rdsadmin'


	if (@dbName is not null)
	begin

		insert into  @tblDatabase 
		(
			[name]
		)
		select
			@dbname

	end
	else
	begin

		/*

			Skip Databases that are offline

				a) databasepropertyex(name, 'collation')	
						is null when a db is offline

		*/
		insert into  @tblDatabase 
		(
			[name]
		)
		select   tblSD.name

		from   sys.databases tblSD

		where  not exists
				(
					select [name]
					from   @tblDatabaseSkip tblDS
					where  tblSD.[name] = tblDS.[name]
				)

		and   databasepropertyex
				(
					   tblSD.[name]
					, 'collation'
				) 
					is not null

	end

	/*
		Get Number of databases
	*/
	set @idMax
			=
				(
					select max(id)
					from    @tblDatabase 
				)

	/*
		reset pointer
	*/
	set @id = 1

	while (@id <= @idMax)
	begin

		/*
			Get contextual data
		*/
		select 
				@db = tblD.name
		from   @tblDatabase tblD
		where  tbLD.id = @id

		if (@addTS = 1)
		begin

			set @dtNow = getdate()

			set @TSAsString = convert(varchar(30), @dtNow, @dateFormat)
			set @TSAsString = replace(@TSAsString, @CHAR_DASH, '')
			set @TSAsString = replace(@TSAsString, @CHAR_COLON, '')
			set @TSAsString = replace(@TSAsString, @CHAR_PERIOD, '')

		end

		/*
			Get computed data
		*/
		set  @s3arn =  @s3arnBase 
						+ @s3Bucket 
						+ case
							when @s3folder is null then ''
							when @s3folder = '' then ''
							else @CHAR_BACKSLASH + + @s3folder 
							end
							
						+ @CHAR_BACKSLASH
						+ @db
						+ case
							when (@addTS = 1) 
								then '__' + @TSAsString
							else ''
						  end	
						+ @charExt

		/*
			issue backup command
				note that backup is not completed, only issued
		*/
		set @log = 's3arn :-' + @s3arn

		print @log

		if (
				   ( @scriptOnly = 0)
				or ( @scriptOnly is null)
		   )
		begin

			exec msdb.dbo.rds_backup_database 
				  @source_db_name= @db
				, @s3_arn_to_backup_to=@s3arn
				, @overwrite_S3_backup_file = @overwriteS3BackupFile
				, @type = @type

		end

		/*
			prepare for next record
		*/
		set @id = @id + 1

	end


END
GO



dbo.usp_RDSDBBackupFullAutomated



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if object_id('[dbo].[usp_RDSDBBackupFullAutomated]') is null
begin

    exec('create procedure [dbo].[usp_RDSDBBackupFullAutomated] as ')

end
go

ALTER PROCEDURE [dbo].[usp_RDSDBBackupFullAutomated]
AS
BEGIN


    SET NOCOUNT ON;

    declare  @s3Bucket  varchar(100) 
    declare  @s3Folder  varchar(100) 
    declare  @type	    varchar(30)
	declare  @addTS		bit

    set @s3Bucket = 'dbabackupmssql'
    set @s3Folder = 'full'
    set @type = 'FULL'
	set @addTS = 1

    exec [dbo].[usp_RDSDBBackupCore]
              @s3Bucket = @s3Bucket
            , @s3Folder = @s3Folder
            , @type = @type
	    , @addTS = @addTS


END
GO





Scheduler

Invoke Scheduler and scheduled job that calls the  dbo.usp_RDSDBBackupFullAutomated Stored Procedure.

Job Properties -Tab – General

 

Job Properties -Tab – Steps

 

Review

SQL Server

Stored Procedure

Stored Procedure – msdb.dbo.rds_task_status

Script

declare @dbName sysname

exec msdb.[dbo].[rds_task_status]
       @db_name = @dbName

Output

 

S3

Access S3 Service and the specific folder and you should see the files.

 

Source Control

GitHub

DanielAdeniji/SQLServerBackup.AWS
Link

SQL Server – Linked Server – TroubleShooting – SysInternals / Process Monitor

Background

Though better minds would disagree, I am lured into running OLE/DB providers out of process when configuring Linked Servers.

 

SysInternals/Process Monitor

For one, it is a bit easier to debug and troubleshoot via SysInternals/Process Monitor.

 

Filter

Here is what we will be filtering on…

Image

Explanation

  1. Process Name
    • is
      • dllhost.exe

 

Capture

Image

Explanation

  1. sqlservr.exe
    • TCP*
      • TCP Accept
      • TCP Receive
      • TCP Send
  2. DLLhost.exe
    • Process Create
    • Thread Create
  3. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • combase.dll
      • sechost.dll
      • rpcss.dll
  4. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( OLE-DB )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32.dll
      • C:\Windows\System32\MSDART.dll
      • C:\Windows\System32\ole32.dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32r.dll
  5. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • C:\Windows\System32\comsvcs.dll
      • C:\Windows\System32\authz.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
  6. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDB12.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\VERSION.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDBgmr12.dll
      • OCI
        • E:\app\oracle\client\product\12.2.0\client_1\bin\OCI.dll
        • C:\Windows\System32\OCI.dll
        • E:\app\oracle\client\product\12.2.0\client_1\oci.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCP120.dll
  7. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows – MSVCP* )
    • dll
      • Visual C++ Redistributable Packages for Visual Studio 2013
        • C:\Windows\System32\msvcp120.dll
        • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCR120.dll
  8. Oracle.key
    • E:\app\oracle\client\product\12.2.0\client_1\bin\Oracle.Key
  9. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
        • The oraociei12.dll file is the main binary for Instant Client
      • E:\app\oracle\client\product\12.2.0\client_1\oraons.dll
        • Oracle Basic Instant Client & Oracle Basic Light Instant Client
  10. Oracle Key & OLEDB DLLS
    • E:\app\oracle\client\product\12.2.0\client_1\Oracle.Key
    • E:\app\oracle\client\product\12.2.0\client_1\Oraoledbic12.Dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDB12us.dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDBpus12.dll
  11. Oracle Network
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\oraaccess.xml
    • C:\Windows\System32\mswsock.dll
      • MS Windows WinSock
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\sqlnet.ora
    • E:\app\oracle\client\product\12.2.0\client_1\log\diag\clients
    • C:\Windows\System32\SHCore.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\intchg.ora
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnav.ora
    • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnames.ora
    • E:\app\oracle\client\product\12.2.0\client_1\NETWORK\mesg\tnsus.msb
  12.  Network
    • TCP Reconnect
      • :

        -> [destination/hostname]:[destination/port-number (1521) ]

  13. Oracle Client Logs
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace\sqlnet.log
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\incident
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\metadata
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\alert\log.xml
  14. Network
    • TCP Send/Receive/Disconnect
      • [destination/hostname]:[destination/port-number] ->

        :

  15. Thread Exit/Process Exit/Close File

 

 

Oracle Log files

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_####_###\trace

Image

Textual


***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.2.0.1.0 - Production
Time: 21-FEB-2018 10:46:59
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: Message 12535 not found; No message file for product=NETWORK, facility=TNS
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Message 505 not found; No message file for product=NETWORK, facility=TNS
nt secondary err code: 60
nt OS err code: 0
Client address: 

Tabulated

  1. Errors Recorded
    • Main
      • Fatal NI connect error 12170
    • Detail
      • TNS-12535
        • ns secondary err code :- 12560
        • nt main err code :- 505
      • TNS-00505
        • ns secondary err code :- 60
        • nt OS err code :- 0

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_##\alert\log.xml

Image

Explanation

XML file which contains error messages from the Oracle Connectivity Client.

 

Summary

Our error is “Fatal NI connect error 12170” and that translates to “ORA-12170: TNS:Connect timeout occurred“.

Basic firewall issues.

Though DCOM itself as a container does not expose a lot of errors, client applications themselves are free to do so.

Using SysInternals’ Process Monitor, we are able to quickly familiarize ourselves with our OLE-DB Provider.

 

References

  1. Native Instruments
    • Native Instruments > General > OS and Computer Tuning
      • Windows Error Message: Missing MSVCP120.dll File
        Link
  2. Oracle Technology Network / Database / Database Features / Oracle Call Interface
    • Oracle Instant Client ODBC Release Notes
      Link
  3. Burleson Consulting
    • Donald Burleson
      • fatal ni connect error 12170
        Link

WordPress – Post – Convert to PDF Document

Background

Recently I wrote a document using WordPress.

Really should have used Google Docs as the document is really too big for WordPress.

Besides, Google Docs has much nicer and accessible Convert to other document formats options.

 

Too late

Too late to cry over spilled milk.

Besides, it is only Monday and like Bangles Tuesdays are my fun day.

 

Convert

Would rather have the document as Doc or Docx, as in Word for Windows.

But, for now having as a PDF will get me halfway.

 

Convert To PDF

Options

Here are some options for converting the document to PDF.

  1. printFriendly
    • Web Site
      Link
    • Chrome Extension
      • Print Friendly & PDF
        Link
  2.  wk<html>topdf
    • Links

 

Indepth

printfriendly – web site

Steps
  1. Please go here
  2. Enter the URL you will like captured
Screen Shots

print Friendly & PDF – Chrome Extension

Install
  1. Launch Chrome
  2. Access Extension
    • Visit here 
    • Or google for “print friendly chrome extension
Screen Shots

 

 

wk<html>topdf

Script – MS Windows

set "_folderApp=C:\Program Files\wkhtmltopdf\bin"
set "_appName=wkhtmltopdf.exe"

set "_appNameFull=%_folderApp%\%_appName%"

set "_url=http://biblehub.com/numbers/11-15.htm"
set "_title=c:\temp\linopen.pdf"

"%_appNameFull%" %_url%  %_title%

Dedicated

Dedicating to Traffic Generation Cafe, as they pointed me to the PrintFriendly.com

How to Convert Blog Post Into PDF
Link

What makes PrintFriendly so awesome?

Link

 

SQL Server – Linked Server – TroubleShooting

Background

There are a variety of avenues for diagnosing Linked Server related connectivity issues.

 

Follow Up

As a quick follow up to earlier conversations on SQL Server and heterogeneous data sources:

  1. SQL Server / Linked Server – Oracle ODAC
    Link

 

Outline

Here are some of the tools for diagnosis heterogenous data related issues:

  1. Query
    • DBCC
      • Trace Flag 7300
  2. SQL Server Profiler
  3. Event Viewer
  4. SysInternals
    • Process Explorer

 

 

Query

DBCC

Trace Flag 7300

Query
Syntax

dbcc traceon(7300) 
	with no_infomsgs
	;
go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample

dbcc traceon(7300) 
	with no_infomsgs
	;
go

declare @linkedServer sysname

set @linkedServer = 'HRDB';

exec sp_tables_ex
		@table_server = @linkedServer

go

dbcc traceoff(7300)
	with no_infomsgs
	;
go


Sample – Grid
Sample – Textual

OLE DB provider "OraOLEDB.Oracle" for linked server "PRD" returned message "ORA-12170: TNS:Connect timeout occurred".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 4]
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "PRD".


Explanation
  1. Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message
      • “ORA-12170: TNS:Connect timeout occurred”.
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  2. Using DBCC TraceOn (7300)
    • Allows us to see that we are experiencing connection timeout to the Oracle Server
    • Likely means network or TNS configuation error

 

 

SQL Server Profiler

Trace Properties

Trace Event Selections

Here are the events that we will be tracing on…

Event Category Event Event Description
Errors and Warnings
ErrorLog  Indicates error events logged in the SQL Server error log.
EventLog  Indicates events logged in the Windows application event log.
Exception  Indicates that an exception has occurred in SQL Server.
Execution Warnings  Indicates any warnings that occurred during the execution of a SQL Server statement or stored procedure.
User Error Message  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
OLEDB
OLEDB Call Event Includes event classes produced by the execution of stored procedures.
OLEDB Data Read Event Displays OLE DB IRowset::GetData calls made by SQL Server for fetching row data for distributed queries and remote stored procedures.
OLEDB Errors Indicates that an OLE DB error has occurred.
OLEDB Provider Information Occurs when a distributed query is run and collects information corresponding to the provider connection. This event class contains all the properties collected from the remote provider using various property sets such as DBPROPSET_DATASOURCEINFO, SQLPROPSET_OPTHINTS, DBPROPSET_SQLSERVERDATASOURCEINFO (SQL Server only), DBPROPSET_SQLSERVERDBINIT (SQL Server only) and DBPROPSET_ROWSET and interface IDBInfo.
OLEDB Query Interface Event Displays OLE DB IUnknown::QueryInterface calls made by SQL Server for distributed queries and remote stored procedures.
Security Audit
Audit Login Collects all new connection events since the trace was started, such as when a client requests a connection to a server running an instance of SQL Server.
Audit Logout Collects all new disconnect events since the trace was started, such as when a client issues a disconnect command.
Stored Procedures
RPC:Completed Occurs when a remote procedure call has been completed.
TSQL
SQL:BatchCompleted Occurs when the Transact-SQL statement has completed.
SQL:BatchStarting Occurs when a Transact-SQL batch is starting.

 

 

Traces

Sample Traces

Sample Trace #01
Image – Full Conversation

Image – OLEDB Call Event
Explanation
  1. SQL:BatchStarting
    • Issues sp_tables_ex
    • Query Linked Server asking for a listing of tables
  2. OLEDB QueryInterface Event
    • IID_IDBProperties
      • <ppunk>0x0000003C47F941D8</ppunk>
        • Set ppunk to 0x0000003C47F941D8
  3. OLEDB Call Event
    • Property
      • DBPROP_INIT_TIMEOUT
      • DBPROP_INIT_GENERALTIMEOUT
      • DBPROP_INIT_DATASOURCE
      • DBPROP_AUTH_USERID
      • DBPROP_AUTH_PASSWORD
    • Result
      • hresult
        • 265946
          • ???
  4. OLEDB QueryInterface Event
    • IID_IDBInitialize
      • input
        • IID_IDBInitialize
      • hresult
        • 0
      • ppunk
        • <ppunk>0x0000003C47F941E0</ppunk>
          • returns 0x0000003C47F941E0
    • IID_ISSAsynchStatus
      • input
        • IID_ISSAsynchStatus
      • hresult
        • -2147467262
  5. OLEDB Errors
    • hresult
      • -2147467259
  6. OLEDB Query Interface Event
    • input
      • IID_ISupportErrorInfo
    • hresult
      • 0
    • outputs
      • 0x00000044686EBFC0
  7. OLEDB Call Event
    • input
      • IID_IDBInitialize
    • hresult
      • 0
  8. User Error Message
    • OLE DB provider “OraOLEDB.Oracle” for linked server “PRD” returned message “ORA-12170: TNS:Connect timeout occurred“.
  9. Exception
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.
  10. User Error Message
    • Cannot initialize the data source object of OLE DB provider “OraOLEDB.Oracle” for linked server “PRD”.

 

Event Viewer

Security

Filter

Tabulate

Courtesy of SANS.org:

SANS Institute
InfoSec Reading Room
Windows Logon Forensics
Link

Event  ID = Windows XP / Windows 2000 Windows 2008/2012/2014, etc Event Description
 528 4624 Successful logon: A user successfully logged on to a computer. For
information about the type of logon, see the next section
 529 4625  Logon failure. A logon attempt was made with an unknown user name
or a known user name with a bad password. For Windows 2008 and
above, event ID 4625 logs every failed logon attempt with failure status
code regardless of logon type or type of account
530 4625 Logon failure for a logon attempt to log on outside of the allowed time
 531 4625  Logon failure for a logon attempt using a disabled account.
 532 4625  Displays the error message as seen by the user in the case or an error or exception. The error message text appears in the TextData field.
 533  4625 Logon failure. A logon attempt was made by a user who is not allowed
to log on at this computer.
 534 4625 Logon failure. The user attempted to log on with a type that is not allowed.
 535 4625 Logon failure. The password for the specified account has expired.
 536 4625 Logon failure. The Net Logon service is not active
 537 4634 Logon failure. The logon attempt failed for other reasons. In some
cases, the reason for the logon failure may not be known.
 538 4634 The logoff process was completed for a user.
 538/551 4647 A user initiated the logoff process. It is logged for Interactive and
RemoteInteractive logons in place of logoff event 538/4634.
 539 4625 Logon failure. The account was locked out at the logon
 540 4624 Successful network logon: A user successfully logged on over a
network.
 552 4648 A user successfully logged on to a computer using explicit credentials while already logged on as a different user
 638 4778 A user has reconnected to a disconnected terminal session.
 683 4625 A user disconnected a terminal session without logging off.

 

Image

 

Sample
Sample 01
Image – Top

Image – Bottom
Explanation
  1. Security ID
    • NULL SID
  2. Logon Type
    • Logon Type 3 is Network
  3. Impersonation Level
    • Impersonation
  4. New Logon
    • Security ID
      • LABdadeniji
    • Account Name
      • dadeniji
    • Account Domain
      • LAB
  5. Network Information
    • Workstation Name
      • DADENIJI
    • Source Network Address
      • w.x.y.z
    • Source Network Port
      • #####
  6. Detailed Authentication
    • Logon Type
      • NtLmSsp
    • Authentication Package
      • NTLM
    • Package Name (NTLM only)
      • NTLM V2
    • Key Length
      • 128

SysInternals

Process Explorer

Application Context

OLE-DB Provider can be configured to run in process or out of process.

If run in process we need to track the sql server service.

When ran out of process, we need to track via dllhost.exe

Sample

Sample – 01
Image

Explanation
  1. dllhost.exe
    • COM Class
      • MSDAINITIALIZE Class
      • c:program filescommon filessystem32ole dboledb32.dll
      • OLEDB Core Services

tasklist

Preface

The OLE-DB Provider can either be configured to run as in-process or out-of process.

When in process it runs within the sqlservr.exe address space.

When out of process, it runs within a surrogate process, dllhost.exe.

out of Process – dllhost.exe

syntax

tasklist /m /fi "Imagename eq dllhost.exe"

Output
Output # 01

Explanation
  1. We can see that Oracle dlls are loaded within the dllhost.exe

 

References

  1. Microsoft
    • Microsoft Developer
      • SQL BI / Data Access Technologies
        • Snehadeep
          • Troubleshooting “Cannot create an instance of OLE DB provider”
            Link
    • CSS SQL Server Engineers
      • pssql
        • How to get up and running with Oracle and Linked Servers
          Link
  2. Gianluca Sartori
    • Setting up linked servers with an out-of-process OLEDB provider.
      Link
  3. Sans Institute
    • Windows Login Forensics
      Link