Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: Dynamic Query Problem

    I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.

    If I try to run it with QA, I get the following message:
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'FCST'.
    UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...

    However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.

    What could I be missing?

    Code:
    UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst,
     Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst,
     Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst,
     Dec_07=Dec_fcst 
    FROM (SELECT  [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT, 
      Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst, 
    Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst 
    FROM  PLAN_SHIP INNER JOIN BOD_HEADER 
    ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX 
    WHERE      (SCEN_ID = 1) AND ([Year] = 2007)
    ) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX
    WHERE     OPSPLAN.SRCPLAN = 'SHIP'
    Inspiration Through Fermentation

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd sic the SQL Profiler on this fella. My suspicion is that the UPDATE is being mis-parsed, possibly because of a syntax error within the previous SQL statement. Profiler ought to give you some clues if that is the case. No outright answers, just clues, but that's more than you have now.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    OK, I read up on it in BOL, and figured out how to get profiler running.
    I found the line where that particular statement is executing.
    I'm looking at StmtStarting and StmtCompleted. Is there something in
    particular that I should be looking for?
    Inspiration Through Fermentation

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Since the string "FCST" does not appear independently in your code, but only in conjuction with a month and an underscore character, I'd say that somewhere and underscore character is being dropped.

    Set the dynamic sql statement to print rather than execute, and bump up the Max characters setting in the Query Analyzer Options. Then see what code is actually being executed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Yeah, that "FCST" was throwing me, too. All of the instances in my string
    are "fcst" not "FCST". Anyway, after pouring through the sproc over and over, I found a PRINT statement that was causing the completely valild statement to display under the error message. It wasn't the cause at all,
    it was a previous statement. After I eliminated that, it was easy to narrow it down.

    Thanks
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •