So here's a period at the end of the chapter, although a rather unsatisfying one.

I played with the T-SQL routine that does the actual backup, tidying up the syntax, adding...

• This reply was modified 3 years, 11 months ago by pdanes.

Well, now I feel like a complete dork. I re-connected to the server this morning, which was still logged into my account from last week, and still  had SSMS open...

• This reply was modified 3 years, 11 months ago by pdanes.

No, the database name is correct. From the VBScript routine, it is passed as a string literal, and those have not changed, nor has the name of any of the...

When choosing a plan optimiser has to estimate the data sets coming out of each of the objects (tables, views, functions) joined in the query.

it uses indexes, statistics, to...

Yes.    Eirikur's version of DelimitedSplit8k is fast AND is an inline table valued function.   Everyone who can't use String_Split should get a copy.   Also, use of...

Yes.    Eirikur's version of DelimitedSplit8k is fast AND is an inline table valued function.   Everyone who can't use String_Split should get a copy.   Also, use of an inline...

You could maybe replace the whole WHILE LOOP'ing function with Eirikur's LEAD splitter.  If this is SQL Server 2016+ you could use the built-in STRING_SPLIT itvf (since the...

The query could be simplified, no?  Maybe something like this

select top(1) p.AkcesAutoID
from dbo.podrobnosti p
     join ro.fntStringsToTable('J') ltrs on ltrs.EvidenceLetter = p.EvidenceLetter
 ...

Yes, in theory could be less efficient in some cases than a LOOP join.  But it's still reasonable performance.  You'll never totally drop off a performance cliff going from...

Looking at the two plans, it seems that the TOP 1 version tries to avoid the sort.   (With disastrous results.)    Apparently it can read the correct sequence from...

Well, I was thinking of replacing the entire JOIN clause with WHERE P.EvidenceLetter In('J') [or In('J','K','L')]  and so on. I tried that in my initial tests, and it also made...

BTW, the TVF does not always have only one record. Usually one, but it is possible to have almost 50. However, that would be an extreme case, and probably an...

It also doesn't matter where I put the HASH directive. Both of these work fine, and yield what looks to be the same execution plan:

INNER JOIN RO.fntStringsToTable...

Yep, that did it. Inner HASH Join, instead of Inner Join, and the query runs normally. The execution plan is still slightly more convoluted than without the Top clause, but...

• This reply was modified 4 years, 4 months ago by pdanes.

GOT IT!!! Finally - this site had all the necessary details in one place.

https://www.mssqltips.com/sqlservertip/2143/creating-a-sql-server-proxy-account-to-run-xpcmdshell/