Transact SQL – XQuery – Ancestor

Background Playing around with XML, but dug in a quicksand. XML Here is the XML Image Code Code Code Find Nodes whose element matches specific value SQL Output Pass X PATH :- Child::bar/Attribute SQL Output Find Descendants SQL Output Find Ascendants SQL Output Output - Textual Output - Image Source Code Control Git Hub TransactSQLXQuery/ancenstor/ … Continue reading Transact SQL – XQuery – Ancestor

AWS/RDS – SQL Server – Error – “The EXECUTE permission was denied on the object ‘agent_datetime’, database ‘msdb’, schema ‘dbo’ “

Background Here is an error I have been wanting to talk about for a while here. Code msdb.dbo.agent_datetime Outline The agent.date_time function accepts two integer values, date and time. And, returns the corresponding datetime value. SQL Output Output - AWS Here is the result when we issue command against an ASW/RDS MS SQL Server Instance. … Continue reading AWS/RDS – SQL Server – Error – “The EXECUTE permission was denied on the object ‘agent_datetime’, database ‘msdb’, schema ‘dbo’ “

Transact SQL – Non-ANSI Joins

Background Trying to see which SQL modules uses legacy Non-ANSI SQL Joins. Discovery There are a couple of pathways we can use to find sql code that relies on Non-Ansi SQL Joins. The mediums we will use are :- Code Execute Code Dynamic Management Views System Catalog Views sys.sql_Modules SQL Server Profiler Code Code Execution … Continue reading Transact SQL – Non-ANSI Joins

Transact SQL :- Error – Msg 15138 – “The database principal owns a schema in the database, and cannot be dropped”

Background Cleaning up a database as we move it from Development to Production. Recreate Drop User SQL Error Msg 15138 Error Text Error Image   Troubleshoot Metadata sys.schemas SQL Output Explanation Schema db_datareader The owner for the db_datareader schema has been assigned to a database account, other than itself Remediate Change Schema Owner ALTER AUTHORIZATION … Continue reading Transact SQL :- Error – Msg 15138 – “The database principal owns a schema in the database, and cannot be dropped”

Transact SQL – Drop Temp Table if it it exists

Background Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL. Code Original Code SQL Explanation When the temp table does not exist, an error is raised. Because the drop table is enclosed in a try/catch block the … Continue reading Transact SQL – Drop Temp Table if it it exists

SQL Server – Number of Error Log Files

  Background Trying to review error logs on one of our SQL Server Instances and discovered that it is likely that the error logs we might have to mine is so far back and we might no longer have it. And, so took to see if we have¬†properly calibrated the Number of Error Logs Files … Continue reading SQL Server – Number of Error Log Files

SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps

Preface In an earlier post we spoke about discovering that we can change our SQL Server Instance by restarting SQL Server with the /q option. That post is here.   Real life experience If truth be told, our first use-case was a recently installed SQL Server instance without any user databases. In this post we … Continue reading SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps