title: SQLSTATE 42000 (Error 102) Incorrect syntax near ')'
body: <p><strong>General Setting</strong></p>
  
  <p>This is a weird question and I am sorry about that. I actually spent quite some time searching and analyzing our code. We have a job calling a stored procedure which selects some data and then calls other stored procedures (some which names are retrieved using the select-statements, since they might vary). It calls about 20 different of these stored procedures about 10'000 times (summed up the different calls), just varying the parameters. These stored procedures retrieve some data and then inserts the data into our databse.</p>
  
  <p><strong>Version of SQL-Server</strong></p>
  
  <p>This worked fine in Microsoft SQL-Server 2005, but since a little while we upgraded to SQL-Server 2012 (11.0.3000.0) and this problem seems to started occurring since then, or we simply were not aware of it before.</p>
  
  <p><strong>Error</strong></p>
  
  <p>So we get this error every time we execute it:</p>
  
  <pre><code>Executed as user: #DATABASEUSER_RMV_FOR_STACKOVERFLOW. Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102)  Incorrect syntax near ')'. 
  [SQLSTATE 42000] (Error 102).  The step failed.
  </code></pre>
  
  <p>I know this is very little information, but our scripts are pretty big and I would like to ask what some of you would to to figure out the problem.</p>
  
  <p><strong>What I did so far</strong></p>
  
  <p>I looked at the scripts, did some dry runs (b/c the entire script runs for about an hour... (it's a nightly job)). The dry runs worked fine. Also we hardly have opening brackets, and they always close. As soon as it retrieves data, after an hour of running it 'crashes' with this error... </p>
  
  <p>So what I did:</p>
  
  <ul>
  <li>Dry runs (without actually loading the data but calling most of the stored procedures) - ok, success</li>
  <li>Called the main-store-procedure directly (not as job) - did not work either</li>
  <li>Read through the code searched for bracket-errors - ok, no bracket errors found</li>
  <li>Runned with different users: db-admin, my user - did not work either</li>
  <li>Search through Google/Stackoverflow/a littlbe bit through stackexchange in general</li>
  </ul>
  
  <p><strong>Questions</strong></p>
  
  <p>It looks like everything the script has to do, is done correctly and completely, so we do not understand why it does not return 'success' and throws this error message at us.</p>
  
  <p>I could imagine that there might be a field it retrieves which contains an escape character... would that make sense? </p>
  
  <p>Could I set like a universal breakpoint, that the execution of the script would break as soon as this 'error' occurs and show me what the data is causing this error... like debugging code in visual studio?</p>
  
  <p>So my main question is: Could you please give me a hint/help how to approach this error in the best way? What I should do?</p>
  
  <hr>
  
  <p><strong>Job</strong></p>
  
  <pre><code>EXEC MY_SCHEME.dbo.MY_STOREDPROCEDURE_MAIN
  </code></pre>
  
  <p><strong>MY_STOREDPROCEDURE_MAIN</strong></p>
  
  <pre><code>SET NOCOUNT ON;
  
  -- Insert statements for procedure here
  DECLARE @userId INT
  DECLARE @fullHistory BIT
  
  SELECT @userId = userId
  FROM MY_SCHEME.dbo.USERS
  WHERE loginname = 'SOME_NAME'
  
  SET @fullHistory = 0
  
  EXECUTE MY_SCHEME.dbo.spStartMyNightlyJob @userId=@userId, @processFullHistory=@fullHistory
  </code></pre>
  
  <p><strong>spStartMyNightlyJob</strong></p>
  
  <pre><code>PROCEDURE [dbo].[spStartMyNightlyJob]
      @userId INT,
      @processFullHistory BIT
  AS
  BEGIN
      SET NOCOUNT ON
  
      DECLARE @logReport VARCHAR(255)
      SET @logReport = 'NightlyJob'
  
      INSERT INTO TEMP_LOGREPORT (text, report) VALUES('=======================================================================================================', @logReport)
      INSERT INTO TEMP_LOGREPORT (text, report) VALUES('NightlyJob started at ' + CAST(GETDATE() AS VARCHAR), @logReport)
      INSERT INTO TEMP_LOGREPORT (text, report) VALUES('=======================================================================================================', @logReport)
  
      DECLARE taskCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
          SELECT
              r.taskId, link.Id, i.Description, link.externalId, rdef.name, rdir.fromDB, rdir.toDB,
              rdef.procedureName, rfs.fillStrategyId, rp.parameterId
          FROM MY_TASK r
          LEFT OUTER JOIN some_table_1 link
          ON r.LinkId = link.LinkId
          LEFT OUTER JOIN some_table_2 i
          ON link.Id = i.Id
          LEFT OUTER JOIN some_table_3_TASK_DEFINITION rdef
          ON r.taskDefinitionId = rdef.taskDefinitionId
          LEFT OUTER JOIN some_table_4_TASK_DIRECTION rdir
          ON rdef.directionId = rdir.directionId
          LEFT OUTER JOIN some_table_5_FILL_STRATEGY rfs
          ON rdef.fillStrategyId = rfs.fillStrategyId
          LEFT OUTER JOIN some_table_6_PARAMETER rp
          ON rdef.parameterId = rp.parameterId
          WHERE r.active = 1
          AND rdef.taskDefinitionId NOT LIKE 17
  
  
      DECLARE @taskId INT
      DECLARE @someOtherId INT
      DECLARE @someOtherName VARCHAR(255)
      DECLARE @externalSomeOtherId INT
      DECLARE @taskName VARCHAR(50)
      DECLARE @fromDB VARCHAR(50)
      DECLARE @toDB VARCHAR(50)
      DECLARE @storedProcedure VARCHAR(100)
      DECLARE @fillStrategyId INT
      DECLARE @parameterId INT
  
      OPEN taskCursor
      FETCH NEXT FROM taskCursor
      INTO
          @taskId, @someOtherId , @someOtherName , @externalSomeOtherId , @taskName, @fromDB, @toDB, @storedProcedure,
          @fillStrategyId, @parameterId
  
      WHILE @@FETCH_STATUS = 0 BEGIN
          INSERT INTO TEMP_LOGREPORT (text, report) VALUES('-------------------------------------------------------------------------------------------------------', @logReport)
          INSERT INTO TEMP_LOGREPORT (text, report) VALUES('Performing task: ' + @taskName, @logReport)
          INSERT INTO TEMP_LOGREPORT (text, report) VALUES('Nightly Job between: ' + @fromDB + ' -&gt; ' + @toDB, @logReport)
          INSERT INTO TEMP_LOGREPORT (text, report) VALUES('Executive procedure: ' + @storedProcedure, @logReport)
          INSERT INTO TEMP_LOGREPORT (text, report) VALUES('Involved : ' + @someOtherName + ' (' + CAST(@someOtherId AS VARCHAR) + ')', @logReport)
  
          EXECUTE @storedProcedure @someOtherId , @externalSomeOtherId , @fillStrategyId, @parameterId, @userId, @processFullHistory
  
          INSERT INTO TEMP_LOGREPORT (text, report) VALUES('', @logReport)
  
          SET @taskId = NULL
          SET @someOtherId = NULL
          SET @someOtherName = NULL
          SET @externalSomeOtherId = NULL
          SET @taskName = NULL
          SET @fromDB = NULL
          SET @toDB = NULL
          SET @storedProcedure = NULL
          SET @fillStrategyId = NULL
          SET @parameterId = NULL
  
          FETCH NEXT FROM taskCursor
          INTO
              @taskId, @taskId , @someOtherName , @externalSomeOtherId , @taskName, @fromDB, @toDB, @storedProcedure,
              @fillStrategyId, @parameterId
      END
      CLOSE taskCursor
      DEALLOCATE taskCursor
  
      INSERT INTO TEMP_LOGREPORT (text, report) VALUES('=======================================================================================================', @logReport)
      INSERT INTO TEMP_LOGREPORT (text, report) VALUES('NightlyJob finished at ' + CAST(GETDATE() AS VARCHAR), @logReport)
      INSERT INTO TEMP_LOGREPORT (text, report) VALUES('=======================================================================================================', @logReport)
      RETURN 0
  END
  </code></pre>
  
  <p>After this it opens up to about 15 different stored procedures, depending which tasks are 'defined' every evening..</p>
  
  <p>If I comment-out the actual execution of it</p>
  
  <pre><code>EXECUTE @storedProcedure @someOtherId , @externalSomeOtherId , @fillStrategyId, @parameterId, @userId, @processFullHistory
  </code></pre>
  
  <p>then it completes without error. </p>
  
  <p>Thanks already for looking at my question ;-)</p>