Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running .sql file using C# .Net - Accents Problem


Running .sql file using C# .Net - Accents Problem

Author
Message
DiegoPeña
DiegoPeña
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 233
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);
}
}
DiegoPeña
DiegoPeña
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 233
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?
dmbaker
dmbaker
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 3438
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.



DiegoPeña
DiegoPeña
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 233
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.
dmbaker
dmbaker
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 3438
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.



DiegoPeña
DiegoPeña
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 233
Here is the example file, I just edited out the code.

When I open the file with notepad it says "ANSI".
Attachments
example.sqlplan (6 views, 555 bytes)
DiegoPeña
DiegoPeña
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 233
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.
DiegoPeña
DiegoPeña
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 233
Its working now!! :-D

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.
dmbaker
dmbaker
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 3438
Yup, code page 1252 is the ANSI code page so it all makes more sense now!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search