Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Running .sql file using C# .Net - Accents Problem Expand / Collapse
Author
Message
Posted Tuesday, October 26, 2010 10:51 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:19 AM
Points: 39, Visits: 195
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);
}
}
Post #1011010
Posted Wednesday, October 27, 2010 1:53 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:19 AM
Points: 39, Visits: 195
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?
Post #1011853
Posted Thursday, October 28, 2010 7:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 12:44 PM
Points: 519, Visits: 2,835
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.



Post #1012313
Posted Thursday, October 28, 2010 9:55 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:19 AM
Points: 39, Visits: 195
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.

Post #1012446
Posted Thursday, October 28, 2010 10:41 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 12:44 PM
Points: 519, Visits: 2,835
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.



Post #1012491
Posted Thursday, October 28, 2010 10:49 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:19 AM
Points: 39, Visits: 195
Here is the example file, I just edited out the code.

When I open the file with notepad it says "ANSI".


  Post Attachments 
example.sqlplan (5 views, 555 bytes)
Post #1012500
Posted Thursday, October 28, 2010 11:01 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:19 AM
Points: 39, Visits: 195
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.
Post #1012507
Posted Thursday, October 28, 2010 11:07 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 9:19 AM
Points: 39, Visits: 195
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.
Post #1012514
Posted Thursday, October 28, 2010 11:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 12:44 PM
Points: 519, Visits: 2,835
Yup, code page 1252 is the ANSI code page so it all makes more sense now!


Post #1012518
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse