Installing PostgreSQL 11 on Windows with Step-by-step instructions (2020-09-03)

 SQLServerCentral Newsletter for September 3, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Installing PostgreSQL 11 on Windows with Step-by-step instructions]( - [How do You Identify Object Dependencies in SQL Server]( - [From the SQL Server Central Blogs - Exchanging Schemas with SQL Compare Snapshots]( - [From the SQL Server Central Blogs - Persist Sample Percent in SQL Server IS NOT PERSISTED!]( Question of the Day - [Getting Debug Messages in PowerShell]( [Redgate SQL Prompt]( The Voice of the DBA  Daily Coping Tip Share Action for happiness with someone 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. Can You Catch SQL Injection? SQL Injection has been the bane of database administrators all over the world, with this being one of the main attack vectors used by hackers all over the world. I'd have hoped by 2010 we had stopped allowing most of these attacks, but this is still an issue in 2020. At least [it's not the biggest security issue](. Recently there was [a description of a hack against the Google cloud platform]( from some security researchers. They describe the process of working their way through an attack on the [Google Cloud SQL platform](. Specifically, they are going against a MySQL instance, though PosgreSQL and SQL Server are available as managed services. In their work, they find a way with SQL Injection to write to the file system, then they use this to start their malicious attack. Actually, it's not, but it could be if these weren't researchers. What I found really interesting was that as they were experimenting and doing things, someone detected their work. On the file system, they found a file, greetings.txt, with a note from an SRE at Google. They had detected the unauthorized hacking and left a note. Presumably they were also looking to patch the issue. They did, and eventually were in touch with Google the issue was patched. It's hard to tell how Google detected their work, perhaps when a container crashed, which isn't what we want from a database. However, in this new world of persistent storage and containerized apps, maybe that is how our database platforms will run. In any case, their efforts were detected. Would you detect some hacking on your database? Do you have some equivalent of [Advanced Threat Protection](~:text=New%2520SQL%2520Advanced%2520Threat%2520Protection%2520%28ATP%29&text=ATP%2520provides%2520a%2520single%2520go%2Ca%2520threat%2520to%2520the%2520database.) enabled? Any logging that might detect some unauthrorized use of xp_cmdshell or the equivalent? I bet more people don't, and wouldn't know about any attack until the attacker broke something. That's not ideal, and certainly, it's something that I would hope is addressed in future versions of SQL Server. I hope we get more security knobs, more auditing, and better controls. Of course, it's more likely that we'll just get advanced thread detection deployed locally through Azure Stack and at a $15/month cost. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Prompt](   Featured Contents [Installing PostgreSQL 11 on Windows with Step-by-step instructions]( greff379 from SQLServerCentral PostgreSQL is a free database management system (DBMS). PostgreSQL 11 is the new version of this DBMS. We will take a look at how to install PostgreSQL 11 on Windows. This article covers where to download PostgreSQL 11, how the installation process looks like, and how to connect to the PostgreSQL server. Step-by-Step Installation on Windows […] [How do You Identify Object Dependencies in SQL Server]( Additional Articles from MSSQLTips.com In this article we look at different ways to determine if one SQL Server database object is dependent on another database object. From the SQL Server Central Blogs - [Exchanging Schemas with SQL Compare Snapshots]( Steve Jones - SSC Editor from The Voice of the DBA Recently I was working with a customer and they asked if they could somehow package up their schema without the data and send this to a colleague. Absolutely, and... From the SQL Server Central Blogs - [Persist Sample Percent in SQL Server IS NOT PERSISTED!]( jsterrett from John Sterrett When a bug jumps out and surprises me I like to share it so others do not run into the same unexpected result. I don’t think there is any...   Question of the Day Today's question (by Steve Jones - SSC Editor):  Getting Debug Messages in PowerShell I have a script that uses Write-Debug to output certain debug information to the console. How do I get this information to appear for the user. I am testing with this script: write-host("starting") write-debug("debug 1") write-debug("debug 2") write-host("ending") If I run this from the command line, how do I get the debug messages ("debug 1" and "debug 2") to appear without stopping the script from running? 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) How Many Sales for Each Year? I have an R dataframe, called Sales. In this I have two columns, denoted Month and Year. I want to get a count for the number of records I have for each year. What expression should I use if I have the dplyr package loaded? Answer: sales %>% count(Year) Explanation: We only want to count by years, so we can ignore the Month column. With dplyr, there is a count() function that will do this for us. We pass in the dataframe as %>% the pipe operator. This sends the dataframe to the function. With the column of year specified, we get the count by year. Output for my dataframe is: > sales %>% count(Year) Year n 1 2011 2677 2 2012 2677 3 2013 24443 4 2014 29398 5 2015 24443 6 2016 29398 Ref: - Count observations by group - - Pipes in R - [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 - Administration [SPN for Always On Listener]( - Suppose I have the following two node cluster configured with an AG. All are on the same domain, use the same service account, and have named instance SQLInstance. SQLA and SQLB run on port 123 and SQLC (listener) runs on port 789 Node1 - SQLA Nodel2 - SQLB AG Name - TestAG AG Listener - […] [Unexplained tempdb growth]( - I left for vacation 2 weeks ago and while I was out, the tempdb drive had to be extended because it was growing like crazy. Before I left, I was monitoring some connections in the Activity Monitor; and upon leaving, I just locked my desktop. Would the Activity Monitor cause the tempdb to grow like […] [Planning a SQL Server Reporting Services (SSRS) 2016 installation]( - Hello experts, I'm trying to learn the architectural concepts of SSRS. Does SSRS have to be installed on the same host as the SQL Server that houses the ReportServer and ReportServerTempDB databases? Or can those dbs be hosted on, say, a separate cluster with the SSRS "front end" being set up as a web server? […] [Linked server to Oracle timing out]( - Hello experts, I am trying to get a linked server to an Oracle database to work. But I don't know why it keeps hanging when I try to expand the Tables section, with this error: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft […] SQL Server 2016 - Development and T-SQL [SUM and JOIN 2 columns with different tables but it duplicates results]( - Good day! Need help re SUM and JOIN 2 columns with different tables but it duplicates results here's the sample data tables; Transfer Shipment table; Loc Code  -  Item No  -  Description  -   Description2  -  Variant Code  -  Qty Location1      Item01        Active         Black […] [Remove spaces from an unstructured Text field]( - Hi Forumers, I've got a table with several Notes fields and I'm trying to remove ALL spaces from the text & replace with an underscore so the text is still easy to read. I've used REPLACE with LTRIM & RTRIM which fixed most spaces but not all. I found when I pasted the text into […] Development - SQL Server 2014 [Parse Semi colon data in seperate columns]( - Hi: I have semi-colon separated data in one column and I would like to split that in separate columns. CREATE TABLE #tblTest (FileData nvarchar(MAX)) INSERT INTO #tblTest values ('TEST1;Testing 123;') INSERT INTO #tblTest values ('TEST2;Testing.234;') INSERT INTO #tblTest values ('TEST3;Testing 345;') INSERT INTO #tblTest values ('TEST4;Testing 456;') INSERT INTO #tblTest values ('TEST5;Testing5.67;') SELECT * FROM […] [GROUP BY and SUM in inner join]( - My project contains below TABLES and sample data for it :: CREATE TABLE [DBO].[TBL_Budget] ( BudgetId INT IDENTITY(1, 1) NOT NULL ,BudgetName NVARCHAR(200) NOT NULL ,BudgetType INT NOT NULL ,BudgetAmount INT NOT NULL ,CONSTRAINT PK_Budget PRIMARY KEY (BudgetId) ) INSERT INTO [DBO].[TBL_Budget] VALUES ('B1',101, 500000) INSERT INTO [DBO].[TBL_Budget] VALUES ('B2',201, 1000000) --------------------------------------------------------------- CREATE TABLE [DBO].[TBL_School] […] SQL Server 2019 - Administration [Table Create Date issue?]( - I'm running SQL Server 19. Is there a known issues with the table create date? (search didn't yield anything) The Create and Modify date are taken from sys.all.objects. I created a database on July 17th and created most of the tables on the same date. Now looking at the Create Date (as part of a […] SQL Server 2019 - Development [SSIS Expression Task - Does it even work?]( - Okay, so I gave up on setting a string variable... and now I'm trying to just set a value for a Boolean variable, based on whether or not a variable with an integer row count is greater than zero or not.  This continues to fail with the error: Error: The wrapper was unable to […] [SSIS Expression Task - Can you set the value of a NON-Boolean variable?]( - From what I've seen online in various Google searches, folks have repeatedly claimed to be able to set the value of a variable using an Expression Task, but then the only examples show it setting a value for a Boolean variable as opposed to any kind of string or numeric type of variable.  I need […] [SSIS Variables can't be NULL, so how to pass NULL to sproc in Execute SQL Task?]( - This one has me flummoxed.  SSIS variables are not capable of holding a NULL value, so it seems seriously inconvenient that I can't construct a query with NULL values as well as ?'s for the other parameters, as then the Execute SQL task complains that it can't parse the query.  Is that a false error, […] Reporting Services [SSRS DAX Replace NULL Values]( - Hi, I've been trying to solve this for some time now, but nothing worked, and couldn't find any specific help online. I have a report built in SSRS using DAX, and I'm having multi valued parameters. In order to feed each parameter I have a separate dataset for each, and in this example I will […] SSDT [how to pass datetime variable from Execute SQL task to the execute process task]( - i have created two datetime variables in execute sql task and wanted to pass those variable to executed procees task following it. I was able to create the variables in the result set of Execute SQL task but they are not passed on to the next task. any suggestions are welcome. thanks Integration Services [Weird behaviour on Excel connection manager and files]( - I have an SSIS package that iterates over Excel files and it has been working fine without any issue for the past fortnight. Recently, it has been failing when new files are added due to the 'External table not in the expected format'. Nothing about the files have changed, but what I have noticed is […]   [RSS Feed]([Twitter]( 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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -