SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

Get-DbaPowerShell

When I first saw PowerShell, at TechEd 2006 (ish) and I was enamored. This was a much better environment than VBScript for working in a shell. Then I realized I needed -eq, -gt, etc. and was less excited. These language elements felt like a step back. Then I tried to build a restore script for SQL Server databases early on and was much less excited.

Across the years, I've played with various tasks in PoSh as opposed to T-SQL or simple command scripts in various languages, and I find myself going back and forth. PoSh is very useful in some ways, more cumbersome in others, and I still am not sure exactly how I feel about it as a go-to tool. I am coming around to use more PoSh because of the dbatools project, and I find myself considering PoSh instead of T-SQL, especially when I think I may want to work across instances and string together commands.

I was quite excited recently by T-SQL Tuesday #94, hosted by Rob Sewell. The topic was PoSh, and quite a few people participated, as you can see from the Get-PostRoundup. There are some creative uses of PoSh, such as importing Excel data, administering SSRS, turning Azure VMs off, cleaning up orphaned files, and more. Personally, once you start to use Azure more, PoSh makes the portal seem cumbersome and slow, and I find that for recurring tasks, it's a much better tool.

Recently I saw the DBAfromtheCold write a short piece on why you should learn PoSh. It's a good look at some of the tasks that are easy through PowerShell. Some of these could be done in T-SQL, some with other scripts, but PoSh certainly is a possibility and an easy way to manage some of the tasks you might need to do at scale.

I don't think PoSh is the best way to do everything. In fact, I often still lean on T-SQL for many admin things, especially if I already have a connection open in SSMS. However, as I learn more about PoSh and how to use it, I can make those judgment calls about when it's a better fit. That knowledge helps me become a more capable and stronger data professional, because I have choices about how to work with systems, and I have some knowledge or basis for the decisions I make.

I advocate continuing to learn throughout your career, and certainly including PoSh as a topic if you work on the Microsoft platforms.

Steve Jones from SQLServerCentral.com

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


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

ADVERTISEMENT
SQL Monitor

Don’t just fix SQL Server problems, prevent them from happening

SQL Monitor helps teams looking after SQL Server be more proactive. Not only does SQL Monitor alert you to current issues, it gives you the information you need to stop them happening in the future. Download SQL Monitor now and get a 14 day free trial

SQL Prompt

Write, format, and refactor SQL effortlessly with SQL Prompt

Writing SQL is 50% faster with SQL Prompt. Your SQL code can be formatted just the way you like it, you can create and share snippets with your team, and with tab coloring you’ll never deploy to the wrong environment again. Download your free trial

Featured Contents

 

Real-time moving averages for IoT with R

Nick Burns from SQLServerCentral.com

Computing an average over all your data is easy, but what if your data arrives incrementally and you need to be able to compute real-time averages on the fly? That's what we are going to look at. More »


 

Should DBAs learn Linux?

Additional Articles from Brent Ozar Unlimited Blog

Brent Ozar looks at the similarities between SQL Server on Linux and SQL Server on Windows Core from back in 2012, and asks whether DBAs should bother learning it. More »


 

SQL Prompt Tip: how to control when the suggestion box pops up

Additional Articles from Redgate

By default, SQL Prompt shows code auto-completion suggestions automatically, and continuously. 90% of the time this is exactly what you want, but there are other times where you just need a bit of space - Phil Factor shows how. More »


 

From the SQLServerCentral Blogs - Don’t forget to drop your views

Daniel Janik from SQLServerCentral Blogs

I had a fairly puzzling issue today, which took a few minutes to figure out. Some time ago I created... More »


 

From the SQLServerCentral Blogs - Query Azure CosmosDB from a SQL Server Linked Server

Rolf Tesmer (Mr. Fox SQL) from SQLServerCentral Blogs

Recently I had a requirement to combine data that I already had in SQL Server (2016) with JSON document data... More »

Question of the Day

Today's Question (by Steve Jones):

I run this code:

USE [master]
GO
ALTER DATABASE [sandbox2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

I get this error:

Msg 12824, Level 16, State 1, Line 3
The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database.  You may need to use RECONFIGURE to set the value_in_use.

What is wrong?

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


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: contained databases.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Expert T-SQL Window Functions in SQL Server

Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

When logically processing a query, which of these filtering operations takes place last?

Answer: HAVING

Explanation:

The logical order of query processing is:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

Ref: SELECT - click here


» 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 : SQL Server 2016 - Administration

SQL Compare Red Gate - Hello, Does SQL Compare by Red-Gate  can show me if the syntax of Stored Procedure at one instance is different from...

sa password fault - Possibly OK after restart - All, Setup is as follows; Two Server 2012 Datacenter servers Availability group running on SQL 2016 Standard. The first server started to refuse the...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Unpivot? - Hi all, I'm trying to turn Table A into Table B with a query.

Strange COALESCE issue - All, I think it's correct to provide a table structure for any questions so I'll include that first: /****** Object: Table...

Best method for coding stored proc SQL to get next key to avoid DUPs - Developer called.   She need best practice to accommodate the following: TableXYZ has a numeric KEY value, 1,2,3,4,5, etc... Application needs to grab...

Object Dependency - Hi All, I am using SQL below to get the object dependency. select Obj.name,dep.referenced_entity_name from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies as...


SQL Server 2014 : Administration - SQL Server 2014

Need to Change Compatibility Level of Lots of Databases - I have a SQL Server 2014 instance with quite a few databases on it.  I want to change the compatibility...

Database get faster after restart sql service - any reason for this? After restart it take 1 day for used all memory that fixed (128GB) every query running...

Automatic Failover in AO - Hi , We have a 2 Node SQL Server Failover Cluster and another node with Always ON. Now in Always On...

Insert fails when trying to create a table. -

Linked servers cannot be used under impersonation without a mapping for the impersonated login - Hi all I have a stored procedure that selects data from two instances. If I run the stored procedure it works...


SQL Server 2014 : Development - SQL Server 2014

select from flat file - How do select from a flat file? SELECT Field1 ,Field2 ,Field3   FROM OPENROWSET(BULK 'C:\Test.txt' , FORMATFILE='C:\Test.txt') AS Test WHERE Field1 <> 'N/A' This...

insert from flat file - How do you insert data into a table variable from a flat file? DECLARE @Format TABLE (     Field1 VARCHAR(50),     Field2 VARCHAR(50),     Field3 VARCHAR(10) ) BULK INSERT @Format (SELECT...

How can this query be rewitten? - Hi Experts, I have written a query based on our requirement using co-related sub-query. Checking if this can re-written using...


SQL Server 2012 : SQL Server 2012 - T-SQL

deleting duplicate rows based on a condition - I've cobbled together other answers from similar posts but can't quite get over the finish line. I'm trying to remove duplicate...


SQL Server 2008 : SQL Server 2008 - General

What else can take up space in a database besides tables - I have a database that says it's 108 GB USED - I verified with dbcc showfilestats as well as just clicking...


Reporting Services : SSRS 2016

Display Percentage for Top Value -

This email has been sent to {user_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.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com