sp_send_dbmail XML attachment line break issue

I was initially working on creating an event notification that sends me an email alert for our dba team making it work under in a tsql procedure

so once they receive email alert they can open dead lock graph directly from the email account only if ssms is installed on the machine

 

First I tried some thing like this below then I noticed


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ – Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20′
, @attach_query_result_as_file = 1

Then I realized that output text is getting truncated to 256 charecters then I used @query_no_truncate = 1 to avoid the truncation


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ – Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20′
, @attach_query_result_as_file = 1
,@query_no_truncate = 1

Once I opened the xml attachment file I noticed that there are some weird line breaks for about every 256 characters then I realized that I might need to use @query_result_width with default of 256 with max of 32767


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ – Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20′
, @attach_query_result_as_file = 1
,@query_result_width = 32767
,@query_no_truncate = 1

seems to work as my xml output doesnt seem to exceed 32767 characters

 

dead lock graph email attachment

 

actual dead lock graph

Fix: invalid common user or role name ORACLE 12C

In four steps will fix the issue

Error at Command Line:16 Column:13
Error report:
SQL Error: ORA-65096: invalid common user or role name

Determine the current container

SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM   dual;
SYS_CONTEXT(‘USERENV’,’CON_NAME’)
——————————————————
CDB$ROOT
List all the pluggable databases

select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
PDB$SEED                       READ ONLY
PDBORCL                        MOUNTED
PDB need to open read write atleast once to inegrate into CDB

ALTER PLUGGABLE DATABASE PDBORCL OPEN READ WRITE;
pluggable database is altered
Now PDB is ready to be used we just need to change the current session

ALTER SESSION SET CONTAINER = PDBORCL
Use this query to make sure you are in right container
SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM dual;
 
CREATE USER sqljunkie IDENTIFIED BY sqljunkie DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

user SQLJUNKIE created.


GRANT CREATE PROCEDURE
, CREATE session
, CREATE TABLE
, CREATE type
, CREATE VIEW
, CREATE SYNONYM
, CREATE TRIGGER
, RESOURCE TO sqljunkie;
Once we opened database it runs as its own service we can verify this
lsnctl service modified
 if you are planning to connect using TNSnaming service make sure you have something like below in tnsnames.ora
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
pdb connection
there we go

One of the best SQL Server Index Internals: A Deep Dive

its from Tim Chapman, one of the best index internal presentation

I will try to highlight the things he talk through this presentation

Indexes

  • Clustered vs Non clustered and filtered indexes
  • Non clustered indexes and include column effect on index structure
  • Heap
  • Heap vs Clustered
  • Various DMV utilization to understand index internals
  • How to detect fragmentation
  • Logical fragmentation and effects on query perfromance
  • Page splits , Page density, page fullness

Statistics

  • How to view statistics
  • Effect of statistics on query performance
  • effect of stats from index rebuild and reorg

Log usage

  • Index rebuild vs reorg
  • Log usage for Index rebuilds and reorganize

Over all must watch

 

How to resolve suspect Database issue (Emergency Repair) SQL Server

There are several reasons why database goes into suspect mode, but best way to resond that I found very helpfull

1) Set the database to emergencey mode

ALTER DATABASE ‘db name’ SET EMERGENCY

2) Set the database to Single user mode so to do dbcc repair

ALTER DATABASE DAXBI SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

3) Run dbcc repair command

DBCC CHECKDB (N’db name’, REPAIR_REBUILD ) WITH ALL_ERROMSGS, NO_INFOMSGS;

that will take care of any consistency errors with indexes and partitions

if you want the database to be back online not worried about the data loss

DBCC CHECKDB (‘db name’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERROMSGS, NO_INFOMSGS;

4) Set databsae to multi user mode

ALTER DATABASE ‘db name’ SET MULTI_USER

Unspecified Error SSIS Excel connection manager

When you create a data flow with an Excel destination and click ‘Name of Excel Sheet’ I get an “Unspecified Error”.  When you look under ‘Show Advanced Editor’ of the destination you will see following erros

Error at blah [Connection manager ""]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: “Microsoft JET Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”. Error at Insert blah [Destination – blah [199]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “DestinationConnectionExcel” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

To Fix this three easy steps

First step:

please make sure you have this files

C:\windows\system32\odbcjt32.dll

C:\windows\system32\msjet40.dll

C:\windows\system32\msexcl40.dll

C:\Program Files\Common Files\System\Ole DB\oledb32.dll

C:\Program Files\Common Files\System\ado\msado15.dll

 

Second Step

If they are, please manually register them.  Run each of the following commands from command prompt:

Regsvr32 “C:\Program Files\Common Files\system\Ole DB\oledb32.dll”

Regsvr32 “C:\Program Files\Common Files\system\ado\msado15.dll”

Regsvr32 “C:\windows\system32\msjet40.dll”

Regsvr32 “C:\windows\system32\msexcl40.dll”

 

Out put should look like below do this for all the commands above

Unspecified error ssis excel

 

Step three : Restart Visual studio developer studio

 

 

 

DMV : sys.dm_exec_connections

Returns information about the connections established to this instance of SQL Server and the details of each connection.

Column name Data type Description
session_id int Identifies the session associated with this connection. Is nullable.
connect_time datetime Timestamp when connection was established. Is not nullable.
num_reads int Number of packet reads that have occurred over this connection. Is nullable.
num_writes int Number of data packet writes that have occurred over this connection. Is nullable.
last_read datetime Timestamp when last read occurred over this connection. Is nullable.
last_write datetime Timestamp when last write occurred over this connection. Not Is nullable.
client_net_address varchar(48) Host address of the client connecting to this server. Is nullable.

sys.dm_exec_connections

–Client wich has more than one connection

SELECT  client_net_address ,COUNT(*) number_of_connections

FROMsys.dm_exec_connections

GROUP BY client_net_address

HAVING  COUNT(*)> 1

ORDER  BY number_of_connections desc

–Oldest connection to server

SELECT  session_id,connection_id,connect_time FROMsys.dm_exec_connections

ORDER  BY connect_time asc

–Connection with highest network utilization

SELECT  num_reads,num_writes FROMsys.dm_exec_connections

ORDER  BY num_reads DESC,num_writes DESC