Solved

'CREATE SCHEMA' must be the first statment in a query batch

Posted on 2014-04-28
2
483 Views
Last Modified: 2014-04-28
Hi all,

I am trying to create and read in a database dynamically using c#. I have exported to an SQL file from SQL Managment studio.

I have the following code;

try
        {
            string connectionString = WebConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                string createQuery = String.Format("CREATE DATABASE [{0}{1}]", dbPrefix, databaseName);

                using (SqlCommand command = new SqlCommand(createQuery, connection))
                {
                    try
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                    catch (System.Exception ex)
                    {
                        //log
                    }
                    finally
                    {
                        connection.Close();
                    }
                }

                //now read in and execute the script
                FileInfo file = new FileInfo(defaultDBScript);
                string script = file.OpenText().ReadToEnd();

                //sub in the databaseName
                script = script.Replace("@Database", dbPrefix + databaseName);
                script = script.Replace("GO", ";");
                    
                using (SqlCommand command = new SqlCommand(script, connection))
                {
                    try
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                    catch (System.Exception ex)
                    {
                       //log
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
                
                file.OpenText().Close();
 
            }'

Open in new window


However I am getting the following error message?

Could someone please tell me how I can export a db so I can programatically read it into another using c#?
0
Comment
Question by:flynny
2 Comments
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 40027146
The problem isn't in the exporting, it's with the fact that some statement type must exist as the first statement in a batch (CREATE SCHEMA, CREATE VIEW, etc).

So, what you need to do is split your file contents on the batch separator and execute each one individually:
//now read in and execute the script
FileInfo file = new FileInfo(defaultDBScript);
string script = file.OpenText().ReadToEnd();

//sub in the databaseName
script = script.Replace("@Database", dbPrefix + databaseName);
                    
using (SqlCommand command = new SqlCommand("", connection))
{
    string[] statements = script.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);

    try
    {
        connection.Open();
        foreach (string statement in statements)
        {
            command.CommandText = statement;
            command.ExecuteNonQuery();
         }
    }
    catch (System.Exception ex)
    {
        //log
    }
    finally
    {
        connection.Close();
    }
}

Open in new window

0
 
LVL 7

Expert Comment

by:Utkarsh Kulkarni
ID: 40027157
Is it required to create Database and then execute the script ?

You can check Ref - https://support.microsoft.com/kb/307283/EN-US
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now