Stairway to Server Management Objects (SMO) Level 4: Scripting and Copying Small Databases (2023-12-13)

SQLServerCentral Newsletter for December 13, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to Server Management Objects (SMO) Level 4: Scripting and Copying Small Databases]( - [SQL GROUP BY Alias - An Oracle feature that would be great in SQL Server]( - [From the SQL Server Central Blogs - Azure Workbooks]( - [From the SQL Server Central Blogs - Write to backup block blob device failed. Device has reached its limit of allowed blocks.]( - [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Question of the Day - [Getting a List of Names]( The Voice of the DBA  Who Still Uses SP_ for Naming? [An interesting post]( recently from Chad Callihan on the reasons why someone shouldn't use sp_ to prefix their stored procedures. This is advice that I've seen for years, but I haven't run into this lately with many customers. It seems I do see some usp_, but not sp_. I think that's good, and if you're not sure why, read Chad's post. I think my main reason for not doing this is that I sometimes create procs in master. Not because it's a good idea (it isn't) or I want to, but because I'll make mistakes. I'll accidentally connect to master and deploy a script, because I'm not paying enough attention. Usually, I'll catch this right away, but if not, soon after I've told a client things are working, they'll let me know they aren't. Then I connect to the right database. In a vacuum, or even as you sit reading this today, it's easy to criticize someone else for making the mistake of connecting to the wrong database, or even forgetting to delete the object in master after I've created it in the right one. However, all of us are human. We'll make mistakes. We'll be pressed for time or sick or tired or want to leave or some other situation where we aren't using all of our faculties. Even if none of those are true, we might depend on muscle memory because we've done this 1,000 times (or may 12 times in the last hour) and then don't notice we've connected to the wrong database. What might be even worse if when we're updating objects and potentially update in one place but not another. What if v7 of the proc is in the user database, but v5 is in master. Who knows if we (or the client) will notice things aren't working as expected right away. Mistakes are one of the reasons I try hard to get everyone to follow a DevOps approach. Put your code in version control (so you know where it is when you make mistakes), use automation and CLI calls to deploy code. Whether you use something like [Flyway]( or your own SQLCMD scripts, make sure things are automated. Then ensure you have logs with commands run, and if needed, output from the scripts. We're human, we make mistakes, so figuring out how to automate a process and then ensuring that automation is running results in repeatable, reliable deployments. Something we should all want, especially the control-obsessive DBAs. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to Server Management Objects (SMO) Level 4: Scripting and Copying Small Databases]( laertesqldba from SQLServerCentral In the fourth level of this series, we will examine how you can use the scripting classes to create the code to manage your SQL Server instances and databases. [External Article]( [SQL GROUP BY Alias - An Oracle feature that would be great in SQL Server]( Additional Articles from MSSQLTips.com Learn about using an alias in the GROUP BY clause for complex SQL Server code instead of having to rewrite the code in the GROUP BY. [Blog Post]( From the SQL Server Central Blogs - [Azure Workbooks]( hellosqlkitty from SQLKitty I’m saddened that the Workspace Summary is being deprecated in Log Analytics Workspace. I am trying to reproduce it in workbooks. While it isn’t an exact match, workbooks provide... From the SQL Server Central Blogs - [Write to backup block blob device failed. Device has reached its limit of allowed blocks.]( david.fowler 42596 from SQL Undercover Picture this, you’re happily backing up your database to a Azure blob storage until suddenly it starts mysteriously failing with the error… Write to backup block blob device https://******... [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( [The Definitive Guide to Azure Data Engineering: Modern ELT, DevOps, and Analytics on the Azure Cloud Platform]( Site Owners from SQLServerCentral Build efficient and scalable batch and real-time data ingestion pipelines, DevOps continuous integration and deployment pipelines, and advanced analytics solutions on the Azure Data Platform. This book teaches you to design and implement robust data engineering solutions using Data Factory, Databricks, Synapse Analytics, Snowflake, Azure SQL database, Stream Analytics, Cosmos database, and Data Lake Storage Gen2.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Getting a List of Names There are 500 names in the Person.Person table in my database. I run this query: SELECT STRING_AGG (CONVERT (NVARCHAR(MAX), p.FirstName), ',') FROM person.Person AS p; How many rows are returned? 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) Attaching Databases With SQL Server 2022, what is the recommended code to attach a database? Answer: CREATE DATABASE Explanation: The recommended way to attach databases is with CREATE DATABASE. Sp_attach_db is deprecated. Ref: - CREATE DATABASE - [ - sp_attach_db - [ [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 2016 - Development and T-SQL [STUFF Command Not Working]( - I am trying to use the STUFF command, however, the column using the STUFF just seems to just include everything, instead of the ones specific to the user. I am hoping someone can notice what I am doing incorrectly in my SQL Statement. Below is the code I am using. SELECT DISTINCT G.[Period], G.[Sector], G.RID, […] SQL Server 2019 - Administration [Drop or delete a existing partition]( - Hello, so I was reading on partitioning a large table, Which i successfully did, using the clustered index and partition function/schema... example the table is a simple table with 4 columns CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, ItemID INT, OrderDate DATETIME );  CREATE CLUSTERED INDEX Order_Date_Added_Partition ON dbo.Orders (OrderID) WITH […] [Compare all tables row counts for 2 server instances?]( - USE [DBName] SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName] , SUM(sPTN.Rows) AS [RowCount] FROM sys.objects AS sOBJ INNER JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id WHERE sOBJ.type = 'U' AND sOBJ.is_ms_shipped = 0x0 AND index_id < 2 -- 0:Heap, 1:Clustered GROUP BY sOBJ.schema_id , sOBJ.name ORDER BY [TableName] I have the code above […] [Ola hallengren backup not deleting old backup files for SQL 2016 Clusters]( - Ola hallengren stopped deleting older files for SQL Server 2016 which are on cluster. Files can be deleted manually but not with SQL script which had been working. SQL Server 2019 - Development [Improving Code Readability]( - Below is a code i wanted to improve some readability and functionality: GO /****** Object: View [Prod]. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW [Prod]. AS --------------------------------------------------------------------------------------------------------------------------------------------------------- SELECT MAN_ADJ_ALL.[FY] ,MAN_ADJ_ALL.[Period] ,MAN_ADJ_ALL.[Group] --ADJUST THIRD PARTY TO ALLOCATED PLANT ,CASE WHEN MAN_ADJ_ALL.[Cost Element] LIKE 'Variable 3rdP Cycle1' THEN MAN_ADJ_3RDPARTY.[Plant] ELSE MAN_ADJ_ALL.[Plant] END AS [Plant] […] [Selecting from a view, base table has a DENY on a column]( - This is something I've never seen before and I can't think of the right way to search properly for this, so I'd like to throw it out to this group. We have an audit table that stores the old password value when someone changes it. There is a DENY on this column for all but […] [JSON data with Pivoted]( - Hi All, I need some assistance and not sure how to achieve the expected output. I have a JSON input parameter which is a nvarchar(max) passed from my application as per my sample code below. I've started to break up the JSON into a table output and am a bit stuck on getting how I […] SQL Azure - Administration [SQL Replication - Disconnected from Azure Storage with OS result code: 0]( - Hello, We had a few errors with replication for SQL Managed Instance stopped working with "Disconnected from Azure Storage "\\StorageLocation" with OS result code: 0. Can restart it again with no issues but not sure why it has happened now a few times. Any suggestions on how to troubleshoot?  Thanks,  Daniel Azure Data Lake [Lake Database Performance Optimization]( - I recently created a synapse link for Dataverse. The resultant Lake Database in Synapse is powering a number of Power BI reports, however the performance of the lake database is a slower than we had expected. What steps can I implement to improve the performance of the lake database? Analysis Services [Relationship between dimension and measures]( - There is a Fact Table (.....,MenuID, MOption, CallTime) There is also a Dimension "Menu" Table (MenuId,OptionID) Relationship between these tables (MenuID and MOption) In the Dimension table, I created another field - keyField (string(MenuID+MOption) as OptionId2) when creating Dimension, I use the new key (OptionId2) and build the hierarchy accordingly -MenuId -OptionId2 (ключ)  but […] PostgreSQL [PostgreSQL authenticated against Microsoft Active Directory]( - Can I authenticate with Active Directory and get into PostgreSQL? Version: PostgreSQL 16.1 on Windows Server 2022. I'm running DBeaver Community (portable version) on Windows 11 and successfully connect using database user postgres. I found this doc that suggests using GSSAPI to authenticate against Microsoft Active Directory. Confused now if I should be using LDAP […] SQL Server 2022 - Administration [Provider not found in SSMS]( - I am trying to setup a linked server and per the documentation it needs to use MSODBCSQL18 - and so we've installed that. It's installed because if I go into the ODBC Data Sources in Windows, I can see it under Drivers and create a connection with it there. However, in SSMS I still only […] SQL Server 2022 - Development [Retrieving First Word, First + Second Word, First + Second + Third Word, First]( - Hi All, I have the company names in one column. Need a sql server query to fetch in separate column like Example: ABC private limited company First Word ABC First + Second Word ABC private First + Second + Third Word ABC private limited First + Second + Third + Fourth Word ABC private limited […] [Why don't these two queries return the same data?]( - I have a table-value function that returns data for a report. However, it's not returning the correct data, so I've reworked it and it's now returning what I'd expect ... the thing is, to my obviously unseeing eyes the two queries should be functionally identical. Please can someone help? This query doesn't work: SELECT Grades.[Name] […] [SQL Challenge: Employee Salary Analytics]( - onsider an employees table: employee_id (int) employee_name (varchar) department (varchar) joining_date (date) salary (decimal) Highest Earner by Department: Find the employee with the highest salary in each department. Average Salary by Department and Year: Calculate the average salary for each department per year. Longest Serving Employee: Identify the employee with the longest tenure. Please provide […]   [RSS Feed]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -