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.

Oracle Provider and Batch Statements

I am trying to use the Execute SQL Task to execute a very simple batch against the Oracle database:

Code Snippet

TRUNCATE TABLE FactTable1;
TRUNCATE TABLE FactTable2;
TRUNCATE TABLE FactTable3;
TRUNCATE TABLE FactTable4;
TRUNCATE TABLE FactTable5;

I have an Oracle Connection Manager configured to source off of an Oracle 9i database. I have tried both the Microsoft OLEDB provider for Oracle and the native Oracle provider.

When I run the above statement in SQL Plus or SQL Workbench, it works just fine.

In fact, it works in SQL Plus and SQL Workbench like this as well:

Code Snippet

TRUNCATE TABLE FactTable1

/
TRUNCATE TABLE FactTable2

/
TRUNCATE TABLE FactTable3

/
TRUNCATE TABLE FactTable4

/
TRUNCATE TABLE FactTable5

Although both of these would introduce the need to probably do some expressions to add the ";" or "/" conditionally only if the target is Oracle (we are *trying* to have one package support both SQL and Oracle) neither works. I get the following error message from SSIS command window:

...failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can anyone please provide some guidance on how to accomplish this with the Execute SQLTask task, taking into consideration that I very much generalize on the Oracle side?

Many thanks,

Rick

Well, I have tried every permutation that I can come up with and it seems that both Oracle providers simply do not support multiple statements in an Execute SQL Task.

If anyone knows otherwise, I would very much appreciate your input.

Rick

|||This is shot in the dark; but have you tried putting the staments in a file; then tell the execute sql task to use the file.|||

I haven't tried this and am happy to give it a shot, but can we talk about where this would be going? Obviously, reading the statements from a text file would not be a production solution but I imagine it would give you some insight as to where the issue might be?

Also, kind of along the same lines, I considered resorting to a ForEach Lookup tasks which certainly is more elegant than creating a bunch of sepereate Execute SQL Tasks but geez, something this fundamental (that works against SQL) shouldn't be this hard.

Many thanks.

Rick

|||Here's another suggestion... have you tried separating your statements with "GO"? GO is how most T-SQL clients parse for batches and although the ExecSQL works with other databases besides SQL Server, it uses the same parser as some of the other T-SQL clients we ship, so GO may work (unless the ExecSQL task is special casing T-SQL).|||My workaround is to create an Oracle stored proc wrappering in the statements in one SQL task, execute it in a second and drop the proc. in a third.

Oracle Provider and Batch Statements

I am trying to use the Execute SQL Task to execute a very simple batch against the Oracle database:

Code Snippet

TRUNCATE TABLE FactTable1;
TRUNCATE TABLE FactTable2;
TRUNCATE TABLE FactTable3;
TRUNCATE TABLE FactTable4;
TRUNCATE TABLE FactTable5;

I have an Oracle Connection Manager configured to source off of an Oracle 9i database. I have tried both the Microsoft OLEDB provider for Oracle and the native Oracle provider.

When I run the above statement in SQL Plus or SQL Workbench, it works just fine.

In fact, it works in SQL Plus and SQL Workbench like this as well:

Code Snippet

TRUNCATE TABLE FactTable1

/
TRUNCATE TABLE FactTable2

/
TRUNCATE TABLE FactTable3

/
TRUNCATE TABLE FactTable4

/
TRUNCATE TABLE FactTable5

Although both of these would introduce the need to probably do some expressions to add the ";" or "/" conditionally only if the target is Oracle (we are *trying* to have one package support both SQL and Oracle) neither works. I get the following error message from SSIS command window:

...failed with the following error: "ORA-00911: invalid character". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can anyone please provide some guidance on how to accomplish this with the Execute SQLTask task, taking into consideration that I very much generalize on the Oracle side?

Many thanks,

Rick

Well, I have tried every permutation that I can come up with and it seems that both Oracle providers simply do not support multiple statements in an Execute SQL Task.

If anyone knows otherwise, I would very much appreciate your input.

Rick

|||This is shot in the dark; but have you tried putting the staments in a file; then tell the execute sql task to use the file.|||

I haven't tried this and am happy to give it a shot, but can we talk about where this would be going? Obviously, reading the statements from a text file would not be a production solution but I imagine it would give you some insight as to where the issue might be?

Also, kind of along the same lines, I considered resorting to a ForEach Lookup tasks which certainly is more elegant than creating a bunch of sepereate Execute SQL Tasks but geez, something this fundamental (that works against SQL) shouldn't be this hard.

Many thanks.

Rick

|||Here's another suggestion... have you tried separating your statements with "GO"? GO is how most T-SQL clients parse for batches and although the ExecSQL works with other databases besides SQL Server, it uses the same parser as some of the other T-SQL clients we ship, so GO may work (unless the ExecSQL task is special casing T-SQL).|||My workaround is to create an Oracle stored proc wrappering in the statements in one SQL task, execute it in a second and drop the proc. in a third.