SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Moving Dynamic Data Masked Data to New Locations - Step 5 of the Stairway to Dynamic Data Masking

Steve Jones - SSC Editor from SQLServerCentral.com

In this level of the Stairway to Dynamic Data Masking we examine how masking affects data movement.

Execute SSIS Package with SQLCMD and Parameters

Additional Articles from MSSQLTips.com

In this article we walk through the steps of how to run an SSIS package using SQLCMD along with passing parameters to the SSIS package.

Sharing SQL Code Snippets with Coworkers, The World and Yourself

Additional Articles from Redgate

Louis Davidson explains how he manages and shares several different sources of SQL Prompt code snippets, in a single Snippet library, using Dropbox and source control.

Free eBook: SQL Server Execution Plans, Third Edition

Press Release from Redgate

If a query is performing poorly, and you can't understand why, then that query's execution plan will tell you not only what data set is coming back, but also what SQL Server did, and in what order, to get that data. It will reveal how the data was retrieved, and from which tables and indexes, what types of joins were used, at what point filtering, sorting and aggregation occurred, and a whole lot more. These details will often highlight the likely source of any problem.

From the SQL Server Central Blogs - On table variable row estimations

Gail Shaw from SQL In The Wild

At first glance, the question of how many rows are estimated from a table variable is easy. But, is it really that simple? Well, not really. To dig into...

From the SQL Server Central Blogs - Setting Defaults for New SQL Compare Projects

Steve Jones - SSC Editor from The Voice of the DBA

Recently I wrote about ignoring comments in SQL Compare. That seems like something I want to do in all my projects, so I went looking for how to set...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	Starting a Python Program
I have a Python script full of functions. When I run this as a program, which function will be executed first?
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)

More CTE Issues

There is a problem in this CTE code. What is it?

WITH cteData (lowerbound, upperbound, letter, pwd)
AS (   SELECT
            SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) AS lowerbound
          , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) AS upperbound
          , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1)
          , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50)
       FROM dbo.Day2 AS d
   ORDER BY d.letter)
   , cteSolution
AS (   SELECT
            LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) AS occ
          , CASE
                WHEN LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) >= d.lowerbound
                     AND LEN(d.pwd) - LEN(REPLACE(pwd, d.letter, '')) <= d.upperbound THEN
                    1
                ELSE
                    0
            END AS valid
       FROM cteData AS d)
SELECT COUNT(*) FROM cteSolution WHERE valid = 1;
GO

Answer: The first CTE cannot have an ORDER BY

Explanation: A CTE cannot include an ORDER BY expression. Ref: CTE - https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-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 - Development

Number of rows in a table between two dates - Hi, I have a table that has dates and customers, I am trying to count the number of customers that signed up in the last hour up to that signed up. I have been trying to do a row_number count, but I am not sure how to add the "CREATE_DATE between 'CREATE_DATE and DATEADD(HOUR,-1,CREATE_DATE). CREATE […]

get data from two sql server table - Hello, I have two tables in my DB sql Server : Table1: LIST_POSTES columns : PK_POSTE , NAME_POSTE PK_POSTE NAME_POSTE ----------- -------- 1 Poste1 2 Poste2 Table 2: PARAMS_LIST_POSTES columns: FK_POSTE,NAME_PARAM, VALUE_PARAM FK_POSTE NAME_PARAM […]

SQL removing characters - I am using the below case statement , CASE WHEN Attributes LIKE ',Size = %,' Then right(Attributes, len(Attributes) - charindex('Size ', Attributes)) ELSE '' END as Size To get the below result ,Size = XXL ,Fits to Chest Size = 48 to 50 in,,Closure Type = Snap Button ,Material = Cotton ,Color = Khaki ,Sleeve […]

SQL Server 2016 - Development and T-SQL

Convert YTD data to Monthly data - Please can anyone help with converting a table with year-to-date (YTD) to monthly data. Test data: CREATE TABLE ytdExample ( [Year] VARCHAR(2) ,[Month] VARCHAR(2) ,[SiteCode] VARCHAR(5) ,[AccountCode] VARCHAR(4) ,[YTDValue] INT ) INSERT INTO ytdExample ([Year],[Month],[SiteCode],[AccountCode],[YTDValue]) VALUES ('19','01','SITE1','ACC1','1') ,('19','02','SITE1','ACC1','2') ,('19','03','SITE1','ACC1','4') ,('19','04','SITE1','ACC1','5') ,('19','05','SITE1','ACC1','7') ,('19','06','SITE1','ACC1','10') ,('19','07','SITE1','ACC1','12') ,('19','08','SITE1','ACC1','13') ,('19','09','SITE1','ACC1','14') ,('19','10','SITE1','ACC1','16') ,('19','11','SITE1','ACC1','17') ,('19','12','SITE1','ACC1','19') ,('19','01','SITE1','ACC2','3') ,('19','02','SITE1','ACC2','10') ,('19','03','SITE1','ACC2','11') ,('19','04','SITE1','ACC2','12') ,('19','05','SITE1','ACC2','15') ,('19','06','SITE1','ACC2','18') ,('19','07','SITE1','ACC2','20') […]

Development - SQL Server 2014

Problem in Bulk insert - I am trying to bulk insert from the below code for the given attached file but it fails. My code is as below USE AdventureWorks IF OBJECT_ID('dbo._export_annual_accounts', 'u') IS NOT NULL DROP TABLE dbo._export_annual_accounts; CREATE TABLE [dbo].[_export_annual_accounts] ( [record-id] [NVARCHAR](1000) COLLATE Latin1_General_CI_AS NULL , [document-id] [NVARCHAR](1000) COLLATE Latin1_General_CI_AS NULL , [company] [NVARCHAR](1000) COLLATE Latin1_General_CI_AS NULL […]

SQL 2012 - General

How to make pivot to feature values based on part ca and x exist on input data ? - I work on SQL server 2012 . I face issue I can't pivot features for every part based on display order . I need to display Features as Pivot for Parts Based on Part Id Exist on Table partsdata so I need to display features for multiple part on one row as pivot based on […]

SQL Compact and SQL Express/localdb conflict - I am having an issue with two SQL instances on the same machine: The primary application uses Microsoft SQL Server Compact 4.0 but when I install the Veeam Backup agent, it installs: -Microsoft SQL Server 2012 Express LocalDB -Microsoft SQL Server 2012 management Objects (x64) -Microsoft System CLR Types for SQL Server 2012 (x64) On […]

SQL Server 2019 - Administration

SQL Backup Not Compressing with TDE Enabled - All, I ran into a strange bug and wanted to see if anyone else has experienced this. It's on a SQL 2019 Standard (CU 8) instance with a TDE enabled database and database compression turned on at the instance level. There seems to be a bug when backing up a TDE enabled database with backup […]

MSSQL 2019 - SQL Server Agent - Jobs account - Hi everyone, I have a question regarding the execution of jobs in MSSQL 2019. I’m managing several MSSQL servers but I’m not a DBA so maybe my question will sound vapid. On a SQL mono-instance server, I have a user who is member of “ SQLAgentUserRole” on the database “msdb”. He created jobs […]

SQL Server 2019 - Development

Running a SQL Server 2008 database with CLR on SQL 2019 with strict security - Hi there We have a SQL 2019 server which has strict security enabled. On this server , we have a SQL 2008 R2 database which has assemblies . Now we want to be able to execute the CLR functionality of the 2008 R2 database but obviously the assemblies need to be signed with a certificate. […]

Reporting Services

Barcode fonts not rendering in report view or PDF export - We have a web application we use to generate reports on our inventory data and print barcodes for them. We've come across an issue where our barcode fonts don't show up. We've done the following: Confirmed the barcode font is installed on the application, database, and SSRS servers, as well as the workstations (just to […]

XML

Confusion with XML - I really have not worked a lot with XML and I've only learned of CROSS APPLY in the last couple weeks, but I've googled a lot on trying to solve this and after a lot of time, I hope I could get some help with the query. I have data stored using XML tags in […]

General

SQL Colors and Formatting - Hello, I am using the latest version of SSMS. I want to make it so that my variables, (Anything that starts with @) comes up a different color. Is there any way to do that? Thank You.

Integration Services

Visual studio 2019- Better performance to load 6.5 million record from flat file - Visual studio 2019- Better performance to load 6.5 million record from flat file to ole db destination (i.e. database table in ssms 2019) What is the scenario: I am using Visual studio 2019 to load data from flat files (txt files) to database tables in SSMS 2019. These tables are Staging tables. I have 10 […]

Article Discussions by Author

Download SQL Patch Info - Comments posted to this topic are about the item Download SQL Patch Info, which is is not currently available on the site.

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.