Post reply

Geography methods causing syntax error in job

  • 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

  • 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

     

  • 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

  • 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?

  • 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

  • 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

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

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