SQL Server – Upgrade – “It is not possible to change the SQL Server features to be upgraded in this release”

Background

Trying to upgrade the SQL Server Instance, but kept shooting blanks.

 

Blanks

SQL Server v2005 to v2014

Upgrade to SQL Server 2014

Shared Features

Textual

It is not possible to change the SQL Server features to be upgrade in this release.

Image

Select Features

Textual

There are validation errors on this page.  Click OK to close this dialog box. Review errors at the bottom of the error page, then provide valid parameters or click Help for information.

There are no features selected for upgrade.

TroubleShooting

Looked everywhere on this one.

But, then thought back to a few months past.

 

SQL Server Instance

Version Info

SQL


select @@version

Output

Image

Textual

Microsoft SQL Server 2005 – 9.00.5000.00 ( Intel X86 )
Dec 10 2010 10:56:29
Express Edition on Windows NT 6.1 ( Build 7601: Service Pack 1)

 

Tabulate

  1. Version :-
    • We are running Microsoft SQL Server v2005
  2. Platform :- Intel x86
  3. Edition :- Express Edition
  4. OS :- Windows NT 6.1 ( Build 7601 : Service Pack 1 )
    • MS Windows 2008/R2
    • Service Pack 1

 

32 Bit?

It appears that we are running 32-bit SQL Server

Control Panel

Services
Image

Textual
  1. path to executable :- C:\Program Files (x86) \XDM-SQLExpress\MSSQL.1\MSSQL\Binn\sqlservr

 

Explorer

Folder :- C:\Program files (x86) \ XDM-SQLExpress – MSSQL.1 \ MSSQL \ Data
Image

Explanation

Confirmed that our files are in the “C:\Program Files x86” folder

Remediation

  1. Prepare to re-Install
    • Preserve SQL Server data and log files
      • Take SQL Server Services offline and copy files over to safe location
      • Backup databases
    • Capture SQL Server Logins
  2. Uninstall Microsoft SQL Server v2005
  3. Install Microsoft SQL Server v2014
    • Our OS, MS Windows 2008-R2, only let us go up v2014
    • Apply MS SQL Server v2014 – SP2 and Cumulative Updates
  4. Restore
    • Re-create SQL Server Logins
    • Re-attach data & log files

Summary

Sometimes simple SQL Server Upgrades take on a life of their own.

Office365 – Email Connectivity Testing through Mozilla Thunderbird

Background

Earlier this weekend got an email that analysts were no longer receiving emails that we had subscribed them to via Microsoft Reporting Services.

Troubleshooting

Log Files

Checked the various log files ( Reporting Services, IIS SMTP).

BTW, we are using IIS SMTP as a Smarthost Relay.

The logs were not illuminating.

 

Mozilla Thunderbird

Tried the same configuration with slight variations.  Actually many more times than I can remember.  And, definitely much more than I will admit.

 

Google

That chick named Google is your friend and so looked for how to configure email clients to use Office 365.

Found some good leads, but nothing worked out.

Configuration

Process

Here is the process we undertook to configure Mozilla Thunderbird to use Microsoft Office 365.

  1. Launch Mozilla Thunderbird
  2. Choose the Account
    • Right click on the Account you have selected
    • And, from the dropdown menu, choose “Settings
  3. Account Settings
    • The “Account Settings” window appears
    • At the bottom of the “Account Settings” window we will observe the “Account Actions” panel
    • One of the choices available in the “Account Actions” panel is the “Add Mail Account” option
    • Please choose the “Add Mail Account” option
  4. “Mail Account” Setup
    • The “Mail Account Setup” window appears
    • Initial Screen
      • It is prefilled with your current system’s full name
      • Please enter your full email address
      • And, password
      • Once entered, please press the Continue button
    • Suggested Configuration
      • Based on the email address entered, Mozilla retrieves the domain name
      • The registered DNS Provider for the domain name is contacted
      • And, asked for MX record
      • Using the MX record, the servers registered for Mail are then contacted
      • If the Mail providers are able to provide mail registration data, communication is started with them
    • Manual Configuration
      • Please click on the manual config button to review or adjust configuration data

Images

Mail Account Setup
Access Account Configuration

Account Settings

Account Settings

Mail Account Setup – 01

Mail Account Setup – 02

Mail Account Setup – Looking up Configuration – Email Provider

Mail Account Setup – Looking up Configuration – Email Provider – Configuration found in Mozilla ISP database

Mail Account Setup – Manual Configuration

Configuration Results

Flow Protocol Server hostname Port SSL Authentication
Incoming IMAP imap-mail.outlook.com 993 SSL/TLS Normal password
Outgoing SMTP smtp-mail.outlook.com 587 STARTTLS Normal password

 

Network Monitoring

Microsoft Network Monitor

Once we were satisfied that we were able to establish communication between our host and the Mail Server, we used Microsoft Network Monitor to review the traffic.

Here is the filters we employed and what we captured via “Microsoft Network Monitor“.

Filter

Textual

ProcessName.Contains(“thunder”)

Image

Network Conversations

 

References

  1. Microsoft
    • Technet
      • Network Monitor Conversation Filtering
        Link

Summary

A good solution was so far away until we discovered that Mozilla Thunderbird is able to communicate with the Provider and request configuration data.

SQL Server – Scripting Database Mail Configurations

Background

Need to make sure that a couple of SQL Server Instances have the same configuration.

 

Sample Code

Sought out sample code on the Net and found this one:

Mohammad Abdul Majeed
Script to Script out EXISTING database mail settings
Link

Code

Outline

What are we scripting…

  1. Database Mail Components
    • Mail Profile
    • Mail Account
    • Mail Profile & Account binding

 

Stored Procedures

email.sp_DatabaseMailSetupScript

Script


use master
go

/*

    email.sp_scriptDatabaseMailSetup

*/

if schema_id('email') is null
begin

    exec('create schema [email] authorization [dbo]');

end
go

if object_id('[email].[sp_scriptDatabaseMailSetup]') is not null
begin

    drop procedure [email].[sp_scriptDatabaseMailSetup]

end
go

if object_id('[email].[sp_DatabaseMailSetupScript]') is null
begin

    exec('create procedure [email].[sp_DatabaseMailSetupScript] as print ''shell'' ')

end
go

alter procedure [email].[sp_DatabaseMailSetupScript] 
(
      @profile  sysname = null
    , @account  sysname = null
)
as
begin

    /*

        Referenced Work

        a) Mohammad Abdul Majeed
           Script to Scipt out EXISTING database mail settings
           https://gallery.technet.microsoft.com/scriptcenter/Script-to-Scipt-out-14a19eda

    */

    set nocount on;
    set XACT_ABORT on;

    declare @CHAR_TAB               varchar(10)
    declare @CHAR_SPACE             varchar(10)
    declare @CHAR_COMMA             varchar(10)
    declare @CHAR_NEWLINE           varchar(10)
    declare @CHAR_NULL              varchar(10)
    declare @CHAR_QUOTE             varchar(10)
    declare @CHAR_ZERO              varchar(10)
    declare @CHAR_COMMENT           varchar(10)
    
    declare @MAIL_PROFILE_CREATE        nvarchar(600)
    declare @MAIL_ACCOUNT_CREATE        nvarchar(4000)
    declare @MAIL_PROFILE_ACCOUNT_BIND  nvarchar(600)

    declare @CHAR_BEGIN             varchar(10)
    declare @CHAR_END               varchar(10)

    set @CHAR_TAB = char(9);
    set @CHAR_NEWLINE = char(13) + char(10);

    set @CHAR_SPACE = ' ';
    set @CHAR_COMMA = ',';
    set @CHAR_NULL = ' null';
    set @CHAR_QUOTE = ''''
    set @CHAR_ZERO = 0
    set @CHAR_COMMENT = '--';

    set @CHAR_BEGIN = 'begin';
    set @CHAR_END = 'end';

    /*
        exec msdb.dbo.sysmail_help_profile_sp
    */
    set @MAIL_PROFILE_CREATE 
            = 'if not exists ( SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''%s'' ) '
                + @CHAR_BEGIN   
                + @CHAR_TAB
                + ' EXECUTE msdb.dbo.sysmail_add_profile_sp '   
                + @CHAR_TAB + @CHAR_TAB + '   @profile_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @description = ''%s'' '   
                --+ @CHAR_TAB
                + @CHAR_END             

    -- use [msdb]; exec sp_help 'dbo.sysmail_add_account_sp' 
    -- exec [msdb].dbo.sysmail_help_account_sp
    set @MAIL_ACCOUNT_CREATE 
            = 'if not exists ( SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''%s'' ) '
                + @CHAR_BEGIN       
                + @CHAR_NEWLINE 
                + ' EXECUTE msdb.dbo.sysmail_add_account_sp '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_SPACE + '@account_name = ''%s'' '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@email_address = ''%s'' '  
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@display_name  = ''%s'' '  
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@replyto_address  = ''%s'' '
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@description  = ''%s'' '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@mailserver_name  = ''%s'' '       
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@mailserver_type  = ''%s'' '   

                /*
                    Error: 50000, Severity: -1, State: 1. (Params:). 
                    The error is printed in terse mode because there was error during formatting. 
                    Tracing, ETW, notifications etc are skipped.  
                */
                --+ @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@port  = ''%s'' '        
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@port  = %d '  
            
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@username  = %s '  
            
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@use_default_credentials  = %d '   
                + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@enable_ssl  = %d '    

                /*
                    + @CHAR_NEWLINE + @CHAR_TAB + @CHAR_TAB + @CHAR_COMMA + '@account_id = %d ' 

                */

                + @CHAR_NEWLINE 
            
                + @CHAR_END 

    -- exec msdb..sysmail_configure_sp
    set @MAIL_PROFILE_ACCOUNT_BIND 
            = 'if not exists '
                + ' ( '
                + ' SELECT * '
                + ' FROM   msdb.dbo.sysmail_profileaccount tblSMPA '
                + ' inner join  msdb.dbo.sysmail_profile tblSMP '
                + '       on tblSMPA.[profile_id] = tblSMP.[profile_id] '
                + ' inner join msdb.dbo.sysmail_account tblSMA '
                + '       on tblSMPA.[account_id] = tblSMA.[account_id] '
                + ' WHERE tblSMP.[name] = ''%s'' '
                + ' and   tblSMA.[name] = ''%s'' '
                + ' ) '
                + @CHAR_BEGIN   
                + @CHAR_TAB
                + ' EXECUTE  msdb.dbo.sysmail_add_profileaccount_sp  '  
                + @CHAR_TAB + @CHAR_TAB + '   @profile_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @account_name = ''%s'' '  
                + @CHAR_TAB + @CHAR_TAB + ' , @sequence_number = %d '   
                --+ @CHAR_TAB
                + @CHAR_END     

    SELECT 
               = 'Mail Profile'
            , [profileID] = tblSMP.[profile_id]
            , [profileName] = tblSMP.[name]
            , [description] = tblSMP.[description]
            , [profileModifiedBy] = tblSMP.[last_mod_user]
            , [profileModifyDate] = tblSMP.[last_mod_datetime]
            , [script] = FORMATMESSAGE
                            (
                                  @MAIL_PROFILE_CREATE
                                , tblSMP.[name] -- profile name
                                , tblSMP.[name]  -- profile name
                                , isNull(tblSMP.[description], '') -- profile description
                            )

    FROM   msdb.dbo.sysmail_profile tblSMP

    where  tblSMP.[name] = case

                                when ( @profile is null) then tblSMP.[name]
                                when ( @profile = '') then tblSMP.[name]
                                else @profile
                           end       

    -- select * from msdb.dbo.sysmail_server 
    SELECT 
               = 'Mail Account'
            , [accountID] = tblSMA.[account_id]

            , [account] = tblSMA.[name]
            , [emailAddress] = tblSMA.[email_address]

            , [displayName] = tblSMA.[display_name]
            , [replyToAddress] = tblSMA.[replyto_address]

            , [description] = tblSMA.[description]

            , [mailServerName] = tblSMS.[servername]
            , [mailServerType] = tblSMS.servertype
            , [mailServerPort] = tblSMS.[port]

            , [useDefaultCredentials] = tblSMS.use_default_credentials  
            

            , [accountModifiedBy] = tblSMA.[last_mod_user]
            , [accountModifyDate] = tblSMA.[last_mod_datetime]

            , [script] = FORMATMESSAGE
                            (
                                  @MAIL_ACCOUNT_CREATE
                                , tblSMA.[name] -- account name
                                , tblSMA.[name]  -- account name
                                , isNull(tblSMA.[email_address], @CHAR_SPACE) 
                                , isNull(tblSMA.[display_name], @CHAR_SPACE) 
                                , isNull(tblSMA.[replyto_address], @CHAR_SPACE) 
                                , isNull(tblSMA.[description], @CHAR_SPACE) 
                                , isNull(tblSMS.servername, @CHAR_SPACE) 
                                , isNull(tblSMS.servertype, @CHAR_SPACE)
                                , isNull(tblSMS.[port], @CHAR_SPACE)
                             
                                , case
                                    when tblSMS.[username] is null then @CHAR_NULL
                                    else @CHAR_QUOTE + tblSMS.[username] + @CHAR_QUOTE
                                  end

                                  /*
                                    Msg 2748, Level 16, State 1, Line 132
                                    Cannot specify bit data type (parameter 11) as a substitution parameter.
                                  */
                                , case 
                                    when tblSMS.[use_default_credentials] is null then @CHAR_ZERO
                                    else cast(tblSMS.[use_default_credentials] as tinyint)
                                  end
                                    
                                , case

                                    when tblSMS.[enable_ssl] is null then @CHAR_ZERO
                                    else cast(tblSMS.[enable_ssl] as tinyint)
                            
                                  end       

                                /*

                                    , case

                                        when tblSMA.[account_id] is null then @CHAR_ZERO
                                        else tblSMA.[account_id]
                            
                                      end                               

                                */
                                 
                            )

    FROM   msdb.dbo.sysmail_account tblSMA

    LEFT OUTER JOIN msdb.dbo.sysmail_server tblSMS
     
            ON tblSMA.account_id = tblSMS.account_id 

    where  tblSMA.[name] = case

                                when ( @account is null) then tblSMA.[name]
                                when ( @account = '') then tblSMA.[name]
                                else @account
                           end  
                            
    SELECT 
               = 'Mail Profile Account Binding'
            , [profileID] = tblMPA.profile_id
            , [profile] = tblSMP.[name]

            , [accountID] = tblMPA.account_id
            , [account] = tblSMA.[name] 

            , [sequenceNumber] = tblMPA.[sequence_number]
            , [script] = FORMATMESSAGE
                            (
                                 @MAIL_PROFILE_ACCOUNT_BIND

                                , tblSMP.[name] -- profile name
                                , tblSMA.[name] -- account name

                                , tblSMP.[name] -- profile name
                                , tblSMA.[name] -- account name
                             

                                  /*
                                    Msg 2748, Level 16, State 1, Line 132
                                    Cannot specify bit data type (parameter 11) as a substitution parameter.
                                  */
                                , case 
                                    when tblMPA.sequence_number is null then @CHAR_ZERO
                                    else cast(tblMPA.sequence_number as tinyint)
                                  end
                                 
                            )

    FROM msdb.dbo.sysmail_profileaccount tblMPA 
    
    INNER JOIN msdb.dbo.sysmail_profile tblSMP 
        ON tblMPA.profile_id = tblSMP.profile_id 
    
    INNER JOIN msdb.dbo.sysmail_account tblSMA
        ON tblMPA.account_id = tblSMA.account_id

    where  tblSMP.[name] = case

                                when ( @profile is null) then tblSMP.[name]
                                when ( @profile = '') then tblSMP.[name]
                                else @profile
                           end  

    and  tblSMA.[name] = case

                                when ( @account is null) then tblSMA.[name]
                                when ( @account = '') then tblSMA.[name]
                                else @account
                           end  

end
go


 

Invoke



declare @profile sysname
declare @account sysname
 
exec [email].[sp_DatabaseMailSetupScript] 
          @profile = @profile
        , @account = @account


Output

 

Source Control

Github

  1. Project
    • DanielAdeniji/SQLServerDatabaseMailConfiguration
      Link
  2. Files
    • email.sp_DatabaseMailSetupScript.sql
      Link

 

Dedicated

Dedicated to Mohammad Abdul Majeed

Church in Society ( 2017-Oct )

Background

Again, this is an area, I will rather not go.

Yet, it is one that stirs our conscience and thoughts.

And, that is reason enough to see what people are saying and why they are saying it.

Predication

Introduction

We all have our own bias and depositions.

And, it is best to lay them bare ahead of time.

Most of my comments will be based on what I have heard from others prior to anything that I will cover in this post.

It is often useful to have some foundational predisposition as that lessens the opportunity to be swayed within each decision point.

Even better when commentators are not even speaking specifically on the new material.

Earlier Words

  1. Damon Thompson
    • Sometimes God places something in our heart, but unfortunately so much  of what has gone out in the Prophetic World was not undergirded in scripture
    • And, so I, Damon Thompson, need the foundation of Scripture
  2. Neil Ellis
    • This is a Church of matured faith
    • You have been preaching, but you were fearful of how the preaching will be accepted
    • Sometimes the Devil magnifies things and makes them appear more than they really are
    • Sometimes as a pastor what makes us press even though there is “Hell on the Outside” is that we are satisfied that we have the confidence of the people inside
    • Can the Lord trust you, and can the Bishop/Leadership depend on you?
    • Free your Spirit, but challenge your people
    • A faith that has not been tested, is a faith that can not be trusted
  3. Sonia Sotomayor, Associate Justice of the Supreme Court of the United States
    • Sotomayor was asked to explain her remarks from a 2001 speech in which she said she agreed that “there is no objective stance but only a series of perspectives — no neutrality, no escape from choice in judging.”  She told the committee that, in every case, the two opposing parties view the facts from vastly different perspectives. “You can’t just throw up your hands and say I’m not going to rule,” she said. There is a choice in judging, which means “you have to rule.”

Videos

Here are some of the videos that I will later comment on.

  1. Paula White
    • Trump’s spiritual adviser: More devout than you…
      Donald Trump’s spiritual adviser, televangelist Paula White, spoke to CNN’s Erin Burnett in August about the Republican presidential nominee’s faith and the phone call that kicked off their friendship.
      Channel :- CNN
      Published On :- 2016-Oct-20th
      Link
    • Inauguration
      • Meet The Woman Who Many Call President Elect Donald Trump’s Spiritual Adviser | NBC Nightly News
        Critics have slammed her as a charlatan, but in a rare interview with NBC’s Anne Thompson, Paula White defends her message and gives insight into the president-elect’s beliefs.
        Channel :- NBC News
        Published On :- 2017-Jan-19th
        Link
    • Jim Baker
      • Two Corinthians’ Gaffe Was ‘A Setup
        • RWW News: Paula White Says That Trump’s ‘Two Corinthians’ Gaffe Was ‘A Setup’
          Published On :- 2017-August-23th
          Link
      • Opposition to God
        • RWW News: Paula White Says Opposition To President Trump Is Opposition To God
          Published On :- 2017-August-21st
          Link
  2. Pastor Jamal Bryant
    • Pastor Jamal Bryant – The problem with Paula White…
      Published On :- 2017-August-25th
      Link
  3. Roland Martin
    • Roland Martin To Paula White: Be A Prophetic Voice And Don’t Just Be A Profitable Voice
      Roland Martin rips Pastor Paula White for her partisan comments about Donald Trump: Be a prophetic voice and don’t just be a profitable voice.
      Published On :- 2017-August-23th
      Link

 

Leadership

Seated Under

Those in leadership do not always get to take a day off.

Not only does what they think matter, but also how they got there.

I have grown from listening in person or via youtube to the people listed above.

And, that makes me forever grateful.

Influencing Capital

The further we grow, the more influencing capital we personally have and have access to.

After long and hard battles our role will be examined; especially if our role is seen to have moved the needle towards an unexpected end.

Unfortunately it is not just our choice that is examined, but the totality of our live.

Circumference of Relationships

Relationships often covers so much space.

And, some of those spaces overlap and are not necessarily disjointed.

And, so even within a body, for instance Faith community, uniformity does not necessarily translate to conformity.

People sometimes reach different conclusions and they should be free to do so.

This is more so where there are personal relationships with those that are being discussed.

 

In Essentials Unity, In Nonessentials Liberty, In All Things Charity

Link
Philip Schaff, the distinguished nineteenth-century church historian, calls the saying in our title “the watchword of Christian peacemakers” (History of the Christian Church, vol. 7, p. 650). Often attributed to great theologians such as Augustine, it comes from an otherwise undistinguished German Lutheran theologian of the early seventeenth century, Rupertus Meldenius. The phrase occurs in a tract on Christian unity written (circa 1627) during the Thirty Years War (1618–1648), a bloody time in European history in which religious tensions played a significant role.

 

Sola Scriptura

  1. Laws of Social Responsibility
    If ever you take your neighbor’s cloak in pledge, you shall return it to him before the sun goes down, for that is his only covering, and it is his cloak for his body; in what else shall he sleep? And if he cries to me, I will hear, for I am compassionate.
    You shall not curse God, nor curse a ruler of your people. ( Exodus 22:27-28 )
  2. Paul Before the Sanhedrin
    • But those standing nearby said, “How dare you insult the high priest of God!”
      “Brothers, Paul replied, “I was not aware that he was the high priest, for it is written: ‘Do not speak evil about the ruler of your people.’”
      ( Acts 23:4-5 )
  3. David, Saul, Jonathan, & Mephibosheth
    • And Mephibosheth the son of Saul came down to meet the king. He had neither taken care of his feet nor trimmed his beard nor washed his clothes, from the day the king departed until the day he came back in safety. ( 2nd Samuel 19:24 )
    • He answered, “My lord, O king, my servant deceived me, for your servant said to him, ‘I will saddle a donkey for myself, that I may ride on it and go with the king.’ For your servant is lame. ( 2nd Samuel 19:26 )
    • He has slandered your servant to my lord the king. But my lord the king is like the angel of God; do therefore what seems good to you. ( 2nd Samuel 19:27 )
    • For all my father’s house were but men doomed to death before my lord the king, but you set your servant among those who eat at your table. What further right have I, then, to cry to the king?” ( 2nd Samuel 19:28 )
    • And the king said to him, “Why speak any more of your affairs? I have decided: you and Ziba shall divide the land.” ( 2nd Samuel 19:29 )
    • Mephibosheth said to the king, “Let him even take it all, since my lord the king has come safely to his own house.” ( 2nd Samuel 19:30 )

Summary

In summary I find myself always returning to a couple of words :-

  1. Shaykh Abdal Hakim Murad
    • Allah’s hand of protection is with the maintenance of unity, you should beware of division.
    • The one isolated from the group is a prey to Satan, just as the one isolated from the flock is a prey to the wolves
    • You can not base a spiritual life on suspiciousness, cursing and allocating blame.
  2. Abraham Lincoln
    • I have been driven many times upon my knees by the overwhelming conviction that I had nowhere else to go. My own wisdom and that of all about me seemed insufficient for that day.”

Closing

Love never fails … for we know in part and we prophesy in part.

Google Docs – Filters and Filter Views

Background

Microsoft Excel has a very nice interface that allows the user to show just the data that needs to be projected for different scenarios.

In Microsoft Excel 2010 the functionality is exposed through the “Filter” toolbar.

 

Google Docs

Let us examine whether Google Docs exposes a similar functionality and if so how and how well it does.

Spreadsheet

Looked on the Internet for sample Excel Spreadsheets.

Found one from Tableau.

It is called Sample – Superstore Sales (Excel).xls and it is available at community.tableau.com.

The specific URL is Link.

 

Import

We downloaded the sample data, accessed Google Docs, and imported the data.

 

Imported Data

Here is what the imported data looks like.

OpenedFile_20171009_0927PM

 

Filtering Data

There are two types of Filters required.

One is a Filter and the other is a Filter View.

We will touch on how to create each type of Filter and later on the differences between them.

Filter

Outline

To filter the data, please take the following steps:

  1. Select a range of cells.
  2.  Click Data and then Filter (  )
  3. To see filter options, go to the top of the range and click 
    • Filter by condition: Choose from a list of conditions or write your own.
    • Filter by values: Uncheck any data points that you want to hide and click OK.
  4. To clear Filter
    • Please click the menu items Filter / “Turn off Filter

Image

Access Filter Menu

Filtering By Customer Name – Begin

Filtering By Customer Name – Filtering

FilterByCustomerName_20171010_0824AM

 

Filter Views

Outline

To filter the data, please take the following steps:

  1. Create New Filter View
    • Repeat the steps of creating a new Filter
      • Select a range of cells.
      •  Click Data and then Filter (  )
      • To see filter options, go to the top of the range and click 
        • Filter by condition: Choose from a list of conditions or write your own.
        • Filter by values: Uncheck any data points that you want to hide and click OK.
    • Create New Filter View
      • Please choose the menu item Data/Filter Views
      • In the Name Bar, please give a Meaningful and Relevant Name
    • Customize the Filtered View
      • Customize the Filtered View as you see fit
      • In our case, we chose a specific Customer
  2. Capture and Share URL
    • Please capture and share the URL for the customized Filtered View
    • With a customized URL, each user has a specific and tailored view of the data
  3. Close Filter View
    • To close your filter view, go to the top right and click Close.
  4. Delete Filter View
    • To delete or duplicate a filter view go to the top right and click Settings Settings and then Delete or Duplicate.

 

Image

Filter By Column – Customer Name
Filter By Column – Customer Name – Initiate – Andrew Gjertsen

Filter By Column – Customer Name – Completed – Andrew Gjertsen

Filter Views Maintenance

Filter versus Filter View

Item Filter Filter View
Sharing Mechanism Send URL or List Send URL for List View
Sample URL Link Link
Multiple Instances Supported No Yes
Maintenance Supported Turn off filtering Naming, Rename, Duplicate, and Delete

 

Screen Shot

Filter

Filtered View

Summary

I wish I could have explained this far better.

But, nevertheless, if you have need to share Excel Type Sheets within a group and you will like tailored view of data, please consider using Google Docs.

It’s Filtered View functionality offers an accessible, polished, uncluttered, and fluid interface.

References

  1. support.google.com
    • Sort and Filter your data
      Link
  2. Sites.Google.com
    • Working with Data
      Link

 

SQL Server Agent – Message – “Failed to notify…”

Background

I have been tending to a job that often fails, and wanted to add email notification, for the last couple of weeks.

It is bad that enough that the job itself fails, but worse no email.

 

Monday Morning

Monday Morning is often a good day of the week to make use of another carefree weekend and see where the error lies.

Error Message

Image

 

Textual

Message
The job succeeded. The Job was invoked by Schedule 25 (Every Day). The last step to run was step 12 (Say Completed). NOTE: Failed to notify ‘Daniel Adeniji’ via email.

 

TroubleShooting

SQL Server Agent Error Log

here is what is logged in log file for SQL Server Agent.

Please note we are referencing SQL Server Agent log files, and not SQL Server Engine.

Image

Textual

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail

 

Remediate

Access SQL Server Agent Properties and set SQL Mail…

GUI

Unset

When Unset

Here is how things look before configuring SQL Server Agent Mail Profile

 

Set

Us setting SQL Server Agent Mail Profile

Script

Read SQL Server Agent Properties

Code


declare @useDatabasemail     int
declare @DatabaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @useDatabasemail OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @DatabaseMailProfile OUT
        , @no_output = N'no_output'

exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'UseDatabaseMail'
        , @param = @AgentMailType OUT
        , @no_output = N'no_output'



--NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
EXEC master.sys.xp_instance_regread 
          'HKEY_LOCAL_MACHINE'
        , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
        , N'Start'
        , @ServiceStartMode OUTPUT



--declare @ServiceAccount nvarchar(512)
EXEC master.sys.xp_instance_regread
           'HKEY_LOCAL_MACHINE'
         , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
         , N'ObjectName'
         , @ServiceAccount OUTPUT


exec master.dbo.xp_instance_regread 
              N'HKEY_LOCAL_MACHINE'
            , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
            , N'AGTGroup'
            , @AgtGroup OUTPUT

select 
          [@use_databasemail] = @useDatabasemail
        , [@DatabaseMailProfile] = @DatabaseMailProfile      
        , [@AgentMailType] = @AgentMailType
        , [@ServiceStartMode] = @ServiceStartMode
        -- NOTE: When setting the the services start value, 2 == auto-start, 3 == Don't auto-start
        , [@ServiceStartModeLiteral] 
                = case  
                        when @ServiceStartMode=2  then 'Auto Start'
                        when @ServiceStartMode=3  then 'Don''t Auto Start'
                  end   
        , [@ServiceAccount] = @ServiceAccount
        --, [@AgtGroup] = @AgtGroup

Output

Set SQL Server Agent Properties

Code


USE [msdb]
GO

set nocount on
set XACT_ABORT on
go

declare @sqlInstance                sysname
declare @databaseMailProfileDesired sysname

declare @FORMAT_MESSAGE_SET         nvarchar(600)
declare @message                    nvarchar(600)

declare @databaseMailProfile nvarchar(255)
declare @AgentMailType       int
declare @ServiceStartMode    int
declare @ServiceAccount      nvarchar(512)
declare @AgtGroup            nvarchar(512)

declare @commit              bit

set @commit =1
set @sqlInstance = cast(SERVERPROPERTY('servername') as sysname)
set @databaseMailProfileDesired = N'DBAMail'

set @FORMAT_MESSAGE_SET = 'On %s, setting database mail profile to %s'

/*
    Access Registry (SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent') 
        a) Read Database Mail Profile
*/
exec master.dbo.xp_instance_regread 
          N'HKEY_LOCAL_MACHINE'
        , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
        , N'DatabaseMailProfile'
        , @param = @databaseMailProfile OUT
        , @no_output = N'no_output'

print '@DatabaseMailProfile :' + isNull(@DatabaseMailProfile, '')

begin tran
    

    if  
        ( @DatabaseMailProfile is null )
    begin

        exec master.dbo.xp_sprintf      
                  @message output
                , @FORMAT_MESSAGE_SET
                , @sqlInstance
                , @databaseMailProfileDesired

        print @message

        /*
            Set SQL Sql Server Agent Properties
        */
        EXEC msdb.dbo.sp_set_sqlagent_properties 
                  @email_save_in_sent_folder=1
                , @databasemail_profile=@databaseMailProfileDesired
                , @use_databasemail=1
                , @alert_replace_runtime_tokens=1


    end

while (@@TRANCOUNT > 0)
begin

    if (@commit = 1)
    begin

        print 'commit tran'

        commit tran

    end
    else
    begin

        print 'rollback tran'

        rollback tran

    end
end

GO

Output

Reset SQL Server Agent Properties

To revert changes, please run the script below.


USE [msdb]
GO

EXEC msdb.dbo.sp_set_sqlagent_properties
          @email_profile=N'' 
        , @databasemail_profile=N''
        , @use_databasemail  =0
GO


Listening

Listening to …

Jazmine Sullivan – In Love With Another Man
Link