Sql ошибка 102

MSSQLSERVER_102

Applies to: SQL Server (all supported versions)

Details

Explanation

Indicates a syntax error. Additional information is not available because the error prevents the Database Engine from processing the statement.

Can be caused by attempting to create a symmetric key using the deprecated RC4 or RC4_128 encryption, when not in 90 or 100 compatibility mode.

User Action

Search the Transact-SQL statement for syntax errors.

If creating a symmetric key using the RC4 or RC4_128, select a newer encryption such as one of the AES algorithms. (Recommended.) If you must use RC4, use ALTER DATABASE SET COMPATIBILITY_LEVEL to set the database to compatibility level 90 or 100. (Not recommended.)

Источник

MSSQLSERVER_102

Применимо к: SQL Server (все поддерживаемые версии)

Сведения

Объяснение

Указывает на синтаксическую ошибку. Дополнительная информация недоступна из-за того, что ошибка мешает компоненту Компонент Database Engine обработать инструкцию.

Может быть вызвана попыткой создать симметричный ключ с помощью устаревшего шифрования RC4 или RC4_128 при нахождении не в режимах совместимости 90 или 100.

Действие пользователя

Выполните поиск синтаксической ошибки в инструкции Transact-SQL.

При создании симметричного ключа с помощью RC4 или RC4_128 выберите более новое шифрование, например один из алгоритмов AES. (Рекомендуется.) Если необходимо использовать RC4, используйте ALTER DATABASE SET COMPATIBILITY_LEVEL, чтобы установить базу данных на уровень совместимости 90 или 100. (Не рекомендуется.)

Источник

Sql server error message 102

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I am very new to T- SQL and I got below find blocking SPID script from one of the blog spot.I’m just testing it and tried to modify it on SQL 2008 R2 and SQL 2012 to get an alert when there is blocking between SPID lasts more than 120 seconds. Below is my modified script which I tried to filter it but getting below error.

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ‘>’.
Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ‘>’.

— Checked for currenlty running queries by putting data in temp table
SELECT s.session_id
,r.STATUS
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) > 120.0 ‘WaitSec’
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) > 120 ‘ElapsSec’
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) + N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”) AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id

IF (
SELECT count(*)
FROM #temp_requests
WHERE blocking_session_id > 50
) 0
BEGIN
— blocking found, sent email.
DECLARE @tableHTML NVARCHAR(MAX);

Источник

SQLSTATE 42000 (Error 102) Incorrect syntax near ‘)’

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.

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

Error

So we get this error every time we execute it:

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.

• 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

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?

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

MY_STOREDPROCEDURE_MAIN

spStartMyNightlyJob

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

If I comment-out the actual execution of it

then it completes without error.

Thanks already for looking at my question 😉

Источник

SQLErudition.com

Learning SQL Server

Resolve Error: 102 while creating Full-Text Index Stoplist in SQL Server

One of my SQL Server databases was returning an error 102 while creating a full-text stoplist. We were trying to create a stoplist based on the system stoplist and later also tried to create a blank stoplist. The error happened both via SSMS, and equivalent TSQL commands.

In This Article

The Error, 102

The following TSQL gave the error –

The error dialog box –

Image 1 (click to enlarge)

The text in the error message –

The Cause

I looked at the MSDN page related to the TSQL command to check if I was using the right syntax.

REFERENCE:

• CREATE FULLTEXT STOPLIST (Transact-SQL)
  https://msdn.microsoft.com/en-us/library/Cc280405(v=sql.105).aspx

My syntax was correct but there was something else on the page that looked relevant. Right at the top of the documentation page is the following message –

Important
CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes.

To verify if the compatibility level of my database could indeed be an issue, I checked the properties of the database by –

There you have it! My database was originally on a SQL Server 2005 installation so its compatibility level was 90, and that was the reason the CREATE/ALTER/DROP STOPLIST commands were unavailable. The current server that I was working on was SQL Server 2008 R2, which could be checked by –

So the resolution to the error lies in changing the compatibility level. As per the documentation, the highest compatibility level I could go on a SQL Server 2008 R2 installation was 100.

REFERENCE:

• View or Change the Compatibility Level of a Database
  https://msdn.microsoft.com/en-us/subscriptions/index/bb933794
• ALTER DATABASE Compatibility Level (Transact-SQL)
  https://msdn.microsoft.com/en-us/subscriptions/index/bb510680

Changing the Compatibility Level

I checked that no other users were connected to the database and then issued this command to change the compatibility level.

It ran successfully and I could verify in the sys.databases catalog view that the compatibility level has changed to 100.

Now I was able to create a Stop List, Full-text Catalog and a Full-text Index on my table, and was able to run queries using the CONTAINS and CONTAINSTABLE keywords.

Источник

Hi All,

I am very new to T- SQL and I got below find blocking SPID script from one of the blog spot.I’m just testing it and  tried to modify it on SQL 2008 R2 and SQL 2012 to get an alert when there is blocking between SPID lasts more than 120 seconds. Below
is my modified script which I tried to filter it but getting below error.

Please help..

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ‘>’.
Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ‘>’.

SET NOCOUNT ON

— Checked for currenlty running queries by putting data in temp table
SELECT s.session_id
,r.STATUS
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) > 120.0 ‘WaitSec’
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) > 120 ‘ElapsSec’
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) + N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”) AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id

IF (
SELECT count(*)
FROM #temp_requests
WHERE blocking_session_id > 50
) 0
BEGIN
— blocking found, sent email.
DECLARE @tableHTML NVARCHAR(MAX);

SET @tableHTML = N’Blocking Report’ + N” + N” + N’session_id’ + N’Status’ +
N’blocking_session_idwait_typewait_resource’ +
N’WaitSec’ + N’cpu_time’ +
N’logical_reads’ + N’reads’ +
N’writes’ + N’ElapsSec’ + N’statement_text’ + N’command_text’ +
N’command’ + N’login_name’ + N’host_name’ + N’program_name’ +
N’host_process_id’ + N’last_request_end_time’ + N’login_time’ +
N’open_transaction_count’ + ” + CAST((
SELECT td = s.session_id
,”
,td = r.STATUS
,”
,td = r.blocking_session_id
,”
,td = r.wait_type
,”
,td = wait_resource
,”
,td = r.wait_time / (1000.0) > 120.0
,”
,td = r.cpu_time
,”
,td = r.logical_reads
,”
,td = r.reads
,”
,td = r.writes
,”
,td = r.total_elapsed_time / (1000.0) > 120.0
,”
,td = Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1)
,”
,td = Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) +
N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”)
,”
,td = r.command
,”
,td = s.login_name
,”
,td = s.host_name
,”
,td = s.program_name
,”
,td = s.host_process_id
,”
,td = s.last_request_end_time
,”
,td = s.login_time
,”
,td = r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
AND blocking_session_id > 0
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id
FOR XML PATH(‘tr’)
,TYPE
) AS NVARCHAR(MAX)) + N”;

EXEC msdb.dbo.sp_send_dbmail @body = @tableHTML
,@body_format = ‘HTML’
,@profile_name = N’SQLMail’
,@recipients = N’biradarsangamesh7@gmail.com’
,@Subject = N’Blocking Detected’
END

DROP TABLE #temp_requests

MSSQLSERVER_102

Применимо к: SQL Server (все поддерживаемые версии)

Сведения

Объяснение

Указывает на синтаксическую ошибку. Дополнительная информация недоступна из-за того, что ошибка мешает компоненту Компонент Database Engine обработать инструкцию.

Может быть вызвана попыткой создать симметричный ключ с помощью устаревшего шифрования RC4 или RC4_128 при нахождении не в режимах совместимости 90 или 100.

Действие пользователя

Выполните поиск синтаксической ошибки в инструкции Transact-SQL.

При создании симметричного ключа с помощью RC4 или RC4_128 выберите более новое шифрование, например один из алгоритмов AES. (Рекомендуется.) Если необходимо использовать RC4, используйте ALTER DATABASE SET COMPATIBILITY_LEVEL, чтобы установить базу данных на уровень совместимости 90 или 100. (Не рекомендуется.)

Источник

Error code 102 in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I am very new to T- SQL and I got below find blocking SPID script from one of the blog spot.I’m just testing it and tried to modify it on SQL 2008 R2 and SQL 2012 to get an alert when there is blocking between SPID lasts more than 120 seconds. Below is my modified script which I tried to filter it but getting below error.

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ‘>’.
Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ‘>’.

— Checked for currenlty running queries by putting data in temp table
SELECT s.session_id
,r.STATUS
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) > 120.0 ‘WaitSec’
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) > 120 ‘ElapsSec’
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) + N’.’ + Quotename(Object_name(st.objectid, st.dbid)), ”) AS command_text
,r.command
,s.login_name
,s.host_name
,s.program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
INTO #temp_requests
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC
,r.STATUS
,r.blocking_session_id
,s.session_id

IF (
SELECT count(*)
FROM #temp_requests
WHERE blocking_session_id > 50
) 0
BEGIN
— blocking found, sent email.
DECLARE @tableHTML NVARCHAR(MAX);

Источник

MSSQLSERVER_102

Applies to: SQL Server (all supported versions)

Details

Explanation

Indicates a syntax error. Additional information is not available because the error prevents the Database Engine from processing the statement.

Can be caused by attempting to create a symmetric key using the deprecated RC4 or RC4_128 encryption, when not in 90 or 100 compatibility mode.

User Action

Search the Transact-SQL statement for syntax errors.

If creating a symmetric key using the RC4 or RC4_128, select a newer encryption such as one of the AES algorithms. (Recommended.) If you must use RC4, use ALTER DATABASE SET COMPATIBILITY_LEVEL to set the database to compatibility level 90 or 100. (Not recommended.)

Источник

SQLErudition.com

Learning SQL Server

Resolve Error: 102 while creating Full-Text Index Stoplist in SQL Server

One of my SQL Server databases was returning an error 102 while creating a full-text stoplist. We were trying to create a stoplist based on the system stoplist and later also tried to create a blank stoplist. The error happened both via SSMS, and equivalent TSQL commands.

In This Article

The Error, 102

The following TSQL gave the error –

The error dialog box –

Image 1 (click to enlarge)

The text in the error message –

The Cause

I looked at the MSDN page related to the TSQL command to check if I was using the right syntax.

REFERENCE:

• CREATE FULLTEXT STOPLIST (Transact-SQL)
  https://msdn.microsoft.com/en-us/library/Cc280405(v=sql.105).aspx

My syntax was correct but there was something else on the page that looked relevant. Right at the top of the documentation page is the following message –

Important
CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes.

To verify if the compatibility level of my database could indeed be an issue, I checked the properties of the database by –

There you have it! My database was originally on a SQL Server 2005 installation so its compatibility level was 90, and that was the reason the CREATE/ALTER/DROP STOPLIST commands were unavailable. The current server that I was working on was SQL Server 2008 R2, which could be checked by –

So the resolution to the error lies in changing the compatibility level. As per the documentation, the highest compatibility level I could go on a SQL Server 2008 R2 installation was 100.

REFERENCE:

• View or Change the Compatibility Level of a Database
  https://msdn.microsoft.com/en-us/subscriptions/index/bb933794
• ALTER DATABASE Compatibility Level (Transact-SQL)
  https://msdn.microsoft.com/en-us/subscriptions/index/bb510680

Changing the Compatibility Level

I checked that no other users were connected to the database and then issued this command to change the compatibility level.

It ran successfully and I could verify in the sys.databases catalog view that the compatibility level has changed to 100.

Now I was able to create a Stop List, Full-text Catalog and a Full-text Index on my table, and was able to run queries using the CONTAINS and CONTAINSTABLE keywords.

Источник

DBeaver reports SQL Error [102] [S0001]: Incorrect syntax near ‘,’. on correct SQL #5805

Comments

rafael-yure commented Apr 25, 2019 •

System information:

• Operating system (distribution) and version
  Windows 10 1809 Build 17763.437
• DBeaver version
  6.0.3
• Java version
  1.8.0_191
• Additional extensions
  None

Connection specification:

• Database name and version
  SQL Server
  Microsoft SQL Sever 12.00.1400 (Azure)
• Driver name
  Microsoft JDBC Driver 7.0 for SQL Server 7.0.0.0
• Do you use tunnels or proxies (SSH, SOCKS, etc)?
  No.

Describe the problem you’re observing:

While trying to execute a valid SQL with a table alias, I get the SQL Error [102] [S0001]: Incorrect syntax near ‘,’. error:

Steps to reproduce, if exist:

Simply try to execute a SQL like this:
select c.column1, c.column2 from dbo.table c where c.column = 1 and c.colum3 = YVALUE and anotherValue is not null;

This returns the error. The error only occurs if you don’t select the whole line to run the query. If you select it, the query runs normally, proving that there is nothing wrong with the query itself.

This bug started with the release of the 6.0 version.

Include any warning/errors/backtraces from the logs

!ENTRY org.jkiss.dbeaver.model 4 0 2019-04-25 20:54:41.117 !MESSAGE Error executing query !SUBENTRY 1 org.jkiss.dbeaver.model 4 0 2019-04-25 20:54:41.118 !MESSAGE SQL Error [102] [S0001]: Incorrect syntax near ‘,’. !SUBENTRY 2 org.jkiss.dbeaver.model 4 0 2019-04-25 20:54:41.118 !MESSAGE Incorrect syntax near ‘,’. !STACK 0 com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ‘,’. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608) at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:859) at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:759) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218) at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:739) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:338) at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:467) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:407) at org.jkiss.dbeaver.model.DBUtils.tryExecuteRecover(DBUtils.java:1684) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:405) at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:849) at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2720) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:102) at org.jkiss.dbeaver.model.DBUtils.tryExecuteRecover(DBUtils.java:1684) at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:100) at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:102) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

The text was updated successfully, but these errors were encountered:

Источник

Общая настройка

Это странный вопрос, и я сожалею об этом. Я действительно потратил некоторое время на поиск и анализ нашего кода. У нас есть работа, вызывающая хранимую процедуру, которая выбирает некоторые данные, а затем вызывает другие хранимые процедуры (некоторые имена извлекаются с помощью операторов select, поскольку они могут меняться). Он вызывает около 20 различных этих хранимых процедур примерно в 10 000 раз (суммирует различные вызовы), просто изменяя параметры. Эти хранимые процедуры извлекают некоторые данные, а затем вставляют данные в нашу базу данных.

Версия SQL-Server

Это отлично работало в Microsoft SQL Server 2005, но с тех пор, как мы перешли на SQL-Server 2012 (11.0.3000.0), и эта проблема, похоже, началась с тех пор, или мы просто не знали об этом раньше.

ошибка

Поэтому мы получаем эту ошибку каждый раз, когда мы ее выполняем:

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.

Я знаю, что это очень мало информации, но наши скрипты довольно большие, и я хотел бы спросить, что некоторые из вас могли бы решить эту проблему.

Что я сделал до сих пор

Я просмотрел сценарии, сделал несколько сухих пробегов (b/c весь скрипт работает около часа… (это ночная работа)). Сухие трассы прекрасно работали. Также у нас почти нет открывающих скобок, и они всегда закрываются. Как только он извлекает данные, после часа работы он “сбой” с этой ошибкой…

Итак, что я сделал:

• Сухой запуск (без фактической загрузки данных, но вызов большинства хранимых процедур) – хорошо, успех
• Вызывается непосредственно основная процедура магазина (не как работа) – не работала ни
• Прочтите код, искавшийся для ошибок в скобках – ok, ошибок в скобках не найдено
• Работало с разными пользователями: db-admin, мой пользователь – не работал ни
• Поиск через Google/Stackoverflow/littlbe бит через stackexchange в целом

Вопросов

Похоже, все, что должен сделать скрипт, сделано правильно и полностью, поэтому мы не понимаем, почему он не возвращает “успех” и бросает это сообщение об ошибке.

Я мог представить, что может быть поле, которое он извлекает, содержащий escape-символ… это имело бы смысл?

Могу ли я установить универсальную точку останова, что выполнение скрипта сломается, как только появится эта ошибка, и покажите мне, что данные вызывают эту ошибку… например, отладка кода в visual studio?

Поэтому мой главный вопрос: не могли бы вы дать мне подсказку/помочь как лучше подойти к этой ошибке? Что я должен делать?

работа

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

После этого он открывает около 15 различных хранимых процедур, в зависимости от того, какие задачи “определяются” каждый вечер.

Если я прокомментирую фактическое его выполнение

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

то он завершается без ошибок.

Спасибо уже за то, что посмотрел на мой вопрос 😉