Synapse Spark executing SQL (via JDBC driver) funkiness - can't update newly created column, error is "Invalid column name"

276 Views Asked by At

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?

0

There are 0 best solutions below