In your format file, you are telling it to delimit column 2 onwards with a Tab character, but the sample data you posted is comma delimited.

If you rename the view (e.g. from SSMS), sql_modules doesn't get updated, resulting in exactly what you are seeing.

Doesn't the last bit of the error message give it away?  "The text is encrypted"

I can't really work out how your code produces anything, but I'm not a Powershell expert by any means.

I assume your $server object is an SMO server object that you...

GilaMonster (8/11/2016)

TomThomson (8/10/2016)

Any particular place in Scotland? ...

Absolutely not... all contractors are scumbags......

More questions than answers, I'm afraid, but...

Taking your example of the driving licence, why wouldn't the scan of the driving licence just be an attribute of the person_driving_licence table?

I...

With no data or test script to work with, my guess would be that this part of the query

nashpats (7/22/2016)

...

The 2 queries are not equivalent. For example, you have included this join in the new code,

INNER JOIN BusIdentity bi ON tp.busIdentityId = bi.Id

but I can see no such relationship...

The replacement script you posted doesn't have a left join. Does this work?

INSERT #operator_new (operatorCode, storeId, personId, storeCode, password, name, mnemonic, accessGroup,active, loggedOn, language, country, dialect, changePassword, isInShift)

...

I suspect this line is your problem

AND timePeriod.personId =* person.id

You need to make the join to timePeriod a LEFT JOIN

One sheet short of a bog roll...

I'm with you with regard hating XML, but does this do what you want:-

DECLARE @input XML = '<TaxAudit xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Year>20152016</Year>

<AccountNumber>1234567890</AccountNumber>

<CertificateRequested>true</CertificateRequested>

</TaxAudit>'

SELECT

NodeName = C.value('local-name(.)',...

Without the extra detail that the others have requested, your problem is down to guesswork on our part.

That said, this line rings some alarms bells with me:

SELECT DISTINCT @Table =...