Order by Issue

I am having a small issue with the order by command.

Everytime I run the code below, everything displays in the correct order which was assigned by the order by command:

select *
from [table1]
order by column_a, column_b, column_c

My problem is that I want it to be stored in another table in the order that I specify, so I run the code below which has the same order:

select *
into [new table]
from [table1]
order by column_a, column_b, column_c

My problem is that when I do a select * form the new table that I inserted all the information into in order, it will not be returned in order. Why would that be? My understanding is that it would store the info in the new table by the way I specified on the order by line.

Any help would be appreciated. It is driving me crazy!Data in a recordset has no order unless specified. While you may have specified an order when you inserted the records, there is no guarantee that they will be maintained in that order, or returned in that order.

You can ensure that they are stored in a specific order by placing a clustered index on the columns you want, and MOST of the time queries against the table will return the records in that order, but again there is no guarantee.

You should always issue an ORDER BY clause on all SQL Statements which require data to be returned in a specified order.|||The only time the resultset will be in the order of the clustered index is if you run your SELECT on a single processor machine, or if you set only 1 processor for SQL Server service, or if you use OPTION (MAXDOP 1). In any other case the order of the clustered index does not affect the order of the resultset without ORDER BY.

ORDER BY in a SP

I'm getting a little frustrated... I'm trying to write a stored proc... the
following code works fine on its own in Query Analyzer... when I put it into
my stored proc... SQL Server complains about the ORDERS BY clause.
SELECT TOP 1 UserID FROM Users
WHERE UserID > 50
ORDER BY UserID DESC
According to MSDN "The ORDER BY clause is invalid in views, inline
functions, derived tables, and subqueries, unless TOP is also specified."...
so I don't see why it isn't working.what is the Procedure code you are using. Looks like some minor typing error
It works at my end .
CREATE TABLE Users
(
User_ID int,
User_Name varchar(10)
)
GO
INSERT INTO Users
SELECT 1, 'Jon'
UNION
SELECT 10,'Mary'
UNION
SELECT 50,'Ron'
UNION
SELECT 60, 'Harry'
UNION
SELECT 80, 'Nancy'
GO
CREATE PROCEDURE usp_test
AS
SELECT TOP 1 * FROM Users
WHERE User_ID > 50
Order BY User_ID DESC
GO
Exec usp_test
GO
Cheers,
Siaj
"andrew" wrote:

> I'm getting a little frustrated... I'm trying to write a stored proc... th
e
> following code works fine on its own in Query Analyzer... when I put it in
to
> my stored proc... SQL Server complains about the ORDERS BY clause.
> SELECT TOP 1 UserID FROM Users
> WHERE UserID > 50
> ORDER BY UserID DESC
> According to MSDN "The ORDER BY clause is invalid in views, inline
> functions, derived tables, and subqueries, unless TOP is also specified.".
.
> so I don't see why it isn't working.|||I figured it out... I had the SELECT statement completely enclosed in ( ) -
which seemed to work fine without an ORDER BY... but when I added in the
ORDER BY... which was still in between the ( )... suddenly there was a
problem... YAY computers.
"siaj" wrote:
> what is the Procedure code you are using. Looks like some minor typing err
or
> It works at my end .
> CREATE TABLE Users
> (
> User_ID int,
> User_Name varchar(10)
> )
> GO
> INSERT INTO Users
> SELECT 1, 'Jon'
> UNION
> SELECT 10,'Mary'
> UNION
> SELECT 50,'Ron'
> UNION
> SELECT 60, 'Harry'
> UNION
> SELECT 80, 'Nancy'
> GO
> CREATE PROCEDURE usp_test
> AS
> SELECT TOP 1 * FROM Users
> WHERE User_ID > 50
> Order BY User_ID DESC
> GO
> Exec usp_test
> GO
> Cheers,
> Siaj
> "andrew" wrote:
>

Order By Date Format

I have inherited some code with a view that formats dates like
Mar 26, 2003
May 06, 2006
Jun 05, 2006
Example
SELECT DISTINCT TOP 5000 createDate, responsibilities
FROM vw_someview
ORDER BY createDate DESC,responsibilities
the order by doesnt come out right. The view select for the field is
convert( varchar(50), pp.createDate, 107 ) as createDate,
It is stored as 2003-03-26 14:29:41.880
Id like to order by properly but was wondering if I can do another
convert to make it order by properly?
Thanksuse order by convert(datetime,CreateDate)
take a look at these 2 example
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by CreateDate
select * from
(select 'Mar 26, 2003' CreateDate union all
select 'May 06, 2006' union all
select 'Jun 05, 2006') z
order by convert(datetime,CreateDate)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Brian wrote:
> I have inherited some code with a view that formats dates like
> Mar 26, 2003
> May 06, 2006
> Jun 05, 2006
> Example
> SELECT DISTINCT TOP 5000 createDate, responsibilities
> FROM vw_someview
> ORDER BY createDate DESC,responsibilities
> the order by doesnt come out right. The view select for the field is
> convert( varchar(50), pp.createDate, 107 ) as createDate,
> It is stored as 2003-03-26 14:29:41.880
> Id like to order by properly but was wondering if I can do another
> convert to make it order by properly?
> Thanks|||Brian,
Sounds to me like your view needs changing, to report the date as a date.
How about leaving the formatting until you need to display it?
Currently, you're converting the date into a string and then querying that,
and then trying to sort by the result of a function on that string. That's
messy. Instead, let your view report the date itself, and tell whatever uses
that view to display the date in an appropriate format. If you really need
the view to report the date as string, then put it in a different column.
Of course, refactoring like this will be so much nicer in Visual Studio Team
Edition for Database Professionals, because you should be able to rename a
column in a view (to, say, CreateDateFormatted), and have it run through
your solution and do all the renaming for you.
Rob
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1149525868.872085.191850@.i40g2000cwc.googlegroups.com...
> use order by convert(datetime,CreateDate)
> take a look at these 2 example
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by CreateDate
>
> select * from
> (select 'Mar 26, 2003' CreateDate union all
> select 'May 06, 2006' union all
> select 'Jun 05, 2006') z
> order by convert(datetime,CreateDate)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Brian wrote:
>

Order By Case Confusion

I can't seem to figure out what is going on here... try this code. It should return 3 rows with no problem as is.

===========
DECLARE
@.SortOn varchar(20)

SET @.SortOn = 'dicount'

SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END
===========

Now set @.SortOn = 'discounttype' and you get:
Server: Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to numeric.

?

Now for more confusion.. when you comment out lines 3,4,and 5 of the CASE statement (WHEN 'lowqty'...'highqty'...'dicount' lines) you once again get 3 rows returned without error.

Anyone able to shed some light on what is goin on?

[MESSAGE EDITED: misspelled discounttype.. sorry]The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause. SQL is expecting a numeric - look at data type precedence.|||Try this:

DECLARE @.SortOn varchar(20)

SET @.SortOn = 'dicount'

SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY
CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN CAST(stor_id AS VARCHAR)
WHEN 'lowqty' THEN CAST(lowqty AS VARCHAR)
WHEN 'highqty' THEN CAST(highqty AS VARCHAR)
WHEN 'dicount' THEN CAST(discount AS VARCHAR)
END|||The only problem with that code is that it will sort the numerics as varchars(ascii character comparision) and not as a true numeric. So values 10,9,12 would be sorted as 10,12,9 (ascending) and 9,12,10 (descending).|||The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause i've looked at that answer several times, and the error message too, and i still don't understand why it would throw an error

but then, the query's kind of weird too

why not stick the whatever-i-want-to-sort-on-this-week into an extra column in the result set and sort on that...SELECT discounttype
, isNull(stor_id,'') stor_id
, isNull(lowqty,0) lowqty
, isNull(highqty,0) highqty
, discount
, CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END as sortfield
FROM pubs..discounts
ORDER BY sortfieldmaybe that'll throw the same error, i dunno

rudy
http://rudy.ca/|||Originally posted by r937
maybe that'll throw the same error, i dunno

Nice thought but that errors as well.

I have solved my problem by seperating each datatype with an IF clause like so:

IF @.SortOn in (..) BEGIN -- varchars
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (varchars... etc..)
END
END ELSE BEGIN -- numerics
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (numerics... etc..)
END
END

Kinda messy but it works. Any thoughts to make it a bit more graceful would be appreciated.

Thanks for the replies!

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR
(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it i
s a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the
numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001,
015, 101, etc. The codes are alphanumeric in nature. There are hundreds of
codes, which makes it difficult for a user to scroll down to find the corre
ct code. Is there a way to
sort a character field in this manner? ThanksORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CH
AR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account t
he numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 1
01, etc. The codes are alphanumeric in nature. There are hundreds of codes, which
makes it difficult for a user to scroll down to find the correct code. Is there a w
ay
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it is a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to
sort a character field in this manner? Thanks
ORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like
1, 101, 15, DC1, etc
This code is displayed in a drop down list and sorts as follows because it is a character field
10
1
DC
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows
1
10
DC
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? ThankORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? Thanks
--
(Please reply only to the newsgroup)

Oracle with Reporting Services. "illegal variable name/number"

I'm developing a report with SQL Server Reporting Solutions (obviously ;)) against an Oracle DB and have run into a problem.

This is the code that I'm using for the dataset query that my report is to be based on:

="SELECT * FROM IRMFE_*************_V WHERE " &
IIf(Parameters!Invoice_No.Value(0)<> "ALL", "SUPPLIER_INVOICE_NO IN (:InvNo)", "SUPPLIER_INVOICE_NO LIKE '%'")

The parameter ":InvNo" is a paramter in the report and the user can select multiple values. I also included an "ALL" option manually in the query for the parameter. The query works fine with single or multiple options but if I select "ALL" I get the following error:

"Query execution failed for dataset 'FREIGHT'.
ORA-01036: illegal variable name/number'

There seems to be no problem when I manually run the query (SELECT * FROM IRMFE_*************_V WHERE SUPPLIER_INVOICE_NO LIKE '%')

The reason I used "SUPPLIER_INVOICE_NO LIKE '%'" is because I'll be adding a few more parameters and wanted to avoid any problems with adding "AND" between the conditions.

Any resolution or suggestion on this would be much appreciated guys.
Kev.After 1/2 of bangin my head against the wall I've resolved it. The reason it wasn't picking it up in the first place was that the check should have been:

IIf(Parameters!Invoice_No.Value(0)<> "'ALL'"

instead of

IIf(Parameters!Invoice_No.Value(0)<> "ALL"

I found Chris Hays solution and will just use that though.

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.

oracle oledb provider not registered in local machine

Hi,

I am trying to establish a connection to an Oracle database using the following code in a script task:

Dim oOleDbConnection As OleDbConnection
Dim sConnString As String = _
"Provider=OraOLEDB.Oracle;" & _
"Data Source=DBxxx;" & _
"User ID=Userxxx;" & _
"Password=Passxxx"
oOleDbConnection = New OleDb.OleDbConnection(sConnString)
oOleDbConnection.Open()

When I execute the script task, I receive the following error:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
Am using the correct provider?

I do not know how to resolve the said error.
Here are some facts:
Oracle 8i is installed.
Tnsnames.ora is updated.
I have successfully connected to Oracle SQL *Plus to test the above credentials.

Please help.
Thanks.

Do you have the Oracle OLE DB driver installed?

http://www.oracle.com/technology/software/tech/windows/ole_db/index.html|||

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

|||

r214acc wrote:

Shouldn't this be part of SQL Server 2005 standard installation?

I tried creating a new OLEDB connection and found in the drop down: Native OLE DB\Microsoft OLE DB Provider for Oracle.
I checked its full properties and saw provider specified: MSDAORA.1
Does this mean that the provider for Oracle is already installed?
I already tried MSDAORA.1 in my code but the same error appears.

The Microsoft OLE DB Provider for Oracle is not the same as the Oracle OLE DB Provider. The Oracle OLE DB Provider is published by Oracle, not Microsoft, and isn't part of the SQL Server 2005 standard installation. I know this because I'm running SQL Server Standard on one of my machines.|||So, like I said, try downloading the Oracle OLE DB driver, and then try your code.|||

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

|||

r214acc wrote:

Hi Duane,

You are right. However, I tried both MS OLE DB Provider for Oracle and Oracle OLE DB Provider but the error still persists.
Is it possible that Oracle 8i (on the server running SSIS) does not have the components to be used by the SQL Server 2005? or can it connect to a Oracle 9i 64-bit?

Please help me find the answer. Thanks.

Sorry, I don't have an answer for your question. However, I have a question for you. Are you using the 64 bit version of SQL Server 2005?|||

I am using 32 bit version of SQL Server 2005.
Is this the cause of the problem?

|||

There are some issues with Oracle drivers on 64 bit, hence Duane asked.

You say Oracle and SQL with SSIS are installed on the same server. That is fine, but are you really working on the server itself or a desktop?

|||

Hi,

I am working on the server itself.
I found something in the forum that may be related to the problem:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=449593&SiteID=17
It says that there is a bug on parsing folder names with parenthesis which happens when a 64-bit OS installs 32-bit programs.
Since I am running a 32-bit SSIS. This may be the problem.

Is there a fix here?
Thanks.

Oracle Acquire Connection for ssis return null

Hi All!

I'm writing a custom component in c# for SSIS and I have a problem with AcquireConnection...

I wrote this code:

public override void AcquireConnections(object transaction)

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmAdo = cm.InnerObject as ConnectionManagerAdoNet;

if (cmAdo == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO connection.");

this.conn = cmAdo.AcquireConnection(transaction) as OracleConnection;

}

but the 'conn' is ALWAYS null...

I tried

this.conn = ((IDTSConnectionManagerDatabaseParameters90)cmAdo).GetConnectionForSchema() as OracleConnection;

too, but no result: the 'conn' is null again...

If I use oledbconnection or sqlconnection instead of oracleconnection the method works fine... I really don't understand

could you help me plz?

Using "as" will return NULL if the object can't be cast to the specified type. Is the connection manager actually an Oracle connection?|||

Yes, the connection manager is an Oracle Connection.
I use .Net Providers --> OracleClient Data Provider....

|||

I find the problem: I reference the class Oracle.DataAccess.Client and Oracle.DataAccess.Type and the 'conn' is null...

If I reference the System.Data.OracleClient all is right!

But I must use the Oracle.DataAccess... :-(

Has anybody use it yet?

|||

What is Oracle.DataAccess.Client? Is it an ADO.NET provider? If it is an ADO.NET provider you need to use an appropriate connection type that comes with it. Or you should be able to use generic connection (DbConnection) object.

HTH.

|||

Check the type of the object being returned, as there is some cinfusion as to what you have done here. Perhaps some code like -

object test = cmAdo.AcquireConnection(transaction);

Debug.Assert(false, test.Type.ToString());

I would also look at the ConnectionManagerType property for the connection, as that shoudl also tell you the type of ADo.Net connection you can expect back. See th example value below, showing that I used the MS ADO.Net oracl provider, and the class I know is System.Data.OracleClient.OracleConnection, found in the assemby System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

ADO.NETystem.Data.OracleClient.OracleConnection, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Perhaps you have not used a managed provider, but the .Net OleDb provider to connect to an Oracle OLE-DB provider. In which case your connection manager type would be -

ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089