SQL SQLSTATE 42000（错误 102）')' 附近的语法不正确

Question

提问by skofgar

General Setting

通用设置

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.

这是一个奇怪的问题，我很抱歉。我实际上花了很多时间搜索和分析我们的代码。我们有一个调用存储过程的作业，该过程选择一些数据，然后调用其他存储过程（其中一些名称是使用选择语句检索的，因为它们可能会有所不同）。它调用了大约 20 个不同的这些存储过程大约 10'000 次（总结了不同的调用），只是改变了参数。这些存储过程检索一些数据，然后将数据插入到我们的数据库中。

Version of SQL-Server

SQL-Server 的版本

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.

这在 Microsoft SQL-Server 2005 中运行良好，但由于我们升级到 SQL-Server 2012 (11.0.3000.0) 一段时间后，此问题似乎从那时起开始发生，或者我们之前根本没有意识到。

Error

错误

So we get this error every time we execute it:

所以我们每次执行都会得到这个错误：

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.

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.

我知道这是很少的信息，但我们的脚本非常大，我想问你们中的一些人想找出问题。

What I did so far

到目前为止我所做的

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...

我查看了脚本，做了一些试运行（b/c 整个脚本运行了大约一个小时......（这是一项夜间工作））。干运行运行良好。此外，我们几乎没有左括号，而且它们总是闭合的。一旦它检索到数据，运行一个小时后它就会“崩溃”并出现此错误...

So what I did:

所以我做了什么：

Dry runs (without actually loading the data but calling most of the stored procedures) - ok, success
Called the main-store-procedure directly (not as job) - did not work either
Read through the code searched for bracket-errors - ok, no bracket errors found
Runned with different users: db-admin, my user - did not work either
Search through Google/Stackoverflow/a littlbe bit through stackexchange in general

试运行（没有实际加载数据但调用大部分存储过程） - 好的，成功
直接调用 main-store-procedure（不是作为工作） - 也不起作用
通读搜索括号错误的代码 - 好的，没有发现括号错误
与不同的用户一起运行：db-admin，我的用户 - 也不起作用
一般通过 Google/Stackoverflow/一点点通过 stackexchange 搜索

Questions

问题

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.

看起来脚本必须做的所有事情都正确且完整地完成了，所以我们不明白为什么它不返回“成功”并向我们抛出此错误消息。

I could imagine that there might be a field it retrieves which contains an escape character... would that make sense?

我可以想象它可能检索到一个包含转义字符的字段......这有意义吗？

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?

我可以设置一个通用断点，一旦发生这个“错误”，脚本的执行就会中断，并告诉我是什么数据导致了这个错误......就像在 Visual Studio 中调试代码一样？

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?

所以我的主要问题是：你能给我一个提示/帮助如何以最好的方式解决这个错误吗？我该做什么？

Job

工作

EXEC MY_SCHEME.dbo.MY_STOREDPROCEDURE_MAIN

MY_STOREDPROCEDURE_MAIN

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

spStartMyNightlyJob

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 + ' -> ' + @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

After this it opens up to about 15 different stored procedures, depending which tasks are 'defined' every evening..

在此之后，它会打开大约 15 个不同的存储过程，具体取决于每天晚上“定义”哪些任务。

If I comment-out the actual execution of it

如果我注释掉它的实际执行

EXECUTE @storedProcedure @someOtherId , @externalSomeOtherId , @fillStrategyId, @parameterId, @userId, @processFullHistory

then it completes without error.

然后它完成没有错误。

Thanks already for looking at my question ;-)

已经感谢您查看我的问题 ;-)

Answer 1

回答by MusicLovingIndianGirl

The problem is with your 2nd Insert statement.Replace your old one with this.

问题在于您的第二个 Insert 语句。用这个替换旧的。

create table #temp1 (text nvarchar(100),report nvarchar(500))
INSERT INTO #temp1 (text, report) VALUES('NightlyJob started at ' + ''  + CAST(GETDATE() AS VARCHAR) + '','Report')
select * from #temp1

SQL SQLSTATE 42000（错误 102）')' 附近的语法不正确

提问by skofgar

回答by MusicLovingIndianGirl

相关推荐

最近更新

标签

SQL SQLSTATE 42000（错误 102）')' 附近的语法不正确

提问by skofgar

回答by MusicLovingIndianGirl

相关推荐

如何在 Oracle 中查找模式名称？当您使用只读用户在 sql 会话中连接时

SQL 你如何返回表的列名？

TRY_CONVERT 在 SQL Server 2012 上失败

如何将 SQL 子查询转换为连接

相关推荐

最近更新

标签