SQLServerCentral - www.sqlservercentral.com

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

Featured Contents

Featured Script

The Voice of the DBA

Stairway to AlwaysOn Crossword

See if you can solve this crossword puzzle based on the contents of the Stairway to AlwaysOn articles.

Stairway to AlwaysOn Crossword

Across

6. A PC on the network.

7. Setup

10. You are the weakest link.

11. A set of databases that fails over together.

13. Grouped together.

16. Switch over to a secondary.

18. No commit until the secondaries are set.

20. Alpha.

21. The Domain Server's connected to the FCI. The FCI's connected to the Web Server. Etc.

22. Commit now, sync later (maybe).

24. Only planning S locks.

25. The person you are speaking to.

Down

1. Maintain the 9s.

2. Fancy Abacus

3. [Computer Node]{2,} = [IP]

4. I belong!

5. Doorways to SQL Server.

8. It's not a heart, spade, club or diamond, but it will help you connect with others.

9. Rust

12. Marketing term that covers Availability Groups and Failover Cluster Instances

14. Majority of voters

15. Things you need to get the work done.

17. Store copies in tempdb until not needed.

19. Location on the network.

23. Asynchronous failover.

Kenneth Fisher from SQLServerCentral.com

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

ADVERTISEMENT
GDPR

How to make your SQL Server development GDPR ready

Redgate’s SQL Data Privacy Suite helps you to build a data catalog of your SQL Server estate, and put in processes to protect and monitor personal information. Find out more about how our tools help with GDPR compliance

SQL Change Automation

CI/CD  for your SQL Server database

Feeling the pain of managing and deploying database changes manually? Redgate SQL Change Automation completes your database delivery process by building, testing, and deploying the database changes you and your team check into version control.  Try it free

Featured Contents

 

Stairway to SSAS Tabular Level 1: Why use Analysis Services

Thomas LeBlanc from SQLServerCentral.com

In this first level of the SSAS Tabular stairway, learn the benefits of implementing an SSAS solution. More »


 

Free eBook: SQL Server Transaction Log Management

Press Release from Redgate

When a SQL Server database is operating smoothly and performing well, there is no need to be particularly aware of the transaction log, beyond ensuring that every database has an appropriate backup regime and restore plan in place. When things go wrong, however, a DBA's reputation depends on a deeper understanding of the transaction log, both what it does, and how it works. More »


 

Examples of using XQuery to update XML Data in SQL Server

Additional Articles from MSSQLTips.com

What is the most efficient way to modify XML values? To allow for precise modification of XML nodes and values, the XQuery XML query language employs an extension known as the XML Data Modification Language (DML). The XML DML uses the XML modify() method, which makes use of three operational keyword sets: insert, replace value of, and delete. Seth Delconte takes a closer look. More »


 

From the SQLServerCentral Blogs - A bug in merge replication with FILESTREAM data

Gianluca Sartori from SQLServerCentral Blogs

I wish I could say that every DBA has a love/hate relationship with Replication, but, let’s face it, it’s only... More »


 

From the SQLServerCentral Blogs - Contained Database – No more need for Server Level Logins

SQLEspresso from SQLServerCentral Blogs

Starting in SQL Server 2012 and in Azure SQL Database, Microsoft introduced the concept of a contained database. A contained... More »

Question of the Day

Today's Question (by Steve Jones):

I have this data in the file sscdownload.py:

from urllib.request import urlopen

with urlopen('https://www.sample-videos.com/text/Sample-text-file-10kb.txt') as events:
    for line in events:
        print(line.decode('utf-8'))
 

At a command REPL for Python, I type this:

import sscdownload 

What happens?

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: Python.

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

Exam Ref 70-765 Provisioning SQL Databases

Prepare for Microsoft Exam 70-765–and help demonstrate your real-world mastery of provisioning SQL Server databases both on premise and in SQL Azure. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Henrico Bekker):

When deploying SQL Database as a Service in Azure, the following system databases are created by default on the logical server:

Answer: master, tempdb

Explanation:

Only the 'master' and 'tempdb' databases are created storing connection, security and logical server properties and configuration.

click here

Even though 'tempdb' is not visible from SSMS, it is possible to create and store temporary objects for the session in it as # objects, and size is subject to your Azure Database selected Tier.

Example:

create table #temp (new int) --Success

create table tempdb.dbo.temp (new int) --Failure

click here


» Discuss this question and answer on the forums

Featured Script

STRING and FIND SQL Functions

Pedro Costa from SQLServerCentral.com

----------------------------------------------------------------------------------------------------------------------------------

SQL – STRING Function

            Context

In transact-SQL there is no easy way to operate over strings, one having frequently to work with system functions like SubString, CharIndex, Stuff, Right, Left and others, that bring unnecessary difficulty for a t-SQL developer.

            Objective

Create a funtion STRING that allows an easy and intuitive application of one or several operators over a string in similarity with what already exists in object oriented-languages and in the new interpreted languages like Python and R.

            Definition

----------------------------------------------------------------------------------------------------------------------------------

            STRING (Transact-SQL)

Applies to: SQL Server (starting with 2012)

Returns a new string by applying operations over a string parameter.

            Syntax

STRING ( expression [, operator ] )

            Arguments

expression

Is a character, text or ntext expression.

For expression of different data types, converts to character.

operator

Character optional argument that defines the operations pattern to be applied to the expression

If the operator is not provided, the original expression is returned

            Operator Types

[i]                    Returns the character on the ith position

                                    i < 0: returns the character position of |i| counting backwards from the end

[i1:i2]               Returns the characters between the i1 and i2 positions

                                    i1 empty: returns all the characters up until i2

                                    i2 empty: returns all the characters starting from i1

                                    i1 and i2 empty: returns all the characters

                                    i1 or i2 < 0: counts the position of |i| backwards from the end

                                    i1 > i2: returns the characters in reverse from right to left

[o1,o2]             Returns the concatenation of the results from indexing operator o1 and o2

o1,o2                Returns the concatenation of the results from operator o1 and o2

s                      Returns the string s

            Return Types

Returns character data as:

Specified expression                                      Return Type

char/varchar/text                                            varchar

nchar/nvarchar/ntext                                      nvarchar

            Remarks

If the expression is null or empty or the result of applying the operators results in error, returns empty.

To include the ‘,’ as text, the comma should be preceded by the \ (backslash) character as ‘\,’.

The ith operator are int values. If numeric, they are rounded to int. If non-numeric, the operation is not considered.

The ith positions are 1 based.

Examples

A. Returning the 3rd and 5th character from expression

STRING(expression, '[3,5]')

B. Returning the first 2 and last 2 characters from expression

STRING(expression, '[:2],[-2:]')

C. Returning the last 3 characters in reverse order from expression

STRING(expression, '[:-3]')

D. Returning formated phone numbers as +351 ## ### ## ## from clients with format #########

Select

  STRING(PhoneNumber, '+351,[:2], ,[3:5], ,[6:7], ,[8:9]')

From

  Clients

E. Returning the last name from clients name

Select

  STRING(Name, '[' + FIND(Name, ' ', null, -1) + ':]' )

From

  Clients

            See also

FIND (Transact-SQL)


----------------------------------------------------------------------------------------------------------------------------------

SQL – FIND Function

            Context

In transact-SQL it is not versatile to perform searches over strings, one being limited to use the CharIndex function or equivalents.

            Objective

Create a function FIND that allows some versatility and easiness for searching the existence and position of an expression on a string.

            Definition

----------------------------------------------------------------------------------------------------------------------------------

            FIND (Transact-SQL)

Applies to: SQL Server (starting with 2008)

Searches an expression for another expression and returns its starting position if found.

            Syntax

FIND ( expression, expressionToFind [, startLocation ] [, occurrenceNumber ]  )

            Arguments

expression

Is a character, text or ntext expression to be searched.

For expression of different data types, converts to character.

expressionToFind

Sequence to be found

startLocation

int expression at which the search starts. If negative or zero, the search starts at the beginning of expressionToSearch

occurrenceNumber

int expression defining the number of the occurrence to be found.

If occurrenceNumber < 0 counts the occurrence to be found backwards from the end of the expression.

            Return Types

Returns int/bigint

            Remarks

If the expression or expressionToFind is null, returns null.

If expressionToFind is not found within expression or the solicited number of occurrence is greater then the number of occurrences found, returns 0.

The startingLocation and occurrenceNumber are 1 based.

Examples

A. Returning the starting position of ‘.’ in the expression

FIND(expression, '.')

B. Returning the position of the second occurrence of ‘.’ in the expression, starting from position 10

FIND(expression, '.', 10, 2)

C. Returning the last position of ‘.’ in the expression

FIND(expression, '.', null, -1)

----------------------------------------------------------------------------------------------------------------------------------

--==========================================================================

-- Description: Examples For FIND and STRING SQL Server Functions
-- Author: Pedro Costa
-- Create date: 2018-06-05
--==========================================================================
Use AdventureWorks2012
GO

-- ====== FIND

-- A. Returning the starting position of ‘-’ in the Phone Number
Select Distinct PhoneNumber, dbo.FIND('-', PhoneNumber, null, null) As Result From Person.PersonPhone

-- B. Returning the position of ‘.’ in the Document Summary
Select DocumentSummary, dbo.FIND('.', DocumentSummary, null, 1) As Result From Production.Document Where DocumentSummary Is Not Null

-- B.2 Returning the position of ‘.’ in the Document Summary, starting from position 60
Select DocumentSummary, dbo.FIND('.', DocumentSummary, 60, 1) As Result From Production.Document Where DocumentSummary Is Not Null

-- C. Returning the last position of ‘ ’ in the Reviewer Name
Select ReviewerName, dbo.FIND(' ', ReviewerName, null, -1) From Production.ProductReview

-- ====== STRING

-- A. Returning the 3rd and 5th character from ProductNumber
Select ProductNumber, dbo.STRING(ProductNumber, '[3,5]') As Result From Production.Product

-- B. Returning the first 2 and last 2 characters from ProductNumber
Select ProductNumber, dbo.STRING(ProductNumber, '[:2],[-2:]') As Result From Production.Product

-- C. Returning the last 3 characters in reverse order from Product Number
Select ProductNumber, dbo.STRING(ProductNumber, '[:-3]') As Result From Production.Product

-- D. Returning formated Credit Card Number as ### ### ### ##-### from clients with format ##############
Select CardNumber, dbo.STRING(CardNumber, '[:3], ,[4:6], ,[7:9], ,[10,11],-,[12:14]') As Result From Sales.CreditCard

-- ====== STRING & FIND

-- E.1 Returning the First Sentence of Document Summary
Select DocumentSummary, dbo.STRING(DocumentSummary, '[:' + CAST(dbo.FIND('.', DocumentSummary, null, 1) as varchar) + ']') As Result From Production.Document Where DocumentSummary Is Not Null

-- E.1.2 Returning the First Sentence of Document Summary, starting from position 60
Select DocumentSummary, dbo.STRING(DocumentSummary, '[:' + CAST(dbo.FIND('.', DocumentSummary, 60, 1) as varchar) + ']') As Result From Production.Document Where DocumentSummary Is Not Null

-- E.2 Returning the LoginID from Employees without the Domain
Select LoginID, dbo.STRING(LoginID, '[' + CAST(dbo.FIND('\', LoginID, null, null) as varchar) + ':]') As Result From HumanResources.Employee

-- E.3 Returning the last name from Stores Name
Select [Name], dbo.FIND(' ', [Name], null, -1) As Find, dbo.STRING([Name], '[' + CAST(dbo.FIND(' ', [Name], null, -1) as varchar) + ':]') As Result From Sales.Store

-- E.4 Returning the Reviewer Name from Product Review As LastName, First Name
Select
  ReviewerName,
  Case
    When dbo.FIND(' ', ReviewerName, null, -1) != 0
      Then dbo.STRING(ReviewerName, '[' + CAST(dbo.FIND(' ', ReviewerName, null, -1) as varchar) + ':],\, ,[:' + CAST(dbo.FIND(' ', ReviewerName, null, null) - 1 as varchar) + ']')
    Else
      ReviewerName
  End As Result
From Production.ProductReview

More »

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

SQL 2017 FCI with File Share Witness - permissions? - I have Win2016, SQL 2017 FCI with 2 node non shared storage using Always On AGs.  I have a separate...


SQL Server 2017 : SQL Server 2017 - Development

SQL design question. - As a way to keep my 72y/o brain stimulated, I am working on developing a C#/SQL server 2017 based health...

Need to display a column with deleted overlapping dates - I want to get rid of the following overlapping records from the table and display the below output. Also, I...


SQL Server 2016 : SQL Server 2016 - Administration

A Stairway series on Performance Tuning would be amazing! - Hi all I didn't know where to post this so just posting this here. I know it's a big topic with...


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

Dynamic SQL using sp_executesql with DbName parameter - What am I doing wrong in this statement or one like it? DECLARE @SQLString nvarchar(500); SET @SQLString =   N'SELECT BusinessEntityID, NationalIDNumber, JobTitle,...

SSRS Fixed Position Rectangles - Hello, I have a report that is designed to give us a visual representation of available kennel space in our facility....

Expression Builder - Default to 2 Saturdays ago - I'm building a SSIS pkg that runs every Monday; it picks up a .csv file from a share which gets...

Adding a Qualifier inside a format file .xml - Hello, I'm not sure if this may have been posted before but after searching and not finding it, here it goes; ...


SQL Server 2014 : Administration - SQL Server 2014

The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\120\COM' directory. System returned errorcode 5. - Hi Publisher: SQL 2008 Distributor: upgraded from SQL 2012 to SQL 2014 Subscribers: SQL 2008 / 2012 Current error on transactional replication: The distribution...


SQL Server 2014 : Development - SQL Server 2014

how do i find a specific status, and only that status - We have a table that contains part numbers, along with a status. because of the way the DB was designed, a...

It is possible to create a dynamic table from a result set ? - Hi guys, I need help for store in a table #temp result set of a function PIVOT dynamics and join others...

XML Query - Hi I have a row/column oriented xml to be inserted into a table. How do I write a XQuery (????? in code...


SQL Server 2012 : SQL Server 2012 - T-SQL

XML nodes help - The XML in the attached file is the XMLTEXT field in my CDRecords table. What code would i use to cross...


SQL Server 2008 : SQL Server 2008 - General

Clustered index scan for few items and index seek for other records - We have an issue with one of our old legacy system. After upgrading hardware to this system (SQL 2000), same...


SQL Server 2008 : T-SQL (SS2K8)

How to Use xp_dirtree to List All Files in a Folder - hello to everyone, i use the following code to list a filenames in a temporary table.the code runs well when i...


Data Warehousing : Integration Services

Help in getting SSIS to load a Data Partition - Hello, I have created a 1200 Version (2016) tabular cube. And in that cube I have create a number of...

TFS / SSIS - Get Latest Version not showing all dtsx files Solution Explorer - So myself and two other co-workers are using Visual Studio 2015 connected to TFS.  When they add a package to...


SQL Server 2005 : Development

Appdomain.CreateDomain throws SecurityException - throws error message "The domain manager specified by the host could not be instantiated." in clr stored procedure when I try...


SQL Server 7,2000 : T-SQL

Urgent! - How to extract number from a string - Hi, I try to extract number from a string, is there a SQL Server function to do that? If not, how...


Microsoft Access : Microsoft Access

Database Audit -

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 ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com