SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Question of the Day

Which Error

The Voice of the DBA

Daily Coping Tip

Add to your list of joys (or make one)

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.

Which Work Is Suited for In-Person?

In this article, number four makes a good point. What work is suited for live, in-person interaction? Which aspects of your job benefit from seeing others face-to-face?

Today I'm asking data professionals to think about it. I know we can do all our jobs remotely, after all, many of us have been doing that for at least a couple of years, and some for far longer. I can't remember the last time I actually needed to look at a physical server I wrote code for? Probably when I self-hosted SQL Server Central back in 2002-2003 and went to reboot a machine or add a disk drive. Even then, those weren't DBA or developer jobs, but really Operations hardware tasks.

These days many companies are trying to decide how to handle office and workers. I don't know I've seen anyone come up with a good plan other than leaving everyone remote and figure out how to make things work. Every day in the office doesn't seem great, and I'm not sure I've seen a good company hybrid plan. I see some groups deciding to meet once a week or once a month and that works well, but I think that's more a reason to have some social time than it is a need for productivity.

Not that allocating some amount of time specifically to bond socially is a bad idea. I am in favor of this. We used to do this when we were in offices every day, so why not schedule this periodically now?

While you can do your job remotely, I wonder if there are not some aspects of development or operations that are better done in person. Or maybe, sometimes in-person. I can see brainstorming and planning as being smoother in person. Being focused and closely collaborating always feel stilted and confusing online. I prefer being in person. Perhaps pair programming or a similar arrangement is better in person at times. I know we can share a screen and code together, but doing this in the same room periodically builds a bond that I think would make remote sessions more productive.

The only other thing that I think matters is cross-team bonding, perhaps doing some debate and discussion of how teams work together or how their work affects others is better done in person. Perhaps not every day/week/month but once a quarter or a few times a year I can see this building better relationships between people, much like in-person pair programming.

If you have other ideas or experiences, let us know. If you think there isn't a reason to every go in the office, that's something worth sharing as well.

Steve Jones - SSC Editor

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

Featured Contents

Linked Servers: a Peek Behind the Curtain

maxtardiveau from SQLServerCentral

Learn how a linked server caused an issue with Gallium Data.

SQL Server 2022: Backup Database Options to AWS S3 Bucket - Part 2

Additional Articles from MSSQLTips.com

In this article we look at how to backup a SQL Server 2022 database using AWS S3 storage along with all the supported backup options.

[From the archives] Your guide to choosing the right monitoring tool [Video]

Additional Articles from Redgate

Join Grant Fritchey in this 35 minute video to learn his top tips for evaluating monitoring tools and vendors, the common pitfalls when implementing a new solution, and how you can demonstrate Return on Investment (ROI) to the business post-purchase.

From the SQL Server Central Blogs - Speaker diversity

Tracy Boggiano from Database Superhero’s Blog

Recently PASS Data Community Summit sessions were selected and the wounds were opened on Twitter around speaker diversity in the community. I heard from several people via my DMs...

From the SQL Server Central Blogs - SharePoint List only returns 100 items in Azure Logic App

Koen Verbeeck from Koen Verbeeck

I was reading a SharePoint List using the “Get Items” activity in an Azure Logic App. I explain how you can create such a Logic App in the blog...

Question of the Day

Today's question (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?
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)

ALTER Transactions

I want to ensure that if a user runs an ALTER TABLE statement, that a new BEGIN TRANSACTION is always started. What can I change in the settings for all users to ensure this is the case.

In other words, I want to require each admin to execute a COMMIT after running their ALTER TABLE.

Note: We don't have any ODBC or OLEDB connection libraries in use.

Answer: Use sp_configure for the setting "user options" and a value of 2.

Explanation: This setting is the implicit transaction setting. The only way to ensure all users get this enabled is to change the instance settings. There is an sp_configure option for use options that will set this for all users. Ref: Configure the user options Server Configuration Option - https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option?view=sql-server-ver15

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

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 […]

Uploading 1 M file contents to SQL Server - Hi, How to upload large file contents in to sql server when there is no file stream enabled? Received the following error MSG>[Microsoft][ODBC SQL Server Driver] Warning: Partial insert/update. The insert/update of a text or image column did not succeed.

SQL Server 2016 - Development and T-SQL

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

Going after my MCSA SQL Server 2016 and need MOC recommendations. - As above. My employer has allocated $5,000 for my education in this budget and I have to use it or lose it. I want to get my MCSA for SQL Server 2016 and am trying to pick a MOC provider. TechSherpas is desirable on paper because of the exam voucher you get and access to […]

Error converting data type varchar to bigint. - Hello Community, I'm getting the following SQL error in Azure Synapse Error converting data type varchar to bigint. ;WITH CTE1 AS ( SELECT *,ROW_NUMBER()OVER(ORDER BY ts_primarysecondaryfocus)RowNum FROM [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 C1.ts_primarysecondaryfocus<>ISNULL(C2.ts_primarysecondaryfocus,'')THEN C2.ts_primarysecondaryfocus ELSE C1.ts_primarysecondaryfocus END AS ts_primarysecondaryfocus ,C1.customertypecode,C1.address1_addresstypecode,C1.accountclassificationcode,C1.ts_easeofworking ,CASE WHEN C1.ts_ukrow<>ISNULL(C2.ts_ukrow,'')THEN C2.ts_ukrow ELSE C1.ts_ukrow END AS ts_ukrow ,C1.preferredappointmenttimecode,C1.xpd_relationshipstatus,C1.ts_relationship FROM CTE1 C1 LEFT JOIN CTE2 C2 ON C1.RowNum = C2.RowNum The problem is, I don't know why Azure Synapse is trying to convert the Data Type to BIGINT. Also, I don't know how to […]

Administration - SQL Server 2014

SQL Server 2014 Service Packs? - I noticed that there's SQL Server 2014 Service Pack 2 CU4 and Server Pack 3 CU18 in WSUS.. KB's 4500180 and 4500181 respectively. I am a LITTLE hesitant to install these,, as the old adage, if it aint broke, don't try to fix it,, however, i'm wondering if i don't install these, will I potentially […]

Filter Pivot Table - Hi I have below query & i want to filter data on 2 parameters Create PROC USP_Book ( @ID VARCHAR(500), @SID VARCHAR(500) ) AS BEGIN declare @cols as nvarchar(max)=''; declare @query as nvarchar(max)=''; select @cols = @cols + QUOTENAME(SName) + ',' from (Select distinct SName from View_Book) as tmp select @cols = substring(@cols,0,len(@cols)) set @query […]

Query - Hi I have data like below. Brands can be dynamic Loc1 Brand Qty Value Loc1 Omega 10 1000000 Loc2 Swatch 5 […]

Incorrect syntax near the keyword end - Create PROC [dbo].[SP_Planning] AS BEGIN Truncate TABLE Planning Select * into Planning from (select T0.ID,T1.SID,[dbo].[Planning](T1.Id,T0.SId) as Status from BDetails T0 cross join SDetails T1) END

SQL Server 2019 - Administration

Conversion failed when converting the varchar value - Hi Error - Conversion failed when converting the varchar value '+ cast(@BookId as varchar(Max))+' to data type int. ALTER PROC [dbo].[USP_Session] --'0','20,21,22' ( @BookID VARCHAR(500), @StudentID VARCHAR(500) ) AS BEGIN with ROWCTE as ( SELECT * from View_Session where BookID IN('+ cast(@BookId as varchar(Max))+') ) SELECT * FROM ROWCTE Thanks

Incorrect syntax near 0 - BookId & StudentId has int DataType ALTER PROC [dbo].[USP_SessionBookPlanningSearch] --'0','20,21,22' ( @BookID VARCHAR(500), @StudentID VARCHAR(500) ) AS BEGIN with ROWCTE as ( SELECT * from View_Session Where (('''+@@BookId+'''=''0'' OR BookID IN('+ cast(@@BookId as varchar(Max))+')) and (('''+@@@StudentID+'''=''0'' OR StudentID IN('+ cast(@@@StudentID as varchar(Max))+')) ) SELECT * FROM ROWCTE end

SQL Server 2019 - Development

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. […]

Populate Column Based on Distinct Values - Hello! I am trying to populate a column based on the number of distinct values in a different column. If there is only one distinct department, Export Department should be populated with *, if there is more than one distinct department, Export Department should be populated with the same value in Department. Any help would […]

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 […]

Integration Services

How to Replace LF to CRLF in file source connection - hi, I have a issue where ,the flat text file (fixed width) used to come with CRLF and now it is coming as LF and it is failing. How can i fix it, Is it possible? Please suggest. Thanks in Advance, Komal.

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.