I have a SQL query which creates a database and declares the path of an mdf and its log files. The same query works when I execute it in management studio (I am sure it's the same query I copy paste it from SQL Profiler) but gives a syntax error below:
incorrect syntax near go.
I have tried using executeNonQuery, ExecuteReader and ExecuteScalar to execute my query but nothing worked. It was giving syntax error due to the lack of newlines in query from my code hence the newline methods in the query. Any help would be appreciated.
Visual Studio code:
string truvamdf = @"C:\Truva\Data\"+VeritabaniAdiTextBox.Text+".mdf";
string truvaldf = @"C:\Truva\Data\"+VeritabaniAdiTextBox.Text+"_log.ldf";
string connectionString = (@"" + File.ReadAllText(@"C:\Truva\Ivdexcel\IVDVeritabaniconfig.ini") + "");
string cmdtext = "" +
"USE[master] " + Environment.NewLine + "" +
"GO " + Environment.NewLine + "" +
"DECLARE @mdfPath NVARCHAR(max), @ldfPath NVARCHAR(max) , @SQL NVARCHAR(MAX), @instName NVARCHAR(max) = '" + VeritabaniAdiTextBox.Text + "' " + Environment.NewLine + "" +
"SELECT @mdfPath = '" + truvamdf + "' " + Environment.NewLine + " " +
",@ldfPath = '" + truvaldf + "' " + Environment.NewLine + " " +
"FROM master.sys.master_files WHERE database_id = 1 " + Environment.NewLine + "" +
"SELECT @SQL = " + Environment.NewLine + "" +
"'CREATE DATABASE ["+VeritabaniAdiTextBox.Text+ "] " + Environment.NewLine + " " +
"CONTAINMENT = NONE " + Environment.NewLine + " " +
"ON PRIMARY " + Environment.NewLine + "" +
"(NAME = N'''+@instName+''', FILENAME = N'''+@mdfPath+''' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) " + Environment.NewLine + " " +
" LOG ON " + Environment.NewLine + " " +
" (NAME = N'''+@instName+'_log'', FILENAME = N'''+@ldfPath+''' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10 %)' "+Environment.NewLine+ " " + Environment.NewLine + "" +
"EXECUTE(@SQL) " + Environment.NewLine + " " + Environment.NewLine + "" +
" GO ";
// try
// {
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand(cmdtext, connection);
cmd.ExecuteNonQuery();
}
The same SQL query in SSMS:
USE[master]
GO
DECLARE @mdfPath NVARCHAR(max), @ldfPath NVARCHAR(max) , @SQL NVARCHAR(MAX), @instName NVARCHAR(max) = 'dddssd'
SELECT @mdfPath = 'C:\Truva\Data\dddssd.mdf'
,@ldfPath = 'C:\Truva\Data\dddssd_log.ldf'
FROM master.sys.master_files WHERE database_id = 1
SELECT @SQL =
'CREATE DATABASE [dddssd]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'''+@instName+''', FILENAME = N'''+@mdfPath+''' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
(NAME = N'''+@instName+'_log'', FILENAME = N'''+@ldfPath+''' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10 %)'
EXECUTE(@SQL)
GO
