Geography methods causing syntax error in job

• delizat4g

  Old Hand

  Points: 369

  More actions

  July 16, 2019 at 10:49 pm

  #3663625

  So...I'm trying to work with some geography in a job step. The entire step runs fine in the query window, but when put into the job the step fails. Here are a few examples from the job...does anything jump out?

  SET @point = (select "geo" FROM "objectLocation" where "objectId" = @objectId).MakeValid()

  SET @objarea = (select top 1 "objname" FROM "objarea" WHERE "areazdef".MakeValid().STIntersects(@point.MakeValid()) = 'TRUE' ORDER BY "ranking" desc)

  v/r

   

  Code-Blooded

• Wryan138

  SSCertifiable

  Points: 5660

  More actions

  July 17, 2019 at 3:43 am

  #3663656

  Check the setting for @@textsize ... I don’t think you’ seeing all of your geo data.

  Wayne

   

• delizat4g

  Old Hand

  Points: 369

  More actions

  July 17, 2019 at 7:12 pm

  #3663897

  Thanks for your reply

  It was getting all the geo data.

  I put the code in a stored procedure and called the procedure from the job. Anyone have an explaination on why something like this wouldn't work directly in a job step?

  Code-Blooded

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737892

  More actions

  July 17, 2019 at 9:39 pm

  #3663927

  What doesn't work? There is a different security context, perhaps different SET options, etc. You'd have to explain a bit about "what doesn't work".

   

  Have you tried running this under a proxy or other security context from SQLCMD?

• delizat4g

  Old Hand

  Points: 369

  More actions

  July 17, 2019 at 10:14 pm

  #3663937

  Here are some examples of what I'm trying to do, but the problem doesn't seem to be the syntax.

  SET @point = (select "geo" FROM "objectLocation" where "objectId" = @objectId).MakeValid()
  
  SET @objarea = (select top 1 "objname" FROM "objarea" WHERE "areazdef".MakeValid().STIntersects(@point.MakeValid()) = 'TRUE' ORDER BY "ranking" desc)

  When trying to use geography methods in a job step the job fails and returns the error "Incorrect syntax near....".

  The job is executing, atm, as a sysadmin. user.

  When the exact same code is put into a stored procedure and called from a job it works fine.

  v/r

  Code-Blooded

• delizat4g

  Old Hand

  Points: 369

  More actions

  July 17, 2019 at 10:29 pm

  #3663943

  Well...I figured it out.

  Pretty new upgrade to SQL Server 2016 instance and recently took the database out of 2008R2 compatibility also.

  So the stored procedure had 'SET QUOTED_IDENTIFIER ON'  the job didn't. Worked fine when I added it to the code in the job step.

  I thought it was set to default , buuut looks like it aint. lol

  Thanks for your help.

  v/R

  Code-Blooded