Unexpected Syntax Error "."

Question

Background:

I am using SMO to read a script and then execute it on my database. There are parts of my script that need to change before I execute it, like the data of the databases.

The variable is cleansed and checked before it is allowed, and can only contain upper case letters.

The variable is this case is prefix.

However for some reason when I use a variable in this particular part of the script I get the below error.

Code:

The line of code that produces this error report is shown below.

sql += @"FROM dbo." + prefix + "reportEmails";

The full block of code can be seen below.

     sql += @"

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO";
            sql += Environment.NewLine;
            sql += @"CREATE VIEW [dbo].[" + prefix + "reportEmails]";
            sql += @"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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO";
            sql += Environment.NewLine;
            sql += @"CREATE VIEW [dbo].[" + prefix + "unreadEmails]";
            sql += @"AS
SELECT     COUNT(*) AS UnreadEmails, UserID";
            sql += @"FROM dbo." + prefix + "reportEmails";
sql += @"WHERE     (EmailRead IS NULL)
GROUP BY UserID
GO
...

Question:

Why am I getting this error?

Incorrect syntax near '.'

Answer 1

Answer

After displaying what was actually being put into the variable SQL I discovered there were times where the code wasn't being put on a newline. This was causing joining of words.

String in SQL Variable

For Example, see line beginning with Select

Actual Solution

Adding a newline before the keywords.

sql+=Environment.NewLine;

Example

            sql += Environment.NewLine;
            sql += @"CREATE VIEW [dbo].[" + prefix + "unreadEmails]";
            sql += Environment.NewLine;
            sql += @"AS";
            sql += Environment.NewLine;
            sql += @"SELECT     COUNT(*) AS UnreadEmails, UserID";
            sql += Environment.NewLine;
            sql += @"FROM dbo." + prefix + "reportEmails";
            sql += Environment.NewLine;

Answer 2

"GO" is not actually SQL. It is recognized by sqlcmd.exe and SQL Server Mngmnt Studio as a batch delimiter. But it is not valid in SMO. You will have to remove all 'GO's and execute the batches in separate statements.

I guess I was wrong about GO and SMO. Thanks Martin.