sp_send_dbmail and the dreaded error code "-2147467259"

There are various reasons for this error code when trying to send emails from SQL Server. Most of the resolution folks have posted are related to security and permissions. 

That was not in my case, i had to do numerous iterations to get the sequence of the parameters right. Below is how i sequenced the parameters for the procedure:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DB_MAIL',
                                     @recipients = @Recipients,
                                     @subject = @Subject,
                                     @body = @body,
                                     @execute_query_database = 'mydb',
                                     @query_result_separator = @tab,
                                     @query_result_header = 1,
                                     @query_result_width = 32767,
                                     @query_result_no_padding = 1,
                                     @attach_query_result_as_file = 1,
                                     @query_attachment_filename = @FileName,
                                     @query = @QueryTxt;

Arithmetic overflow error converting numeric to data type numeric

I had encountered a while ago and had completely forgotten how the CONVERT(DECIMAL(p,s), 'hopefully some number') works.

Arithmetic overflow error converting numeric to data type numeric

Basically in DECIMAL(p,s), p (precision) equals to the number of digits on left of the decimal point and to the right hand side as well. s (scale) is the number of digits you need after the decimal point (right hand side)

Example: SELECT CONVERT(DECIMAL(10, 6), '2003201.8561484918793503') will return this error has the total number of digits to the left and right hand side to the decimal is of course more than 10 (which is our value of p)

For successfully converting the given value to decimal with 6 digits to the right is when p = 23

SELECT CONVERT(DECIMAL(23, 6), '2003201.8561484918793503') gives 2003201.856148

Google is my friend

ITIL Framework

ITIL Framework

Msg 8623, Level 16, State 1, Line 1

Msg 8623, Level 16, State 1, Line 1
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

SQL Server: Database principal owns a schema in the database and cannot be dropped

Got this error today when i was playing around with login and user created selecting different options.

I had check db_datareader and db_datawriter in OwnedSchemas tab in Security >> Logins of the database server. This was causing the error and the fix was to transfer the schema ownership to a different login so we can drop the user we were trying to.

ALTER AUTHORIZATION ON SCHEMA::db_datareader to whome;
ALTER AUTHORIZATION ON SCHEMA::db_datawriter to whome;

Hope this helps someone and Google is my friend 😎

Testing Oracle trigger firing before or after transaction control statements

create table vsk_test1 (col1 varchar2(100));

create table vsk_audit1 (audit1 varchar2(100));

CREATE OR REPLACE TRIGGER VSK_TEST1_TRG 

BEFORE INSERT OR DELETE OR UPDATE of col1 ON VSK_TEST1 

REFERENCING OLD AS old NEW AS new 

FOR EACH ROW 

BEGIN

  insert into vsk_audit1 (audit1) values (systimestamp || :new.col1);

END;

insert into vsk_test1 (col1) values ('aaaaaaaaaaa');

rollback;

select * from vsk_test1;

select * from vsk_audit1;

drop table vsk_test1;

drop table vsk_audit1;

Find day, date, etc in SQL Server

I am not the original author of these queries and have found them while surfing as usual when looking for a quick solution.

First Day Of Current Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)

First Day Of Last week
select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)

First Day Of Next Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)

First Day Of Current Month
select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

First Day Of Last Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)

First Day Of Next Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)

First Day of Last Year
select CONVERT(varchar,dateadd(year,datediff(year,0,getdate())-1,0),106)

First Day Of Current Year
select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)

First Day Of Next Year
select CONVERT(varchar,dateadd(YEAR,DATEDIFF(year,0,getdate())+1,0),106)

Last Day Of Current Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

Last Day Of Last Week
select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)

Last Day Of Next Week
select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)

Last Day Of Current Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)

Last Day Of Last Month
select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)

Last Day Of Next Month
select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)

Last Day Of Current Year
select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)

Last Day Of Last Year
select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate()),0))),106)

Last Day Of Next Year
select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+2,0))),106)

BizTalk Artifacts Monitoring

I have to create jobs to monitor send ports, orchestrations and receive locations and this post will be update as i make progress.

Below is a list of things to remember and consider when creating these monitoring jobs:

• All BizTalk databases should not be considered as our usual databases and extreme caution should be practices when using them
• BizTalk installation has 4 databases BizTalkMsgBoxDb, BizTalkMgmtDb, BizTalkTrackingDb and SSODB
• These databases are dependent on each other and data is moved between them 
• Check on using WITH (READPAST): excludes locked rows from result set

To Be Cont....

SELECT TOP from @VAR and DISTINCT TOP @VAR

Simple and useful:

SELECT TOP (@MAX_REC_COUNT)
from DBO.MY_TABLE WITH (NOLOCK);

This way we can get the @max_rec_count from a config table instead of hard coding the record count to retrieve. This is useful when you can modify the config table from a UI instead of making code changes.

I wish there was a better way to get DISTINCT TOP @VAR from a query but unfortunately no better way than:

SELECT TOP @MAX_REC_COUNT 
FROM (
           SELECT DISTINCT COL1, COL2, ...COLN
           from DBO.MY_TABLE WITH (NOLOCK)
          ) XX;


HTH

Database and Instance in Oracle and SQL Server

http://channel9.msdn.com/posts/SQL-Server-Essentials-for-Oracle-DBAs-01-Database-and-Instance

Good and very informational video. Hope they don't mind me sharing knowledge :)

SP_HELPDB

sp_helpdb function returns information about that databases in the particular database server. Below is an example:

SP_EXECUTESQL expects NTEXT/NCHAR/NVARCHAR

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Pretty straight forward error, just change the datatype of the variable you are using to assign the dynamically generated string.

Hope this helps....

PL/SQL Collections

This is one of those topics i used to dread when i started out as a database developer, not anymore. But still from time to time i need to refresh my memory when it comes to collections:

I recently started reading a book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" and it describes collections in such a way that i have to make a post of it. It is precise, with an example and perfect read for me:

Below are a few pointers that i usually like to remember:

Associative Arrays (TABLE OF)

• AKA Index by tables
• Of course use an index by BINARY_INTEGER or PLS_INTEGER or VARCHAR2
• Sparse collection
• Need not be consecutive
• Unbounded - no upper boundary
• Collection is extended by assigning values to non existing index values
• Data can be deleted
• Start with FIRST method to access data

Nested Tables (TABLE OF)

• No index
• Unbounded colleciton
• Dense collection when creating collection
• Data can be deleted
• NEXT and PRIOR (?) helps access collection elements

VARRAYS (VARRAY OF)

• No index
• Bounded collection so cannot be extended above the specified limit
• Data cannot be deleted
• Dense data collection
• Consecutive data
• Start with FIRST method to access data

Methods that can be used with collections. Not all methods can be used with all collections:

• EXISTS - exists(v) - returns boolean
• DELETE - delete, delete(v1) and delete(v1,v8) - removes data
• COUNT - returns number
• TRIM - trim and trim (v) - removes data
• LIMIT - returns number
• EXTEND - extend, extend(v1) and extend(v1,v8) - adds NULL elements
• FIRST - returns index of first element
• LAST - returns index of last element
• PRIOR - prior(v) - returns index of prior element
• NEXT - next(v) - returns index of next element

Hope this helps....

Quick Tip #4

To see what SQL a session is running in the database, find the SPID for that user using sp_who2 and then pass it as an input to the dbcc command.


sp_who2

dbcc inputbuffer (:SPID)

NULL SELF Argument Is Disallowed - Oracle

ORA-30625: method dispatch on NULL SELF argument is disallowed 

Cause: A member method of a type is being invoked with a NULL SELF argument. 
Action: Change the method invocation to pass in a valid self argument.


Well i was trying to parse an XML with multiple namespaces and was using EXISTSNODE function. When traversing between nodes and subnodes i forgot to remove a subnode name and the XPATH formed was incorrect:

    l_xmltype := xmltype (l_doc) ;
    l_index := 1;
    v_count := l_xmltype.existsnode (
    'ns1:NewSubscriptionNotificationRequest/subscriptionInfo/subscriptionKey/subscriptionInfo [' || to_char (l_index
    ) || ']', v_namespaces);
    dbms_output.put_line ('------' || v_count);
    --
    WHILE l_xmltype.existsnode (
    'ns1:NewSubscriptionNotificationRequest/subscriptionInfo [' || TO_CHAR (l_index
    ) || ']', v_namespaces) > 0
    LOOP
        l_value := l_xmltype.extract (
        'ns1:NewSubscriptionNotificationRequest/subscriptionInfo [' || to_char (
        l_index) || ']/duration/text()', v_namespaces) .getstringval () ;
        dbms_output.put_line ('-------------> ' || l_value) ;
        --
        l_index := l_index + 1;

    END LOOP;

This error is same as the evil NULL POINTER EXCEPTION in JAVA....

Just correcting the subnode path (remove subscriptionInfo from EXISTSNODE) to represent right structure helped resolve this problem.

Many thanks to A_Non On XML for his detailed explanation and examples on this blog about XML parsing. I hope he does not mind a well deserved praise.

PLSCOPE_SETTINGS in Oracle

Today i was working on an object creation script and had a trigger for audit columns like date inserted, date updated etc...

I constantly kept getting the error below and on further research found out a workaround from one of the blogs (thank you fellow blogger):


Error report:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 -  "ORACLE server session terminated by fatal error"
*Cause:    An ORACLE server session is in an unrecoverable state.
*Action:   Login to ORACLE again so a new server session will be created
Failed to resolve object details 

Workaround: ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

By default the value is IDENTIFIERS:ALL 
The database default value is NONE and later found out it was SQL Developer setting.


After altering the session the trigger was compiled without any problems....hope this 
helps.

PL/SQL Compiler Setting in SQL Developer

Google is my friend :)

SYS_CONTEXT in Oracle

I know there is a functions to get user environment details in Oracle along with other valuable information for auditing purposes in your application. But i always tend to forget it, not anymore

SYS_CONTEXT (NAMESPACE, PARAMETER)       <<<< LOOK MA I AM PINK

Example: select sys_context('USERENV', 'HOST') from dual;

SET DEADLOCK_PRIORITY HIGH

Incase you are expecting a DEADLOCK in an environment where your process/job is going to run executing DML statements and you are fully aware that the process take priority over all other processes/jobs, you can set its priority to HIGH.

This specifies the rank/priority of your process higher than any other process when deadlock is detected.

Deadlock priority is set before the TRY block in the procedure.

Hope this helps....

Error: CREATE/ALTER PROCEDURE must be the first statement in a query batch

I had not come across this error before so was a new one for me. 

declare @cmd varchar(200)
set @cmd = 'sp_rename myproc1, myproc'
exec @cmd
GO

create procedure myproc (input_id int)
as
begin
  if input_id = 0
  begin
    print 'i am in begin block #2'
  end
end

The GO indicates end of batch or acts as a batch separator was missing from the script and as a result the error was generated. Once i added the GO statement, the script executed like a happy little monster....

Hope this helps....

DATEADD and DATEDIFF

I always try to remember these functions but for some reason end up going to MSDN to find the actual function name and syntax.

DATEADD (datepart , number , date )
select DATEADD (mm, 1, getdate())

DATEDIFF ( datepart , startdate , enddate )
select DATEDIFF (hh, getdate(), '2013-04-16 00:11:11.00')

Table below is from MSDN, just to you everyone an overview of possible option to use 
with these functions (along with any other DATE functions)

    

   
datepart
   
abbreviations
  
   
year
   
yy,   yyyy
  
   
quarter
   
qq,   q
  
   
month
   
mm,   m
  
   
dayofyear
   
dy,   y
  
   
day
   
dd,   d
  
   
week
   
wk,   ww
  
   
hour
   
hh
  
   
minute
   
mi,   n
  
   
second
   
ss,   s
  
   
millisecond
   
ms
  
   
microsecond
   
mcs
  
   
nanosecond
   
ns
  

Hope this helps and as always good is my friend....