2

Very simple table and insert statement, fails if the string is 'Golf', but works for any other string. Table create:

CREATE TABLE [dbo].[market]
(
    [marketID] [int] NOT NULL,
    [name] [varchar](50) NULL,
CONSTRAINT [PK_market] PRIMARY KEY CLUSTERED ([marketID] ASC)
)

Insert:

INSERT [dbo].[market] ([marketID], [name]) VALUES (4, N'Golf')

Error: Unclosed quotation mark after the character string ''. (State:37000, Native Code: 69)
Error: Incorrect syntax near ''. (State:37000, Native Code: 66)
Error: Unclosed quotation mark after the character string ')'.

Now, if I change the insert to:

INSERT [dbo].[market] ([marketID], [name]) VALUES (4, N'Test')

It works just fine. Has anyone ever seen that before?

Mike Nicewarner
  • 51
  • 3
  • Seems to work just fine on SQLFiddle: http://sqlfiddle.com/#!3/263f9/1/0 – EfrainReyes Mar 11 '14 at 13:38
  • What application are you running this in? Does it alter the query in any way? Perhaps dodgy home made parsing of `GO`? – Martin Smith Mar 11 '14 at 13:38
  • I think you select half of the query and then run then it will give error, either it's not possible to give error because both statement are identical. – KuldipMCA Mar 11 '14 at 13:39
  • 1
    The only thing that I can see ist that you are inserting a Unicode string into a non-unicode column - `name` is of type `varchar` and you insert `N'Golf'` – Filburt Mar 11 '14 at 13:47

1 Answers1

3

I think the application you are using must be broken and splits into batches if a GO is found anywhere in the string.

e.g. it uses a naive approach such as the below.

Regex.Split(sqlString, "GO", RegexOptions.IgnoreCase);

Then executes the results as separate batches.

Running

INSERT [dbo].[market] ([marketID], [name]) VALUES (4, N'

Returns the first two errors you report.

Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string ''.

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ''.

Executing

lf')

Returns the third one.

Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string ') '.

A temporary workaround might be to try 

INSERT [dbo].[market] ([marketID], [name]) VALUES (4, N'G' + 'olf')

But of course the application really needs to be fixed.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845