Issues adding and updating a column

Question

Post reply

Issues adding and updating a column

richardmgreen1

SSChampion

Points: 10351
More actions
July 23, 2024 at 11:00 am

#4439077
Hi all

I'm hoping someone will able to say "you're an idiot because....." on this one.

We download a database but we have to add a column to a table and then update it.
The code to add/update is as follows:
```
IF NOT EXISTS
(
SELECT
*
FROM
UK_Health_Dimensions_New.INFORMATION_SCHEMA.COLUMNS c
WHERE
c.TABLE_SCHEMA= 'ODS'
AND c.TABLE_NAME = 'NHS_Trusts_And_Trust_Sites_SCD'
AND c.COLUMN_NAME = 'Organisation_Type'
)
BEGIN
ALTER TABLE UK_Health_Dimensions_New.ODS.NHS_Trusts_And_Trust_Sites_SCD ADD Organisation_Type VARCHAR(5) NULL;
END;

UPDATE
UK_Health_Dimensions_New.ODS.NHS_Trusts_And_Trust_Sites_SCD
SET
Organisation_Type= 'Site';

UPDATE
ntatss
SET
ntatss.Organisation_Type = 'Trust'
FROM
UK_Health_Dimensions_New.ODS.NHS_Trusts_And_Trust_Sites_SCD ntatss
WHERE
EXISTS
(
SELECT
*
FROM
UK_Health_Dimensions_New.ODS.NHS_Trusts_SCD nts
WHERE
nts.Organisation_Code = ntatss.Organisation_Code
);
```
This is run as part of the download job and only fails when it runs as part of the job.
If I run the code manually it runs the code without an issue.

The error message we get when running the job is:-
Invalid column name 'Organisation_Type'.

I've tried it would the BEGIN/END around the ALTER TABLE statement but it makes no difference (at least as far as running the job goes).

The job is owned by the SA account (which is disabled) and the step runs a calling script to do various bits and pieces.
The step is a standard "Transact-SQL Script (T-SQL)" according to the step type.

Anyone any ideas why it runs fine manually but won't run as part of the job?

TIA

Richard
- This topic was modified 4 months ago by richardmgreen1. Reason: Extra info

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

richardmgreen1 SSChampion Points: 10351 More actions · Answer 1

Hi all

Found the answer to my own issue (I hope).

It looks like you can't add and update a column in the same batch.

I've split the script into two stored procedures (they were one to begin with) and retesting.

Hopefully it will work this time.

Richard

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Using dynamic SQL will probably also do the job, should you wish to keep it in one.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 3

That makes sense. I was going to ask if maybe another script runs at the same time. Afaik processing-wise, the IF condition and the ALTER TABLE statement are 2 separate blocks so timing issues could occur

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

richardmgreen1 SSChampion Points: 10351 More actions · Answer 4

@phil - I've split it now. Might try dynamic SQL to see if there's a speed gain (unlikely to be anything worth mentioning)