Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Question of the Day
The Voice of the DBA
 

Daily Coping Tip

Get active singing today (even if you can’t sing)

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.

Using T-SQL over PoSh

Why would you use SSMS/T-SQL over PowerShell (PoSh)? When is T-SQL directly a better option than PoSh? That's a question I ask myself regularly as I see articles and blogs that discuss how to accomplish a particular task using one tool or the other. There is plenty of overlap in the capabilities for each language when it comes to working with SQL Server.

There also appears to be a bias towards one tool or the other for each individual . Many people traditionally have used T-SQL to accomplish most database tasks, and they tend to always look for a solution with a script in SSMS. Others are excited by PoSh and I have seen plenty of questions on the SQL Server Central forums asking how to structure their code in that language. In both cases, there is no shortage of people that argue that you should use T-SQL instead of PoSh or vice versa.

Personally, I think that there are lots of development items where I'd use T-SQL. Any sort of schema change, most data changes, and a lot of administrative things I would use T-SQL first. Trying to alter a table in PoSh vs. T-SQL doesn't make sense to me. Now the deployment of these changes is something where I'd use PoSh to run the T-SQL, which is what we do in the Redgate Deploy tools.

I was with a panel recently and all the individuals on the panel said they wouldn't use SSMS over PoSh for much of anything. The exception is where a task involved working with files or folders in the file system. PoSh excels here, and for work that might delete old files or move files from one folder to another, PoSh is preferred.

I think the defining line for me is whether I need to accomplish a task inside of SQL Server or outside of it. When I cross instances or work with the file system, then PoSh is my preferred method. I can use xp_cmdshell or a linked server as well as anyone, but I prefer not to. Anything inside SQL Server, usually has me reaching for SSMS instead of VS Code.

Steve Jones - SSC Editor

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

 
 Featured Contents
SQLServerCentral Article

Basic Git for DBAs: Getting Started with Git

Steve Jones - SSC Editor from SQLServerCentral

Learn the basics of Git in this article if you've never used this version control system before. We will cover setting up a repo, adding files, changing them, and getting your history.

External Article

CASE STUDY: The hybrid monitoring needs of a fast-growing global business

Additional Articles from Redgate

With ambitious expansion plans, PointsBet needed an expansive overview of its server estate, whether on Azure or on-premises, anywhere around the world. Find out how SQL Monitor rose to the challenge.

External Article

How to get length of Text, NText and Image columns in SQL Server

Additional Articles from MSSQLTips.com

There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

Blog Post

From the SQL Server Central Blogs - Authentication Error in Azure Devops when Deploying DACPAC

Koen Verbeeck from Koen Verbeeck

I was building a CI/CD pipeline in Azure Devops, where I was building my SSDT project into a DACPAC file and then deploying it to the development server. For...

Blog Post

From the SQL Server Central Blogs - SQL Server 2019 :- Step By Step Installation.

Almighty from SQLSERVERSdba

In this blogpost we will see the step by step installation and few details about SQL Server 2019. For SQL Server 2019 Installation, We have following System Requirements SQL...

 

 Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

SQLCMD Variables

I have this script saved on my filessystem: 
USE AdventureWorks;

SELECT x.$(ColumnName)
FROM Person.Person x
WHERE x.BusinessEntityID < 5;
I want to call this from SQLCMD and replace the variable with a value. What parameter should I add to this code to get this to work? 
sqlcmd -S localhost -E  -i sqlcmdvar.sql

Think you know the answer? Click here, and find out if you are right.

 

 

 Yesterday's Question of the Day (by Steve Collins)

Scalar function string replacement

You create the following scalar function in SQL Server:

create function dbo.scalar_string_replace(
  @stringnvarchar(max))
returns nvarchar(max) as 
begin
    select @string = replace(@string, v.str_key, v.str_val)
    from (values (N'a', N'x'),
                 (N'b', N'y'),
                 (N'c', N'z')) v(str_key, str_val);

    return @string;
end

You run the following:

select dbo.scalar_string_replace(N'abc');

What is the output?

 

 

Answer: xyz

Explanation: The scalar function replaces all of the search terms using key value pairs from the virtual table.  When the variable assignment is reflexive, i.e. @string equals a REPLACE function with @string as an argument, the replacements happen in a loop.  The hierarchy of replacement is by string length so longer strings are replaced first. Ref: REPLACE - https://docs.microsoft.com/en-us/sql/t-sql/functions/replace-transact-sql?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
What are the dangers of utilizing the "No validation expected" in MSDTC - In our current circumstance we have two servers on two separate areas (one is in DMZ, other in inside organization) and we really want them to have the option to utilize dispersed exchanges. The exchanges would be started from the server on the inside network through connected server. The server on the DMZ would simply […]
Backup verify with third party tools - Is backup verify option available with third party tools (DDBoost or Lite speed or Redgate) like native backup in sql? What are the negative impact using backups with verify option?
Pre allocating free space to db growth - Hi SQL experts, What are advantages/Disadvantages, if you  pre allocate the extra space to the database growth (70-90%) ahead. How can you release the pre allocated free space? When I choose task shrink file, release unused space. It didn’t release any space even though there’s is 90% free space Thanks Ramana
Seeing messages in Errorlog - Hi All, We are seeing below messages in ERRORLOG. Does it indicate any pressure or just informational messages? Anything needs to be looked into? 2022-04-16 00:01:05.360 spid21s FlushCache: cleaned up 134823 bufs with 18733 writes in 62497 ms (avoided 3639 new dirty bufs) for db 27:0 2022-04-16 00:01:05.360 spid21s average writes per second: 299.74 writes/sec […]
Failed to open the explicitly specified database - Hello guys, have a nice day. I have sqlserver 2017 enterprise, i view log in ssms. I see error: Login failed for user 'WORKGROUP\SERVER$'. Reason: Failed to open the explicitly specified database 'SSISDB'. [CLIENT: ] I check in my database, SSISDB have no exist. This error writter many line in log. Can you guys […]
SQL Server 2017 - Development
When run Python script from SQL server 2017 get error ? - when run python script from sql server  2017 I get error Msg 39004, Level 16, State 20, Line 0 A 'Python' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004. Msg 39019, Level 16, State 2, Line 0 An external script error occurred: Error in execution. Check the output for more information. Traceback (most […]
Error when import data from excel to SQL server using Python ? - I work on SQL server 2017 I add script python to import data from excel to SQL python version 3.10 when run query below i get error declare @ImportPath NVARCHAR(MAX)='G:\ImportExportExcel' declare @DBConnectionString NVARCHAR(MAX) = 'Database=z2data;Uid=sa;Pwd=321' declare @ImportAll BIT=0 declare @CombineTarget BIT=0 declare @ExcelFileName NVARCHAR(200)='dbo.studentsdata' declare @ExcelSheetName NVARCHAR(50)='students2' --BEGIN TRY SELECT @ImportPath = CASE WHEN RIGHT(@ImportPath,1) […]
SQL server 2017 question - Hi all. Im wondering if you could all point me in the right direction. Im currently in school and the schools machines have SQL Server 2017 developper installed. Im trying to find myself and install media to install it on my own machind but every link a find brings me back to the 2019 version […]
Development - SQL Server 2014
SQL Server 2014 Standard - I just bought a SQL 2014 standard license from a vendor, but noticed that the product key can be looked up on google..... Does SQL 2014 have unique keys or does it just use one product key based on editions?
SQL Server 2019 - Administration
Delta Airlines Reservations Contact Number 818-286-3607 - Delta Airlines Reservations Contact Number - Delta Airlines is one of the major carriers of the world. It is the world largest carrier of the world by its fleet size, passenger carried, revenue and profit. Together with its regional partner Airlines Delta Eagle, they offer 6700 flights daily to 350 destinations in 50 countries. It […]
SET QUERY_STORE settings (SQL Server 2019) - I have a query I periodically run against the query store for retrieving the number of milliseconds of CPU consumed by the current database for time windows of the recent past (e.g. each 5-minute window for the past 4 hours).  That's all I'm using the query store for.  What are the best values to use […]
CPU usage of database for time spans in SQL Server 2019 - I want to track how much CPU usage (in ms, not %) was consumed for a database in a given time window.  I know how to do this with the Query Store, but I think it stores a lot more than I need, and I don't want to lose data due to seldom-used query plans […]
SQL Server 2019 - Development
OPENJSON unable to parse Portuguese characters - I'm trying to convert my JSON data into a table format in SQL Server Express 2019. Following are my JSON data: DECLARE @token INT; DECLARE @ret INT; DECLARE @url NVARCHAR(MAX); DECLARE @json AS TABLE(Json_Table NVARCHAR(MAX)) SET @url = 'http://www.test.com/webservice.php?user=DHRWWF&pass=CVernise&key=DFGJRTSrnwieuwn3&format=json' EXEC @ret = sp_OACreate 'MSXML2.XMLHTTP', @token OUT; IF @ret <> 0 RAISERROR('Unable to open HTTP connection.', […]
How a batch can get how much CPU it used in SQL Server 2019 - Is there something I can put at the end of a batch to find out how many milliseconds of CPU it used? Something like SET STATISTICS TIME but for the whole batch, not each statement.
C# Script - Hi everyone I have been working on a C# script to automate data collection.  I have been testing it and I have discovered one scenario that failed so my script needs to be amended.  I am not super experienced with coding so I am not quite sure how to make the fix.  I have included […]
 

 

RSS FeedTwitter

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

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -