Tuesday, March 20, 2012

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 AsNew SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd AsNew SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i AsInteger = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

No comments:

Post a Comment