I’m trying to run the following fairly simple query in SQL Server Management Studio:
SELECT TOP 1000 *
FROM
master.sys.procedures as procs
left join
master.sys.parameters as params on procs.object_id = params.object_id
This seems totally correct, but I keep getting the following error:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ».
It works if I take out the join and only do a simple select:
SELECT TOP 1000 *
FROM
master.sys.procedures as procs
But I need the join to work. I don’t even have the string » in this query, so I can’t figure out what it doesn’t like.
halfer
19.7k17 gold badges95 silver badges183 bronze badges
asked Nov 1, 2013 at 15:57
Joshua FrankJoshua Frank
12.9k11 gold badges45 silver badges91 bronze badges
7
Such unexpected problems can appear when you copy the code from a web page or email and the text contains unprintable characters like individual CR or LF and non-breaking spaces.
halfer
19.7k17 gold badges95 silver badges183 bronze badges
answered Nov 4, 2013 at 8:15
5
Panagiotis Kanavos is right, sometimes copy and paste T-SQL can make appear unwanted characters…
I finally found a simple and fast way (only Notepad++ needed) to detect which character is wrong, without having to manually rewrite the whole statement: there is no need to save any file to disk.
It’s pretty quick, in Notepad++:
You should easily find the wrong character(s)
answered May 21, 2019 at 12:50
2
The error for me was that I read the SQL statement from a text file, and the text file was saved in the UTF-8 with BOM (byte order mark) format.
To solve this, I opened the file in Notepad++ and under Encoding, chose UTF-8. Alternatively you can remove the first three bytes of the file with a hex editor.
answered Sep 7, 2018 at 3:19
3
You can identify the encoding used for the file (in this case sql file) using an editor (I used Visual studio code). Once you open the file, it shows you the encoding of the file at the lower right corner on the editor.
encoding
I had this issue when I was trying to check-in a file that was encoded UTF-BOM (originating from a non-windows machine) that had special characters appended to individual string characters
You can change the encoding of your file as follows:
In the bottom bar of VSCode, you’ll see the label UTF-8 With BOM. Click it. A popup opens. Click Save with encoding. You can now pick a new encoding for that file (UTF-8)
answered Jan 15, 2021 at 18:32
I was using ADO.NET and was using SQL Command as:
string query =
"SELECT * " +
"FROM table_name" +
"Where id=@id";
the thing was i missed a whitespace at the end of "FROM table_name"+
So basically it said
string query = "SELECT * FROM table_nameWHERE id=@id";
and this was causing the error.
Hope it helps
answered Jun 7, 2019 at 3:39
IamButtmanIamButtman
1672 silver badges15 bronze badges
I got this error because I pasted alias columns into a DECLARE statement.
DECLARE @userdata TABLE(
f.TABLE_CATALOG nvarchar(100),
f.TABLE_NAME nvarchar(100),
f.COLUMN_NAME nvarchar(100),
p.COLUMN_NAME nvarchar(100)
)
SELECT * FROM @userdata
ERROR:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘.’.
DECLARE @userdata TABLE(
f_TABLE_CATALOG nvarchar(100),
f_TABLE_NAME nvarchar(100),
f_COLUMN_NAME nvarchar(100),
p_COLUMN_NAME nvarchar(100)
)
SELECT * FROM @userdata
NO ERROR
answered Jul 2, 2019 at 13:35
For me I was miss single quote in the statement
Incorrect One : "INSERT INTO Customers (CustomerNo, FirstName, MobileNo1, RelatedPersonMobileNo) VALUES ('John123', John', '1111111111', '1111111111)"
missed quote in John’ and ‘1111111111
Correct One: "INSERT INTO Customers (CustomerNo, FirstName, MobileNo1, RelatedPersonMobileNo) VALUES ('John123', 'John', '1111111111', '1111111111')"
answered Feb 23, 2022 at 13:37
abdellaabdella
2982 silver badges9 bronze badges
I was able to run this by replacing the ‘Dot’; with and ‘Underscore’; for the [dbo][tablename].
EXAMPLE:
EXEC sp_columns INFORMATION_SCHEMA.COLUMNS
GO //**this will NOT work. But will intelliSence/autocomplete as if its correct.
EXEC sp_columns INFORMATION_SCHEMA_COLUMNS
GO //**This will run in Synapse. but funny enough will not autocomplete.
answered Aug 15, 2022 at 19:16
- Remove From My Forums
-
Question
-
Can someone please tell me what normally causes this error message? I can’t even tell what line or what page is producing the error.
I’m trying to update information in SQL via an ASP.NET control — in my SET command I’m not currently including all columns in the statement (just testing to see if one column updates correctly first); could that be causing this
error, or is it something else?Thanks,
Matt
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ‘,’.
Source Error:
Server Error in ‘/www.mywebsite.com’ Application.
Incorrect syntax near ‘,’.
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near ','.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2032870 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5037992 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +215 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +178 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +394 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +697 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +95 System.Web.UI.WebControls.FormView.HandleUpdate(String commandArg, Boolean causesValidation) +1154 System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +408 System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +112 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +169 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +9 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +176 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
Version Information: Microsoft .NET Framework Version:4.0.21006; ASP.NET Version:4.0.21006.1
Answers
-
With help from MS tech support, I was able to resolve the issue by revising the BindFormView method (the Update code per all the comments here was fine—problem was rooted in the very last line where the BindFormView code is called)…In case it helps someone
else, here is the BindFormView code that enabled me to successfully use FormView to display and update images in MS SQL:Private Sub BindFormView() ' Get the connection string from Web.config. Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("MyDatabaseConnectionString").ToString()) ' Create a DataSet object. Dim dsPerson As New DataSet() ' Create a SELECT query. Dim strSelectCmd As String = "SELECT UserId,UserName,Age FROM Advertisements Where UserId=@UserId" ' Create a SqlDataAdapter object Dim da As New SqlDataAdapter() Dim selectCMD As SqlCommand = New SqlCommand(strSelectCmd, conn) da.SelectCommand = selectCMD selectCMD.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier) Dim currentUser As MembershipUser = Membership.GetUser() If currentUser IsNot Nothing Then ' Determine the currently logged on user's UserId value Dim currentUserId As Guid = CType(currentUser.ProviderUserKey, Guid) ' Assign the currently logged on user's UserId to the @UserId parameter selectCMD.Parameters("@UserId").Value = currentUserId Else selectCMD.Parameters("@UserId").Value = Guid.Empty End If ' Open the connection conn.Open() ' Fill the DataTable named "Advertisements" in DataSet with the rows ' returned by the query. Dim dsAdvertisements As DataSet = New DataSet() da.Fill(dsAdvertisements, "Advertisements") ' Bind the FormView control. AdvertisementForm.DataSource = dsAdvertisements AdvertisementForm.DataBind() End Using End Sub
-
Marked as answer by
Monday, July 5, 2010 11:26 PM
-
Marked as answer by
SQL Server 2012 Enterprise SQL Server 2012 Business Intelligence SQL Server 2012 Developer SQL Server 2012 Standard SQL Server 2012 Web SQL Server 2012 Express More…Less
Microsoft SQL Server 2012 Service Pack 1 fixes are distributed as one downloadable file. Given that the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 Service Pack 1 fix release.
Symptoms
When you run a Transact-SQL statement in SQL Server 2012, you receive the following error message:
Incorrect syntax near ‘begi’.
This issue occurs when the following conditions are true:
-
The statement contains an IF condition.
-
The IF condition does not contain BEGIN and END statements.
-
The IF condition is followed by a BEGIN TRY block.
-
The IF block is recompiled when you run the query.
Resolution
Cumulative update information
Cumulative Update 4 for SQL Server 2012 SP1
The fix for this issue was first released in Cumulative Update 4. For more information about how to obtain this cumulative update package for SQL Server 2012 SP1, click the following article number to go to the article in the Microsoft Knowledge Base:
2833645 Cumulative update 4 for SQL Server 2012 SP1Note Given that the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 SP1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to go to the article in the Microsoft Knowledge Base:
2772858 The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.
More Information
To reproduce this issue, run the following Transact-SQL statements in SQL Server 2012:
DECLARE @i INT
IF object_id(‘tempdb..#temptable’) IS NOT NULL
DROP TABLE #temptable
CREATE TABLE #temptable (id INT)
INSERT INTO #temptable VALUES (1),(2),(3);
IF year(getdate())=2012 SELECT @i=(SELECT COUNT(*) AS nr FROM #temptable);
BEGIN TRY
SELECT ‘message’
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH
Workaround
To work around this issue, add BEGIN and END statements to the IF condition.
References
For more information about the Incremental Servicing Model for SQL Server, click the following article number to go to the article in the Microsoft Knowledge Base:
935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems For more information about the naming schema for SQL Server updates, click the following article number to go to the article in the Microsoft Knowledge Base:
822499 Naming schema for Microsoft SQL Server software update packages For more information about software update terminology, click the following article number to go to the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates
Need more help?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SqlCommand command = new SqlCommand("INSERT INTO [Pacients] (Звание, ФИО, Родился(ась), И.Б.№, Проживание, Служба, Осмотрен лечащим врачом, Жалобы, Анамнез настоящего заболевания, Анамнез жизни, Перенесенные заболевания, Наследственность, Привычки, Прививки, Аллергологический анамнез, Данные объективного исследования, Местный статус, Диагноз, Зав. Отделением1, Лечащий врач1, План обследования, Зав. Отделением2, Лечащий врач2)VALUES(@Звание, @ФИО, @Родился(ась), @И.Б.№, @Проживание, @Служба, @Осмотрен лечащим врачом, @Жалобы, @Анамнез настоящего заболевания, @Анамнез жизни, @Перенесенные заболевания, @Наследственность, @Привычки, @Прививки, @Аллергологический анамнез, @Данные объективного исследования, @Местный статус, @Диагноз, @Зав. Отделением1, @Лечащий врач1, @План обследования, @Зав. Отделением2, @Лечащий врач2)", sqlConnection); command.Parameters.AddWithValue("Звание", textBox2.Text); command.Parameters.AddWithValue("ФИО", textBox1.Text); command.Parameters.AddWithValue("Родился(ась)", textBox3.Text); command.Parameters.AddWithValue("И.Б.№", textBox4.Text); command.Parameters.AddWithValue("Проживание", textBox5.Text); command.Parameters.AddWithValue("Служба", textBox6.Text); command.Parameters.AddWithValue("Осмотрен лечащим врачом", textBox7.Text); command.Parameters.AddWithValue("Жалобы", textBox8.Text); command.Parameters.AddWithValue("Анамнез настоящего заболевания", textBox9.Text); command.Parameters.AddWithValue("Анамнез жизни", textBox10.Text); command.Parameters.AddWithValue("Перенесенные заболевания", textBox11.Text); command.Parameters.AddWithValue("Наследственность", textBox12.Text); command.Parameters.AddWithValue("Привычки", textBox13.Text); command.Parameters.AddWithValue("Прививки", textBox14.Text); command.Parameters.AddWithValue("Аллергологический анамнез", textBox15.Text); command.Parameters.AddWithValue("Данные объективного исследования", textBox16.Text); command.Parameters.AddWithValue("Местный статус", textBox17.Text); command.Parameters.AddWithValue("Диагноз", textBox18.Text); command.Parameters.AddWithValue("Зав. Отделением1", textBox19.Text); command.Parameters.AddWithValue("Лечащий врач1", textBox20.Text); command.Parameters.AddWithValue("План обследования", textBox21.Text); command.Parameters.AddWithValue("Зав. Отделением2", textBox45.Text); command.Parameters.AddWithValue("Лечащий врач2", textBox44.Text); |
I’m trying to execute the following stored procedure:
CREATE PROCEDURE dbo.Compress_taille(@nom_table VARCHAR(64))
AS
PRINT @nom_table
declare @results table
(
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
Longest varchar(250),
SQLText varchar(250),
position float
)
INSERT INTO @results(TableName,ColumnName,DataType,MaxLength,Longest,SQLText,position)
SELECT
Object_Name(c.object_id) as TableName,
c.name as ColumnName,
t.Name as DataType,
case
when t.Name not like '%char%' Then 'NA'
when c.max_length = -1 then 'Max'
else CAST(c.max_length as varchar)
end as MaxLength,
'NA' as Longest,
'SELECT Max(Len([' + c.name + '])) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id) as SQLText,
column_id as position
FROM
sys.columns c
INNER JOIN
sys.types t ON c.system_type_id = t.system_type_id
WHERE
c.object_id = OBJECT_ID(@nom_table)
and t.Name <> 'sysname'
order by column_id
DECLARE @position varchar(36)
DECLARE @sql varchar(200)
declare @receiver table(theCount int)
DECLARE cursor_script CURSOR
FOR SELECT position, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN cursor_script
FETCH NEXT FROM cursor_script
INTO @position, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (theCount)
exec(@sql)
UPDATE @results
SET Longest = (SELECT theCount FROM @receiver)
WHERE position = @position
DELETE FROM @receiver
FETCH NEXT FROM cursor_script
INTO @position, @sql
END
CLOSE cursor_script
DEALLOCATE cursor_script
DECLARE @script_sql varchar(max)
set @script_sql=' create table [AQR_INF_2017T2].[dbo].'+ left(@nom_table, LEN(@nom_table)-LEN('39CR_201703')) +'39CR_201706('
DECLARE
@TableName VARCHAR(80),
@ColumnName VARCHAR(80),
@DataType VARCHAR(80),
@MaxLength VARCHAR(80),
@Longest VARCHAR(80),
@code_colonne VARCHAR(1000)
DECLARE getemp_curs CURSOR
FOR
SELECT TableName, ColumnName, DataType,MaxLength,
coalesce(case when Longest='0' then '10' else Longest end ,'1') as Longest,
position,
coalesce(
case when DataType like '%numer%' then '[' + ColumnName + '] float,'
when DataType like '%char%' then '[' + ColumnName + '] char(' + coalesce(case when Longest='0' then '10' else Longest end ,'1') + '), '
else '[' +ColumnName + '] ' + DataType+ ',' end,'[' +ColumnName + '] nvarchar(1),')
AS code_colonne FROM @results order by position
OPEN getemp_curs
FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne
WHILE @@FETCH_STATUS = 0 BEGIN
set @script_sql=@script_sql + @code_colonne
FETCH NEXT FROM getemp_curs into @TableName, @ColumnName, @DataType,@MaxLength,@Longest,@position,@code_colonne
END
CLOSE getemp_curs
DEALLOCATE getemp_curs
set @script_sql= case when left(@script_sql,1)=',' then left(@script_sql, LEN(@script_sql) -1) else @script_sql end + ') '
PRINT '@script_sql: ' + @script_sql
exec @script_sql
GO
But I when I execute this code:
DECLARE @table varchar(255)
DECLARE cursor_test CURSOR
FOR SELECT name FROM sysobjects WHERE type='U' and substring(name,1,3) not in ('T_P','T_Z','T_R','TEST_AQR') order by name -- SUPPRIME LES TABLES NON UTILES POUR L'INFOCENTRE AQR
OPEN cursor_test
FETCH NEXT FROM cursor_test
INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC AQR_INF_2017T2.dbo.Compress_taille @table
FETCH NEXT FROM cursor_test
INTO @table
END
CLOSE cursor_test
DEALLOCATE cursor_test
I get this error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.
This code was working for one year and now it doesn’t. Our version control does not seem to help either, and, unfortunately, the logic does not seem straightforward to me.
One thought was about the version of SQL Server causing breaking changes, but I am not convinced.
How would I go about troubleshooting this issue? Are there any good industry practices for tracking down script issues when dynamic sql is involved?
I need to verify where the breaking code starts, not necessarily where the syntax error occurs.
In this article let us go over the scenarios which causes an error like the below one and how to solve it.
Msg 102, Level 15, State 1,
Line 2 Incorrect syntax near ‘)’.’
Basically, we will get the error like the above one if we use GETDATE() like function as a parameter to EXECUTE/EXEC Stored Procedure/Function Statement.
To demonstrate this error scenario, let us first create a Stored Procedure and a User defined function as below:
--Create Demo Stored Procedure CREATE PROCEDURE dbo.SPGetYearPart(@Date AS DateTime) AS BEGIN RETURN YEAR(GETDATE()) END GO --Create Demo User Defined Function CREATE FUNCTION dbo.fnGetYearPart(@Date AS DateTime) RETURNS INT AS BEGIN RETURN YEAR(@Date) END GO
Below two examples demonstrates when this error occurs and how to solve it:
Example 1:
Try executing the above created stored procedure SPGetYearPart with GETDATE() as parameter:
DECLARE @RetValue INT EXEC @RetValue = dbo.SPGetYearPart GETDATE() SELECT @RetValue 'SP Return Value' GO
RESULT:
Solution:
Solution to this problem is: instead of passing GETDATE() function as parameter directly in the execute statement, assign it to a local variable and pass the assigned local variable as the parameter as shown in the below script:
DECLARE @RetValue INT DECLARE @Today DATETIME SET @Today = GETDATE() EXEC @RetValue = dbo.SPGetYearPart @Today SELECT @RetValue 'SP Return Value' GO
RESULT:
Example 2:
Try executing the above created user defined function fnGetYearPart with GETDATE() as parameter:
DECLARE @RetValue INT EXEC @RetValue = dbo.fnGetYearPart GETDATE() SELECT @RetValue 'Function Return Value' GO
RESULT:
Solution:
Solution to this problem is: instead of passing GETDATE() function as parameter directly in the execute statement, assign it to a local variable and pass the assigned local variable as parameter as shown in the below script:
DECLARE @RetValue INT DECLARE @Today DATETIME SET @Today = GETDATE() EXEC @RetValue = dbo.fnGetYearPart @Today SELECT @RetValue 'Function Return Value' GO
RESULT: