Code Page

I read text files in ASP on server side, then try to enter data into a
database in SQL Server. The files are in ibm852 (1250) coding - at least
that's the code page with which they are shown properly when putting the
lines read on the output.
However, when I enter them into the SQL Server (currently simply by setting
a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
it through the connection such as oConn.Execute sSQL), the special
characters (Hungarian) are all changed to meaningless characters, such as
'hell:' for 'helló' etc.
The texty columns are of type varchar(n). I also tried nvarchar, but nothing
has changed.
So, how can I set the appropriate code page in SQL Server or transform the
strings so that the special characters don't get messed up when entered?Agoston,
Try changing the column type to nvarchar(n) and executing this query:
INSERT INTO T1(6, N'helló')
Perhaps you simply forgot to type the N required to signify a Unicode
string.
Steve Kass
Drew University
Agoston Bejo wrote:
>I read text files in ASP on server side, then try to enter data into a
>database in SQL Server. The files are in ibm852 (1250) coding - at least
>that's the code page with which they are shown properly when putting the
>lines read on the output.
>However, when I enter them into the SQL Server (currently simply by setting
>a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
>it through the connection such as oConn.Execute sSQL), the special
>characters (Hungarian) are all changed to meaningless characters, such as
>'hell:' for 'helló' etc.
>The texty columns are of type varchar(n). I also tried nvarchar, but nothing
>has changed.
>So, how can I set the appropriate code page in SQL Server or transform the
>strings so that the special characters don't get messed up when entered?
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
> Agoston,
> Try changing the column type to nvarchar(n) and executing this query:
> INSERT INTO T1(6, N'helló')
> Perhaps you simply forgot to type the N required to signify a Unicode
> string.
It doesn't change a thing. The same messy characters are in the db. Any
other ideas?
> Steve Kass
> Drew University
> Agoston Bejo wrote:
> >I read text files in ASP on server side, then try to enter data into a
> >database in SQL Server. The files are in ibm852 (1250) coding - at least
> >that's the code page with which they are shown properly when putting the
> >lines read on the output.
> >
> >However, when I enter them into the SQL Server (currently simply by
setting
> >a string to an "INSERT INTO T1(6, 'hello')"-like statement and then
execute
> >it through the connection such as oConn.Execute sSQL), the special
> >characters (Hungarian) are all changed to meaningless characters, such as
> >'hell:' for 'helló' etc.
> >The texty columns are of type varchar(n). I also tried nvarchar, but
nothing
> >has changed.
> >
> >So, how can I set the appropriate code page in SQL Server or transform
the
> >strings so that the special characters don't get messed up when entered?
> >
> >
> >
> >|||I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
Unicode string? If not, it will not preserve the accented characters.
There ought to be some way to specify that it be Unicode, similar to the
way you do in SQL Server with the N prefix. If that fails to produce
the right result, I'm not sure what could be happening, since Unicode
strings shouldn't be affected by code page settings, but I'd probably
try specifying the 1250 code page somewhere on the ASP page - maybe the
ASP programmers have a better idea.
SK
Agoston Bejo wrote:
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
>
>>Agoston,
>> Try changing the column type to nvarchar(n) and executing this query:
>>INSERT INTO T1(6, N'helló')
>>Perhaps you simply forgot to type the N required to signify a Unicode
>>string.
>>
>
>It doesn't change a thing. The same messy characters are in the db. Any
>other ideas?
>
>
>>Steve Kass
>>Drew University
>>Agoston Bejo wrote:
>>
>>I read text files in ASP on server side, then try to enter data into a
>>database in SQL Server. The files are in ibm852 (1250) coding - at least
>>that's the code page with which they are shown properly when putting the
>>lines read on the output.
>>However, when I enter them into the SQL Server (currently simply by
>>
>setting
>
>>a string to an "INSERT INTO T1(6, 'hello')"-like statement and then
>>
>execute
>
>>it through the connection such as oConn.Execute sSQL), the special
>>characters (Hungarian) are all changed to meaningless characters, such as
>>'hell:' for 'helló' etc.
>>The texty columns are of type varchar(n). I also tried nvarchar, but
>>
>nothing
>
>>has changed.
>>So, how can I set the appropriate code page in SQL Server or transform
>>
>the
>
>>strings so that the special characters don't get messed up when entered?
>>
>>
>>
>
>|||Steve Kass wrote:
> I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
> Unicode string? If not, it will not preserve the accented characters.
> There ought to be some way to specify that it be Unicode, similar to
> the way you do in SQL Server with the N prefix. If that fails to
> produce the right result, I'm not sure what could be happening, since
> Unicode
> strings shouldn't be affected by code page settings, but I'd probably
> try specifying the 1250 code page somewhere on the ASP page - maybe
> the ASP programmers have a better idea.
>
Good thought, but vbscript is unicode by default.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Code Page

I read text files in ASP on server side, then try to enter data into a
database in SQL Server. The files are in ibm852 (1250) coding - at least
that's the code page with which they are shown properly when putting the
lines read on the output.
However, when I enter them into the SQL Server (currently simply by setting
a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
it through the connection such as oConn.Execute sSQL), the special
characters (Hungarian) are all changed to meaningless characters, such as
'hell:' for 'hell' etc.
The texty columns are of type varchar(n). I also tried nvarchar, but nothing
has changed.
So, how can I set the appropriate code page in SQL Server or transform the
strings so that the special characters don't get messed up when entered?
Agoston,
Try changing the column type to nvarchar(n) and executing this query:
INSERT INTO T1(6, N'hell')
Perhaps you simply forgot to type the N required to signify a Unicode
string.
Steve Kass
Drew University
Agoston Bejo wrote:

>I read text files in ASP on server side, then try to enter data into a
>database in SQL Server. The files are in ibm852 (1250) coding - at least
>that's the code page with which they are shown properly when putting the
>lines read on the output.
>However, when I enter them into the SQL Server (currently simply by setting
>a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
>it through the connection such as oConn.Execute sSQL), the special
>characters (Hungarian) are all changed to meaningless characters, such as
>'hell:' for 'hell' etc.
>The texty columns are of type varchar(n). I also tried nvarchar, but nothing
>has changed.
>So, how can I set the appropriate code page in SQL Server or transform the
>strings so that the special characters don't get messed up when entered?
>
>
|||"Steve Kass" <skass@.drew.edu> wrote in message
news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
> Agoston,
> Try changing the column type to nvarchar(n) and executing this query:
> INSERT INTO T1(6, N'hell')
> Perhaps you simply forgot to type the N required to signify a Unicode
> string.
It doesn't change a thing. The same messy characters are in the db. Any
other ideas?
[vbcol=seagreen]
> Steve Kass
> Drew University
> Agoston Bejo wrote:
setting[vbcol=seagreen]
execute[vbcol=seagreen]
nothing[vbcol=seagreen]
the[vbcol=seagreen]
|||I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
Unicode string? If not, it will not preserve the accented characters.
There ought to be some way to specify that it be Unicode, similar to the
way you do in SQL Server with the N prefix. If that fails to produce
the right result, I'm not sure what could be happening, since Unicode
strings shouldn't be affected by code page settings, but I'd probably
try specifying the 1250 code page somewhere on the ASP page - maybe the
ASP programmers have a better idea.
SK
Agoston Bejo wrote:

>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
>
>
>It doesn't change a thing. The same messy characters are in the db. Any
>other ideas?
>
>
>setting
>
>execute
>
>nothing
>
>the
>
>
>
|||Steve Kass wrote:
> I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
> Unicode string? If not, it will not preserve the accented characters.
> There ought to be some way to specify that it be Unicode, similar to
> the way you do in SQL Server with the N prefix. If that fails to
> produce the right result, I'm not sure what could be happening, since
> Unicode
> strings shouldn't be affected by code page settings, but I'd probably
> try specifying the 1250 code page somewhere on the ASP page - maybe
> the ASP programmers have a better idea.
>
Good thought, but vbscript is unicode by default.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

COALESCE with parameters

I am trying to build a report table based on user supplied criteria at run time. The user may or may not enter criteria into one or more fields. I used the COLAESCE as follows (the temp vars may be passed valid data or left null by the user):

select * from dbo.employee

where LastName>=COALESCE(@.ln,lastname) andLastName<=COALESCE(@.ln2,lastname) andFirstName>=COALESCE(@.fn,firstname) andFirstName<=COALESCE(@.fn2,firstname) andhiredate>=COALESCE(@.hire,hiredate) andhiredate<=COALESCE(@.hire2,hiredate) andcheckdate>=COALESCE(@.chk,checkdate) andcheckdate<=COALESCE(@.chk2,checkdate)

The problem comes when I want to return rows that include columns that may be null. For example the CHECKDATE col might be the date the employee was reviewed and for new employees it may be null. I still want to return that row.

I had thought of creating default values for every column when the user adds a row to a table. I can set all char fields = ' ' and int fields = 0, but what is a valid default value for a date type col that won't cause problems when other procs try to grab the field and use it?

Or is there a better way to use the COALESCE function?

Thanks all!

I would add a third parameter to the coalesce function. For instance COALESCE(@.var,FieldName,'1/1/1900'). The third field would be the "default" value if the first two return null. HTH.

-Chris

|||If you couldn't choose some value as "empty", you could add additional parameter like @.ln_is_empty and then use something like (@.ln_is_empty=1 or (Lastname>=@.ln and @.ln_is_empty=0) )|||

Assuming that the query is contained within a stored procedure then you would have to start by declaring another input parameter per search term to indicate whether the WHERE condition for the relevant column should check for NULL or whether the search term should be ignored (i.e. equal to itself), currently it seems that you have no way to distinguish between the two.

Once this has been done you can use:

WHERE ((checkdate >= COALESCE(@.chk,checkdate) and checkdate <= COALESCE(@.chk2,checkdate))

OR (@.checkdateisnull = 1 AND checkdate IS NULL))

As an aside, with a query such as the one you have presented you are unlikely to see great performance. It might be better to dynamically create and execute a SQL string inside the stored procedure, forget using COALESCE inside the SQL string and include only what's actually needed in the WHERE clause - using COALESCE in the way that you have done is likely to lead to table / index scans and gives little scope for performance improvements by indexing.

Chris

|||

There are better ways to write this query than using COALESCE with column names. The above usage will negate use of any indexes on the columns. So you will be pretty much scanning the entire table for any combination of parameters. See the link below for various techniques that will help you solve the problem. Look for my name to see some techniques that use COALESCE/ISNULL but with better results. Erland also covers in detail other techniques that will help you get the best results.

http://www.sommarskog.se/dyn-search.html

|||

Thanks!!!

I'll be studying that document for quite some time!

Clustered index

I have a few tables with a clustered index (SQL Server 2000).
When I open some of these tables, enter a new value into an indexed field
and reopen the table (actually click Run in a context menu), the table is
ordered by the indexed field.
In some other tables, the new value is not ordered and remain in the last
row.
Why is this difference between the tables and what is a proper behaviour of
the clustered index?
Thanks."Vik" <viktorum@.==yahoo.com==> wrote in message
news:%23F%23A0ToEIHA.4228@.TK2MSFTNGP02.phx.gbl...
>I have a few tables with a clustered index (SQL Server 2000).
> When I open some of these tables, enter a new value into an indexed field
> and reopen the table (actually click Run in a context menu), the table is
> ordered by the indexed field.
> In some other tables, the new value is not ordered and remain in the last
> row.
> Why is this difference between the tables and what is a proper behaviour
> of the clustered index?
> Thanks.
>
This is really a feature of whatever application you are using to query the
table. When you "open" a table in Enterprise Manager for example it will
just query the table by selecting all columns and rows but it won't specify
an ORDER BY clause. That means the ordering is uspecified and will be
determined by whatever execution plan the server chooses at runtime. If
ORDER BY isn't specified but the table has a clustered index then there is a
fair chance that the rows will be returned in an order that matches that
index - although that's definitely not guaranteed.
If you want to be sure what your application is doing, then use SQL Profiler
to capture the statements that it uses and see if it includes an ORDER BY
clause.
Just remember that a table is an unordered set of rows. Unless you query it
using ORDER BY you should assume nothing about the order of rows returned.
--
David Portas|||While this is 100% true, it doesn't tell the whole story.
I have noticed the default order of rows being "sorted" on a non-ordered
select for years. At a casual view, this seems to actually be a sort, even
though it isn't. In particular, I've noticed this on freshly built tables
with (and without) clustered indexes and where the internal blocks used to
store the data were contigious. When clustered indexes are used, I've seen
the "sort" maintain itself after an insert.
At one point I was convinced this was reliable behavior, sadly, it isn't.
Sure looks that way though. I suspect it has to do with the physical order
the data was entered into the database and the way clustered indexes are
implimented.
So, it is not automatically a feature of something, it could be the order
the data is coming out of the database because of the order it was put in
and/or where the insert occured (because of the clustered index).
Jay
> This is really a feature of whatever application you are using to query
> the table. When you "open" a table in Enterprise Manager for example it
> will just query the table by selecting all columns and rows but it won't
> specify an ORDER BY clause. That means the ordering is uspecified and will
> be determined by whatever execution plan the server chooses at runtime. If
> ORDER BY isn't specified but the table has a clustered index then there is
> a fair chance that the rows will be returned in an order that matches that
> index - although that's definitely not guaranteed.
> If you want to be sure what your application is doing, then use SQL
> Profiler to capture the statements that it uses and see if it includes an
> ORDER BY clause.
> Just remember that a table is an unordered set of rows. Unless you query
> it using ORDER BY you should assume nothing about the order of rows
> returned.
> --
> David Portas
>