Oracle DBA to learn MS SQL: return

Showing posts with label return. Show all posts

Monday, March 26, 2012

order by issue

I'm trying to get a query to work that specifies how many records to return
(TOP(###)) and order them by one of the columns in the table. What I'm
getting is a server timeout.
My query looks kinda like this "select top(500) * from mytable order by
mycolumn"
I've tried this on a couple of different servers with the same timeout
error.
Any Ideas on how to fix?How many rows are in the table? Is there an index on mycolumn?
"Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
> I'm trying to get a query to work that specifies how many records to
> return
> (TOP(###)) and order them by one of the columns in the table. What I'm
> getting is a server timeout.
> My query looks kinda like this "select top(500) * from mytable order by
> mycolumn"
> I've tried this on a couple of different servers with the same timeout
> error.
> Any Ideas on how to fix?
>|||There are something over 1.2 million records (rows) in the table.
No the column is not indexed.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F3DD40E4-5AF5-4392-8A0D-A09AEC62FF14@.microsoft.com...
> How many rows are in the table? Is there an index on mycolumn?
>
> "Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
> news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
> > I'm trying to get a query to work that specifies how many records to
> > return
> > (TOP(###)) and order them by one of the columns in the table. What I'm
> > getting is a server timeout.
> >
> > My query looks kinda like this "select top(500) * from mytable order by
> > mycolumn"
> >
> > I've tried this on a couple of different servers with the same timeout
> > error.
> >
> > Any Ideas on how to fix?
> >
> >
>|||In that case, each query requires a full table scan to determine the 500
rows you want to select.
Either increase the connection timeout or (preferably) add proper
indexes.
Also, it is best to only select the columns that you need, and thus
avoid SELECT *
--
Gert-Jan
Jimmy Stewart wrote:
> There are something over 1.2 million records (rows) in the table.
> No the column is not indexed.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:F3DD40E4-5AF5-4392-8A0D-A09AEC62FF14@.microsoft.com...
> > How many rows are in the table? Is there an index on mycolumn?
> >
> >
> > "Jimmy Stewart" <jstewart@.globalparadigmsolutions.com> wrote in message
> > news:u7RQrc1mIHA.4292@.TK2MSFTNGP04.phx.gbl...
> > > I'm trying to get a query to work that specifies how many records to
> > > return
> > > (TOP(###)) and order them by one of the columns in the table. What I'm
> > > getting is a server timeout.
> > >
> > > My query looks kinda like this "select top(500) * from mytable order by
> > > mycolumn"
> > >
> > > I've tried this on a couple of different servers with the same timeout
> > > error.
> > >
> > > Any Ideas on how to fix?
> > >
> > >
> >

Order By ignoring the hyphen character

Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.

Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part

Here is the beginning of the Transactions table create script

CREATE TABLE [Transactions] (
[RecNo] [int] IDENTITY (1,1) NOT NULL,
[Part] [nvarchar] (30) NOT NULL ,
[TransDate] [nvarchar] (8) NOT NULL ,
[TransType] [nvarchar] (4) NOT NULL ,
[FromLoc] [nvarchar] (10) ,

The 'Part' column is an alphanumeric field. The problem I am having is that the
Order By seems to ignore the hyphen character '-' when the returned rows
are ordered by the Part (which can contain hyphens in any column).

Here is an example of what I get.

130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683
I was expecting this ( and I get this in and older database ).

068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072

It is by design. http://support.microsoft.com/kb/305704

What about using another column for sorting?

SELECT part, replace(part,'-','00000') as partForSortingHyphen

FROM Transactions

ORDER BY partForSortingHyphen ASC

|||Thanks,
Your suggusted workaround did the trick.
John
sql

Order By ignoring the hyphen character

Hi,
I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.

Thanks in advance, John
select part from transactions T where (T.transdate between '20070701' and '20070705') and
(T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part

Here is the beginning of the Transactions table create script

Here is an example of what I get.

130909N9
130909N9
130909N9
1-480698-0 * These two should not be here
1-480699-0 *
15-423
164-07700
164-07700
164-07700
1683
I was expecting this ( and I get this in and older database ).

068-03000
068-03000
06A19956
074-03200
077-367-0
08DU08
1-480698-0 * These should be here eariler in the data
1-480699-0
100-364072
100-364072

It is by design. http://support.microsoft.com/kb/305704

What about using another column for sorting?

SELECT part, replace(part,'-','00000') as partForSortingHyphen

FROM Transactions

ORDER BY partForSortingHyphen ASC

|||Thanks,
Your suggusted workaround did the trick.
John

Friday, March 23, 2012

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:

>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
Try:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegrou ps.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:

>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP

Order by clause help

Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

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!

Wednesday, March 21, 2012

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?
Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>
|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
>

ORDER BY CASe

I am creating a stored procedure to return a sorted list
Currently like this
DECLARE @.SORT tinyint
SET @.SORT = 1 --could be anything passed in
SELECT * FROM Table
ORDER BY
CASE
WHEN 1 THEN Col1
WHEN 2 THEN Col2
...etc
Now i would like to add 'ASC' or 'DESC' like follows
CASE
WHEN 1 THEN Col1 ASC
WHEN 2 THEN Col1 DESC
...etc
But this return an error 'Incorrect syntax near the keyword 'ASC'.'
IS there a way to make this work?Yes, but the syntax is a bit weird:
create table #a(b char(1), c char(1))
insert #a values ('a', 'z')
insert #a values ('b', 'y')
insert #a values ('c', 'x')
declare @.orderby int
set @.orderby = 1
SELECT *
FROM #a
ORDER BY
CASE WHEN @.Orderby = 1 THEN b end asc,
CASE WHEN @.Orderby = 2 THEN b end desc,
CASE WHEN @.Orderby = 3 THEN c end asc,
CASE WHEN @.Orderby = 4 THEN c end desc
"GrantMagic" <grant@.magicalia.com> wrote in message
news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> I am creating a stored procedure to return a sorted list
> Currently like this
> DECLARE @.SORT tinyint
> SET @.SORT = 1 --could be anything passed in
> SELECT * FROM Table
> ORDER BY
> CASE
> WHEN 1 THEN Col1
> WHEN 2 THEN Col2
> ...etc
> Now i would like to add 'ASC' or 'DESC' like follows
> CASE
> WHEN 1 THEN Col1 ASC
> WHEN 2 THEN Col1 DESC
> ...etc
> But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> IS there a way to make this work?
>|||Brilliant, that did the job perfectly!!
Thank You!
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OJZDGddkEHA.3648@.TK2MSFTNGP09.phx.gbl...
> Yes, but the syntax is a bit weird:
>
> create table #a(b char(1), c char(1))
> insert #a values ('a', 'z')
> insert #a values ('b', 'y')
> insert #a values ('c', 'x')
> declare @.orderby int
> set @.orderby = 1
> SELECT *
> FROM #a
> ORDER BY
> CASE WHEN @.Orderby = 1 THEN b end asc,
> CASE WHEN @.Orderby = 2 THEN b end desc,
> CASE WHEN @.Orderby = 3 THEN c end asc,
> CASE WHEN @.Orderby = 4 THEN c end desc
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:O0YlBadkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> > I am creating a stored procedure to return a sorted list
> >
> > Currently like this
> >
> > DECLARE @.SORT tinyint
> > SET @.SORT = 1 --could be anything passed in
> >
> > SELECT * FROM Table
> > ORDER BY
> >
> > CASE
> > WHEN 1 THEN Col1
> > WHEN 2 THEN Col2
> > ...etc
> >
> > Now i would like to add 'ASC' or 'DESC' like follows
> >
> > CASE
> > WHEN 1 THEN Col1 ASC
> > WHEN 2 THEN Col1 DESC
> > ...etc
> >
> > But this return an error 'Incorrect syntax near the keyword 'ASC'.'
> >
> > IS there a way to make this work?
> >
> >
>

Friday, March 9, 2012

Oracle query is SLOW, why ?

Hi,
Have the following sql analyzer query which takes 55 seconds to return
data.
SELECT i.TRANSACTION_DATE,
i.TYPE,
i.PART_ID,
i.WAREHOUSE_ID,
i.TRANSACTION_ID,
i.WORKORDER_BASE_ID,
i.QTY,
i.CLASS
FROM VMFG..SYSADM.INVENTORY_TRANS i
WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
order by i.TRANSACTION_ID
Remove the and's and the response goes down to 1 second.
SELECT i.TRANSACTION_DATE,
i.TYPE,
i.PART_ID,
i.WAREHOUSE_ID,
i.TRANSACTION_ID,
i.WORKORDER_BASE_ID,
i.QTY,
i.CLASS
FROM VMFG..SYSADM.INVENTORY_TRANS i
WHERE i.TRANSACTION_ID = 526123
order by i.TRANSACTION_ID
The question is why ? If you look at the data in the single row returned...
2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
17408 1.0000 R
the record clearly contains the correct data. Any insight on this GREATLY
appreciated because we have a reporting services query based on a date range
which also has the same poor response time. If you put the same query into an
Oracle sql Plus query the data returns instantly.
Thanks, Steve.How do you connect...?
In the dts-newsgroup heard some discussions to use text file when
transporting data from oracle to sql - so perhaps you need to transport
these using some kind of bulk export and query on top of an sql table
instead.
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> Hi,
> Have the following sql analyzer query which takes 55 seconds to return
> data.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> order by i.TRANSACTION_ID
> Remove the and's and the response goes down to 1 second.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123
> order by i.TRANSACTION_ID
> The question is why ? If you look at the data in the single row
returned...
> 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> 17408 1.0000 R
> the record clearly contains the correct data. Any insight on this GREATLY
> appreciated because we have a reporting services query based on a date
range
> which also has the same poor response time. If you put the same query into
an
> Oracle sql Plus query the data returns instantly.
> Thanks, Steve.
>|||You mention query analyzer and Oracle sql plus. OK, that makes me think you
are using linked servers. Try the query plan with the query analyzer and I
bet you find out that it is pulling all the data locally and then applying
the where clause. You have several options. One is to use Openquery instead
of the 4 part syntax. The other is to not use linked database. I only use
linked databases as a last resort. I suggest using a shared datasource in RS
to Oracle instead. For straight reporting I never use linked databases. It
complicates matters and buys you very little. What I do is have a shared
datasource that has a readonly user credential stored by RS. This takes
advantage of connection pooling and will be either much faster or much
easier or both than using linked databases.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> Hi,
> Have the following sql analyzer query which takes 55 seconds to return
> data.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> order by i.TRANSACTION_ID
> Remove the and's and the response goes down to 1 second.
> SELECT i.TRANSACTION_DATE,
> i.TYPE,
> i.PART_ID,
> i.WAREHOUSE_ID,
> i.TRANSACTION_ID,
> i.WORKORDER_BASE_ID,
> i.QTY,
> i.CLASS
> FROM VMFG..SYSADM.INVENTORY_TRANS i
> WHERE i.TRANSACTION_ID = 526123
> order by i.TRANSACTION_ID
> The question is why ? If you look at the data in the single row
returned...
> 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> 17408 1.0000 R
> the record clearly contains the correct data. Any insight on this GREATLY
> appreciated because we have a reporting services query based on a date
range
> which also has the same poor response time. If you put the same query into
an
> Oracle sql Plus query the data returns instantly.
> Thanks, Steve.
>|||Through a linked server
"Michael Vardinghus" wrote:
> How do you connect...?
> In the dts-newsgroup heard some discussions to use text file when
> transporting data from oracle to sql - so perhaps you need to transport
> these using some kind of bulk export and query on top of an sql table
> instead.
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Hi Bruce,
Right you are !! Will research creating a "shared data source".
Do not see a ton of info in help on this. Are you able to reference this
shared data source from your stored procedures just as you do the linked
server ?
Many thanks, Steve.
"Bruce L-C [MVP]" wrote:
> You mention query analyzer and Oracle sql plus. OK, that makes me think you
> are using linked servers. Try the query plan with the query analyzer and I
> bet you find out that it is pulling all the data locally and then applying
> the where clause. You have several options. One is to use Openquery instead
> of the 4 part syntax. The other is to not use linked database. I only use
> linked databases as a last resort. I suggest using a shared datasource in RS
> to Oracle instead. For straight reporting I never use linked databases. It
> complicates matters and buys you very little. What I do is have a shared
> datasource that has a readonly user credential stored by RS. This takes
> advantage of connection pooling and will be either much faster or much
> easier or both than using linked databases.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Hi Bruce,
Many thanks for your reply ! Absolutely correct on all accounts
(linked server, plan). Am now researching the "shared datasource" option. Do
you still reference it from your stored procedures ? That probably does not
change.
Any tech article tips or links on this greatly appreciated. Steve.
"Bruce L-C [MVP]" wrote:
> You mention query analyzer and Oracle sql plus. OK, that makes me think you
> are using linked servers. Try the query plan with the query analyzer and I
> bet you find out that it is pulling all the data locally and then applying
> the where clause. You have several options. One is to use Openquery instead
> of the 4 part syntax. The other is to not use linked database. I only use
> linked databases as a last resort. I suggest using a shared datasource in RS
> to Oracle instead. For straight reporting I never use linked databases. It
> complicates matters and buys you very little. What I do is have a shared
> datasource that has a readonly user credential stored by RS. This takes
> advantage of connection pooling and will be either much faster or much
> easier or both than using linked databases.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > Hi,
> > Have the following sql analyzer query which takes 55 seconds to return
> > data.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > order by i.TRANSACTION_ID
> >
> > Remove the and's and the response goes down to 1 second.
> > SELECT i.TRANSACTION_DATE,
> > i.TYPE,
> > i.PART_ID,
> > i.WAREHOUSE_ID,
> > i.TRANSACTION_ID,
> > i.WORKORDER_BASE_ID,
> > i.QTY,
> > i.CLASS
> > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > WHERE i.TRANSACTION_ID = 526123
> > order by i.TRANSACTION_ID
> >
> > The question is why ? If you look at the data in the single row
> returned...
> > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED 526123
> > 17408 1.0000 R
> > the record clearly contains the correct data. Any insight on this GREATLY
> > appreciated because we have a reporting services query based on a date
> range
> > which also has the same poor response time. If you put the same query into
> an
> > Oracle sql Plus query the data returns instantly.
> >
> > Thanks, Steve.
> >
> >
>
>|||Shared datasources are a RS thing, not a SQL Server thing. If what you are
doing is creating a query for the dataset in RS then you can use a shared
data source that the report is based on that goes to Oracle instead of to
SQL Server. If you have to use a stored procedure that resides in SQL Server
then you have to use OpenQuery AND you have to assemble the string to use
with it since you can not use a parameter with openquery. So, if you can get
away with either not using the stored procedure in SQL Server OR if you can
put the stored procedure in Oracle instead then that would be better. So
your options are:
1. Put the query in Report Services dataset and use a data source that goes
directly against Oracle
2. Use the SQL Server stored procedure with OpenQuery assembling the query
string (which gets to be a real pain with single quotes)
3. Use Oracle Stored predures.
Here is an example of some OpenQuery (I happen to be hip deep in this right
now).
select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
select @.SQL = 'insert collect_values select * from openquery(linktest,''' +
'SELECT * from collect_values where time_tag > ' + @.FROM + ' and time_tag <=' + @.TO + ''')'
execute (@.SQL)
Notice the wonderful messing with single quotes.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> Hi Bruce,
> Many thanks for your reply ! Absolutely correct on all
accounts
> (linked server, plan). Am now researching the "shared datasource" option.
Do
> you still reference it from your stored procedures ? That probably does
not
> change.
> Any tech article tips or links on this greatly appreciated. Steve.
> "Bruce L-C [MVP]" wrote:
> > You mention query analyzer and Oracle sql plus. OK, that makes me think
you
> > are using linked servers. Try the query plan with the query analyzer and
I
> > bet you find out that it is pulling all the data locally and then
applying
> > the where clause. You have several options. One is to use Openquery
instead
> > of the 4 part syntax. The other is to not use linked database. I only
use
> > linked databases as a last resort. I suggest using a shared datasource
in RS
> > to Oracle instead. For straight reporting I never use linked databases.
It
> > complicates matters and buys you very little. What I do is have a shared
> > datasource that has a readonly user credential stored by RS. This takes
> > advantage of connection pooling and will be either much faster or much
> > easier or both than using linked databases.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > Hi,
> > > Have the following sql analyzer query which takes 55 seconds to
return
> > > data.
> > > SELECT i.TRANSACTION_DATE,
> > > i.TYPE,
> > > i.PART_ID,
> > > i.WAREHOUSE_ID,
> > > i.TRANSACTION_ID,
> > > i.WORKORDER_BASE_ID,
> > > i.QTY,
> > > i.CLASS
> > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > order by i.TRANSACTION_ID
> > >
> > > Remove the and's and the response goes down to 1 second.
> > > SELECT i.TRANSACTION_DATE,
> > > i.TYPE,
> > > i.PART_ID,
> > > i.WAREHOUSE_ID,
> > > i.TRANSACTION_ID,
> > > i.WORKORDER_BASE_ID,
> > > i.QTY,
> > > i.CLASS
> > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > WHERE i.TRANSACTION_ID = 526123
> > > order by i.TRANSACTION_ID
> > >
> > > The question is why ? If you look at the data in the single row
> > returned...
> > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
526123
> > > 17408 1.0000 R
> > > the record clearly contains the correct data. Any insight on this
GREATLY
> > > appreciated because we have a reporting services query based on a date
> > range
> > > which also has the same poor response time. If you put the same query
into
> > an
> > > Oracle sql Plus query the data returns instantly.
> > >
> > > Thanks, Steve.
> > >
> > >
> >
> >
> >|||Would you use this approach if you were to transport data every night from
oracle to sql or
would you use some kind of bulk export instead ?
/Michael V.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> Shared datasources are a RS thing, not a SQL Server thing. If what you are
> doing is creating a query for the dataset in RS then you can use a shared
> data source that the report is based on that goes to Oracle instead of to
> SQL Server. If you have to use a stored procedure that resides in SQL
Server
> then you have to use OpenQuery AND you have to assemble the string to use
> with it since you can not use a parameter with openquery. So, if you can
get
> away with either not using the stored procedure in SQL Server OR if you
can
> put the stored procedure in Oracle instead then that would be better. So
> your options are:
> 1. Put the query in Report Services dataset and use a data source that
goes
> directly against Oracle
> 2. Use the SQL Server stored procedure with OpenQuery assembling the query
> string (which gets to be a real pain with single quotes)
> 3. Use Oracle Stored predures.
> Here is an example of some OpenQuery (I happen to be hip deep in this
right
> now).
> select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> select @.SQL = 'insert collect_values select * from openquery(linktest,'''
+
> 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and time_tag
<=> ' + @.TO + ''')'
> execute (@.SQL)
> Notice the wonderful messing with single quotes.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > Hi Bruce,
> > Many thanks for your reply ! Absolutely correct on all
> accounts
> > (linked server, plan). Am now researching the "shared datasource"
option.
> Do
> > you still reference it from your stored procedures ? That probably does
> not
> > change.
> > Any tech article tips or links on this greatly appreciated. Steve.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > You mention query analyzer and Oracle sql plus. OK, that makes me
think
> you
> > > are using linked servers. Try the query plan with the query analyzer
and
> I
> > > bet you find out that it is pulling all the data locally and then
> applying
> > > the where clause. You have several options. One is to use Openquery
> instead
> > > of the 4 part syntax. The other is to not use linked database. I only
> use
> > > linked databases as a last resort. I suggest using a shared datasource
> in RS
> > > to Oracle instead. For straight reporting I never use linked
databases.
> It
> > > complicates matters and buys you very little. What I do is have a
shared
> > > datasource that has a readonly user credential stored by RS. This
takes
> > > advantage of connection pooling and will be either much faster or much
> > > easier or both than using linked databases.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > Hi,
> > > > Have the following sql analyzer query which takes 55 seconds to
> return
> > > > data.
> > > > SELECT i.TRANSACTION_DATE,
> > > > i.TYPE,
> > > > i.PART_ID,
> > > > i.WAREHOUSE_ID,
> > > > i.TRANSACTION_ID,
> > > > i.WORKORDER_BASE_ID,
> > > > i.QTY,
> > > > i.CLASS
> > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > order by i.TRANSACTION_ID
> > > >
> > > > Remove the and's and the response goes down to 1 second.
> > > > SELECT i.TRANSACTION_DATE,
> > > > i.TYPE,
> > > > i.PART_ID,
> > > > i.WAREHOUSE_ID,
> > > > i.TRANSACTION_ID,
> > > > i.WORKORDER_BASE_ID,
> > > > i.QTY,
> > > > i.CLASS
> > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > WHERE i.TRANSACTION_ID = 526123
> > > > order by i.TRANSACTION_ID
> > > >
> > > > The question is why ? If you look at the data in the single row
> > > returned...
> > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> 526123
> > > > 17408 1.0000 R
> > > > the record clearly contains the correct data. Any insight on this
> GREATLY
> > > > appreciated because we have a reporting services query based on a
date
> > > range
> > > > which also has the same poor response time. If you put the same
query
> into
> > > an
> > > > Oracle sql Plus query the data returns instantly.
> > > >
> > > > Thanks, Steve.
> > > >
> > > >
> > >
> > >
> > >
>|||Hmmm, this has nothing to do with Reporting Services but that doesn't mean I
don't have an opinion <g>.
It depends on what you are doing and how much data you have plus how complex
is the extraction you are doing. You should look at DTS if you have
transformations you are doing. If all you are doing is pulling and loading
data again it depends on the quantity. It is very easy to do a quick test. I
just happen to be working on a datamart that I am keeping in sync every 5
minutes. There are some tables that I just do once a night and they are more
like bulk load. What I do is I have a database in SQL Server that is for
syncing. It has just the tables with no indexes, pk or anything, just the
columns. I use the openquery technique and insert the data into the table (I
add other error checking, for instance if the table already has data in it
then it means the process failed and I don't do anything). This comes over
very fast. Then I move the data from that table to the target table in the
other database (which is on the same server). I would do a test, you might
be surprised how fast this technique is.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> Would you use this approach if you were to transport data every night from
> oracle to sql or
> would you use some kind of bulk export instead ?
> /Michael V.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > Shared datasources are a RS thing, not a SQL Server thing. If what you
are
> > doing is creating a query for the dataset in RS then you can use a
shared
> > data source that the report is based on that goes to Oracle instead of
to
> > SQL Server. If you have to use a stored procedure that resides in SQL
> Server
> > then you have to use OpenQuery AND you have to assemble the string to
use
> > with it since you can not use a parameter with openquery. So, if you can
> get
> > away with either not using the stored procedure in SQL Server OR if you
> can
> > put the stored procedure in Oracle instead then that would be better. So
> > your options are:
> > 1. Put the query in Report Services dataset and use a data source that
> goes
> > directly against Oracle
> > 2. Use the SQL Server stored procedure with OpenQuery assembling the
query
> > string (which gets to be a real pain with single quotes)
> > 3. Use Oracle Stored predures.
> >
> > Here is an example of some OpenQuery (I happen to be hip deep in this
> right
> > now).
> >
> > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > select @.SQL = 'insert collect_values select * from
openquery(linktest,'''
> +
> > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
time_tag
> <=> > ' + @.TO + ''')'
> > execute (@.SQL)
> >
> > Notice the wonderful messing with single quotes.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > Hi Bruce,
> > > Many thanks for your reply ! Absolutely correct on all
> > accounts
> > > (linked server, plan). Am now researching the "shared datasource"
> option.
> > Do
> > > you still reference it from your stored procedures ? That probably
does
> > not
> > > change.
> > > Any tech article tips or links on this greatly appreciated. Steve.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > You mention query analyzer and Oracle sql plus. OK, that makes me
> think
> > you
> > > > are using linked servers. Try the query plan with the query analyzer
> and
> > I
> > > > bet you find out that it is pulling all the data locally and then
> > applying
> > > > the where clause. You have several options. One is to use Openquery
> > instead
> > > > of the 4 part syntax. The other is to not use linked database. I
only
> > use
> > > > linked databases as a last resort. I suggest using a shared
datasource
> > in RS
> > > > to Oracle instead. For straight reporting I never use linked
> databases.
> > It
> > > > complicates matters and buys you very little. What I do is have a
> shared
> > > > datasource that has a readonly user credential stored by RS. This
> takes
> > > > advantage of connection pooling and will be either much faster or
much
> > > > easier or both than using linked databases.
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > Hi,
> > > > > Have the following sql analyzer query which takes 55 seconds to
> > return
> > > > > data.
> > > > > SELECT i.TRANSACTION_DATE,
> > > > > i.TYPE,
> > > > > i.PART_ID,
> > > > > i.WAREHOUSE_ID,
> > > > > i.TRANSACTION_ID,
> > > > > i.WORKORDER_BASE_ID,
> > > > > i.QTY,
> > > > > i.CLASS
> > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > > order by i.TRANSACTION_ID
> > > > >
> > > > > Remove the and's and the response goes down to 1 second.
> > > > > SELECT i.TRANSACTION_DATE,
> > > > > i.TYPE,
> > > > > i.PART_ID,
> > > > > i.WAREHOUSE_ID,
> > > > > i.TRANSACTION_ID,
> > > > > i.WORKORDER_BASE_ID,
> > > > > i.QTY,
> > > > > i.CLASS
> > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > order by i.TRANSACTION_ID
> > > > >
> > > > > The question is why ? If you look at the data in the single row
> > > > returned...
> > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > 526123
> > > > > 17408 1.0000 R
> > > > > the record clearly contains the correct data. Any insight on this
> > GREATLY
> > > > > appreciated because we have a reporting services query based on a
> date
> > > > range
> > > > > which also has the same poor response time. If you put the same
> query
> > into
> > > > an
> > > > > Oracle sql Plus query the data returns instantly.
> > > > >
> > > > > Thanks, Steve.
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>|||Hi Bruce,
Thanks for your reply !!! Mission accomplished. Really just a
matter of selectinng the oracle driver (from oracle) when configuring the
linked server. Performance is instant ! Details are in Metalink doc
Note:191368.1.
Thanks again. Steve.
"Bruce L-C [MVP]" wrote:
> Hmmm, this has nothing to do with Reporting Services but that doesn't mean I
> don't have an opinion <g>.
> It depends on what you are doing and how much data you have plus how complex
> is the extraction you are doing. You should look at DTS if you have
> transformations you are doing. If all you are doing is pulling and loading
> data again it depends on the quantity. It is very easy to do a quick test. I
> just happen to be working on a datamart that I am keeping in sync every 5
> minutes. There are some tables that I just do once a night and they are more
> like bulk load. What I do is I have a database in SQL Server that is for
> syncing. It has just the tables with no indexes, pk or anything, just the
> columns. I use the openquery technique and insert the data into the table (I
> add other error checking, for instance if the table already has data in it
> then it means the process failed and I don't do anything). This comes over
> very fast. Then I move the data from that table to the target table in the
> other database (which is on the same server). I would do a test, you might
> be surprised how fast this technique is.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
> news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> > Would you use this approach if you were to transport data every night from
> > oracle to sql or
> > would you use some kind of bulk export instead ?
> >
> > /Michael V.
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > Shared datasources are a RS thing, not a SQL Server thing. If what you
> are
> > > doing is creating a query for the dataset in RS then you can use a
> shared
> > > data source that the report is based on that goes to Oracle instead of
> to
> > > SQL Server. If you have to use a stored procedure that resides in SQL
> > Server
> > > then you have to use OpenQuery AND you have to assemble the string to
> use
> > > with it since you can not use a parameter with openquery. So, if you can
> > get
> > > away with either not using the stored procedure in SQL Server OR if you
> > can
> > > put the stored procedure in Oracle instead then that would be better. So
> > > your options are:
> > > 1. Put the query in Report Services dataset and use a data source that
> > goes
> > > directly against Oracle
> > > 2. Use the SQL Server stored procedure with OpenQuery assembling the
> query
> > > string (which gets to be a real pain with single quotes)
> > > 3. Use Oracle Stored predures.
> > >
> > > Here is an example of some OpenQuery (I happen to be hip deep in this
> > right
> > > now).
> > >
> > > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > > select @.SQL = 'insert collect_values select * from
> openquery(linktest,'''
> > +
> > > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
> time_tag
> > <=> > > ' + @.TO + ''')'
> > > execute (@.SQL)
> > >
> > > Notice the wonderful messing with single quotes.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > > Hi Bruce,
> > > > Many thanks for your reply ! Absolutely correct on all
> > > accounts
> > > > (linked server, plan). Am now researching the "shared datasource"
> > option.
> > > Do
> > > > you still reference it from your stored procedures ? That probably
> does
> > > not
> > > > change.
> > > > Any tech article tips or links on this greatly appreciated. Steve.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > You mention query analyzer and Oracle sql plus. OK, that makes me
> > think
> > > you
> > > > > are using linked servers. Try the query plan with the query analyzer
> > and
> > > I
> > > > > bet you find out that it is pulling all the data locally and then
> > > applying
> > > > > the where clause. You have several options. One is to use Openquery
> > > instead
> > > > > of the 4 part syntax. The other is to not use linked database. I
> only
> > > use
> > > > > linked databases as a last resort. I suggest using a shared
> datasource
> > > in RS
> > > > > to Oracle instead. For straight reporting I never use linked
> > databases.
> > > It
> > > > > complicates matters and buys you very little. What I do is have a
> > shared
> > > > > datasource that has a readonly user credential stored by RS. This
> > takes
> > > > > advantage of connection pooling and will be either much faster or
> much
> > > > > easier or both than using linked databases.
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > > Hi,
> > > > > > Have the following sql analyzer query which takes 55 seconds to
> > > return
> > > > > > data.
> > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > i.TYPE,
> > > > > > i.PART_ID,
> > > > > > i.WAREHOUSE_ID,
> > > > > > i.TRANSACTION_ID,
> > > > > > i.WORKORDER_BASE_ID,
> > > > > > i.QTY,
> > > > > > i.CLASS
> > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE = 'I'
> > > > > > order by i.TRANSACTION_ID
> > > > > >
> > > > > > Remove the and's and the response goes down to 1 second.
> > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > i.TYPE,
> > > > > > i.PART_ID,
> > > > > > i.WAREHOUSE_ID,
> > > > > > i.TRANSACTION_ID,
> > > > > > i.WORKORDER_BASE_ID,
> > > > > > i.QTY,
> > > > > > i.CLASS
> > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > > order by i.TRANSACTION_ID
> > > > > >
> > > > > > The question is why ? If you look at the data in the single row
> > > > > returned...
> > > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > > 526123
> > > > > > 17408 1.0000 R
> > > > > > the record clearly contains the correct data. Any insight on this
> > > GREATLY
> > > > > > appreciated because we have a reporting services query based on a
> > date
> > > > > range
> > > > > > which also has the same poor response time. If you put the same
> > query
> > > into
> > > > > an
> > > > > > Oracle sql Plus query the data returns instantly.
> > > > > >
> > > > > > Thanks, Steve.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
>
>|||You will still need to be watchful if you use the 4 part naming. The
decision on what you send to Oracle and what to process locally is done by
SQL Server. Looking at the query plan in query analyzer will always show you
when this has happened.
In particular, the where clause can all of a sudden cause a the processing
to occur locally.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"saustin99" <saustin99@.discussions.microsoft.com> wrote in message
news:70A1E1CF-95A4-422B-9026-3837715525D0@.microsoft.com...
> Hi Bruce,
> Thanks for your reply !!! Mission accomplished. Really just
a
> matter of selectinng the oracle driver (from oracle) when configuring the
> linked server. Performance is instant ! Details are in Metalink doc
> Note:191368.1.
> Thanks again. Steve.
> "Bruce L-C [MVP]" wrote:
> > Hmmm, this has nothing to do with Reporting Services but that doesn't
mean I
> > don't have an opinion <g>.
> >
> > It depends on what you are doing and how much data you have plus how
complex
> > is the extraction you are doing. You should look at DTS if you have
> > transformations you are doing. If all you are doing is pulling and
loading
> > data again it depends on the quantity. It is very easy to do a quick
test. I
> > just happen to be working on a datamart that I am keeping in sync every
5
> > minutes. There are some tables that I just do once a night and they are
more
> > like bulk load. What I do is I have a database in SQL Server that is for
> > syncing. It has just the tables with no indexes, pk or anything, just
the
> > columns. I use the openquery technique and insert the data into the
table (I
> > add other error checking, for instance if the table already has data in
it
> > then it means the process failed and I don't do anything). This comes
over
> > very fast. Then I move the data from that table to the target table in
the
> > other database (which is on the same server). I would do a test, you
might
> > be surprised how fast this technique is.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Michael Vardinghus" <michaelvardinghus@.hotmail.com> wrote in message
> > news:e7T2QNR2EHA.304@.TK2MSFTNGP11.phx.gbl...
> > > Would you use this approach if you were to transport data every night
from
> > > oracle to sql or
> > > would you use some kind of bulk export instead ?
> > >
> > > /Michael V.
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > > news:efHSNuL2EHA.936@.TK2MSFTNGP12.phx.gbl...
> > > > Shared datasources are a RS thing, not a SQL Server thing. If what
you
> > are
> > > > doing is creating a query for the dataset in RS then you can use a
> > shared
> > > > data source that the report is based on that goes to Oracle instead
of
> > to
> > > > SQL Server. If you have to use a stored procedure that resides in
SQL
> > > Server
> > > > then you have to use OpenQuery AND you have to assemble the string
to
> > use
> > > > with it since you can not use a parameter with openquery. So, if you
can
> > > get
> > > > away with either not using the stored procedure in SQL Server OR if
you
> > > can
> > > > put the stored procedure in Oracle instead then that would be
better. So
> > > > your options are:
> > > > 1. Put the query in Report Services dataset and use a data source
that
> > > goes
> > > > directly against Oracle
> > > > 2. Use the SQL Server stored procedure with OpenQuery assembling the
> > query
> > > > string (which gets to be a real pain with single quotes)
> > > > 3. Use Oracle Stored predures.
> > > >
> > > > Here is an example of some OpenQuery (I happen to be hip deep in
this
> > > right
> > > > now).
> > > >
> > > > select @.FROM = ''' + convert(varchar(30),@.UPDATEFROM,9) + '''
> > > > select @.TO = ''' + convert(varchar(30),@.UPDATETO,9) + '''
> > > > select @.SQL = 'insert collect_values select * from
> > openquery(linktest,'''
> > > +
> > > > 'SELECT * from collect_values where time_tag > ' + @.FROM + ' and
> > time_tag
> > > <=> > > > ' + @.TO + ''')'
> > > > execute (@.SQL)
> > > >
> > > > Notice the wonderful messing with single quotes.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in message
> > > > news:DFDD294D-F8F7-4445-817E-C59C012FB32B@.microsoft.com...
> > > > > Hi Bruce,
> > > > > Many thanks for your reply ! Absolutely correct on
all
> > > > accounts
> > > > > (linked server, plan). Am now researching the "shared datasource"
> > > option.
> > > > Do
> > > > > you still reference it from your stored procedures ? That probably
> > does
> > > > not
> > > > > change.
> > > > > Any tech article tips or links on this greatly appreciated. Steve.
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > You mention query analyzer and Oracle sql plus. OK, that makes
me
> > > think
> > > > you
> > > > > > are using linked servers. Try the query plan with the query
analyzer
> > > and
> > > > I
> > > > > > bet you find out that it is pulling all the data locally and
then
> > > > applying
> > > > > > the where clause. You have several options. One is to use
Openquery
> > > > instead
> > > > > > of the 4 part syntax. The other is to not use linked database. I
> > only
> > > > use
> > > > > > linked databases as a last resort. I suggest using a shared
> > datasource
> > > > in RS
> > > > > > to Oracle instead. For straight reporting I never use linked
> > > databases.
> > > > It
> > > > > > complicates matters and buys you very little. What I do is have
a
> > > shared
> > > > > > datasource that has a readonly user credential stored by RS.
This
> > > takes
> > > > > > advantage of connection pooling and will be either much faster
or
> > much
> > > > > > easier or both than using linked databases.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "saustin99" <saustin99@.discussions.microsoft.com> wrote in
message
> > > > > > news:A948FA70-BB51-401E-B7B5-442340F37F16@.microsoft.com...
> > > > > > > Hi,
> > > > > > > Have the following sql analyzer query which takes 55
seconds to
> > > > return
> > > > > > > data.
> > > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > > i.TYPE,
> > > > > > > i.PART_ID,
> > > > > > > i.WAREHOUSE_ID,
> > > > > > > i.TRANSACTION_ID,
> > > > > > > i.WORKORDER_BASE_ID,
> > > > > > > i.QTY,
> > > > > > > i.CLASS
> > > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > > WHERE i.TRANSACTION_ID = 526123 and i.CLASS = 'R' and i.TYPE ='I'
> > > > > > > order by i.TRANSACTION_ID
> > > > > > >
> > > > > > > Remove the and's and the response goes down to 1 second.
> > > > > > > SELECT i.TRANSACTION_DATE,
> > > > > > > i.TYPE,
> > > > > > > i.PART_ID,
> > > > > > > i.WAREHOUSE_ID,
> > > > > > > i.TRANSACTION_ID,
> > > > > > > i.WORKORDER_BASE_ID,
> > > > > > > i.QTY,
> > > > > > > i.CLASS
> > > > > > > FROM VMFG..SYSADM.INVENTORY_TRANS i
> > > > > > > WHERE i.TRANSACTION_ID = 526123
> > > > > > > order by i.TRANSACTION_ID
> > > > > > >
> > > > > > > The question is why ? If you look at the data in the single
row
> > > > > > returned...
> > > > > > > 2004-10-22 00:00:00.000 I 76307-03005-061 QA ACCEPTED
> > > > 526123
> > > > > > > 17408 1.0000 R
> > > > > > > the record clearly contains the correct data. Any insight on
this
> > > > GREATLY
> > > > > > > appreciated because we have a reporting services query based
on a
> > > date
> > > > > > range
> > > > > > > which also has the same poor response time. If you put the
same
> > > query
> > > > into
> > > > > > an
> > > > > > > Oracle sql Plus query the data returns instantly.
> > > > > > >
> > > > > > > Thanks, Steve.
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> >

Oracle provider error

I am using SQL Server 2005 on a 64 bit AMD processor machine using the Oracle 10.0.2.0.2.0 and SELECT OPENQUERY will return results from my Oracle table. When I try to do an UPDATE OPENQUERY I get this error message

Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB".

the same UPDATE OPENQUERY works on SQL Server 2000 using the Oracle 9 client. The provider is configured with Inprocess turned on. Any help would be appreciated.

Hi,

I once has a problem that I had to give a result back from my stored procedure in oracle although I didn#t actually need to return anything. It was just needed for the provider to do some mapping to "a" resultset. perhps you should try that if you are only about to execute a stored procedure with no return values at all.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

From the description of the problem it sounds as the Provider is having a problem fetching a row from the Oracle DBMS specified by the SQL engine by a bookmark. If it worked with Oracle 9/SQL 2k, the cause could be either the SQL engine requesting the row from OLEDB in a different manner or an issue within the OraOLEDB provider. Using MSDAORA instead of OraOLEDB would determine where the issue resides.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

|||I had the same problem on my x64 box and resolved it (mostly) by installing the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.

Oracle provider error

Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB".

the same UPDATE OPENQUERY works on SQL Server 2000 using the Oracle 9 client. The provider is configured with Inprocess turned on. Any help would be appreciated.

Hi,

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

Oracle provider error

Msg 7333, Level 16, State 2, Line 1
Cannot fetch a row using a bookmark from OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB".

the same UPDATE OPENQUERY works on SQL Server 2000 using the Oracle 9 client. The provider is configured with Inprocess turned on. Any help would be appreciated.

Hi,

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Is the behavior the same when you use MSDAORA? Have you checked Oracle's knowledge base? It's also a good idea to install the latest patches for the Oracle client.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hello,

We have the same problem here. I don't have any answer yet...

For info, this is for the 64 bit server. With 32 bit and msdaora everything works perfectly...

Where is the native oracle driver for sqlserver 64 bit entreprise version as said in the doc/promo/site ?

Should we downgrade to 32 bit because the 64 bit can't even work with oracle ?

I hope to find an answer soon...

greetings

Monday, February 20, 2012

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

Monday, March 26, 2012

order by issue

order by issue

Order By ignoring the hyphen character

Order By ignoring the hyphen character

Friday, March 23, 2012

Order by clause help

Order by clause help

Order by clause help

Order By Case Confusion

Wednesday, March 21, 2012

ORDER BY CASe

ORDER BY CASe

Friday, March 9, 2012

Oracle query is SLOW, why ?

Oracle provider error

Oracle provider error

Oracle provider error

Monday, February 20, 2012

Oracle Acquire Connection for ssis return null

Oracle DBA to learn MS SQL

Blog Archive

About Me