Sql server error incorrect syntax near

Incorrect syntax near »

I’m trying to run the following fairly simple query in SQL Server Management Studio:

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:

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.

8 Answers 8

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.

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++:

  • Click «New file»
  • Check under the menu «Encoding»: the value should be «Encode in UTF-8»; set it if it’s not
  • Paste your text
  • From Encoding menu, now click «Encode in ANSI» and check again your text

You should easily find the wrong character(s)

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.

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.

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)

Источник

SqlException (0x80131904): Line 28: Incorrect syntax near ‘(‘.]

Please someone help me! I have an app running on windows 2003 with sql server 2005. When I try to deploy this some app in other server on windows 2003 with sql server 2000, some pages of the app shows the message bellow:

an unsing the tecnologies bellow:

ADO.net and LINQ

8 Answers 8

open your model .edmx with xml editor, and modify this

it might be the linq .first() that generates the sql script ‘Top (1)’ which works in sql2005, but should be ‘top 1’ with out ‘(‘and ‘)’ in sql 2000

it works in my solution.

In the .edmx file you need to change the ProviderManifestToken to 2000 in the Schema tag to ProviderManifestToken=»2000″ (it will be ProviderManifestToken=»2005″)

Some SQL Server 2005 queries which contain 2005-specific extensions like ROW_NUMBER(), common table expressions [e.g. WITH x AS (. ) SELECT . FROM x], etc. are not backwards compatible with SQL Server 2000.

You must either ensure that your application runs only on SQL Server 2005 or you must rework your queries that are specific to SQL Server 2005 to support SQL Server 2000, 2005, and probably 2008 too for good measure. There is plenty of documentation available as to what the differences are in T-SQL syntax and semantics among the different versions of SQL Server.

I’m afraid you’re not going to get much more specific help from anyone without revealing the actual query text. That would be like me asking you to correct my spelling error on line 12 of my 3rd year English term paper. No, you can’t have it, but can you proofread it for me?

READ  Timeout error flip atmel

Источник

KB2791626 — FIX: «Incorrect syntax near» error when you use a «datetimeoffset» type column as the partitioning column in SQL Server 2008 R2

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:

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.

Источник

KB2780146 — FIX: «Incorrect syntax near ‘IDENTITY_VALUE'» error when you try to decrypt data that is migrated from another database in SQL Server 2008 R2, SQL Server 2012 or in SQL Server 2008

Symptoms

Consider the following scenario:

You create a symmetric key from an Extensible Key Management (EKM) provider in Microsoft SQL Server 2008, Microsoft SQL Server 2012 or Microsoft SQL Server 2008 R2.

You encrypt some data in a database by using this symmetric key.

You migrate the data to another database.

You try to create a symmetric key in the new database from the same EKM provider. However, encrypted data in the new database cannot be decrypted because the newly created symmetric key has a GUID number, and this GUID number differs from the original symmetric key that encrypted the data in the original database.

To re-create the symmetric key by using the same GUID as the original symmetric key, you use the IDENTITY_VALUE option to specify the identity phrase that was used with the original symmetric key.

In this scenario, you receive an error message that resembles the following:

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ‘IDENTITY_VALUE’

Resolution

Cumulative update information

Cumulative update 6 for SQL Server 2012

The fix for this issue was first released in Cumulative Update 6. For more information about how to obtain this cumulative update package for SQL Server 2012, click the following article number to view the article in the Microsoft Knowledge Base:

2728897 Cumulative update package 6 for SQL Server 2012Note 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 2012 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:

READ  Атолл 55ф прошивка последняя

2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released

Cumulative Update 11 for SQL Server 2008 R2 Service Pack 1

The fix for this issue was first released in Cumulative Update 11. For more information about how to obtain this cumulative update package for SQL Server 2008 R2 Service Pack 1, click the following article number to view the article in the Microsoft Knowledge Base:

2812683 Cumulative update package 11 for SQL Server 2008 R2 Service Pack 1Note 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 Service Pack 1 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:

2567616 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released

Cumulative Update 9 for SQL Server 2008 Service Pack 3 (SP3)

The fix for this issue was first released in Cumulative Update 9. For more information about how to obtain this cumulative update package for SQL Server 2008 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base:

2799883 Cumulative update package 9 for SQL Server 2008 Service Pack 3Note 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 Service Pack 3 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:

2629969 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 3 was released

Cumulative Update 4 for SQL Server 2008 R2 Service Pack 2 (SP2)

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 2008 R2 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:

2777358 Cumulative update package 4 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 Service Pack 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

Workaround

To work around this issue, decrypt the data before you migrate it to the second database. After you migrate the data to the other database, use a new key to encrypt the data.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the «Applies to» section.

Источник

Incorrect syntax near ‘)’ [closed]

Too localized — this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background).

Closed 5 years ago .

I’m trying to execute the following stored procedure:

But I when I execute this code:

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.

2 Answers 2

While it’s certainly possible that someone changed your stored procedure and broke it in the process, the simplest explanation for something like this breaking is a change in the input data that causes it to break.

READ  Postgresql exception when others then

In the comments, a number of people tried your stored procedure in various versions of SQL Server, and it ran successfully. This makes it even more likely that the problem is a change in the data being provided to the stored procedure.

If you can think of a table that’s using a data type that none of your tables has used before, that could cause your procedure to start running a branch that it had never needed to run before, causing an error.

You can troubleshoot this using the following steps:

You’ve already got a statement that prints out the dynamic SQL you execute to your «Messages» window in SSMS, before it executes it.

Look at the output in that window carefully. Are any tables processed, or are you failing on the very first table?

If you are failing on the very first table, before @script_sql is even printed once, then try running the stored procedure manually. Using SSMS, in the Object Explorer window:

  • open a Database Engine connection to your server;
  • expand your server; then Databases; then your database; then Programmability; then Stored Procedures.
  • Right-click on your stored procedure, and select «Script Stored Procedure As», «CREATE To», «New Query Editor Window».
  • In the query editor window, modify the code that creates your stored procedure so you can run the code directly. remove or comment out the CREATE PROCEDURE line; change the parameter list to DECLARE statements; and comment out or remove the AS .

Figure out the first table name your script is trying to process. Manually set @nom_table to this table name in your DECLARE statement (or in a SET statement right afterwards). Now, run the modified code from your stored procedure. See exactly where it stops (add additional PRINT statements if necessary to determine exactly where it stops).

If you get to the point where @script_sql is printed out, try copying the statement and running that manually, in a new query window. If that fails, look at the SQL statement carefully.

  • Get the length of @script_sql , and compare that with what was output; beyond a certain length, PRINT will truncate your value (so your wouldn’t be getting all of @script_sql in the «Messages» window). You can overcome this by printing substrings of @script_sql
  • If you don’t see any obvious errors in the statement, try running parts of it only. Can you create the table with just the first column? just the first two? etc. As you include additional columns, you will eventually find one or more that are causing issues. For each test, start from the full code in @script_sql , to minimize the likelihood of introducing errors by repeated cutting and pasting.

One of the current answers points out a couple of potential issues in your code; I’ll point out one more. I don’t think the line:

is working as you’d expect it to. I believe it’s intended to strip a trailing comma from the SQL statement before adding the closing parenthesis. However, it’s actually checking if the first character in @script_sql is a comma, not the last. Try this instead:

Finally — you can get an idea of whether anyone has changed your procedure from SSMS. If you don’t still have everything expanded, follow the directions from step 2 above to expand your server and database to the point where you see the «Stored Procedures» folder. Left-click on the folder, then go to the «Windows» menu and choose «Object Explorer Details». You’ll get a view of your stored procedure that includes «Create Date» and «Date Last Modified» (if you don’t see those, right-click on the headers for the columns you do see, and select those columns so you can see them.) Check the create date and last modified date for the procedure; has it been changed recently?

If so, then hopefully you have an old version of the stored procedure in a text or .sql file somewhere; or, you have an old backup of your database (you can restore this under a different name, and script out the stored procedure from the backup. You can then remove the old restored DB, and try the older version of the stored procedure, to see if it works).

You should also be using NVARCHAR for all of this instead of VARCHAR . And change the declaration of @code_colonne VARCHAR(1000) to be NVARCHAR(MAX) . Something might be being silently truncated, or how code_colonne is being built, such as a blank or still NA (instead of a NULL ) for Longest causing the resulting type to be either CHAR() or CHAR(NA) .

You could also run a Profiler trace, to see what’s going on during the procedure execution.

Источник

Smartadm.ru