SQL Server – Identify Indexed Views

Background

Wanted to identify Indexed Views and took a look at sys.views to see if it exposes a guiding attribute.  But, No.

Code

Looked on the Net and found a good track.

Outline

  1. Look for views in sys.views
  2. Perform an inner join against sys.indexes

SQL

select 

          [object]
            = tblSS.[name]
              + '.'
              + tblSV.[name]

        , [index]
            = tblSI.[name]

        , [indexType]
            = tblSI.[type_desc]

from   sys.views tblSV

inner join sys.schemas tblSS

    on tblSV.schema_id = tblSS.schema_id

inner join sys.indexes tblSI

    on tblSV.object_id = tblSI.object_id

order by
        [object]
      , [index]

Transact SQL – User defined error messages

Background

Quickly, how to identify user defined error messages.

Guide Post

Here is the Link :-

Docs / SQL / Relational databases / System stored procedures
sp_addmessage (Transact-SQL)
Link

@msgnum = ] msg_id
Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647.

Explanation

To defined a new error, we have to specify a message id greater than 50001.  Or pass along null, and an id greater than 50001 will be assigned to us.

Code

Outline

  1. sys.messages
    • messsage_id >=50001

SQL


select
          tblSM.[message_id]

        , tblSM.[language_id]

        , tblSM.[severity]

        , tblSM.[is_event_logged]

        , tblSM.[text]

        , [customized]
            = case
                when (tblSM.[message_id] >= 50001) then 'Y'
                else 'N'
               end	

from   sys.messages tblSM

where  tblSM.[message_id] >= 50001

Jan Markell :- Prophecy – “Damage has been done”

Background

Lately the Headlines has been anything but good.

Whether it be Br-exit, Federal Government Shutdown in the US, Los Angeles School Teachers on Strike, and on and on.

The Church

The Church which is supposed to provide a strong and stable voice has not done much better.

Jan Markell

Jan Markell offers her a quick summary on the divergent voices as it relates to interpreting the end time scenarios mentioned in the Book of Revelations.

Be forewarned, she names names; not for the sake of doing so but as textural proof that these are not just hypothetical, nuanced arguments, but ones that are researched, publicly documented, and argued.

Videos

  1. Jan Markell on the Mockery of Bible Prophecy
    • Videos
      • Jan Markell on the Mockery of Bible Prophecy
        Profile :- Why do people mock Bible prophecy? Find out with speaker Jan Markell on the show Christ in Prophecy.
        Video :- Christ In Prophecy
        Published on :- 2018-Oct-10
        Link

In depth

Jan Markell on the Mockery of Bible Prophecy

  1. Burden
  2. Name Names
    • I am going to have name some names
    • May not be fully happy with some of the names that I have to name
  3. Last day madness
    • These are Christians talking
    • They call it last Day Madness
  4. Pastor Tom Hughes
    • Church
      • 412 Church
      • Location
        • San Jacinto, California
      • Branch Of
        • Calvary Chapel
      • Where does 412 come from
        • Acts 4:12
          • Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.
        • Ephesians 4:12
          • To equip his people for works of service, so that the body of Christ may be built up
        • Hebrews 4:12
          • For the word of God is quick, and powerful, and sharper than any twoedged sword, piercing even to the dividing asunder of soul and spirit, and of the joints and marrow, and is a discerner of the thoughts and intents of the heart.
    • Why the Church rejects prophecy
      • Points
        • 1 — They don’t understand it.
        • 2 — They fear offending members of the congregation.
        • 3 — People will be scared.
        • 4 — People might not tithe if they think we’re close to the end.
        • 5 — Fear of looking like the loony-tune fringe.
  5. Dispensationalism
    • Decline in Dispensationalism
    • Literal Tribulation
    • Pre-tribulations
      • She advocates the pre-trib position
    • Israel end time role
    • Timing
  6. Destructive Theology
    • Amilleanialism
      • Everything is Literal
    • Kingdom Now Theology
      • If we work hard enough, we can make the world better
      • If we bring about Social Justice, things will be good
      • How is that working out?
    • Preterism
      • All bible prophecy happened in 70 AD
      • Everything done
    • Replacement Theology
      • Church is the new Israel
    • Palestinian Zionism
      • Jesus was a Palestinian, and not Jewish
  7. Attacks on what we believe
    • Zombie Attacks
      • Christian Today
        • Quotes
          • Why a zombie attack is more probable than the apocalypse
          • Last day madness
          • Jan Markell – End times hysteria conference
      • Scriptural Basis
        • Pastor Paul Begley
          • Standing
            • “Do not be amazed at this, for a time is coming when all who are in their graves will hear his voice and come out – those who have done what is good will rise to live, and those who have done what is evil will rise to be condemned.” ( John 5:28-29 )
    • Jan Markell
      • Last Day madness
    • Rapture?
      • Friends of Israel
        • What ever happened to the rapture
      • Frequent Email
        • Olive Tree Ministries
          • Where can I find a church that does not marginalize Eschatology
          • 1/3 of the Bible is prophetic
      • Date Setters
        • Edgar Whisenant
          • Books
            • 88 Reasons Why the Rapture Will Be in 1988
            • On Borrowed Time

Glossary

  1. es·cha·tol·o·gy (/ˌeskəˈtäləjē/ )
    • The part of theology concerned with death, judgment, and the final destiny of the soul and of humankind.

References

  1. Christian Theology
    • Supersessionism
      • Wikipedia
        • Supersessionism ( Wikipedia )
          Link
  2. Christian Escathology
    • Eschatology
      • Eschatology ( Wikipedia )
        Link
    • Amillennialism
      • Wikipedia
        • Amillennialism ( Wikipedia )
          Link
    • Preterism
      • Wikipedia
        • Preterism ( Wikipedia )
          Link
  3. Christian Today
    • Harry Farley
      • Zombie Apocalypse and the end times: Did the Bible foretell modern life?
        Link
  4. Tom Hughes
    • Eric Barger
      • Five reasons pastors don’t teach bible prophecy
        Link
    • Videos
  5. The Inquisitr
    • Is This The End Of Days? Pastor Claims Bible Predicts Zombie Apocalypse
      Link
  6. End of Days
    • joanna AT JFPenn.com
      • End of Days predictions
        Link
  7. Edgar Whisenant

 

Tom Krazit :- Why some open-source companies are considering a more closed approach

Background

There is a topic that I have been following in the last few months.

And, it is how well Cloud Service Providers are protecting the Open Source Ecosystem.

Writing for GeekWire, here is Tom Krazit update from  2018-Nov-19th.

 

Link

At the peak of its power, open-source software is having an existential crisis.

There’s no question that the concept of open-source software has revolutionized the enterprise software world, which spent billions of dollars fighting the mere idea for several years before accepting that a new future had arrived. But more than a few people are starting to wonder if the very nature of open-source software ― the idea that it can be used by pretty much anyone for pretty much anything ― is causing its developers big problems in the era of distributed cloud computing services.

Since we last explored this topic, two prominent open-source software companies have made the decision to alter the licenses under which some of their software is distributed, with the expressed intent of making it harder ― or impossible ― for cloud computing providers to offer a service based around that software.

Two companies does not a movement make. But as the cloud world packs its bags for Las Vegas and Amazon Web Services’ re:Invent 2018 conference next week, underscoring that company’s ability to set the agenda for the upcoming year, the intersection between open-source projects and cloud computing services is on many people’s minds.

“The way that I would think of it, the role that open source plays in creating commercial opportunities has changed,” said Abby Kearns, executive director of the open-source Cloud Foundry Foundation. “We’re going to see a lot more of this conversation happening than less.”

Changing of the guard

“I would put it in a very blunt way: for many years we were suckers, and let them take what we developed and make tons of money on this.”

Redis Labs CEO Ofer Bengal doesn’t mince words. His company, known for its open-source in-memory database, has been around for eight years, an eternity in the fast-changing world of modern enterprise software.

Cloud computing was very much underway in 2011, but it was still a tool for early adopters or startups that couldn’t afford to bet millions on servers to incubate a promising but unproven idea. Most established companies were still building their own tech infrastructure the old-fashioned way, but they were increasingly realizing that open-source software would allow them to build that infrastructure with open-source components in ways that were much more flexible and cheaper than proprietary packages from traditional enterprise software companies

AWS likes to insist that it is primarily focused on its customers, but that focus also allows it to see which technologies and services are gaining traction in order to offer similar services to its customers. It launched an AWS-managed version of the open-source version of Redis as a cloud service in 2013. Redis became quite popular during that period, and major companies like American Express, Home Depot, and Dreamworks built tech infrastructure using the database. Redis Labs offers its own product, Redis Enterprise, that provides the database as a service on public clouds or supports customers running it on their own infrastructure, and continues to contribute to the Redis open-source project.

Since then, AWS has made “hundreds of millions” of dollars offering Redis to its customers without contributing nearly as much to the open-source community that builds and maintain that project, Bengal said. It’s impossible to know exactly how much money we’re really talking about, but it’s certainly true that AWS and other cloud providers benefit from the work of open-source developers they do not employ.

“Ninety-nine percent of the contributions to Redis were made by Redis Labs,” Bengal said. There’s a longstanding myth in the open-source world that projects are driven by a community of contributors, but in reality, paid developers contribute the bulk of the code in most modern open-source projects, as Puppet founder Luke Kanies explained in our story earlier this year.

That money has to come from somewhere. For a long time, Redis was a good example of a successful open-source business model, shepherding the base project while developing its own software and services around it. But as more and more companies embrace cloud computing and “lift and shift” their existing applications and infrastructure to providers like AWS, it makes a lot of sense to use the AWS Redis service alongside all the other table-stakes AWS services like EC2 and S3 as opposed to a service offered by Redis through the AWS Marketplace.

“This is a problem not just for us but for almost any successful open-source project to date,” Bengal said. And while AWS is always the focus of these conversations due to its market power, it’s far from the only cloud provider around the world offering these types of open-source projects as services.

So in August, Redis Labs decided to convert the license it uses for new database extensions built on top of Redis — but not Redis itself — to the Commons Clause license, which specifies that other companies are not allowed to offer those extensions as a cloud service.

“We keep the freedom to decide for each piece of software whether to put that under the permissive open-source license or Commons Clause,” Bengal said. “This is basically a business decision.”

Then in October, another well-known open-source database company made a similar decision. MongoDB announced that going forward, it would license the MongoDB Community Server software under a different license called SSPL, which allows cloud providers to offer MongoDB as a service but requires them to either open-source all of the code they write to create that service or reach a commercial arrangement with MongoDB.

It’s not a coincidence that two database companies are leading this charge. Databases are extremely complicated projects to develop and absolutely vital to any enterprise company operating at scale.“Whenever a new open-source project becomes popular, cloud providers strip mine the technology, put the freeware on their platform, capture most if not all of the value but give little back to the community,” said Dev Ittycheria, president and CEO of MongoDB, currently valued at $4.3 billion on the Nasdaq. “We think it’s important for someone like us to lead and help the next set of open-source companies and projects thrive and grow.”

Ittycheria estimated that MongoDB had spent $150 million on research and development over the years to create and maintain the open-source version of the database. In its last fiscal year, MongoDB recorded $154.5 million from its commercial software and support services.

“Our view is that open-source software was never intended for cloud infrastructure companies to take and sell,” wrote Salil Deshpande, managing director of Bain Capital Ventures, in a post on Techcrunch after Redis announced its decision. Deshpande is an investor in Redis Labs and whose efforts to organize open-source companies and develop the Commons Clause license we previewed in March.

As open-source software became such an important part of the enterprise software world, it was probably inevitable that commercial interests would start to influence its direction more and more. The questions now are, what does it mean to be open source? And what do companies that generate revenue from offering services around open-source projects they did not develop owe to the creators and maintainers of those projects?

Open and shut

When considering the first question, it’s important to note that Redis’ Commons Clause license is decidedly not an open-source license, which everyone involved readily admits. Redis remains an open-source project under the widely used BSD license, but the company now applies the Commons Clause license to extensions it develops around that project.

With MongoDB, it’s a little different. Because the SSPL license stipulates that more open-source software should be the result when cloud providers offer an open-source project as a service, the company argues that this is consistent with the ethos of open source.

“Everyone wants more open source, but someone’s got to fund it. And to fund it you need to make sure you have a commercial existence,” Ittycheria said.

Adam Jacob, co-founder and CTO of Seattle’s Chef, which maintains a trio of open-source projects designed to make infrastructure and application management easier, was skeptical that this is the right direction for the future of open-source projects, developers, and companies.

“I don’t think it’s a community-based movement, for sure it’s not a movement in the way open source was a movement or that free software was a movement,” Jacob said. “It’s not like there’s a third-leg movement that’s like, ‘you know what we need is stronger commercial protections for businesses.’”
“The more interesting thing hiding inside here is that, from an open-source business model point of view, the open-source part of what they were doing was always a component of distribution for their business model. It was more about developer reach,” Jacob said, the primary goal being to create a “funnel strategy” that would draw in users with a free version and upsell them on the commercial version.After all, when they were getting off the ground and raising money companies like Redis and MongoDB were quite happy to take advantage of the friendly and open-minded nature of the open-source community to spread the word about their creations, he said.

Yet for a lot of small companies, participating in open-source communities and maintaining projects is the only way to compete against the more established players. One of the hardest things to do as a young unproven enterprise software company is to convince other companies they need to pay for your product.

“Open source offers a lot of opportunity, particularly for startup companies,” Kearns said. But at some point, that early strategic decision can become an albatross, she said; another really hard thing to do is to convince someone that what they once enjoyed for free now comes with a price.

Clouds on the horizon

This perhaps gets to the crux of the debate: is there still value in developing software out in the open as a community process that everyone can use, even if the days of the hobbyist open-source developer are long gone and cloud providers can take advantage of that work without having to contribute anything back?

Jacob thinks so.

“My belief is that what we have done wrong, we actually stopped trusting the commons and the value of free software as a necessity for building better communities because we decided very early on that the business and the community were two separate things,” he said.

Redis and MongoDB believe they aren’t throwing their open-source communities under the bus. They argue they have no choice but to find new ways to financially support the work of their developers, who are absolutely essential to the health of those communities, even if some of that work remains proprietary or comes with strings attached.

The big three cloud providers are keeping quiet on these issues for now, declining to make executives available to discuss this shift in open-source licensing. Google has evangelized the value of open-source since its earliest days, while Microsoft and AWS have taken different paths to this new world.

After years as Public Enemy #1, Microsoft has embraced open-source software, hiring developers with strong open-source experience and making some key contributions back to several communities. AWS has been slower to work closely with the open-source community, but has changed its tune over the last few years, bringing on open-source veterans like James Gosling and Adrian Cockcroft to shift its thinking about community contributions.

If more companies switch to this more aggressive style of licensing, it could force a shift in product-development strategies at cloud providers as they assess which services are worth striking commercial deals over, and which are not. For now, there are still lots of startups launching companies and projects around traditional open-source licenses, which means the industry is waiting to see how the market reacts to Redis and MongoDB’s moves.

But cloud computing has changed nearly all of the assumptions around how modern businesses should build and manage the technology they need to compete in the 21st century. It wouldn’t be too surprising if cloud computing changed some of the assumptions behind open-source software development.

SSMS – Linked Server – Column Metadata

Background

Had a good meeting this morning while we try to figure out how to better support our Developers.

One of the issues they brought up was an inability to view the datatype of linked Server tables.

Pictorial

Here is the deepest granularity when we connect to our Linked Server.

Image

ssms.columns.20190114.0425PM.PNG

Explanation

  1. We see the following
    • Server
    • Databases
    • Objects
      • Tables
      • Views

View Columns

Script

Outline

Here are avenues we can explore to view column metadata :-

  1. sp_columns_ex
  2. Openquery/sys
    • sys.all_columns
  3. Openquery/informational_schema
    • informational_schema.columns

Choices

exp_columns_ex

Syntax

exec sp_columns_ex
		  @table_server
		, @table_catalog
		, @table_schema
		, @table_name   

Sample

declare @linkedServer   sysname
declare @linkedDatabase sysname
declare @linkedSchema   sysname
declare @linkedTable    sysname

set @linkedServer= 'AWS-JobBuilder'
set @linkedDatabase = 'acs_ap'
set @linkedSchema = 'dbo'
set @linkedTable = 'ap_user'

exec sp_columns_ex
		  @table_server  = @linkedServer
		, @table_catalog = @linkedDatabase
		, @table_schema  = @linkedSchema
		, @table_name    = @linkedTable

Output

sp_tables_ex.2019014.0443pm

openquery/sys.*

Syntax

select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                    select
                              [server] = serverproperty(''servername'')
                            , [database] = db_name()
                            , [schema] = tblSS.name
                            , [object] = tblSAO.name
                            , [column] = tblSAC.name
                            , [type]   = tblST.[name]
                            , tblST.max_length
                            , tblST.is_nullable

                    from   sys.schemas tblSS

                    inner join sys.all_objects tblSAO

                            on tblSS.schema_id = tblSAO.schema_id

                    inner join sys.all_columns tblSAC

                        on tblSAO.object_id = tblSAC.object_id

                    inner join sys.types tblST

                        on  tblSAC.system_type_id = tblST.system_type_id
                        and tblSAC.user_type_id = tblST.user_type_id

              '
        )

Output

openquery.sys.all.2019014.0452pm

openquery/information_schema.columns

Syntax
select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                select top 100 

                          [server] = serverproperty(''servername'')

                        , [database] = tblSIC.[TABLE_CATALOG]

                        , [schema] = tblSIC.[TABLE_SCHEMA]

                        , [object] = tblSIC.[TABLE_NAME]

                        , [column] = tblSIC.[COLUMN_NAME]

                        , [position] = tblSIC.[ORDINAL_POSITION]

                        , [dataType] = tblSIC.[DATA_TYPE]

                        , [charMaxLength] = tblSIC.[CHARACTER_MAXIMUM_LENGTH]

                        , [charOctetLength] = tblSIC.[CHARACTER_OCTET_LENGTH]

                from   information_schema.columns tblSIC

              '
        )			   	

Output

openquery.openquery.informational_columns.2019014.0515PM.PNG

Summary

Unfortunately, SQL Server Management Studio ( SSMS ) v17.x does not let us view columns on Linked Servers.

To gather column level metadata, one has to write code.

Kris Vallotton

 

Videos

  1. Owls: The Prophets Who See In The Night (Kris Vallotton)
    • Videos
      • Video #1
        Channel :- SomeLittleShoe
        Published On :- 2014-Oct-31st
        Link

In Depth

Owls: The Prophets Who See In The Night (Kris Vallotton)

  1. Eagle Versus Owl
    • The prophetic mascot for this Season is not the eagle, but the owl
    • Why is that
      • Because the owl can see through the darkness
      • The owl lives in the darkness
      • The owl can see through the darkness
  2. The Owl
    • Jennifer Jackson
      • Shared a Story
        • An owl was on the road
        • And, will not move
        • He brought the owl to school
    • Ahmad Givens
      • Second Chance at Love on VH1
      • Share Attorney
      • Encounter with God
        • Want to do something with God
      • Purity Book
        • Ahmad endorses the back
        • Reach people who have problems
      • Owl Experience
        • White Owl
          • 3 Cities
            • Almost hit us
            • Fly Around Me
        • It is an Owl because you are called to purity and because you are called to darkness
        • Australia
          • Telling the Owl Story in Australia
          • And, my cell phone starts to vibrate while I am telling the Story
          • 4th Owl and 5th Owl
  3. Called us to be the river that leaves the Sanctuary and go into the deepest and darkest places of the planet
  4. Minister to people we do not agree with, but we love
  5. Daniel
    • In the days of Daniel, he was never called a prophet
    • Nebuchadnezzar’s Dream of a Tree ( Daniel Chapter 4 )
      • King’s View
        • Finally, Daniel came into my presence and I told him the dream. (He is called Belteshazzar, after the name of my god, and the spirit of the holy gods is in him.)
        • I said, “Belteshazzar, chief of the magicians, I know that the spirit of the holy gods is in you, and no mystery is too difficult for you. Here is my dream; interpret it for me.
      • Daniel
        • Daniel had taken up a new name
          • Belteshazzar, after the name of my god
        • Daniel had taken up a new title
          • Chief of the magicians
  6. My Point
    • If we are going to move into darkness
      • We are going to have to learn to customize without compromise
    • He will not eat the King’s food
    • He will not defile himself
    • He prays three times a day
    • He serves the God, but the King thinks he serves the gods
    • Dream
      • Actual
        • The King has a bad dream, it is not Daniel that has the bad dream
        • But, Daniel has to interpret it
      • Interpretation
        • If only the dream applies to those who hate you and its interpretation to your enemies
    • Daniel’s Condition
      • Daniel is a POW ( Prisoner of War )
      • He has been made a eunuch
      • Parents are likely killed
      • Nation is been destroyed
      • Temple turned down
  7. If we love our enemies
    • If you love him, I will let you influence him
  8. Growing up
    • Growing up in denominations
  9. Protestants
    • We came out protesting
    • We protested interpretation of the scripture and we have been doing it ever since
    • If we do not agree with someone, we do not have permission to be their friend
    • We are moving out of Protestantism into Apostleship
    • We gather because we have the same father not because we agree doctrinally
    • And, we can come into Revelation
  10. Agenda
    • As a protestant I do not have permission to fellowship with a homosexual unless I have an agenda to covert them
    • And, you do not think, they know I want to convert them
    • It is like stepping into a car lot and not think the Salesman is trying to sell me a car
    • I love your jeans
    • You look so good today
    • Tired of manipulation
    • Love them
    • These signs will follow those who believe
  11. Same gender Couple
    • Gave me a holy hug when we got to San Francisco
  12. What happened
    • You see the owl can see through the darkness

 

SSIS Package – Version Number – Setting

Background

We use a bit of SQL Server Integration Services on our site.

When troubleshooting for errors, it is not always easy to note whether we have the right version deployed.

Package

Setting version number on each SSIS package is one way of noting each package.

How To

Outline

  1. Launch SQL Server Data Tools
  2. Load Project
  3. Solution Explorer
    • Access the “Solution Explorer” panel
    • Select Each Package
      • Double-click each package
      • Access the Packages panel
        • Access the Version Group
          • Review and Set the following entries
            • VersionMajor
            • VersionMinor

Images

Package

Properties – Class View

SSISPackage.versionNumber.20190114.0122PM.PNG