SQLServerCentral.com Community Update for {date}

Problems displaying this newsletter? View online.

Featured Contents

Randy_Dyess from SQLServerCentral.com

Randy starts a multiple part series on tuning queries. We've asked Randy to really start from scratch and walk through the process. This week he takes a look at viewing query plans in Query Analyzer.

How to make your 2020 monitoring strategy a success

Press Release from Redgate

Join Redgate’s Arneh Eskandari and BMW’s Tony Maddonna for a free webinar on Nov 20: How to make your 2020 monitoring strategy a success. Learn how to prepare for successful estate growth, expected trends for the year, and the role monitoring plays in engabling growth and stability in your organization.

What’s New in SQL Server 2019

Additional Articles from Brent Ozar Unlimited Blog

In this video Brent takes a 40-minute look at some of his favorite new features: deferred compilation for table variables, adaptive memory grants, adaptive joins, and air_quote_actual plans

From the SQL Server Central Blogs - Invest in Yourself Stop Making Excuses

SQLEspresso from SQLEspresso

Bob Pusateri (B|T) tweeted a quote image that really struck a chord with me and elicited a strong reaction from myself. It got me thinking I need to write...

From the SQL Server Central Blogs - SQLSaturday Orlando Notes – Kahoot

Andy Warren from SQLAndy

The last two years we did Family Feud as part of our end of day gathering. Fun, exciting, and energetic are all words I’d use to describe it. Definitely...

Question of the Day

Today's question (by Steve Jones - SSC Editor):
	The Derived Table
What happens when I run this code? SELECT C.CustomerID FROM ( SELECT sh.CustomerID, OrderDate = MAX(sh.OrderDate) FROM dbo.SalesHeader AS sh GROUP BY sh.CustomerID ) AS C INNER JOIN C AS c2 ON C.CustomerID = c2.CustomerID;
Think you know the answer? Click here, and find out if you are right.

Yesterday's Question of the Day (by Thom A)

Ending a line with a Backslash

You have the below Procedure, that dynamically inserts data into the Table specified by the parameter `@TargetTable`:

CREATE PROC dbo.InsertFilePath @TargetTable   sysname,
                               @DirectoryPath NVARCHAR(255),
                               @FileName      NVARCHAR(255)
AS
BEGIN
    SELECT    @TargetTable = t.name
     FROM
              sys.schemas AS s
         JOIN sys.tables  AS t
             ON s.schema_id = t.schema_id
     WHERE
              s.name     = N'dbo'
              AND t.name = @TargetTable;
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL
        = N'
INSERT INTO dbo.' + QUOTENAME(@TargetTable)
          + N' (DirectoryPath, [FileName])
VALUES(@DirectoryPath, --This should be the full directory path, with the closing . For example \\ShareServer\ShareName\Directory\ 
       @FileName); --This should only be the name of the file, for example MyImage.jpg';
    EXEC sp_executesql
        @SQL,
        N'@DirectoryPath nvarchar(255), @FileName nvarchar(255)',
        @DirectoryPath,
        @FileName;
END;

Assuming the table `ImageFiles` exists on the `dbo` schema, what would be the result of the below SQL?

EXEC dbo.InsertFilePath @TargetTable = N'ImageFiles',
                        @DirectoryPath = 'Share01PublicCats',
                        @FileName = 'SleepyGeorge.png';

Answer: Incorrect syntax near ','.

Explanation: The line starting with `VALUES(@DirectoryPath,` ends with a which is immediately followed by a line break or carriage return and line break and is a literal string or binary value. As a result the will be interpreted as a "Backslash (Line Continuation)" character. This means the `SET @SQL` statement effectively becomes this:

    SET @SQL = N'
INSERT INTO dbo.' + QUOTENAME(@TargetTable) + N' (DirectoryPath, [FileName])
VALUES(@DirectoryPath, --This should be the full directory path, with the closing . For example ShareServerShareNameDirectory       @FileName); --This should only be the name of the file, for example MyImage.jpg';

As `@DirectoryPath,` is followed by a single line comment, this means the rest of the statement is put inside those comments and thus a syntax error is created. The Backslash (Line Continuation) can be used to split up both literal string and binary values. For example:

'This is a long literal string, and it''s too long to put on one line and not easily be readable\  
as a result you can use the backslash () character, and immediate follow it with a line break, or \ 
Carriage return and Line Break to have the string interpreted as one line, however, by the data engine it will be read as one.'

--Would be read as
'This is a long literal string, and it''s too long to put on one line and not easily be readable as a result you can use the backslash () character, and immediate follow it with a line break, or Carriage return and Line Break to have the string interpreted as one line, however, by the data engine it will be read as one.'

--Binary values work too

0x12345\ 
ABDEF

--Would be read as
0x12345ABDEF

It does not, however, work on numerical values:

SELECT 12345 98754

--Returns the error "Incorrect syntax near ''."

Backslash (Line Continuation) (Transact-SQL)

Discuss this question and answer on the forums

Featured Script

T-SQL query to get the latest available backup chain

Brahmanand Shukla from SQLServerCentral

T-SQL query to return the latest available database backup chain (Full, Differential and Log) of individual databases along with their backup size and compressed size.

/*
Author : Brahmanand Shukla
Date : 28-Oct-2019
Purpose : T-SQL query to get the latest available backup chain
*/
DECLARE @server_name VARCHAR(50) = 'ALL'
, @database_name VARCHAR(50) = 'ALL'
-- Uncomment the below section and supply your if you want to fetch the report for specific server
--SET @server_name = 'sql_server_1'
-- Uncomment the below section and supply your if you want to fetch the report for specific database
--SET @database_name = 'sql_database_1'
; WITH cte_Backup
AS
(
SELECT backupset.server_name
, backupset.database_name
, backupset.backup_start_date
, backupset.backup_finish_date
,
CASE backupset.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Log'
END AS [backup_type]
, CAST(((backupset.backup_size / 1024) / 1024) AS NUMERIC(18, 2)) AS [Backup_size_MB]
, CAST(((backupset.compressed_backup_size / 1024) / 1024) AS NUMERIC(18, 2)) AS [Compressed_Backup_size_MB]
, DATEDIFF(SECOND, backupset.backup_start_date, backupset.backup_finish_date) AS [Duration_Seconds]
, backupset.database_creation_date
, backupset.recovery_model
, backupmediafamily.physical_device_name
, backupset.[user_name]
, backupset.[backup_set_uuid]
, backupset.[database_backup_lsn]
, backupset.[differential_base_guid]
, ROW_NUMBER() OVER(PARTITION BY backupset.server_name, backupset.database_name, backupset.type
ORDER BY backupset.backup_finish_date DESC) AS [RowID]
,
CASE backupset.type
WHEN 'D' THEN 1
WHEN 'I' THEN 2
WHEN 'L' THEN 3
END AS [backup_type_sort_order]
FROM msdb.dbo.backupmediafamily backupmediafamily
INNER JOIN msdb.dbo.backupset backupset
ON backupmediafamily.media_set_id = backupset.media_set_id
WHERE (@server_name = 'ALL' OR backupset.server_name = @server_name)
AND (@database_name = 'ALL' OR backupset.database_name = @database_name)
)
, cte_Backup_Full
AS
(
SELECT *
FROM cte_Backup
WHERE [backup_type] = 'Full'
AND [RowID] = 1
)
, cte_Backup_Differential
AS
(
SELECT DIF.[server_name]
, DIF.[database_name]
, DIF.[backup_start_date]
, DIF.[backup_finish_date]
, DIF.[backup_type]
, DIF.[Backup_size_MB]
, DIF.[Compressed_Backup_size_MB]
, DIF.[Duration_Seconds]
, DIF.[database_creation_date]
, DIF.[recovery_model]
, DIF.[physical_device_name]
, DIF.[user_name]
, DIF.[backup_type_sort_order]
, DIF.[database_backup_lsn]
, ROW_NUMBER() OVER(PARTITION BY DIF.[server_name], DIF.[database_name], DIF.[differential_base_guid]
ORDER BY DIF.[backup_finish_date] DESC) AS [RowID]
FROM cte_Backup DIF
INNER JOIN cte_Backup_Full FUL
ON FUL.[server_name] = DIF.[server_name]
AND FUL.[database_name] = DIF.[database_name]
AND FUL.[backup_set_uuid] = DIF.[differential_base_guid]
WHERE DIF.[backup_type] = 'Differential'
AND DIF.[backup_finish_date] > FUL.[backup_finish_date]
)
, cte_Backup_Log
AS
(
SELECT AL.*
FROM cte_Backup AL
INNER JOIN cte_Backup_Differential DIF
ON DIF.[server_name] = AL.[server_name]
AND DIF.[database_name] = AL.[database_name]
AND DIF.[database_backup_lsn] = AL.[database_backup_lsn]
WHERE DIF.[RowID] = 1
AND AL.[backup_type] = 'Log'
AND AL.[backup_finish_date] > DIF.[backup_finish_date]
)
, cte_Backup_Chain
AS
(
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
FROM cte_Backup_Full
UNION ALL
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
FROM cte_Backup_Differential
WHERE [RowID] = 1
UNION ALL
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
FROM cte_Backup_Log
)
, cte_Backup_Full_All_DB
AS
(
SELECT [server_name]
, 'ALL_FULL' AS [database_name]
, NULL AS [backup_start_date]
, NULL AS [backup_finish_date]
, NULL AS [backup_type]
, SUM([Backup_size_MB]) AS [Backup_size_MB]
, SUM([Compressed_Backup_size_MB]) AS [Compressed_Backup_size_MB]
, SUM([Duration_Seconds]) AS [Duration_Seconds]
, NULL AS [database_creation_date]
, NULL AS [recovery_model]
, NULL AS [physical_device_name]
, NULL AS [user_name]
, 0 AS [backup_type_sort_order]
FROM cte_Backup_Chain
WHERE [backup_type] = 'Full'
GROUP BY [server_name]
)
, cte_Backup_Differential_All_DB
AS
(
SELECT [server_name]
, 'ALL_DIFF' AS [database_name]
, NULL AS [backup_start_date]
, NULL AS [backup_finish_date]
, NULL AS [backup_type]
, SUM([Backup_size_MB]) AS [Backup_size_MB]
, SUM([Compressed_Backup_size_MB]) AS [Compressed_Backup_size_MB]
, SUM([Duration_Seconds]) AS [Duration_Seconds]
, NULL AS [database_creation_date]
, NULL AS [recovery_model]
, NULL AS [physical_device_name]
, NULL AS [user_name]
, 0 AS [backup_type_sort_order]
FROM cte_Backup_Chain
WHERE [backup_type] = 'Differential'
GROUP BY [server_name]
)
, cte_Backup_Log_All_DB
AS
(
SELECT [server_name]
, 'ALL_LOG' AS [database_name]
, NULL AS [backup_start_date]
, NULL AS [backup_finish_date]
, NULL AS [backup_type]
, SUM([Backup_size_MB]) AS [Backup_size_MB]
, SUM([Compressed_Backup_size_MB]) AS [Compressed_Backup_size_MB]
, SUM([Duration_Seconds]) AS [Duration_Seconds]
, NULL AS [database_creation_date]
, NULL AS [recovery_model]
, NULL AS [physical_device_name]
, NULL AS [user_name]
, 0 AS [backup_type_sort_order]
FROM cte_Backup_Chain
WHERE [backup_type] = 'Log'
GROUP BY [server_name]
)
, cte_Backup_Full_Differential_Log_All_DB
AS
(
SELECT [server_name]
, 'FULL+DIFF+LOG' AS [database_name]
, NULL AS [backup_start_date]
, NULL AS [backup_finish_date]
, NULL AS [backup_type]
, SUM([Backup_size_MB]) AS [Backup_size_MB]
, SUM([Compressed_Backup_size_MB]) AS [Compressed_Backup_size_MB]
, SUM([Duration_Seconds]) AS [Duration_Seconds]
, NULL AS [database_creation_date]
, NULL AS [recovery_model]
, NULL AS [physical_device_name]
, NULL AS [user_name]
, 0 AS [backup_type_sort_order]
FROM cte_Backup_Chain
GROUP BY [server_name]
)
, cte_Final_Output_Staging
AS
(
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
, 1 AS [sort_priority]
FROM cte_Backup_Full_All_DB
UNION ALL
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
, 2 AS [sort_priority]
FROM cte_Backup_Differential_All_DB
UNION ALL
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
, 3 AS [sort_priority]
FROM cte_Backup_Log_All_DB
UNION ALL
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
, 4 AS [sort_priority]
FROM cte_Backup_Full_Differential_Log_All_DB
UNION ALL
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
, [backup_type_sort_order]
, (4 + [backup_type_sort_order]) AS [sort_priority]
FROM cte_Backup_Chain
)
SELECT [server_name]
, [database_name]
, [backup_start_date]
, [backup_finish_date]
, [backup_type]
, [Backup_size_MB]
, [Compressed_Backup_size_MB]
, [Duration_Seconds]
, [database_creation_date]
, [recovery_model]
, [physical_device_name]
, [user_name]
FROM cte_Final_Output_Staging
ORDER BY ROW_NUMBER() OVER(ORDER BY [sort_priority] ASC, [database_name] ASC, [backup_type_sort_order] ASC, [backup_finish_date] ASC)

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

Maintenance Plans, Agent Jobs, and logging - Open an agent job that's been created by your maintenance plan Open the step Click Advanced Checkmark "Include step output in history" Click OK until you've closed out of the job Open the maintenance plan. Do anything or nothing at all and click save Go back to the job and look at the checkbox Does […]

SQL Server 2017 - Development

Try/Catch Not Working - I have a log table that gets an insert right at the beginning in order to show the process started. Then it should do the merge/insert and either mark it as failed or succeeded. The initial 'in progress' insert is not happening and the failure is not being logged either. I have purposely tried inserting […]

Convert European format values to US Format - Hi: Is there a function or some code to convert a number that's European (4.100,00 - dots in place of comma for thousands and comma for decimal point) into US (4,100.00)? -278.999 should be -278,999 621.562,61 should be 621,562.61 Any help is appreciated. Thanks !

SQL Server 2016 - Administration

SQL Server Integration Services Wizard Oracle Provider Error - Hello experts, I tried posting this in SQL 2008 because that is the SQL Server version. But I got no answer after almost 2 days so I am trying one of the more active forums. Apologies in advance for cross-posting. I think it is more a driver issue than a SQL 2008 issue. A client […]

Ola Hallengren script : Where to change backup frenquency? - Hello, Following the recommendations of this forum, I ended up installing Ola Hallengren scripts weeks ago. During install of I set up the deletion of the backup to be every 7 days. I would like now to change that to 4 days. How would I do that? I had a look at the sp_delete_bakcuphistory but […]

Cannot connect to DB after Reboot but can after SQL Server engine restart - After a reboot of the computer, we see in the SQL Error Log problems to connect to one of the database STORESQL. Our software using this database cannot connect obviously. But after a simple restart of the SQL engine, the connection to the database is possible. Do you have an idea why? The logs […]

SQL Server 2016 - Development and T-SQL

Report every week, beginning 1st of month and ending last day of month - Hello! I need to run weekly report for each month on Friday, beginning the first day of each month, and ending the last day of the month Example · 10-1-2019 thru 10-4-2019 · 10-5-2019 thru 10-11-2019 · 10-12-2019 thru 10-18-2019 · 10-19-2019 thru 10-25-2019 · 10-26-2019 thru 10-31-2019 How can this be achieved? Thanks […]

SQL Data file issue - Hi All , I have a problem with big database that occupies some space in a drive , This drive is running out of space so I want to add more data file in a new added drive I tested a big insert ( million record ) into a table in this database HOWEVER the […]

How to get previous value(last matched records results) in SQL query result - Hi Every one, I need your help to achieve results. Can you please help me any one? My requirement is get last matched records results for unmatched records. I mean when matching the Name column with "ABC" then display corresponding records results value(988777) in expected result. else display previous result value. llly, when matching name […]

Development - SQL Server 2014

Report runs forever.` - Not sure why this report runs continously... Select distinct H.Customs_Entry_Num as [Entry Num] ,H.Entry_Summary_Date ,L.Part_Num ,L.HTS_Num ,l.HTS_Value ,L.Line_Item_Duty AS Duty ,z.HTS_NUMBER ,z. PTNR_ID ,Z.ELIGIBILITY ,z.Created_Date ,z.COMPOSITE_PART FROM ADHOC.ATS_ESH H INNER JOIN adhoc.ATS_ESL L ON h.TRANS_SK = l.TRANS_SK LEFT JOIN [TSI].[ZATS_BROKER_FEED] Z ON L.Part_Num = Z.COMPOSITE_PART AND Z.CREATED_DATE = ( SELECT max(Z.CREATED_DATE) FROM [TSI].[ZATS_BROKER_FEED] Z WHERE […]

SQL 2012 - General

Cannot reset password using T-SQL on migrated user to new server instance - Sorry, just found this: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server I'll have to review my problem.... DELETE post doesn't seem to be available. Hello, I have a newly built instance which is a test to migrate, restored all databases from my live server, I used SQL Server Data Tools to create a job to transfer all users. As […]

SQL Server 2008 - General

Generating unique serial numbers for Batches in Pharma Industry - In the front end when the client enters a quantity of 100/200 of a particular medicine, need to generate unique serial numbers for the batches which have validations of what to include/exclude and save 100/200 rows with all the details of batches along with a unique serial number. For example: if I'm ordering a quantity […]

T-SQL (SS2K8)

last day of the month minus 1 day - I am trying to get last day of the month minus one day. I am using belows query to get lasy day of the month. How can I get Last day of the month -1 day select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

Reporting Services

Maps - What is the best way to add location maps to SSRS - Hello, If I wanted to add a map of our employees and their work sites to an SSRS report. What is the best way to go about this. What is the best way to integrate maps? Thank you,

General

Select and update statement - Kindly assist on the below query 1. Convert all old data (without _N) to _OLD. Example, 000015 to 000015_OLD, same goes for the EmpName table, FURUSAKI to FURUSAKI_OLD while excluding / ignoring certain character such as '85XXXX', '80XXXX', 'TRXXXX', 'CRXXXX', 'MY', etc Basically, I wan amend the data with '00XXX' in front only. 2. Convert […]

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.