SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Fixing MLS Python Services to Load the Runtime

Emmitt Albright from SQLServerCentral

Recently I went to work on a SQL Server 2017 instance and couldn't get the Python runtime to load. I'd keep getting this error: A strange error for me to see, and one that didn't quite make sense. How can the runtime not launch? However, restarting the instance still resulted in this error: Msg 39021, […]

SQL and Scope

Additional Articles from SimpleTalk

Understanding scoping rules is a basic skill for developers. In this article, Joe Celko gives a bit of the history of scoping in early programming languages and shows how scoping applies to SQL queries as well.

Where SQL Server monitoring fits into your tech stack

Additional Articles from Redgate

This article explains the basic components of a tech stack, and the decisions and requirements that affect how and where monitoring tools generally, and a specialist SQL Server monitoring tool like SQL Monitor, in particular, fit into your stack.

From the SQL Server Central Blogs - Get the Governance Data You Need Out of Your Power BI Gateways with PowerShell

Aaron Nelson from SQLvariant

OK, before anyone yells at me: Yes, I know the On-Premises Data Gateways are now part of Power Platform, not just Power BI. I learned how to do everything...

From the SQL Server Central Blogs - Are Stored Procedures Faster Than Stand-Alone Queries?

Bert Wagner from Bert Wagner

Watch this week’s episode on YouTube. A few months ago I was presenting for a user group when someone asked the following question: Does a query embedded in a...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Real REAL
What is the equivalent of the REAL datatype?
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)

Fun with Arrays

I run this code to create a few arrays in my Python REPL.

>>> a = [1,2,3]
>>> b = [4,5,6]
>>> a
[1, 2, 3]
>>> b
[4, 5, 6]
>>> c = [7,8,9]
>>> d = [a,b,c]
>>> d
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]

Now I run this code:

>>> d[0][2] = 10
>>> d
[[1, 2, 10], [4, 5, 6], [7, 8, 9]]

When I now type "a" to see its value, what is returned?

Answer: [1, 2, 10]

Explanation: Since d was set to point to the arrays represented by a, b, and c, a change to d is reflected in those arrays. In this case, the change is to a, and it now has the same values that d shows, [1,2,10]. Since Python is 0 based, the 2nd element was the 3 and is now a 10. Ref: Python arrays - https://docs.python.org/3/library/array.html

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

Managing Mirroring failover in a WSFC cluster - Hi all, hope you can help. I'm not a DBA so have been googling as I go, so forgive my idiocy: I've got a SQL server standard instance with a number of databases. The setup is as follows: Servers 1 and 2 are in a SQL cluster with shared storage. Server 3 is a standalone […]

SQL profiler - Is SQL profile being deprecated and extended events should be used instead? Also, has anyone heard that there is more of a concern around Statistics rather than fragmentation ?

Extended Events- how to log to an SQL table? - I want to enable an Extended events session. However, I don't want it to go to a file on the HDD, I 'd really like to send it all to an SQL table so I can do grouping, analysis, reporting, etc... How can I get that data into an SQL table for longer term storage […]

SQL Server 2017 - Development

Most frequently used text in column - I would like to update a field (yearclass) for each row with the most common text in payclass for each grower, block, section and year combination. In the case where there are equal amounts of payclass, weight should be used to determine yearclass. Highest sum of weight wins. I have tried many queries of which […]

Insert into OPENDATASOURCE with Microsoft.ACE.OLEDB.16.0 provider - failed - Hi all, we export data into Excel file from SQL Server 2017 with a simple command (a part of a stored procedure): The command looks like: INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0', 'Excel8.0;HDR=YES;IMEX=1;Database=\\some_folder\excel1.xls')...Sheet1 SELECT * FROM table1 It works fine. But only if just one export is processing at the same time. If more exports are […]

Removing a double space in the middle of a name - Suppose I had a list of names and one of the names was Kathy Harrison. Notice the double space after the first name. In Excel there is a function called TRIM which could remove extraneous spaces. I am trying to find something like that in SQL. As far as I know, in SQL, TRIM is […]

SQL Server 2016 - Administration

SQL Server security after deleting AD group - I currently have a scenario where an AD group that was defined as a SQL login with particular permissions got deleted from AD but not from SQL. So: domaina\groupb - no longer exists as an AD group but still exists as a SQL login It seems that members of the deleted group still have SQL […]

Very large t-log with backups - I have a database that's 125GB, but the t-log is 190GB. I do full backups each day and t-log backups every 15 minutes. I've verified the log backups have been successful for the past week. Generally when I see a large log like this it is because backups are failing. The log isn't growing out […]

SQL Server 2016 - Development and T-SQL

Exporting very large Data to an XML file from SQL Server - I have a query that creates an xml file but I'm getting errors because the XML is too large. I've already set the Results To Grid Property XML Data to unlimited but I'm still getting the error.

Administration - SQL Server 2014

Always on - huge set of inserts and updates - Hi everyone, I need some advice please on how best to do something. We have a database which is 1TB in size, its part of a SQL 2014 Always On AG (synchronise sync). We have to do a very large data change, initial estimates are from testing it will take around 15 hours. Optimisation has […]

Development - SQL Server 2014

NOOB question with Case statement - hi here i am again with a noob question again: i'm trying to do this: case when columnA <> 0 then columnB = 1 else columnB end but it return error. i used case statment before but im the same column, is it possible to use in multiple column with the argument based on one […]

SQL 2012 - General

service pack Question - Hi, I updated our SQL ser5ver from Sp2 to SP 4 today, and it says it was a success. However when I run say: I get Sp 2 So did I get the update or not if not what can I do? Thank you SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

SQL Server 2012 - T-SQL

join with GEOMETRY data type - I want to perform something like.... SELECT * FROM a JOIN b ON a.geometryField = b.geometryField OR SELECT * FROM a JOIN b ON a.geometryField. STEquals(b.geometryField ) OR SELECT geometryField FROM a WHERE geometryField IN (SELECT geometryField FROM b ) You get the gist. None of the above work. I know I need […]

Need help query for current row and previous row - Hye, I've table and data as following, CREATE TABLE [dbo].[WeightStory]( [Idx] [int] IDENTITY(1,1) NOT NULL, [WeightDte] [datetime] NULL, [WhatWeight] [decimal](18, 3) NULL, CONSTRAINT [PK_WeightStory] PRIMARY KEY CLUSTERED ( [Idx] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[WeightStory] […]

Reporting Services

Failed Executions - Curious what everyone else is doing... I've always had a daily report showing me report subscriptions that failed. I've had a task to better monitor all report interactions (subscriptions, cache refresh and interactive) sitting in my queue, but I put it off because of other higher priority tasks. Right now I'm in the process of […]

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.