Bad ordering when ordering by varchar field

Hi guys,
did anyone come across this problem?
I have a query where I ORDER BY Part_Number field which is varchar but
the ordered result is
10169-G
10169-MVS
10169
which is mess because normal person would put first the 10169 and then
the others?
Thanks for any help Milan
hi Milan,
Milan Reznicek wrote:
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar
> but the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and
> then the others?
> Thanks for any help Milan
can you please post your actual code, as simple repro are correct, on my
instance..
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number
--<--
Part_Number
10169
10169-G
10169-MVS
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What collation is your database using?
"Milan Reznicek" <reznicek@.rezna.info> wrote in message
news:uZCQw9ZSFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar but
> the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and then
> the others?
> Thanks for any help Milan
>
|||Thanks both, you and JJ. Your code is working all right until I add my
collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and which
completely messes up the ordering.
Milan
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> pe v diskusnm pspvku
news:3d4df0F6opoh9U1@.individual.net...
> hi Milan,
> Milan Reznicek wrote:
> can you please post your actual code, as simple repro are correct, on my
> instance..
> SET NOCOUNT ON
> DECLARE @.t TABLE ( Part_Number varchar(10) )
> INSERT INTO @.t VALUES ( '10169-G' )
> INSERT INTO @.t VALUES ( '10169-MVS' )
> INSERT INTO @.t VALUES ( '10169' )
> SELECT t.Part_Number
> FROM @.t t
> ORDER BY t.Part_Number
> --<--
> Part_Number
> --
> 10169
> 10169-G
> 10169-MVS
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Milan,
Milan Reznicek wrote:
> Thanks both, you and JJ. Your code is working all right until I add my
> collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and
> which completely messes up the ordering.
of course collation change the way the sort order is performed...
you can, if you are allowed to from your app design, change the sort order
rules for queries like that like
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number COLLATE Latin1_General_BIN
you specify that way a different sort order and rule the order by clause
must conform with... but verify this do not comprimise other app designs and
constraints...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backups over a network

I am using the following syntax in sql 2000 in order to transfer the backup across the network to a backup machine:

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'

The problem I am experiencing is that when I run this script, I get the response:

Access is denied.
0 file(s) copied

Can anyone tell me what I am doing wrong?Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

Still, don't you think it's a security thing?|||Originally posted by Brett Kaiser
Yea, your service account doesn't have rights to the box..

besides, backing up across the network can't be a good idea..

why not dump it locally and then move it?

he is backing up locally and then trying to copy it over the network .. Brett

read the post again

xp_cmdshell N'copy /Y c:\mssql7\backup\bill_backup.bak \\Backup\backups\Bill\Backup'|||Ok, ive given up and having sql do the move for me. Not exactly sure whats causing the problem, but I have worked around it. I have just written a bat file that copies over a mapped drive that is run thru the schedule tasks interface. Not as clean as I would like, but it works.

Thanks again guys