How to Dynamically Create Views?

Question

Background

I have a website that displays data unique to a client. The site required views to be created ever time a new client is added. Each client is unique and has a different identifying information unique to them. For example an ID number and a prefix.

Everytime a new client is added a new set of views is manually created using a standard view set, which is just changed each time to reflect the clients unique information. This is usually done using a Find and Replace in SQL Server Management Studio (SSMS)

What I have so far?

I have created a Winform app that captures the unique information and puts them into variables. These variables are then put into the the standard script that is used to create the views.

Problem

My script contains SMSS statements are not native SQL statements, this causes my program to error and break in its submission to the database.

The statement in question is the GOkey word used to run batches by SMSS.

What I have tried so far?

I have encapsulated the whole script using String Literal and have inserted a new line before and after the GO statements as suggested in another question. but it didn't seem to work.

What I am trying now?

Using REGEX to split the script up at every 'GO' occurrence. This isn't working either.

Question

Is there a better solution to this problem or a fix for my solution?

Code

 string connectionString = fmDbSelect();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {

                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = connection;
                    connection.Open();
                    var scripts = Regex.Split(sql, @"^\w+GO$", RegexOptions.Multiline);
                    foreach (var splitScript in scripts)
                    {
                        command.CommandText = splitScript;
                        command.ExecuteNonQuery();

                    }


                }
            }

Error Message

{"Incorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'QUOTED_IDENTIFIER'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'QUOTED_IDENTIFIER'.\r\nIncorrect syntax near ')'.\r\n'CREATE VIEW' must be the first statement in a query batch.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near ')'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near 'ANSI_NULLS'.\r\nIncorrect syntax near the keyword 'AS'.\r\nIncorrect syntax near the keyword 'LIKE'.\r\nIncorrect syntax near 'ANSI_NULLS'."}

My Script

/****** Object: View [dbo].[TIDEreportEmails] Script Date: 23/02/2015 12:43:36 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE VIEW [dbo].[TIDEreportEmails] AS SELECT EmailID, EmailContent, EmailSubject, EmailTo, EmailFrom, UserID, ObjectValueID, EmailSent, EmailCreated, EmailRead, EmailFromName, EmailType, EmailFailed, CASE WHEN emailread IS NULL THEN 'Not Read' ELSE 'Read' END AS EmailStatus FROM DEReportingClient2DB.dbo.Emails AS Emails_1 WHERE (UserID IN (SELECT UserID FROM DEReportingClient2DB.dbo.Users WHERE (ClientID = 195)))

GO

/****** Object: View [dbo].[TIDEunreadEmails] Script Date: 23/02/2015 12:43:36 ******/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

CREATE VIEW [dbo].[TIDEunreadEmails] AS SELECT COUNT(*) AS UnreadEmails, UserID FROM dbo.TIDEreportEmails WHERE
(EmailRead IS NULL) GROUP BY UserID

Cause of these errors: `GO` it isn't T-SQL keyword. Solutions: see [Remus's answer](http://stackoverflow.com/questions/14376764/where-can-i-find-microsoft-sqlserver-batchparser-dll). — Bogdan Sahlean, Mar 01 '15 at 21:17

Soyeb L · Accepted Answer · 2015-03-01T22:47:36.910

Your RegEx is not able to split lines correctly; You can use one of following statements to split your script.

sql.Split(new string[] { "GO" }.
Regex.Split(sql, @"\bGO\b", RegexOptions.Multiline);

Following is a code-snippet

      string connectionString = fmDbSelect();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                connection.Open();
                var scripts = Regex.Split(sql, @"\bGO\b", RegexOptions.Multiline);
                //var scripts = sql.Split(new string[] { "GO" }, StringSplitOptions.None);
                foreach (var splitScript in scripts)
                {
                    command.CommandText = splitScript;
                    command.ExecuteNonQuery();

                }


            }
        }

score 1 · Answer 2 · answered Mar 01 '15 at 21:23

Remove the GO statements.

GO is used by SSMS to send separate "batches" to SQL Server... it's nothing more than a delimiter. Most of those GOs you don't need.

Where something must be the first thing in a batch (like a CREATE VIEW statement), just send everything before it in one batch, and then in a separate statement, send the complete CREATE VIEW statement as a separate step in your code.

You can and should use the same connection object, just send one command, then a different one, one for each batch. And there's no need to separate the "SET" statements with GOs. You can put all the SET statements together... and you can do them just once on the connection. So... use one command to send the SET statements. And then one command to send each of the CREATE VIEW statements on its own.

score 1 · Answer 3 · answered Mar 01 '15 at 21:41

Splitting on GO will work if done correctly. You are simply splitting incorrectly. Use the debugger to inspect what you are trying to execute. It will be obviously broken.

Ensure that you are not sending GO to the server.

The line breaks in your script seem messed up. Maybe that's just an artifact of the way to got the script here.