Is it possible to parametrise the 'order by' options of a table/group so
that a single report could be ordered by users on run time depending of
their needs?
The alternative is to write/copy the same report once and again and have it
repeated (one instance) for each orderable column.
Regards.Sorry for this post. I just found it on RS' BOL.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:eXv4RWQXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Is it possible to parametrise the 'order by' options of a table/group so
> that a single report could be ordered by users on run time depending of
> their needs?
> The alternative is to write/copy the same report once and again and have
> it repeated (one instance) for each orderable column.
> Regards.
>
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
ORDER BY issue.
Hi All,
If I use ORDER BY in Union query then it take lot of time .
My query looks like.
Select x,y,z
FROM(
SELECT x,y,z
FROM tt,yy,zz
UNION
SELECT x,y,z
FROM tt1,yy1,zz1
) A
WHERE a.x > '03/03/2004'
order by x
Union query return morethan 200000 records.
It's take lot of time around 20 sec if I removerd it then takes 2 sec.
I can put middle(union) part of query in view but I can put ORDER BY in query but I have to use TOP n.
Can I put any index on column in view or else.
Please suggest me asap.one trick to use top n in a view is to use top 100 percent
create view v_crap
as
select top 100 percent *
from t1
order by c2
i know this doesnt solve your main question but it could allow you to create a view until you can tune correctly.|||First, I find it hard to believe that you can retreive 200,000 rows in 2 seconds...
Secons, Do you have an Index on x,y,z?
Third, Try this (make sure you have an index)
SELECT x,y,z
FROM (SELECT TOP 2 x,y,z
FROM tt,yy,zz
WHERE x > '03/03/2004'
ORDER BY x)
UNION
SELECT x,y,z
FROM (SELECT TOP 2 x,y,z
FROM tt1,yy1,zz1
WHERE x > '03/03/2004'
ORDER BY x) A
ORDER BY x
If I use ORDER BY in Union query then it take lot of time .
My query looks like.
Select x,y,z
FROM(
SELECT x,y,z
FROM tt,yy,zz
UNION
SELECT x,y,z
FROM tt1,yy1,zz1
) A
WHERE a.x > '03/03/2004'
order by x
Union query return morethan 200000 records.
It's take lot of time around 20 sec if I removerd it then takes 2 sec.
I can put middle(union) part of query in view but I can put ORDER BY in query but I have to use TOP n.
Can I put any index on column in view or else.
Please suggest me asap.one trick to use top n in a view is to use top 100 percent
create view v_crap
as
select top 100 percent *
from t1
order by c2
i know this doesnt solve your main question but it could allow you to create a view until you can tune correctly.|||First, I find it hard to believe that you can retreive 200,000 rows in 2 seconds...
Secons, Do you have an Index on x,y,z?
Third, Try this (make sure you have an index)
SELECT x,y,z
FROM (SELECT TOP 2 x,y,z
FROM tt,yy,zz
WHERE x > '03/03/2004'
ORDER BY x)
UNION
SELECT x,y,z
FROM (SELECT TOP 2 x,y,z
FROM tt1,yy1,zz1
WHERE x > '03/03/2004'
ORDER BY x) A
ORDER BY x
Friday, March 23, 2012
ORDER BY decreases performance by 40x?
I am astounded. I haven't read any where that adding a sort order to a query
would drastically increase read time for the same query.
This query performed on a table with 360,000 records:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
ORDER BY [SEARCHKEY] DESC
Takes 40 seconds! While:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
Takes 1 second. Wow.
For now we will query the server without the ORDER BY and do a sort in the
Client application.
It seems like adding an ORDER BY on a large table or view increases the read
time by an order of magnitude.
Removing DESC speeds up the query somewhat.
Any thoughts? Thanks in advance...Hi John
Do you have an index on ID?
What happens if you ORDER by the table column, rather than the column alias,
which is an expression ?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
>|||"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
Take off the rtrim(), and is your ID column indexed in both directions? I
know that more ofther your index is ascending and we always want the max
value on top. So set your index to be descending instead.
HTH
__Stephen|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
Is there an index on SEARCHKEY? Have you looked at the execution plan to
make sure the index is being used?
The "TOP 1000 " causes the query to stop after 1000 rows are retrieved.
Using the ORDER BY forces the entire 360000 rows to be sorted before getting
the top 1000, This can be time consuming, especially when there is no index
to be used.
Your plan to to the sorting in the client has a drawback: you will have
different results doing it that way. Look at this set or data:
36
55
79
28
44
If you take the top 2 and then sort them in descending order, you get
55
36
If you sort them first and then take the top 2, you get
79
55
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
>
Oh, my bad, I did not notice you were sorting on the result of the
calculation. This can really slow things down as it prevents an index from
being used. See the difference if you take Kalen's advice and ORDER BY ID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for the responses guys...
All of them have merit. Bob, you hit the nail on the head when you reminded
me that the ORDER BY is performed on all 360,000 records before the TOP
1000 are selected. I feel like a real dope. Doing an ORDER BY on ID (which
is Indexed ascending) cut the time in half. I can see how Indexing
Descending will help even more.
What really fixed it for us is that we have an indexed INV_DATE field in the
view.
So I tried using
WHERE INV_DATE > DATEADD(DAY,-30,GETDATE()) and got it down to 2 seconds.
This gives me the last 30 days worth of invoices which will work in most
cases for a Browse. We supply other methods in the Client app to get to
specific records like exact Inv#, Date Range, etc in case the needed record
is not in the last 30 days.
Adding the WHERE clause immediately reduced the number of records that
needed to be sorted.
Once again, thank you all for your speedy replies.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:eDDoaKL4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> John Kotuby wrote:
> Oh, my bad, I did not notice you were sorting on the result of the
> calculation. This can really slow things down as it prevents an index from
> being used. See the difference if you take Kalen's advice and ORDER BY ID
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
would drastically increase read time for the same query.
This query performed on a table with 360,000 records:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
ORDER BY [SEARCHKEY] DESC
Takes 40 seconds! While:
SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
FROM [PLVWDIV_INV_SHORT]
Takes 1 second. Wow.
For now we will query the server without the ORDER BY and do a sort in the
Client application.
It seems like adding an ORDER BY on a large table or view increases the read
time by an order of magnitude.
Removing DESC speeds up the query somewhat.
Any thoughts? Thanks in advance...Hi John
Do you have an index on ID?
What happens if you ORDER by the table column, rather than the column alias,
which is an expression ?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
>|||"John Kotuby" <johnk@.powerlist.com> wrote in message
news:ux1ET$K4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I am astounded. I haven't read any where that adding a sort order to a
>query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort in the
> Client application.
> It seems like adding an ORDER BY on a large table or view increases the
> read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
Take off the rtrim(), and is your ID column indexed in both directions? I
know that more ofther your index is ascending and we always want the max
value on top. So set your index to be descending instead.
HTH
__Stephen|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
> Any thoughts? Thanks in advance...
Is there an index on SEARCHKEY? Have you looked at the execution plan to
make sure the index is being used?
The "TOP 1000 " causes the query to stop after 1000 rows are retrieved.
Using the ORDER BY forces the entire 360000 rows to be sorted before getting
the top 1000, This can be time consuming, especially when there is no index
to be used.
Your plan to to the sorting in the client has a drawback: you will have
different results doing it that way. Look at this set or data:
36
55
79
28
44
If you take the top 2 and then sort them in descending order, you get
55
36
If you sort them first and then take the top 2, you get
79
55
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||John Kotuby wrote:
> I am astounded. I haven't read any where that adding a sort order to
> a query would drastically increase read time for the same query.
> This query performed on a table with 360,000 records:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> ORDER BY [SEARCHKEY] DESC
> Takes 40 seconds! While:
> SELECT TOP 1000 RTRIM(ID) AS [SEARCHKEY], *
> FROM [PLVWDIV_INV_SHORT]
> Takes 1 second. Wow.
> For now we will query the server without the ORDER BY and do a sort
> in the Client application.
> It seems like adding an ORDER BY on a large table or view increases
> the read time by an order of magnitude.
> Removing DESC speeds up the query somewhat.
>
Oh, my bad, I did not notice you were sorting on the result of the
calculation. This can really slow things down as it prevents an index from
being used. See the difference if you take Kalen's advice and ORDER BY ID
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks for the responses guys...
All of them have merit. Bob, you hit the nail on the head when you reminded
me that the ORDER BY is performed on all 360,000 records before the TOP
1000 are selected. I feel like a real dope. Doing an ORDER BY on ID (which
is Indexed ascending) cut the time in half. I can see how Indexing
Descending will help even more.
What really fixed it for us is that we have an indexed INV_DATE field in the
view.
So I tried using
WHERE INV_DATE > DATEADD(DAY,-30,GETDATE()) and got it down to 2 seconds.
This gives me the last 30 days worth of invoices which will work in most
cases for a Browse. We supply other methods in the Client app to get to
specific records like exact Inv#, Date Range, etc in case the needed record
is not in the last 30 days.
Adding the WHERE clause immediately reduced the number of records that
needed to be sorted.
Once again, thank you all for your speedy replies.
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:eDDoaKL4FHA.4076@.TK2MSFTNGP15.phx.gbl...
> John Kotuby wrote:
> Oh, my bad, I did not notice you were sorting on the result of the
> calculation. This can really slow things down as it prevents an index from
> being used. See the difference if you take Kalen's advice and ORDER BY ID
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
Order By date and time of insert
I just wanted to know is there any way to order the rows of table based on date and time of insert. I dont have date column in my table. I have to insert values into the table. Based on the system date and time of the values inserted into the table, can i order the rows??As you have posted a question in the articles section it is being moved to SQL Server Forum .
MODERAOTR.|||for that you need to add a new column to the table and store system date and time in that column for every new insert and finally sort the data by that column.
Friday, March 9, 2012
Oracle OleDb Provider as Source
Guys,
I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.
The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
After this happens, if I go into an OLE DB Source within a DFT, I get the following:
No disconnected record set is available for the specified SQL statement.
Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:
1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.
2. Hardcode the connection string in the dtsx XML-behind.
3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.
4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).
5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.
Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.
Thanks,
Rick
Update:
If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:
Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.
Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.
Error at DFT_LoadDimEntities: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Any suggestions would be greatly appreciated.
Thank you,
Rick
|||This turned out to be a combination of quirks related to Oracle tooling and syntax.
I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.
I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):
1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.
2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.
3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.
If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx
If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.
Rick
Oracle OleDb Provider as Source
Guys,
I am having a nightmarish time getting an Oracle Connection Manager working as a source in my SSIS package.
The CM is called "OLTP_SOURCE". When I inspect the configuration and test connection, it succeeds, however when I go to run the package (both in debug mode and via DTEXECUI) I get the following error:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
After this happens, if I go into an OLE DB Source within a DFT, I get the following:
No disconnected record set is available for the specified SQL statement.
Now, if I go back into the CM, enter the password and test, it succeeds. From this point, I will go to preview the data in the OLE DB Source, and it comes back fine. However, when I go to run the package, I get the same error time and time again:
The AcquireConnection method call to the connection manager "OLTP_SOURCE" failed with error code 0xC0202009
The quick reader will suggest that the password is not being persisted. To this end, I have tried each of the following techniques to no avail:
1. Double, Triple and Quadruple check that the "save" password option in the CM is checked.
2. Hardcode the connection string in the dtsx XML-behind.
3. Enable Package Configurations and hardcode the connection string in the dstsconfig file.
4. Run the dtsx file using DTEXECUI, providing it with the configuration (that includes the hard-coded password).
5. Run the dtsx file using DTEXECUI, providing it the connection string in the Connection Managager override UI.
Can anyone help shed some light on what might be going on? So far, it is obvious that there has to be something that I am doing wrong because (syntax dialect differences aside) I can't imagine that Oracle sources should be this much of a headache.
Thanks,
Rick
Update:
If I ignore this anamoly and try to step into debug mode, I get the following error within the IDE:
Error at DFT_LoadDimEntities [DTS.Pipeline]: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.
Error at DFT_LoadDimEntities [DTS.Pipeline]: The layout failed validation.
Error at DFT_LoadDimEntities: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Any suggestions would be greatly appreciated.
Thank you,
Rick
|||This turned out to be a combination of quirks related to Oracle tooling and syntax.
I have learned that integrating Oracle is a multi-faceted project. The first phase is syntax normalization and dealing with the tooling anamolies that come up. The second phase is getting to true interop, where I can flip flop providers on source and target and have the same package, same code base just work.
I think I am just about done with phase 1 stuff, and here are some things to consider if you run into similar problems (many of these sound rediculously trivial, but when something just works against SQL and stops working against Oracle, it can be madenning):
1. Make sure that you are saving the connection string information during design time. The only way to do this is to select one of the "Encrypt..." options. Whereas SQL source/target Connection Managers seem to hapilly retain connection info (i.e. Windows Auth, makes sense), Oracle credentials in the connection string will not get saved. I have found that this creates a number of un-intuitive error messages.
2. Check your syntax. Fire up Oracle SQL Worksheet and test your code to ensure it is interoperable.
3. Just because your code runs in both SQL and Oracle doesn't preclude strange "tooling" issues as I can them from cropping up. For example, in an OLE DB Source task, the MSDAORA provider does not seem to like comments (-- Blah) as the first line. This one drove me nuts for a good couple of days.
If I think of anything else, I'll post it, but I think that most of these problems have been addressed here and on the following blog posting which may prove helpful to others: http://rickgaribay.net/archive/2007/03/15/font-facearialstrikeadventuresstrikefont-contortions--with-ssis-oracle-interop.aspx
If anyone has specific questions, feel free to post or contact me with questions- I'd be happy to share any knowledge I've gleaned along the way.
Rick
Oracle OLEDB and dates not matching
I have a ssas2005 cube built from data in an oracle data warehouse. The Time dimension has begin week dates as a key and joins to the fact table Week Begin Date field. Both are built in the dsv using named queries. The cube built fine until we changed the provider to the Oracle provider for OLEDB. Now the cube build is giving an error : "The attribute key cannot be found: Table: Fact_x0020_Service_x0020_Level, Column: WEEK_BEGIN_DATE, Value: 7/3/2005." on the first record. The dates look the same and the properties look the same. I tried converting the dates to character in the queries and the cube builds - but I have a lot of data missing. We changed the provider because of internal rounding problems throwing the numbers off. Why is this happening? Is there a way to fix this?
The answer is trivial but situation overall quite confusing.
The implementation of OLEDB providers to the same relational datbase varies quite a lot. The changes in functionality happen from one version of OLEDB provider to another, the differences are even greater between OLEDB providers implemented by different companies.
In short: You could think that changing OLEDB provider in the conneciton string is a trivial, but in the reality it is not so at all. If this is absolutely neccessary that you use another OLEDB provider, you should work through every processing error.
Hope that helps
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
|||I've been able to resolve all the problems but the date. It seems the issue is in the internal representation of the date between the .Net provider and SSAS - and there's no way i know of to fix the problem without converting the date to some other format - which causes other problems down the line with my date functions. Anybody have an idea on getting around this?
Oracle OLEDB and dates not matching
I have a ssas2005 cube built from data in an oracle data warehouse. The Time dimension has begin week dates as a key and joins to the fact table Week Begin Date field. Both are built in the dsv using named queries. The cube built fine until we changed the provider to the Oracle provider for OLEDB. Now the cube build is giving an error : "The attribute key cannot be found: Table: Fact_x0020_Service_x0020_Level, Column: WEEK_BEGIN_DATE, Value: 7/3/2005." on the first record. The dates look the same and the properties look the same. I tried converting the dates to character in the queries and the cube builds - but I have a lot of data missing. We changed the provider because of internal rounding problems throwing the numbers off. Why is this happening? Is there a way to fix this?
The answer is trivial but situation overall quite confusing.
The implementation of OLEDB providers to the same relational datbase varies quite a lot. The changes in functionality happen from one version of OLEDB provider to another, the differences are even greater between OLEDB providers implemented by different companies.
In short: You could think that changing OLEDB provider in the conneciton string is a trivial, but in the reality it is not so at all. If this is absolutely neccessary that you use another OLEDB provider, you should work through every processing error.
Hope that helps
Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Saturday, February 25, 2012
ORACLE Linked DB
I'm playing around with Linked Servers in SQL Server 2K and when linking to an Oracle Database I'm having very slow response time. If I just query the whole table with out a WHERE statement, it seems to go at a decent rate. If I include any parameters whatsoever, it goes horribly slow. I have used the four part name scenraio and the openquery scenerio. Also I get Date errors occasionally which could probably be corrected if I change the format of the date. Any ideas why the linked server acts so slow? If I use a DTS extract I can query the database any way I want and it runs fine. But I cannot do this because so much data changes in these particular tables in the Oracle DB that I need a live connection.1) ------------------
For your date problem, I sympathize !
I've got about the same date problem (format is not the same in both of my Databases)
I've decided to use varchar(26) types in my SQL Server db.
You should consult my threads fore more info on the dates
maybe it will give ideas !
2) ------------------
For the perfs problem maybe it's due to :
- no indexes on the "where columns"
- too much transformations to do on all the date columns
- not using pre-compiled queries
You should post your query so we can see it
For your date problem, I sympathize !
I've got about the same date problem (format is not the same in both of my Databases)
I've decided to use varchar(26) types in my SQL Server db.
You should consult my threads fore more info on the dates
maybe it will give ideas !
2) ------------------
For the perfs problem maybe it's due to :
- no indexes on the "where columns"
- too much transformations to do on all the date columns
- not using pre-compiled queries
You should post your query so we can see it
Oracle connection information in SSIS package.
Hi,
I want to make a SSIS package with Oracle and deploy it in no of oracle databases, for it every time I have to open package and change connection information.
How can I make oracle connection information as variable value so that when I deploy my package on Oracle database it will pick all oracle connection information(User Id, Pwd, Server Name) automatically.
Please let me know about this.
Thanks
Hi Anurag,
Experts / MVPs have already addressed this issues about dynamically assigning the Connection details. Follow the steps below
1) Create a table in one of your Oracle database with following fields:
ConnectionDetails(UserID, PWD, ServerName)
2) Insert details of all servers you want to deploy
3) Open the New SSIS Package, Drag and drop an execute sql task, write the query (Select * from ConnectionDetails) to retrieve Conectiondetails into a ResultSet Variable say User::ResultSet of an Object type.
4) Drag and drop the For Each Loop container after the Execute SQL Task and configure details to retrieve each row
5) Store output column values in Package Variables say "v_UserID", "v_passwd" , "v_serverName"
6) Drag and Drop a DataFlow Task into the ForEach Loop Container, determine the source, destination and transformation mappings required.
7) Now assign these variables to connection manager whose detail should change dynamically via expression builder Say
ServerName = @.[User::v_serverName]
UserName=@.[User::v_UserID]
Thanks
Subhash Subramanyam
|||Hi Subash,
Slew of thanks for speedy reply.
I need a little bit change in first 3 steps, instead of making a table in oracle database, i want to keep oracle server and service name information in Text file and reading from this file.
Thanks
|||If you want to read from a file, Instead of first two steps you can place a script task that can read the data from the text file to populate a resultset variable which you can use in for each loop.
Monday, February 20, 2012
Oracle and Analysis Services
We have a world wide ERP built on top of Oracle and .net.
We have no way to change Oracle for SQL Server at this time.
But, we would like to use the Excel OLAP features.
From what we've seen, the OLAP features in Excel only work on top of SQL Server's Analysis Services.
Is this true?
Could we have Oracle and just Analysis Services installed?
What would be the cost of such a solution (could you point a price table)?
Thanks a lot.
Analysis Services is fully supported to build cubes based on the data in Oracle relatonal database.
Analysis Services is integral part of SQL Server product offering, you will need to obtain full licence for SQL Server product to run Analysis Services.
You are not required to install SQL Server relational engine on your machine. Installation of Analysis Serivces is self contained.
Pricing of the Analysis Services is standard pricing for buying a SQL Server licence: http://www.microsoft.com/sql/howtobuy/default.mspx
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Oracle 9i Drivers for Link Server?
Hi All,
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>
Oracle 9i Drivers for Link Server?
Hi All,
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.
http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.
http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>
Oracle 9i Drivers for Link Server?
Hi All,
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>
I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
database to get at stored procs. I am having a devil of a time trying to
find the correct Oracle drivers to place on my SQL Server. Can anyone
direct me to the correct drivers please.
Thanks,
John.http://www.oracle.com/technology/software/index.html
"John" <jrugo@.patmedia.net> wrote in message
news:eQVSq%23NAGHA.360@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I have a SQL Server 2000 and need to create a Link Server to an Oracle 9i
> database to get at stored procs. I am having a devil of a time trying to
> find the correct Oracle drivers to place on my SQL Server. Can anyone
> direct me to the correct drivers please.
> Thanks,
> John.
>
Subscribe to:
Posts (Atom)