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....