Incorrect syntax error near 'GO'

_{Sunday, May 18, 2008 7:39 PM}

Hello, i have problem with GO statement. The error message is "Incorrect syntax error near 'GO'". I have done some suggestions. I have saved as sql file in UTF-8 format and I have put extra newlines above and belove of GO. But still i have same error. I think that there may be a fault in the syntax of paramater of EXECUTE. What do u think about that?

USE master

GO

IF DB_ID (N'Sales') IS NOT NULL
DROP DATABASE Sales

GO

-- Get the SQL Server data path
DECLARE @data_path nvarchar(256)
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1)

-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE Sales
ON
( NAME = Sales_dat,
    FILENAME = '''+ @data_path + 'saledat.mdf'',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = Sales_log,
    FILENAME = '''+ @data_path + 'salelog.ldf'',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB )')

GO

GO

_{Sunday, May 18, 2008 9:32 PM ✅Answered}

It executed correctly for me in Query Editor of Management Studio.

In some environments (SQL Agent job query window) you cannot use "GO" .

This is the GO-less version.

Code Snippet

USE master;

IF DB_ID (N'Sales') IS NOT NULL

DROP DATABASE Sales;

-- Get the SQL Server data path

DECLARE @data_path nvarchar(256)

SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)

FROM master.sys.master_files

WHERE database_id = 1 AND file_id = 1)

-- execute the CREATE DATABASE statement

EXECUTE ('CREATE DATABASE Sales

ON

( NAME = Sales_dat,

FILENAME = '''+ @data_path + 'saledat.mdf'',

SIZE = 10,

MAXSIZE = 50,

FILEGROWTH = 5 )

LOG ON

( NAME = Sales_log,

FILENAME = '''+ @data_path + 'salelog.ldf'',

SIZE = 5MB,

MAXSIZE = 25MB,

FILEGROWTH = 5MB )')

_{Sunday, May 18, 2008 10:21 PM}

As Kalman indicated, there is nothing wrong with your script.

I copied the entire script from above and it executed without error.

You may wish to examine [Tools] | [Options] | [Query Execution] | [General] | [SQL Server] and verify that the

'Batch Separator' is set to 'GO'.

If set to something other than 'GO', you need to reset to 'GO', OR use that alternative batch separator in your scripts.

_{Sunday, May 18, 2008 10:21 PM}

this is true that it works in management studio. but i am calling sql file in a website project in Visual Studio 2005. the problem is in VS 2005. now i will try GO-less version.

_{Sunday, May 18, 2008 10:28 PM}

You can only use GO in the SSMS query window.

Most all other clients do not use or support GO.

_{Sunday, May 18, 2008 10:33 PM}

yes, GO-less version doesnt generate any fault. But i have another problem. my main code doesnt work. it has to create a database. But i cant see that database in Management Studio. Here is the program code. It is very simple. What is your opinions?

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim createDB As String = ""
        Dim fillDB As String = ""
        Dim connectionString As String = "Data Source=GOKHAN;Integrated Security=True"
        Dim sr As StreamReader = New StreamReader("C:\Copy of EnrollmentDBscript.sql")
        Dim sr2 As New StreamReader("C:\CreateDatabase.sql")

        fillDB = sr.ReadToEnd
        createDB = sr2.ReadToEnd

        Using connection As New SqlConnection(connectionString)
            Dim command As New SqlCommand(createDB, connection)
            connection.Open()
            command.ExecuteNonQuery()
        End Using
    End Sub

_{Sunday, May 18, 2008 11:27 PM}

How about using:

Messagebox.show( createDB )

to examine the actual string being passed to SQL Server for execution.

And also catching any errors that may be occuring. I suggest a TRY...CATCH, looking for any error messages.

I have a suspicion that the way the file is being read is causing everything after the comment characters '--' to be ignored.

(You may wish to eliminate the comments to verify that theory.)

_{Monday, May 19, 2008 9:24 AM}

When i using F11 while debugging with a breakpoint, i can see what createDB is. It looks same as original. But when i used a code like following, i saw that the content of createDB is a single line string with no newlines. It means that there is no CR+LF pairs.

TextBox1.Text = createDB

How can i insert CR+LF pairs after GO statements

_{Monday, May 19, 2008 1:43 PM}

The editor that you are using may not be properly placing a CR+LF pair in the script file.

Perhaps it is using only a CR or a LF -but not both.

1. As mentioned earlier, 'GO' has no significance in any situation other than the SSMS query window. Do NOT use 'GO' in your scripts.

2. As mentioned earlier, remove the end of line Comments from the script '-- ...'. Since the file is being read as a 'stream', the comment characters may be causing all code following to be ignored.

3. Carriage return and Linefeeds are only significant to the user reading T-SQL. SQL Server does NOT care about CR+LF. it may be better to NOT 'hard code' CR+LF. SQL Server will properly execute the script as a single line IF there are no 'GO' statements, and IF there are no extraneous characters and IF there are no end of line Comments.

4. Terminate each complete T-SQL with a semi-colon.

_{Monday, May 19, 2008 2:12 PM}

Yes, you are right. it works in SSMS with GO statements and when i discard GO statements, it can also works in Visual Studio 2005. There is no problem about GO, anymore.

 But when the script is run, it doesnt generate any db or i cant see the generated db in anywhere. Where can generated db file be located? The starting line of script is as following:

USE master

 GO
...

is there any mistake in using "master" keyword?

_{Monday, May 19, 2008 2:20 PM}

How many times must I suggest that you remove the comments (and comment characters)?

Have you yet done so and verified the outcome?

_{Monday, May 19, 2008 4:25 PM}

I DID WHAT YOU ALL SAID. I REMOVED COMMENTS, I REMOVED GO STATEMENTS AND I DID WHATEVER ELSE

_{Monday, May 19, 2008 4:31 PM}

AND I HAVE NO MORE PROBLEM WITH ERROR MESSAGE "Incorrect syntax error near GO".
MY PROBLEM IS DIFFERENT. I CANT SEE THE GENERATED DB FILE ANYWHERE.

_{Monday, May 19, 2008 5:23 PM}

So let's see if I can shout louder...

Nah, that doesn't solve anything...

Have you tried to catch any errors?

(Or is the code executing without error?)

Even if there is a permission issue that is stopping the file creation, errors should be happening...

Application Event log.

SQL Server log