INSERT EXEC statement cannot be nested, the Simple Solution (2022-08-19)

SQLServerCentral Newsletter for August 19, 2022 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [INSERT EXEC statement cannot be nested, the Simple Solution]( - [Create a Table in Microsoft SQL Server]( - [5 things to look for in a 3rd party database monitoring tool]( - [From the SQL Server Central Blogs - Solar Production After 5 Months in Power BI]( - [From the SQL Server Central Blogs - Data Relay Speaker Diversity and Feeling Included]( Question of the Day - [Cleaning Tables]( The Voice of the DBA  Daily Coping Tip No plans day – Be kind to yourself and others I also have [a thread at SQLServerCentral]( dealing with coping mechanisms and resources. Feel free to participate. For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from [The Action for Happiness Coping Calendar](. My items will be on [my blog](, feel free to share yours. How Hard is Kubernetes? We've run [Kubernetes]( inside Redgate for some research projects (like Spawn) and we are building some skills running this orchestrator. At the same time, we've had no shortage of challenges in keeping the clusters up at times, patching, fixing issues, upgrading to new configurations, etc. Like any software, there is work involved with managing the orchestrator. I've watched [Andrew Pruski]( and [Anthony Nocentino]( write about containers and Kubernetes and overall they've made me view the clusters like email. It's useful and I want to use it, but I don't want to manage or administer it. I'd want some service like [AKS]( or [EKS]( instead. Let someone else build expertise. If you use containers, do you have an orchestrator running in your data center? [Mercedes does, with over 900 clusters](. They found value early on with container technologies and built in-house expertise within their research arm. I think a large organization like Mercedes likely can make this investment pay off, especially as they likely don't depend on any one person to understand and manage Kubernetes. They can afford someone like Andrew or Anthony quitting and taking another position. The rest of us can't really do that, certainly not without our organization feeling containers and orchestration is a core competency. The key for Mercedes is automation. They note that if they added 500 more clusters, they'd need just one more engineer. That's a key for any of us that want to manage growing numbers of systems without spending a lot of our time reviewing resumes and hoping we can hire good staff. Hiring is hard, and finding good people even harder. When you find them, set them free codifying their knowledge using DevOps, scripting, automation, and more. Then educate others and teach them what your talented engineer is doing. Mercedes notes that finding people is hard, and educating existing people is easier. DevOps, better coding, understanding APIs and declarative scripting are not hard skills, but they are something people need to practice to develop familiarity and skill. We want staffers to be able to easily pick up the work of another, understand it, and extend or improve it. We don't want to depend on the person that wrote it. The way Mercedes has attacked this technology is the way I'd have developers and administrators tackle DevOps. Take advantage of the power of modern software development and infrastructure tools and empower your staff to make things better. They are likely to enjoy their jobs more and remain employed, reducing your need to struggle with the vagaries of findind and hiring good people, a problem no one has solved well. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [SQLServerCentral Article]( [INSERT EXEC statement cannot be nested, the Simple Solution]( Daniel Brink from SQLServerCentral Step-by-step guide to solve the "INSERT EXEC cannot be nested" problem by using a CLR when unit testing stored procedures using the tSQLt framework. [External Article]( [Create a Table in Microsoft SQL Server]( Additional Articles from MSSQLTips.com Learn how to create a table in SQL Server using T-SQL along with several different examples that build upon each other to create the final table. [External Article]( [5 things to look for in a 3rd party database monitoring tool]( Additional Articles from Redgate Are you evaluating the effectiveness of your homegrown database monitoring tool or looking at investing in a 3rd party one? This helpful blog post suggests 5 things to look for in your evaluations. [Blog Post]( From the SQL Server Central Blogs - [Solar Production After 5 Months in Power BI]( Steve Jones - SSC Editor from The Voice of the DBA I’ve been tracking my usage and comparing that with the estimates for my solar power system. I wrote about the database design and tracking the usage and some of... [Blog Post]( From the SQL Server Central Blogs - [Data Relay Speaker Diversity and Feeling Included]( Tracy Boggiano from Database Superhero’s Blog Back in 2018, wow four years ago, I presented at all five stops of Data Relay and rode the bus. For those that don’t know The post Data Relay Speaker...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Cleaning Tables What does DBCC CLEANTABLE do? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) Stored Procedure Return Values I have this stored procedure code: CREATE PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxTotal INT OUTPUT AS -- Do a SELECT using the input parameter. SELECT FirstName, LastName, JobTitle FROM HumanResources.vEmployee WHERE EmployeeID = @EmployeeIDParm -- Set a value in the output parameter. SELECT @MaxTotal = MAX(TotalDue) FROM Sales.SalesOrderHeader; GO I call this procedure with this code: DECLARE @return INT , @count INT; EXEC @return = SampleProcedure 12, @count OUTPUT; SELECT @count, @return; What values is in @return Answer: 0 if no error, another value if there is an error. Explanation: If no return value is specified, and the procedure completes, 0 is returned unless you specify a value. If you do not specify a value, then an error value can be returned. Try this: ALTER PROCEDURE SampleProcedure @EmployeeIDParm INT, @MaxTotal INT OUTPUT AS -- Do a SELECT using the input parameter. SELECT FirstName, LastName, JobTitle FROM HumanResources.vEmployee WHERE BusinessEntityID = @EmployeeIDParm -- Set a value in the output parameter. SELECT @MaxTotal = MAX(TotalDue) FROM Sales.SalesOrderHeader; SELECT 1/0 Ref: Stored procedure parameters - [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Help me understand SSIS Environments]( - Ok, so I understand what they are, they're collections of parameter assignments that you can tell a package to use upon execution. What I'm trying to understand is why or if I need to use them. I'm migrated a bunch of old SSIS packages using the packages deployment model to some new servers using the […] SQL Server 2016 - Administration [Patch SQL Server Failover Instances separately]( - Lets say you have a sql failover cluster environment with n nodes (more than 2) and n instances. Instance1 and Instance2 are for developing and testing. Is it possible/ok to patch, for example, Instance1 and Instance2 from SQL Server 2016 SP1 to SQL Server 2016 SP3 and leave the other instances (Instance3, Instance4, ...) on […] SQL Server 2016 - Development and T-SQL [Finding difference in two databases/tables]( - I have compared two tables with the same data in two different databases(aslo the schema) with SSIS(Comparrison), sql attached. The Schema comparison return an exact match, the table quite a few new updates. The tables are from Production and QA databases. I ran the exact queries on both of these databases, see attached. I am […] Development - SQL Server 2014 [loading data]( - for suppose, my colleague is loading data in a table at same time i want to read that table without any interrupting his loading? how can i do that? SQL Server 2019 - Administration [how to create index rebuild job on SQL Pas machine]( - how to create index rebuild job and stats jobs on SQL Pas machine can any one sugegst. SQL Server 2019 - Development [Analyzing execution plan]( - Hi, can someone help me to understand the following execution plan? I'm not sure why I have at the bottom right Index Seek with cost 99% that returns zero rows? Thanks! [Substitute 0 and 1 values in 1 column]( - Hello everyone, Hi have this column named Sex and it only has value 0 and 1. I would like to substitute those values, 1 for female and 0 for male. Could you let me know how to do it in SQL? Thank you all in advance Pedro [SELECT * FROM ( ... ) X]( - Hi all! I'm trying to understand how does this work. I've read a thread here about the X thing but it wasn't useful at all. Given this example: DECLARE @PAGE INT=1 WHILE (@PAGE<=5) BEGIN DECLARE @ROWS INT=5 SELECT * FROM ( SELECT ROWNUM = ROW_NUMBER() OVER (ORDER BY CLI_COD), * FROM CLIENTE) X WHERE ROWNUM […] [Would like to understand a better way of updating data that a cursor if possible]( - I have a need to update some data and also create a record of that change. From what I know and I don't know a whole hell of a lot, I can do an update with update table set field value where value = value but to make a record of the change I need […] [SQL Question - Column References]( - Hi. Looking for someone to help with a query I'm tring to run. I have a table with thousands of rows and need to find all records referenced by one another by a column The table looks like the following: ID Barcode CreateDate OldBarcode 1 101 8/10/2022 NULL 2 102 8/10/2022 NULL 3 103 8/12/2022 […] [TSQL using Python for market basket analysis ModuleNotFoundError: apyori]( - Apologies if this is the wrong forum as its really python but need to run from TSQL so have a SQL dataset to pass in, not a CSV file, so am hoping this is a common issue and an easy solution. I have installed Python as a SQL feature, and can do the simple microsoft […] [How could I have written this script better?]( - Hi all, I'm looking to sharpen my SQL/Programming skills, below is a sproc I wrote earlier, it's in prod but I can't shake the feeling it's more cobbled together than professional. Looking for any and all critique as to what I could have done better!! TIA ---------------------------------------------------------------------------------------------------------------------------------- --Insert Operator Script (IOS) --The purpose of IOS […] [Best place to practice]( - Hi Guys, We are in the process of moving to a new external system and unfortunately I will not have access the SQL server. SQL is only a small part of my role but I really enjoy it. This means I basically wont have opportunity to practice any SQL development. I really don't want to […] Reporting Services [#Error for calculation in a textbox]( - I am getting the #error when trying to sum a calculated field in ssrs text box. I have seen posts regarding division by zero but this has none of that. The calculated field is appearing correctly in the detail row and I get the error when I try to sum it my equation is =SUM(IIF(Fields!strType.Value="CreditsAndDeposits",Fields!intDT.Value,0)) […] Continuous Integration, Deployment, and Delivery [Database build artefacts: build script, dacpac or .bak file?]( - Curious to know what everybody's preferred build artefacts are for databases? Say you have your databases represented as scripts in source control, what deployable item is that packaged into? I prefer collating all source-controlled scripts into a single, dependency-ordered, idempotent, build script. Then using that for CI, unit tests. And deploying a specified subset of […]   [RSS Feed]([Twitter]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -