If I run this SQL via SSMS (Sql Server Management Studio) there is no issue.
IF NOT EXISTS (
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[MyTableName]') AND name = 'Created'
)
BEGIN
ALTER TABLE MyTableName ADD Created DATETIME CONSTRAINT DF_MyTableName_Created DEFAULT(GETDATE())
UPDATE MyTableName SET Created = '1/1/1900' WHERE Created IS NULL
END
If I try to execute that SQL against a SQL Server instance in a Synapse Notebook, I get the error:
Exception com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'Created'
It's like the check on the legitimacy of the SQL runs prior to execution when running it from a Spark notebook.
I'd prefer not to run the "UPDATE ... WHERE Created IS NULL" everytime I run the notebook bc it's only going to happen at most one time.
Is there a clever way to get around this error?