I have exported data from SQL Server express and I try to import them into another database.
I want to use it as "Repair Table" in which I will add more tables with data and I always get an error and I can't understand why.
The error is
Message - System.Data.SqlClient.SqlException (0x80131904): Unclosed quotation mark after the character string 'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ '. Incorrect syntax near 'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ '.
The descriptions are in Greek language, I don't know if this has something to do with the error.
My code so far
private void startBtn_Click(object sender, EventArgs e)
{
string sqlQuery = DatabaseTables.KodikoiTaric;
if(checkBox1.Checked)
InsertDefaultValues(sqlQuery);
}
void InsertDefaultValues(string tableName)
{
RepairTable(tableName);
// DataTable csvData = GetDataTabletFromCSVFile(DatabaseTables.taric2);
}
void RepairTable(string tableName)
{
try
{
string sqlConnectionString = Parameters.Config.ConnectionString;
string script = tableName;
var sqlqueries = script.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
SqlConnection conn = new SqlConnection(sqlConnectionString);
SqlCommand cmd = new SqlCommand("query", conn);
Server server = new Server(new ServerConnection(conn));
conn.Open();
var progressBar = 10;
foreach (var query in sqlqueries)
{
progressBar += 10;
cmd.CommandText = query;
cmd.ExecuteNonQuery();
conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
{
FileStream ostrm;
StreamWriter writer;
TextWriter oldout = Console.Out;
string _dbUpdateLogPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\DBUpdate" + DateTime.Now.ToString("ddMMyy") + ".txt";
try
{
if (!File.Exists(_dbUpdateLogPath))
{
ostrm = new FileStream(_dbUpdateLogPath, FileMode.OpenOrCreate, FileAccess.Write);
writer = new StreamWriter(ostrm);
Console.SetOut(writer);
Console.WriteLine(e.Message);
Console.SetOut(oldout);
writer.Close();
ostrm.Close();
}
else if (File.Exists(_dbUpdateLogPath))
{
ostrm = new FileStream(_dbUpdateLogPath, FileMode.Append, FileAccess.Write);
writer = new StreamWriter(ostrm);
Console.SetOut(writer);
Console.WriteLine(e.Message);
Console.SetOut(oldout);
writer.Close();
ostrm.Close();
}
}
catch (Exception ex)
{
NLogger.NLogger.LibraryLogClass.Error(ex.ToString());
return;
}
};
}
conn.Close();
}
catch (Exception ex)
{
NLogger.NLogger.LibraryLogClass.Error(ex.ToString());
}
}
I have the file as resource file and the form is like this
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
DELETE FROM [dbo].[KodikosTaric]
GO
SET IDENTITY_INSERT [dbo].[KodikosTaric] ON
INSERT INTO [dbo].[KodikosTaric] ([Id], [KodikosTaric], [KoinotikonMetron_a], [KoinotikonMetron_b], [Perigrafi], [DasmosTritonXoron], [ProtimisiakosDasmos], [SimpliromatikesMonades], [YpologismosKila], [DiasafistisId])
VALUES (1, N'8701100000', NULL, NULL, N'ΕΛΚΥΣΤΗΡΕΣ ΧΕΙΡΟΔΗΓΟΥΜΕΝΟΙ', NULL, NULL, NULL, NULL, NULL),
(2, N'8701201000', NULL, NULL, N'ΚΑΙΝΟΥΡΓΙΟΙ', NULL, NULL, NULL, NULL, NULL),
(3, N'8701209000', NULL, NULL, N'ΜΕΤΑΧΕΙΡΙΣΜΕΝΟΙ', NULL, NULL, NULL, NULL, NULL)
.
.
.
The line that always give me the error is this
INSERT INTO [dbo].[KodikosTaric] ([Id], [KodikosTaric], [KoinotikonMetron_a], [KoinotikonMetron_b], [Perigrafi], [DasmosTritonXoron], [ProtimisiakosDasmos], [SimpliromatikesMonades], [YpologismosKila], [DiasafistisId])
VALUES (782, N'2009809711', NULL, NULL, N'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ GOYAVES', NULL, NULL, NULL, NULL, NULL)
It doesn't matter in which line it is. I try putting it on first line of the file and it throw the same error.
The funny is that if I import that file from SQL Server Express as query it works great.
EDIT: Thanks to @Chris J I start understanding the problem. So the problem is that in the file there is the word
GOYAVES
which when I split the text it sees it as GO and not as GOYAVES. I tried to change the split command
var sqlqueries = script.Split(new[] { "\nGO", "\ngo" }, StringSplitOptions.RemoveEmptyEntries);
and I don't have the exception any more but the problem is even though it seems to working and splits correct the sql file it doesn't write anything to the table.
EDIT 2: I removed those lines from the sql script
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
and everything worked fine. Thanks everyone for your help
The problem is most likely here:
N'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ GOYAVES'
^^
Earlier in your C# code you're doing:
var sqlqueries = script.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
So it's likely splitting your SQL query on that 'GO' that's in the substring. One other clue to this is the error itself:
Unclosed quotation mark after the character string 'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ '.
This string is a substring of that full string up to and including the space.
You probably need to change your C# to look only for GO
at the start of a line.
As for the best way of doing that, it looks like your code reads the file in to memory whole as a string and then you split on that string. For large files, this could be slow and memory inefficient: it's something I generally try and avoid doing myself.
A more efficient method would be to split as you read the file - this saves having to read and process a large string in memory. Something like this would do the job:
private IEnumerable<string> GetStatement(string sqlFile)
{
using (var sr = new StreamReader(sqlFile))
{
string s;
var sb = new StringBuilder();
while ((s = sr.ReadLine()) != null)
{
if (s.Trim().Equals("GO", StringComparison.InvariantCultureIgnoreCase))
{
yield return sb.ToString();
sb.Clear();
continue;
}
sb.AppendLine(s);
}
yield return sb.ToString();
}
}
This method reads the file line by line, and when it encounters "GO" on a line by itself it returns that batch. It can be called in a simple for each:
foreach (var batch in GetStatement("Batch.sql"))
{
Console.WriteLine(batch);
}
If you'd prefer to still read the file in full, and process a string, then you can swap the StreamReader
out for a StringReader
and apply the same method. But unless you really need to load the entire file into memory, it's simpler (and potentially faster for large files) to avoid doing that in the first instance and just process as you go.
How about this:
var sqlqueries = script.Split(new[] { "\rGO", "\r\nGO" },
StringSplitOptions.RemoveEmptyEntries);
The two strings will match the likely forms of line breaks.
User contributions licensed under CC BY-SA 3.0