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 2008 R2 Service Pack 2 SQL Server 2008 R2 Datacenter SQL Server 2008 R2 Enterprise SQL Server 2008 R2 Developer SQL Server 2008 R2 Standard SQL Server 2008 R2 Workgroup More…Less
Microsoft distributes Microsoft SQL Server 2008 R2 Service Pack 2 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 Service Pack 2 fix release.
Symptoms
Consider the following scenario:
-
You have a partitioned table that uses a datetimeoffset type column as a partitioning column in Microsoft SQL Server 2008 R2.
-
You use the New Publication Wizard to create a snapshot publication of the partitioned table in SQL Server Management Studio (SSMS).
In this scenario, you receive the following error message in one of the scripts that is used to create the snapshot publication. The scripts are automatically generated by the New Publication Wizard.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ».
Note For a detailed example scenario in which this issue would occur, refer to the «More Information» section.
Resolution
Cumulative update information
Cumulative Update 5 for SQL Server 2008 R2 SP2
The fix for this issue was first released in Cumulative Update 5. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 SP 2, click the following article number to view the article in the Microsoft Knowledge Base:
2797460 Cumulative update package 5 for SQL Server 2008 R2 Service Pack 2Note Because 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 2008 R2 SP 2 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 view the article in the Microsoft Knowledge Base:
2730301 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 2 was released
More Information
Assume that you create a partition function in SQL Server 2008 R2, and that the data type of input_parameter_type is datetimeoffset. Then you use Script Partition Function as in SSMS. In this scenario, the issue that is described in the «Symptoms» section occurs. Additionally, the date-literal range values generated by SSMS are not quoted in single quotation marks.
For example, you execute the following statement to create a partition function:
CREATE PARTITION FUNCTION PF_TEST (datetimeoffset(7)) as range right for values('20100101', '20100201', '20100301')
Then, you right-click the partition function in the Object Explorer, select Script Partition Function as, select Create To, and then select New Query Editor Window in SSMS. You see the following generated script:
CREATE PARTITION FUNCTION [PF_TEST] (datetimeoffset(7)) AS RANGE RIGHT FOR VALUES (1/1/2010 12:00:00 AM +00:00, 2/1/2010 12:00:00 AM +00:00, 3/1/2010 12:00:00 AM +00:00)
If you try to run the generated script, then you receive the following error message:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ’12’.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.
Need more help?
Incorrect syntax near ‘auto_increment may arise due to the factor that the SQL does not support Auto increment. The solution to the error is easy and requires the user to log in through MySQL.
Bobcares answers all questions no matter the size, as part of our Server Management Services
Let us take a look at the auto-increment error and its solution in detail
Incorrect syntax near ‘auto_increment’ Error
The main reason to get an error on AUTO_INCREMENT as SQL server does not support Auto-increment. SQL Server, like any other database, does not support single quotes in column names. For example, consider the table given below:
CREATE TABLE Invoice(
Invoice_No INT NOT NULL AUTO_INCREMENT,
Order_ID INT NOT NULL,
TotalPrice VARCHAR(30) NOT NULL,
Quantity VARCHAR(30) NOT NULL,
PRIMARY KEY (Invoice No),
FOREIGN KEY (Order_ID) REFERENCES OrderInfo (Order_ID) );
This table will generate the Incorrect syntax near the 'AUTO_INCREMENT'. error
The solution to the error
Firstly make sure to use MySQL, because AUTO INCREMENT doesn’t work with other DBs like SQL Server (use Identity(1, 1) instead). Secondly, when marking it as the PK, use Invoice No rather than Invoice No. Finally, Declare explicitly whether NAME is NULL or NOT NULL, removing the user’s reliance on the current connection settings.
So considering the above-mentioned solutions rewrite the table into the following:
CREATE TABLE sqlalchemy_generic_types ( sqlalchemy_generic_type_id INT IDENTITY(1, 1) PRIMARY KEY,
ObjectName VARCHAR(25) NOT NULL,
Description VARCHAR(100) NOT NULL
);
Take note of the modifications given to the table given above:
- The id assigns an increasing value by IDENTITY(). The id will have a descriptive name.
- Because the space has been removed from ObjectName, the name does not need to be escaped.
- To define the table, no escape characters are required.
[Need assistance with similar queries? We are here to help]
Conclusion
To conclude it is easy to manage and solve the ‘incorrect syntax near auto_increment’ error. The error might occur due to the fact that SQL does not support Auto increment put in a single column.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
GET STARTED
Posted by blakhani on August 28, 2014
Sometimes we are very comfortable and used to with certain things that if they change, we become nervous and uneasy. I have a lovely daughter and she always greets me when I come back home from office. That one “hello” takes away all my worries and I feel alive. Yesterday she didn’t do that and I was worried. I checked with my wife and she told that there was a mild fever and her mood is little different today. That made me little nervous.
Same feeling happened when I saw below in management studio of my colleague:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘GO’.
and management studio intellisense feature was also complaining about syntax. “Incorrect syntax near ‘End Of File’. Expecting ‘=’. As per documentation “GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor”
I went to Tools > Option in Management studio and found that there was a customization done on batch separator. SSMS> Tools > Options >Query Execution > SQL Server > General > Batch Separator.
Which means if I run with “come” it should work and as expected.
Perfect! This mystery is solved now. This made me think that can we customize SQLCMD as well. If we look into help of SQLCMD we can see parameter -c cmdend
Here is the usage of parameter –c . I have used hello as command end parameter and hello works same as go in true sense.
I hope this clears some confusion about batch separator.
If you are getting this error while using ExecuteNonQuery in .net program and running script then refer http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx which has a workaround.
Hope this helps!
This entry was posted on August 28, 2014 at 3:30 AM and is filed under SSMS, Troubleshooting.
Tagged: ExecuteNonQuery, incorrect syntax near go, msg 102, SQL, ssms, ssms tips, ssms tips and tricks. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.