Connection option in MSSQL2005

Hi,
There is a setting in option-> connection -> 'Perform translation for character data' in Query Analyzer in MSSQL 2000. Is there any option in MSSQL2005 has simliar function? Thanks in advance

(stephanielauym@.discussions.microsoft.com) writes:
> There is a setting in option-> connection -> 'Perform translation for
> character data' in Query Analyzer in MSSQL 2000. Is there any option
> in MSSQL2005 has simliar function? Thanks in advance There does not seem to. I had enough with character translation with the 6.5 tools, so I can't say that I miss it. What use would you see for such an option? -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||in the DB, some of the field in varchar contains chinese characters. In MSSQL2000, we un-click this option to read the chinese char.|||

(stephanielauym@.discussions.microsoft.com) writes:
> in the DB, some of the field in varchar contains chinese characters. In
> MSSQL2000, we un-click this option to read the chinese char. And you are saying that there is no way to turn transkation off in Mgmt Studio? I have it off in my QA, but I can't say whether that is the default or not. (I assumed that it was off by default, but maybe I was wrong.) What is the collation of these columns? How do they display in QA currently? If this is important for you I suggest that submit a suggetion on http://lab.msdn.microsoft.com/productfeedback/. It's a good idea to give a background why you want this. -- Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||Yes, I can't find the option which is similar to the option 'Perform translation for character data' in Management Studio. In our case, turning off this option would display chinese in varchar field.sqlsql

Connection Management

Hi:

I have a Point-of-sale application that uses SQL Server2000 for the backend.
Basically, the users perform various functions boiling down to login (check
password from a table) and data entry (insert a food entry). Previously, I
would open a new ADO 2.7 connection to the database each time one of these
types of database accessing functions needed to be performed - but I noticed
that sometimes the DB would freeze the application for 20 seconds or so - or
even cause a timeout error.

To fix this, I open a DB connection when the application first starts,
keeping it open for the life of the application - each time a function needs
to access the DB, it just uses the applications (global) connection that is
constantly open and connected.

This seems to have fixed the problem, however, I am curious, is this an OK
way to handle the connections - keeping in mind that there are four separate
stations - each running the application - at the same time. Therefore, I
have 4 constantly open connections at the same time.

Thanks and regards,

Ryan Kennedy"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:lYnBb.4549$UM4.1037@.nwrdny01.gnilink.net...
> Hi:
> I have a Point-of-sale application that uses SQL Server2000 for the
backend.
> Basically, the users perform various functions boiling down to login
(check
> password from a table) and data entry (insert a food entry). Previously,
I
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I
noticed
> that sometimes the DB would freeze the application for 20 seconds or so -
or
> even cause a timeout error.
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function
needs
> to access the DB, it just uses the applications (global) connection that
is
> constantly open and connected.
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four
separate
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.
>
> Thanks and regards,
> Ryan Kennedy

I don't know much about ADO, but it sounds like you're describing a form of
connection pooling, which is certainly a very common way to manage
connections from multiple clients.

Simon|||"Ryan P. Kennedy" <ryanp.kennedy@.verizon.net> wrote in message
news:lYnBb.4549$UM4.1037@.nwrdny01.gnilink.net...
> Hi:
> I have a Point-of-sale application that uses SQL Server2000 for the
backend.
> Basically, the users perform various functions boiling down to login
(check
> password from a table) and data entry (insert a food entry). Previously,
I
> would open a new ADO 2.7 connection to the database each time one of these
> types of database accessing functions needed to be performed - but I
noticed
> that sometimes the DB would freeze the application for 20 seconds or so -
or
> even cause a timeout error.
> To fix this, I open a DB connection when the application first starts,
> keeping it open for the life of the application - each time a function
needs
> to access the DB, it just uses the applications (global) connection that
is
> constantly open and connected.
> This seems to have fixed the problem, however, I am curious, is this an OK
> way to handle the connections - keeping in mind that there are four
separate
> stations - each running the application - at the same time. Therefore, I
> have 4 constantly open connections at the same time.

Connection pooling, the sharing of a single connection among components of
an application, is very common and a good design principle. It is
particularly great for web based/ASP applications, in order to conserve
resource. Persistant connections, keeping a connection open even when not in
use, is something I shy away from in my client server and my web based app
designs. I prefer to create a connection, pool it, and then open and close
it as needed.

It sounds like you are looking for a solution to a symptom, and not your
problem. If I were you, I would investigate the reason your app is timing
out and solve that.

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

connecting to sql server from mobile device

How can I connect to sql server from a mobile device?
the server is on pc and my app is on the device.
I want to perform commands and stored procedures which will be performed online on the pc and get immediate results.
is there any way of achiving that?

You will need to add a reference to SQLClient in your project.
Once this is done its the same as if you were connecting to sqlserver on the full framework with ASP.net or VB.net
here is some sample code

Imports System.Data.Common
Imports System.Data.SqlClient
Private m_connString As String = "user id=sa;password=;initial catalog=Northwind;Server=xxx.xxx.xxx.xxx;"
Private m_ds As New DataSet
Dim l_sqlConn As SqlConnection
Dim l_da As DataAdapter
Dim l_ds As DataSet
Dim l_sqlStmt As String
Try
l_sqlConn = New SqlConnection(m_connString)
l_sqlConn.Open()
l_sqlStmt = "SELECT * FROM tbl_Users ORDER BY FullName"
l_da = New SqlDataAdapter(l_sqlStmt, l_sqlConn)
m_ds = New DataSet
l_da.Fill(m_ds)

Catch ex As SqlException
Dim l_sqlerr As SqlError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
Finally
If l_sqlConn.State = ConnectionState.Open Then
l_sqlConn.Close()
l_sqlConn = Nothing
End If
End Try
Dim l_cmd As SqlCommand = l_sqlConn.CreateCommand()
l_cmd.CommandType = CommandType.StoredProcedure
l_cmd.CommandText = "<Stored Procedure Name>"
l_cmd.Parameters.Add(New SqlParameter("UnitOutDate", SqlDbType.DateTime))
l_cmd.Parameters("UnitOutDate").Value = Date.Now
l_cmd.executeNonQuery()

|||It's all good and pretty except for one little thing:
when i try to open the connection i get a "PlatformNotSupportedException"
is there any reason that exception could appears?|||on the emulator or on the device|||

let me specify a little:
I have a device which has a microsoft windows ce .net version 4.2 system.
i try to connect it to sqlserver 2000 that is on a pc server(win 2000 for servers). I create SqlConnect object on the device with the connection string
"Persist Security Info=False;Integrated Security=false; database=northwind; server=xxx.xxx.xxx.xxx; Connect Timeout=30; User ID=testing; Password=test;"
so far so good, but when i perform the function Open(on the device itself), I get the "PlatformNotSupportedException".
and i just cannot figure out why.

|||hmmm it sounds as if something is missing on the device like a library.
Try testing it on the emulator and see if you get the same error|||

That usually means DB collation you're using is not supported on particular device. You can change collation of this DB or get localized device which supports locale you’re using.

|||You hit the correct spot, it really is DB collation.
I created another database on the same server and gave it's collation Latin1_General_CI_AS and tried again and it worked :-)

which brings me to this question:
is there any way to tell the connection object what the database collation is? because I dont think i'm allowed to convert the database to Latin1_General_CI_AS collation.
or any other way to solve this mess...|||you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP|||

Darren Shaffer wrote:

you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP

I have this same problem (platformnotsupported) and I don't understand your answer. Problem is with SqlConnection and you say that we must add locale identifier in connection string to SqlCeConnection?
|||

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

|||

Ilya Tumanov wrote:

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

I know
Ad 1. No, i can't change collation of my database.
Ad.2. I can't get device which support my database collation (it is Symbol MC9090 with Win CE 5.0 and my database have Polish_CI_AS collation). Works great when I change colllation to Latin1_general but i can't do this with my main database.

Is there an other option? I try RDA but problem with charsets exists, Polish chars ? ? ? ń etc. changes to strange symbols, its useless.

connecting to sql server from mobile device

How can I connect to sql server from a mobile device?
the server is on pc and my app is on the device.
I want to perform commands and stored procedures which will be performed online on the pc and get immediate results.
is there any way of achiving that?

You will need to add a reference to SQLClient in your project.
Once this is done its the same as if you were connecting to sqlserver on the full framework with ASP.net or VB.net
here is some sample code

Imports System.Data.Common
Imports System.Data.SqlClient
Private m_connString As String = "user id=sa;password=;initial catalog=Northwind;Server=xxx.xxx.xxx.xxx;"
Private m_ds As New DataSet
Dim l_sqlConn As SqlConnection
Dim l_da As DataAdapter
Dim l_ds As DataSet
Dim l_sqlStmt As String
Try
l_sqlConn = New SqlConnection(m_connString)
l_sqlConn.Open()
l_sqlStmt = "SELECT * FROM tbl_Users ORDER BY FullName"
l_da = New SqlDataAdapter(l_sqlStmt, l_sqlConn)
m_ds = New DataSet
l_da.Fill(m_ds)

Catch ex As SqlException
Dim l_sqlerr As SqlError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
Finally
If l_sqlConn.State = ConnectionState.Open Then
l_sqlConn.Close()
l_sqlConn = Nothing
End If
End Try
Dim l_cmd As SqlCommand = l_sqlConn.CreateCommand()
l_cmd.CommandType = CommandType.StoredProcedure
l_cmd.CommandText = "<Stored Procedure Name>"
l_cmd.Parameters.Add(New SqlParameter("UnitOutDate", SqlDbType.DateTime))
l_cmd.Parameters("UnitOutDate").Value = Date.Now
l_cmd.executeNonQuery()

|||It's all good and pretty except for one little thing:
when i try to open the connection i get a "PlatformNotSupportedException"
is there any reason that exception could appears?|||on the emulator or on the device|||

let me specify a little:
I have a device which has a microsoft windows ce .net version 4.2 system.
i try to connect it to sqlserver 2000 that is on a pc server(win 2000 for servers). I create SqlConnect object on the device with the connection string
"Persist Security Info=False;Integrated Security=false; database=northwind; server=xxx.xxx.xxx.xxx; Connect Timeout=30; User ID=testing; Password=test;"
so far so good, but when i perform the function Open(on the device itself), I get the "PlatformNotSupportedException".
and i just cannot figure out why.

|||hmmm it sounds as if something is missing on the device like a library.
Try testing it on the emulator and see if you get the same error

|||

That usually means DB collation you're using is not supported on particular device. You can change collation of this DB or get localized device which supports locale you’re using.

|||You hit the correct spot, it really is DB collation.
I created another database on the same server and gave it's collation Latin1_General_CI_AS and tried again and it worked :-)

which brings me to this question:
is there any way to tell the connection object what the database collation is? because I dont think i'm allowed to convert the database to Latin1_General_CI_AS collation.
or any other way to solve this mess...|||you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP|||

Darren Shaffer wrote:

you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP

I have this same problem (platformnotsupported) and I don't understand your answer. Problem is with SqlConnection and you say that we must add locale identifier in connection string to SqlCeConnection?|||

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

|||

Ilya Tumanov wrote:

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

I know
Ad 1. No, i can't change collation of my database.
Ad.2. I can't get device which support my database collation (it is Symbol MC9090 with Win CE 5.0 and my database have Polish_CI_AS collation). Works great when I change colllation to Latin1_general but i can't do this with my main database.

Is there an other option? I try RDA but problem with charsets exists, Polish chars ? ? ? ń etc. changes to strange symbols, its useless.