SQLServerCentral.com Community Update for July 18, 2022

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Helping Developers Set Up a Sandbox

The Voice of the DBA

Daily Coping Tip

Shift your mood by doing something you really enjoy

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.

The Limited Cloud

The cloud is truly someone else's computer, and it's not limitless. From the perspective of the individual, it might seem like you could scale infinitely if you need more boxes, storage, or networking, but ultimately the cloud vendor (Azure, AWS, GCP, etc.) needs to have spare machines around. Many of them do, and that works well at the scales in which they operate.

To a point.

Azure is having capacity issues in some of its data centers. I'm sure that all of the major cloud providers buy lots of hardware constantly, but there have been shortages during the last year, which has meant for Azure that DCs are running out of capacity. At least for the rest of 2022, which is likely driven by both a limited supply of new machines and the growth of cloud usage by their customers. I know I've seen continued growth in companies moving to the cloud for various services, including their data services.

I haven't seen reports of large AWS issues, though they do have plenty of instances of insufficient capacity errors and solutions. GCP has similar reports.

Truly, the cloud is the cloud vendor's computer. It's also the cloud vendor's infrastructure with lots of tooling to help you manage software configured networking and storage in addition to compute engines. It is limited in capacity, especially when lots of customers want to use these resources in one particular data center. That capacity is usually quite high, but there is definitely a limit.

Does this mean that the cloud isn't better than your data center? That's an "it depends" questions. The cloud is great in many ways, and far superior to a lot of private data centers (or colocations facilities) that I've worked in or seen. I think the cloud is fantastic, but it's not perfect. There are limits and issues at times, and you should be ready to work within or around those limits.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

Featured Contents

How to Connect to an Analysis Services Database in Power BI

Daniel Calbimonte from SQLServerCentral

Learn how to work with SSAS data in Power BI in this article.

Taming Database Documentation with Flyway and MySQL

Additional Articles from Redgate

Database object documentation is essential for explaining to busy developers, and the wider business, the purpose of each object and how to use it. The solution presented in this article consists of a SQL script to allow developers to add comments to MySQL database objects, without affecting the database version, and a simple way to generate a documentation report, in JSON. The SQL script will execute automatically as a callback, during any Flyway Teams migration run, and the report will allow the team to spot any gaps quickly.

T-SQL Comment Best Practices for SQL Server

Additional Articles from MSSQLTips.com

Learn different ways to add comments to your T-SQL code when working with SQL Server along with several examples.

From the SQL Server Central Blogs - Scary Scalar Functions - Part Two: Performance

Zikato from StraightforwardSQL

Scary Scalar Functions series overview

Part One: Parallelism
Part Two: Performance

Foreword
In the second part of this series, we’ll look at how Scalar functions (or UDFs) affect performance.
If you want to follow...

From the SQL Server Central Blogs - Generating Unicode Characters in Power Query

Meagan Longoria from Data Savvy

You may have used the UNICHAR() function in DAX to return Unicode characters in DAX measures. If you haven’t yet read Chris Webb’s blog post on the topic, I...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Helping Developers Set Up a Sandbox
I have created a new database on a development server and added the user, JoeUser, as a user in this database. This user has no rights other than to connect to the database. I then execute this code: CREATE SCHEMA Dev GO GRANT CONTROL ON SCHEMA::Dev TO JoeUser What happens when JoeUser connects and runs this code: CREATE TABLE Dev.MyTable (myid INT)
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)

Which Error

I have this code:

CREATE TABLE dbo.CatchTest ( myid INT NOT NULL ); GO CREATE OR ALTER TRIGGER dbo.CatchTest_Trigger ON dbo.CatchTest AFTER INSERT AS BEGIN BEGIN TRY UPDATE dbo.CatchTest SET myid = NULL END TRY BEGIN CATCH END CATCH; END; GO

I run this:

INSERT dbo.CatchTest (myid) VALUES (1)

Which error is returned to the client?

Answer: An error was raised during trigger execution.

Explanation: You can create a TRY..CATCH that has an empty catch block. This code does create an error, but the CATCH block swallows this and a generic "an error was raised during trigger execution" is returned. The CATCH block swallows this error and only notes to the calling process that an error occurred. Ref: TRY..CATCH - https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver16

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

SSIS Proxy/Credential/Login question - I need a quick refresher on how the Proxy stuff hangs together. I know I created a Credential mapped to an AD account which has permissions to any file system resource I need to access in my packages. I know I then create a Proxy mapped to that Credential and grant the Proxy access to […]

Timeout - System exception.Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at Source: .Net SqlClient Data Provider. Application team received the error. How can you troubleshoot? No information in error log or event log. We have default enabled but didn’t find any useful information from default […]

Database backup sequence - Hi All, How the SQL server backup will work with scheduled job? What is the sequence it follows? What is the criteria it choose that it need to take this DB backup first, second .. like that? Is it possible before it complete the backup of the one database it will start another database backup?

ERROR: Could not find a login matching the name provided - Okay. This is an odd one that I can't quite figure out. I hope someone can advise. A user was having issues logging into a server with a new AD account. One of my coworkers verified the login was part of windows groups which had access to the server. For kicks and giggles, I added […]

SQL Server 2016 - Development and T-SQL

Converting SSIS packages from SSIS catalog to File system in Job - Hello, I need some advise on how to convert SQL agent jobs run a SSIS package( SQL agent job has source as "SSIS catalog" and has a SSIS package as destination which is under path example.. SSISDB - Catalog -->Project and where you select specific package) Now, I want to change the SQL agent job […]

How to Refactor Code with CTE - Hello Community, Can someone show me how to refactor the following code with CTE's? My platform doesn't support CTE's WITH CTE1 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ts_primarysecondaryfocus)RowNum FROM [dataverse_montagu_org5a2bcccf].[dbo].[account] ),CTE2 AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ts_primarysecondaryfocus)RowNum FROM [dbo].[OptionsetMetadata] ) SELECT C1.Id,C1.SinkCreatedOn,C1.SinkModifiedOn,C1.statecode,C1.statuscode, CASE WHEN ISNULL(CAST(C1.ts_primarysecondaryfocus AS VARCHAR(50)),'') <> ISNULL(CAST(C2.ts_primarysecondaryfocus AS VARCHAR(50)),'') THEN CAST(C2.ts_primarysecondaryfocus AS VARCHAR(50)) ELSE CAST(C1.ts_primarysecondaryfocus AS VARCHAR(50)) END AS ts_primarysecondaryfocus ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking ,CASE WHEN ISNULL(CAST(C1.ts_ukrow AS VARCHAR(50)),'') <> ISNULL(CAST(C2.ts_ukrow AS VARCHAR(50)),'') THEN CAST(C2.ts_ukrow AS VARCHAR(50)) ELSE CAST(C1.ts_ukrow AS VARCHAR(50)) END AS ts_ukrow FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum Thank you

SQL Server 2019 - Administration

query that will return the information of every table in a database - Hi, I'm seeking for a query that will return the information listed below for every table in a database. Any suggestions please. Thank you.

SQL Server 2019 - Development

How to setup Linked Server from SQL Server Express 2019 to Sage Line 50 - Hi there We have a Sage Line 50 Reports application located on one of our servers. This has been setup on our server as ODBC System DSN (Screenshots attached) Now Im trying to set up a linked server from this SageLine50 to SQL Server Express 2019 using the following programatically: EXEC sp_addlinkedserver @server='SageLine50Reports', @provider='SQLNCLI', @datasrc='ChrisSQL,1433', […]

Count of word inside cell - Hello all, I am trying to find a query that bring me the count of the exactly word name: PageDefinition like on the example. Thanks a lot all and best regards, Gilad

SSIS Web Service task SSL errors - I've tried different credentials, target server versions, etc. hitting our Primavera WSDL. Works great in any browser. Via the Web Service SSIS task, I get an error when running the package: Target Server 2016: --1. Connection manager "HTTP Connection Manager 1": SSL certificate response obtained from the server was not valid. Cannot process the request. […]

Amazon AWS and other cloud vendors

How to debug a StackSet? - We have a StackSet in our organisation that deploys config-central-logging. I wanted to change the regions this is deployed to and make sure it is deployed to new accounts. Any change I make to the StackSet is greeted with an error. Removing stacks fails, sometimes it says the OU is not part of the stackset […]

General Cloud Computing Questions

Please recommend me a cloud service based on description - I am working on an angular project and created a docker image for that.till now i only worked on the development side and never on full deployment side but I am willing to learn the process,can you please recommend me a cloud service where my docker container can be deployed with less/no fee charges as […]

XML

Parsing a lange XML data-column from one server to another - Dear PS-scripters. As a newbee I encouter the next challange.... I have a PS script that needs to copy data from server A (table VV) to server B (table VV). One of the columns is an XML column. Now I run: Invoke-Sqlcmd -ServerInstance $DestServer -Database $DestDatabase -MaxBinaryLength 204850000 -Query $InsertQuery The $InsertQuery holds just one […]

General

SQL MAX of multiple columns? - How would you return 1 value per row of the max of several columns: TableName [Number, Date1, Date2, Date3, Cost] I need to return something like this: [Number, Most_Recent_Date, Cost] Can anyone help me out with this. Thanks in advance

Powershell

create file name with timezone CEST - Is there any easy script that will take current datetime and convert to CEST and account for DST also? I want to use that in my file name to help identify when process was ran. Thanks

This email has been sent to newsletter@newslettercollector.com. 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.