0

I have a simple .sql file that looks like this:

    USE [master]
    GO

    CREATE DATABASE [NEW_DATABASE]
    GO

When I run it from SSMS, it works fine, the NEW_DATABASE database is created. However, I am trying to run this command by using it as an embedded resource in a C# program. The final version of this .sql file will be a lot more complex but, for now, I'm just trying to get this simple script to work.

Here are the steps that I've taken:

1) I added a resource file (named SqlFiles) to my project.

2) I opened the resource file within VS and added a new file
resource, "create.sql".

3) I saved the resource file.

Then, in my code, I have the following:

// If database doesn't exist, run the script to create it.
if (dbExists == false)
{
    using (var connection = new SqlConnection("Data Source=" + computerName + "\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True"))
    {
        connection.Open();
        var command = connection.CreateCommand();
        command.CommandText = SqlFiles.create;
        command.ExecuteNonQuery();
    }  
}

When I run the program, I get the following error when the command is executed:

Incorrect syntax near 'GO'.

Yet, as I stated, if I open this same file in SSMS and execute it, it works without issue.

I do notice that when I hover over the command variable in debug, the value of the CommandText appears to have formatting text in it (return characters, rather):

USE [master]\r\nGO\r\nCREATE DATABASE [NEW_DATABASE]\r\nGO\r\n

Could this be this issue?

Kevin
  • 4,798
  • 19
  • 73
  • 120
  • 1
    When you run it via SSMS are you using Windows Authentication or a specific SQL user account? – Lex Mar 27 '16 at 22:43
  • @Lex Windows Authentication. – Kevin Mar 27 '16 at 22:48
  • 1
    I'm guessing you're correct about the newline characters in there then. Does it work if you strip those out using something like `command.CommandText = SqlFiles.create.Replace("\\r\\n", " ");`? Maybe also try it with single `\\` as well? My apologies if I'm suggesting something that seems obvious. – Lex Mar 27 '16 at 22:55
  • @Lex Thanks for the suggestion! I did try that, and it didn't make a difference. However, I removed the 'GO' statements, and added a semi-colon to the end of each line. It works now, even with the '\r\n' still showing in the variable value. So, I'm wondering why 'GO' is giving the problem? Very strange, but it appears to be working now. It created the database without issue. – Kevin Mar 27 '16 at 22:58
  • Interesting - glad you figured it out. Maybe related to [this answer](http://stackoverflow.com/a/40830/548997)? – Lex Mar 27 '16 at 23:00
  • @Lex Doh! You posted this right before I posted the answer below. I should I remove the answer, I wonder? – Kevin Mar 27 '16 at 23:02

1 Answers1

4

The GO keyword is not T-SQL, or in other words the method of how your c# is interacting with the database. Go is a a Sql Server Managent Studio keyword that tells the SQL script to be executed as separate commands. So SSMS runs in the script in batches separated by the GO's.

You need to separate and run the batches individually on your own and tell each to execute. Alternatively, if the script is not generated, I store them as a stored procedure and pass my arguments to that.

Lithium
  • 373
  • 7
  • 21
  • Right you are. Lex, in the comments above, pointed out an extremely useful article on dealing with 'GO' in SQL scripts. – Kevin Mar 27 '16 at 23:07