I am executing below query and it gives me
SELECT
t1.s_reference "Section",
t2.m_reference "CourseRef",
t2.m_name "CourseName",
t3.mi_q02m02 "Aim",
t2.m_level "Level",
t2.m_start "Start",
t2.m_end "End",
t2.m_projstudents "Target",
COUNT(DISTINCT t4.e_id) "Enrolled"
FROM
isr t3,
person t5,
department t6,
section t1,
enrolment t4,
course t2
WHERE
t3.mi_id(+)=t2.m_id
AND t4.e_module=t2.m_id
AND t4.e_student=t5.p_id
AND(t2.m_status LIKE '%%'||SUBSTR((TO_CHAR(sysdate,'YYYY')+DECODE(least(to_number(TO_CHAR(sysdate,'mm')),8),8,0,-1)),-2,2) ||'%%')
AND t2.m_reference LIKE 'LL%%'
AND t4.e_status IN('C','S')
AND to_date(t2.m_start,'DD-Mon-RRRR')<=to_date(sysdate,'DD-Mon-RRRR')
AND to_date(t2.m_end,'DD-Mon-RRRR')>=to_date(sysdate,'DD-Mon-RRRR')
AND t2.m_modulesection=t1.s_id
AND t2.m_moduledept=t6.d_id
AND NVL(t2.m_close,0)=0
AND t3.mi_q02m02 IS NOT NULL
AND((
CASE
WHEN((t1.s_reference='LL2a')
AND(t2.m_reference LIKE '%W%'))
THEN 1
ELSE 0
END)=0)
AND(t2.m_reference!='LL3036E15')
GROUP BY
t1.s_reference,
t2.m_reference,
t2.m_name,
t3.mi_q02m02,
t2.m_level,
t2.m_start,
t2.m_end,
t2.m_projstudents,
t1.s_reference,
t2.m_reference
ORDER BY
t1.s_reference,
t2.m_reference
An error occurred while executing the query.
Incorrect syntax near ')'. MS SQL Server Error 102
I ran the same query in SQL Developer and it runs fine.
I tried to remove most of the parenthesis but still doesn’t help.
Any idea why this happens?
Thanks
Aruna
I got the following error when using CURSOR in creating temp tables:
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.
Msg 208, Level 16, State 0, Line 33
Invalid object name '##TEMP_Branch'.
without CURSOR everything works fine.
Here is the entire code:
declare @TableSchema table
(
Id Int Identity(1,1),
Name nVarchar(50),
DataType nVarchar(50)
)
declare @reportid uniqueidentifier
set @reportid = '597d37c0-563b-42f0-99be-a15000dc7a65'
declare @ttl nvarchar(100)
declare cur CURSOR LOCAL for
SELECT title
FROM ReportItems
where reportid = @reportid
and del = 0
ORder by so
open cur
fetch next from cur into @ttl
while @@FETCH_STATUS = 0 BEGIN
INsert @TableSchema Values(@ttl,'nVarchar(max) NULL')
fetch next from cur into @ttl
end
close cur
deallocate cur
Declare @Statement Varchar(1000)
Select @Statement = 'Create Table [##TEMP_Branch](FieldID Varchar(50)'
Select @Statement = COALESCE(@Statement +',','') + Name + ' ' + DataType from @TableSchema
Select @Statement = @Statement + ')'
EXEC(@Statement)
Select * from ##TEMP_Branch
drop table ##TEMP_Branch
Any kind help would be HIGHLY appreciated.
Ошибка Microsoft SQL Server: SQL SERVER-Msg 102, Уровень 15, состояние 1, строка 2 неправильный синтаксис рядом‘) ‘ или msg 102 уровень 15 состояние 1 строка 3 Неправильный синтаксис рядом ‘ ‘
Описание ошибки:
Это сообщение об ошибке возникает, если мы используем функцию GETDATE() like в качестве параметра хранимой процедуры или оператора функции. или вторая ошибка относится к проблеме, когда вставка данных может быть решена, сделав столбец не нулевым, а также может быть сделана по умолчанию для некоторого значения.
Фактическое сообщение об ошибке:
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’.’
Решение:
Эта проблема может быть решена с помощью локальной переменной для хранения значения Getdate() вместо передачи функции GETDATE() в качестве параметра непосредственно в операторе execute, а затем передать назначенную локальную переменную в качестве параметра.
или другое сообщение об ошибке подобное приведено ниже
Еще одно сообщение об ошибке:
msg 102 level 15 state 1 line 3 incorrect syntax near ' '
Решение:
Эту проблему при вставке данных можно решить, сделав столбец не нулевым, а также можно сделать значение по умолчанию некоторым значением.
alter table tablename
add default 0 for person_id
или
alter table TableName
Alter Column ColumnName NOT NULL SET DEFAULT 0
Пожалуйста, обратите внимание, что alter column может потребоваться удалить столбец и создать его заново, что не есть хорошо, когда таблица очень большого размера.
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
Table of Contents
Details of Error:
This error message occurs if we use GETDATE() like function as a parameter to Stored Procedure or Function Statement. or the second error is pertaining to problem when inserting data can be solved by making the column not null and also can be made default to some value.
Actual error message:
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ‘)’.’
Resolution:
This problem can be solved by using a local variable for storing the value of Getdate() instead of passing GETDATE() function as parameter directly in the execute statement and then pass the assigned local variable as parameter.
or Another error message similar is below
Another error message is:
msg 102 level 15 state 1 line 3 incorrect syntax near ' '
Resolution:
This problem when inserting data can be solved by making the column not null and also can be made default to some value.
alter table tablename
add default 0 for person_id
or
alter table TableName
Alter Column ColumnName NOT NULL SET DEFAULT 0
Additional information below:
Please note that alter column may need column to be dropped and re-created which can not useful when table is very large in size