Order by clause work incorrect

when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_dateIf I am not mistaken, you have no influence on the physical order of recordsets saved in tables in MSSQL, so your table #fifo_temp will not be ordered by out_date.

Choose the order of recordsets when extracting the data from the table, so use:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')

DROP TABLE ##stuff_fifo
select * from #fifo_temp
ORDER BY out_date

Regards

kbk|||KBK is correct. A table has no inherant "order" for either columns or rows, although a result set has order for both. The only place where order makes any sense (or difference) is in the result set.

-PatP|||Set Out_Date as your clustered index and the data will be ordered the way you want, but if the order is important to you then it is best to specify it each time you select from the dataset using and ORDER BY clause.|||I see the problem.Thank all kindly friends.

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures this
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PWD=password ;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and no
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie
|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>

Oracle10 ODBC connection

I have a windows 2003 server with sql server 2000
I run daily jobs that delete contents of sql tables an insert data from an
oracle9 database using
SELECT
*
INTO
DBname
FROM
OPENROWSET
(
'MSDASQL',
'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
'SELECT
*
FROM
OracleDBName’
)
This worked until the oracle9 database was upgraded to oracle10. The new
oracle10 server is running windows 2003 server.
On the sql server I created a new service name in oracle client and tested
the connection it works but when I try to run the jobs–stored procedures t
his
is the following error
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Driver Manager] Data source name not found and no
default
driver specified".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSDASQL" for
linked server "(null)".
The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
MSORCL32.dll dated 3/24/2005.
Appreciate any help I can get."HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
>I have a windows 2003 server with sql server 2000
> I run daily jobs that delete contents of sql tables an insert data from an
> oracle9 database using
> SELECT
> *
> INTO
> DBname
> FROM
> OPENROWSET
> (
> 'MSDASQL',
> 'DSN=ODBCOracleAccount;UID=oracleuser;PW
D=password;',
> 'SELECT
> *
> FROM
> OracleDBName'
> )
> This worked until the oracle9 database was upgraded to oracle10. The new
> oracle10 server is running windows 2003 server.
> On the sql server I created a new service name in oracle client and tested
> the connection it works but when I try to run the jobs-stored procedures
> this
> is the following error
> OLE DB provider "MSDASQL" for linked server "(null)" returned message
> "[Microsoft][ODBC Driver Manager] Data source name not found and n
o
> default
> driver specified".
> Msg 7303, Level 16, State 1, Line 2
> Cannot initialize the data source object of OLE DB provider "MSDASQL" for
> linked server "(null)".
> The Microsoft ODBC for Oracle is version 2.576.1830.00 the file is
> MSORCL32.dll dated 3/24/2005.
> Appreciate any help I can get.
>
Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Can you test it and connect? Is 10g perhaps in a different home than 9i
was?
- Arnie|||"Arnie" wrote:

> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:E51B8327-CCA7-435D-99EA-66686990C68C@.microsoft.com...
> Silly questions. Did you set up an ODBC system DSN for the Oracle server?
Yes
I can not connect using the ODBC drivers I set up a different serivce name
and tested the oracle client made a connection but no connection using ODBC
> Can you test it and connect? Is 10g perhaps in a different home than 9i
> was?
home meaning
the old oracle9i server was renamed and the IP address changed
the new oracle 10g has the same DNS name and same IP address as the old 9i

> - Arnie
>
>