Running .sql file using C# .Net - Accents Problem
-
Hello, I have this small app to run a batch of .sql files on multiple databases. When the .sql files have accents, (most of them comments) I get this error:
The thing is that I can't remove the accents I must leave them there...
So if anyone has any experience or advice to share it would be much appreciated.
This is a example of the code im using:
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";
FileInfo file = new FileInfo("C:\\myscript.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
-
Anyone? 😉
Ok for example one of the .sql files could be something like this
--------------------------------------------------
CREATE PROCEDURE my_procedure
AS
DECLARE @my_value varchar(100)
SET @my_value = 'This will select everything fróm my táblé'
UPDATE my_table SET my_column = @my_value
--------------------------------------------------
This "fróm my tablé" will generate the error posted earlier
Any ideas?
-
I'm not sure what the problem is, but this works fine for me so I don't think the problem is the accents...
declare @table table ( x varchar(255) )
declare @my_value varchar(100)
set @my_value = 'This will select everything fróm my táblé'
insert into @table (x) values (@my_value)
select * from @table
Maybe it's a Unicode conversion problem when you're reading the file, I dunno. Is there a particular reason for your using the Microsoft.SqlServer.Management assemblies? If not, just use System.Data and System.Data.SqlClient, and use the File object to read your file instead of FileInfo, something like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace ConsoleApplication1
{
static class Program
{
static void Main(string[] args)
{
string sqlConnectionString = "<your connection string here>";
using (SqlConnection conn = new SqlConnection(sqlConnectionString))
{
string sqlCommandFilePath = "<path to your file>";
if (File.Exists(sqlCommandFilePath))
{
string script = File.ReadAllText(sqlCommandFilePath);
using (SqlCommand cmd = new SqlCommand(script, conn))
{
int affectedRows = cmd.ExecuteNonQuery();
}
}
}
}
}
}
Try this, see if it works any better.
-
Hello dmbaker, the code you give me didn't solve my problem.
This is how the stored procedure looks inside the .sql file
This how the stored procedures ends up in the database when i execute the .sql file with your code or mine.
Is there a particular reason for your using the Microsoft.SqlServer.Management assemblies?
No there is no particular reason. I just googled how to execute sql files on sql server databases.
This is what my app is doing:
1. List available servers
2. After choosing a server, lists its databases
3. Choose the databases to work on, (one or many)
4. Choose the .sql files (one or many)
5. Execute every .sql file on the selected databases
But I think that you are right about an unicode conversion problem. I read about it but I can't get my head around it
Thank you for you reply.
-
Can you attach a sample file, maybe a test file that exhibits the behavior but doesn't do anything important?
If it *is* an encoding problem and you can determine what encoding the source file is using, then the ReadAllText() method may be able to help. It takes a second parameter that specifies the encoding of the source file. If the file is, in fact, Unicode-encoded, then maybe this will work. Replace the line:
string script = File.ReadAllText(sqlCommandFilePath);With this:
string script = File.ReadAllText(sqlCommandFilePath, System.Text.Encoding.Unicode);If it's an encoding issue then it's possible that it's encoded in something other than Unicode (and the enumerations of System.Text.Encoding may contain the one you need if it's not Unicode), but maybe if you can attach a sample file, we'll be able to tell if this is really the problem.
You can also use Windows Notepad to see the encoding...open the file in Notepad and then do a "File/Save As...". In the dialog box that appears, you should see an "Encoding" drop-down that will show the current encoding of the file. If it's other than "ASCII" then that's the encoding to specify in your code. If it shows as "ASCII" then I don't think it's an encoding problem, it must be some other issue, maybe on the SQL Server side -- do code page or collation settings or something come into play? I don't know.
-
Here is the example file, I just edited out the code.
When I open the file with notepad it says "ANSI".
-
Well I tried with this options and it shows the same error.
string script = File.ReadAllText(sqlCommandFilePath, System.Text.Encoding.ASCII);
string script = File.ReadAllText(sqlCommandFilePath, System.Text.Encoding.Unicode);
string script = File.ReadAllText(sqlCommandFilePath, System.Text.Encoding.UTF32);
string script = File.ReadAllText(sqlCommandFilePath, System.Text.Encoding.UTF7);
string script = File.ReadAllText(sqlCommandFilePath, System.Text.Encoding.UTF8);
But System.Text.Encoding doesn't show ANSI to choose from the list.
-
Its working now!! 😀
string script = File.ReadAllText(sqlCommandFilePath, System.Text.Encoding.GetEncoding(1252));1252 is the codepage for my country.
I couldn't have put this together without your help dmbaker. Thank you.
-
Yup, code page 1252 is the ANSI code page so it all makes more sense now!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply