An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning
and such, is to either remove the item from inventory when it is placed in
the cart and set it to "promised" - not fully considering it sold until the
entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventor
y by the number of items that are sitting in abandoned carts on your site -
at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational t
o the customer and you don't attempt to keep that absolutely sync'd with you
r real unpromised inventory. I've seen sites where they do a final availabil
ity check at purchase time
(which is often on a separate server from the browsing server) and tell you
then that the item is no longer available. I think that is a pretty bad cust
omer experience, but you will need to strike the balance with your other bus
iness needs.
There are other techniques for dealing with available to promise, but they t
end to be more loosely coupled and that doesn't fit well when your inventory
per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order[vbcol=seagreen]
> out
> Any
>
Showing posts with label store. Show all posts
Showing posts with label store. Show all posts
Friday, March 30, 2012
Order Entry System
An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
Two things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>
|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time
(which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs.
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.
|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.[vbcol=seagreen]
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order
> out
> Any
>
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
Two things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>
|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time
(which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs.
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.
|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.[vbcol=seagreen]
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order
> out
> Any
>
Order Entry System
An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete - credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time (which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > An online shopping store with a lot of products... Unfortunately the
> number
> > of products to be sold are limited.. kind of in an inventory...
> >
> > I could have millions of customers logging on someday ... How can I
scale
> > this for concurrency and also ensure that data is consistent and i do
not
> > oversell the products that are not in the inventory..thats assuming i
show
> > all the customers all the products available and everyone decides to
order
> > it which i would not be able to control. I can imagine a product catalog
> > going thru some sort of a replication architecture for concurrency/scale
> out
> > purposes as data is static. How does one do it for an inventory system..
> Any
> > suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
> >
> >
>
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete - credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time (which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > An online shopping store with a lot of products... Unfortunately the
> number
> > of products to be sold are limited.. kind of in an inventory...
> >
> > I could have millions of customers logging on someday ... How can I
scale
> > this for concurrency and also ensure that data is consistent and i do
not
> > oversell the products that are not in the inventory..thats assuming i
show
> > all the customers all the products available and everyone decides to
order
> > it which i would not be able to control. I can imagine a product catalog
> > going thru some sort of a replication architecture for concurrency/scale
> out
> > purposes as data is static. How does one do it for an inventory system..
> Any
> > suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
> >
> >
>
Monday, March 12, 2012
Oracle RowID in SQLServer?
Hi,
In Oracle we have a datatype called 'ROWID' - Oracle uses this datatype to store the address (rowid) of every row in the database. Do we have any equivalent datatype in SQLServer similar to this ?
Regards,
SnNo. At least not in SQL Server 2000.
Data in a relational database has no inherent order. That said, I think that this functionality has been implemented in 2005 anyway.
There are alternative ways of achieving the same effect in SQL Server, depending upon what you want to use the row number for. Are you doing paging?|||That said, I think that this functionality has been implemented in 2005 anyway.
Say it ain't SO!!!!
I can't believe Uncle Bill is caving in to the Oracle crowd (though I grew up on Tandem SQL, and it had the concept of row numbers also).
That is just SO wrong.
Jeeze. I'm going to retire early and head to my cabin in the deep, dark Idaho forest with my Commodore 64.|||yes, in 2005 there is RANK(), DENSE_RANK(), and ROW_NUMBER() which all do similar things:
http://msdn2.microsoft.com/en-us/library/ms186734.aspx|||OK, so listen up all y'all...
let's all agree we won't use such constructs even though they are available, shall we? ;)
Kinda similar to urban assault weapons...just because they exist, doesn't make it right to use them ;)|||They can have my row_number when they pry it from my cold dead data.|||Well, there is a crucial difference. The row_number of a row in SQL 2005 can change, but the rownum in oracle can not, as it is stored with the data itself. That should keep the Oracle folks on their toes for a considerable while.|||I have a guilty secret....
I have been using row_number for a little while now to satisfy TOP x BY GROUP queries for tables with wide composite keys.
Does that mean I can't join your gang? :o|||I have a guilty secret....
I have been using row_number for a little while now to satisfy TOP x BY GROUP queries for tables with wide composite keys.
Does that mean I can't join your gang? :oNot necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.|||Well, there is a crucial difference. The row_number of a row in SQL 2005 can change, but the rownum in oracle can not, as it is stored with the data itself. Yes - it is determined by its own little order by clause so it isn't totally naughty. I'm not really terribly up on these things but I think it is in the SQL99 standard... maybe.|||Not necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.Yay! And there was me thinking there might be a catch.|||Not necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.
Nobody told me I was allowed to take this damn thing off!
In Oracle we have a datatype called 'ROWID' - Oracle uses this datatype to store the address (rowid) of every row in the database. Do we have any equivalent datatype in SQLServer similar to this ?
Regards,
SnNo. At least not in SQL Server 2000.
Data in a relational database has no inherent order. That said, I think that this functionality has been implemented in 2005 anyway.
There are alternative ways of achieving the same effect in SQL Server, depending upon what you want to use the row number for. Are you doing paging?|||That said, I think that this functionality has been implemented in 2005 anyway.
Say it ain't SO!!!!
I can't believe Uncle Bill is caving in to the Oracle crowd (though I grew up on Tandem SQL, and it had the concept of row numbers also).
That is just SO wrong.
Jeeze. I'm going to retire early and head to my cabin in the deep, dark Idaho forest with my Commodore 64.|||yes, in 2005 there is RANK(), DENSE_RANK(), and ROW_NUMBER() which all do similar things:
http://msdn2.microsoft.com/en-us/library/ms186734.aspx|||OK, so listen up all y'all...
let's all agree we won't use such constructs even though they are available, shall we? ;)
Kinda similar to urban assault weapons...just because they exist, doesn't make it right to use them ;)|||They can have my row_number when they pry it from my cold dead data.|||Well, there is a crucial difference. The row_number of a row in SQL 2005 can change, but the rownum in oracle can not, as it is stored with the data itself. That should keep the Oracle folks on their toes for a considerable while.|||I have a guilty secret....
I have been using row_number for a little while now to satisfy TOP x BY GROUP queries for tables with wide composite keys.
Does that mean I can't join your gang? :o|||I have a guilty secret....
I have been using row_number for a little while now to satisfy TOP x BY GROUP queries for tables with wide composite keys.
Does that mean I can't join your gang? :oNot necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.|||Well, there is a crucial difference. The row_number of a row in SQL 2005 can change, but the rownum in oracle can not, as it is stored with the data itself. Yes - it is determined by its own little order by clause so it isn't totally naughty. I'm not really terribly up on these things but I think it is in the SQL99 standard... maybe.|||Not necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.Yay! And there was me thinking there might be a catch.|||Not necessarily, but you would definitely need to wear the Ceremonial Pink Tutu a lot longer than the rest of us did.
Nobody told me I was allowed to take this damn thing off!
Saturday, February 25, 2012
Oracle Connection String Stored in SQL Server Configuration Not Working
I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages
(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).
Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!
Mark
Hi Mark,
for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.
HTH,
Ovidiu Burlacu
in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.
This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...
Fridtjof|||
This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.
We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?
Thanks,
Mark
|||Here are a couple examples of how I am doing this:
Oracle adonet
<Configuration ConfiguredType="Property" Path="\Package.Connections[one.adonet].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
-Oracle OLEDB
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[one.oledb].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
--SQL Server oledb
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
|||Mark,since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof|||
Friedel wrote:
Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof
It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.
-Jamie
|||Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof|||
Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof
Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.
-Jamie
|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...
Fridtjof
Oracle Connection String Stored in SQL Server Configuration Not Working
I'm trying to store connection information in a SQL Server SSIS Configuration. I can see the information in the SSISConfigurations table and it appears to be reading the data as the package loads in the designer, but I'm getting connection failure messages
(The AcquireConnection method call to the connection manager "APPLPROD" failed with error code 0xC0202009. MTL_SYSTEM_ITEMS.dtsx 0 0).
Do I have to do anything in the package other than set up the initial configuration? Any clues as to why my connection isn't picking up the configuration information?
Thanks!
Mark
Hi Mark,
for security reasons, we stripe out the password from a connection string. You need to go and change the configuration to include the password.
HTH,
Ovidiu Burlacu
in the case of OLEDB provider for Oracle I made the experience that providing the password
in a package configuration does not work. With a ADO.Net Provider this works fine.
This is of course a disappointing limitation but I hope that this will be solved in some of the future
releases...
Fridtjof|||
This has been our experience as well; we are able to get the Oracle connection properly set from an XML configuration, and we have set all the properties in the SQL Server configuration, but the SQL configuration just does not work.
We have multiple packages in our projects, and each package pointed to the same configuration file, and when we deployed we were getting an error about the file already existing (apparently another known bug) - hence the attempt to switch to a SQL Server configuration. The ADO.Net Provider is an option, but we're using dynamic SQL so we set the Data Access Mode in the connection to "SQL Command from Variable", and I can't seem to be able to set this property using a Data Reader source. So right now we're kind of stuck. Any suggestions?
Thanks,
Mark
|||Here are a couple examples of how I am doing this:
Oracle adonet
<Configuration ConfiguredType="Property" Path="\Package.Connections[one.adonet].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
-Oracle OLEDB
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[one.oledb].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=dwprod;User ID=appacnt;Password=notsecret;Provider=MSDAORA.1;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
--SQL Server oledb
<?xml version="1.0"?>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[datamart.SM.oledb].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=datamart;User ID=ssis agent;Password=Guessit2;Initial Catalog=SM;Provider=SQLOLEDB.1;Persist Security Info=True;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
|||Mark,since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof
|||
Mark,
since I've been using dynamic SQL for Oracle and package configuration I don't see
any alternative to using OLEDB. I don't store the passwords in the pkg config but
as long as pwds don't change when deploying packages to other machinesthis
means no problem.
Nevertheless I don't give up hoping, that in SP1 storing pwds in Package configs for
OLEDB will work *dreaming*
Fridtjof
It DOES work and Phillipe has shown you examples of it working. I've got exactly the same thing set up for a number of Oracle sources and they all work perfectly well. If something isn't working it isn't the configuration - maybe this is masking something else.
-Jamie
|||Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof
|||
Jamie,
I was talking about OLDEB and *NOT* about ADO.NET connector what phillipe has shown.
I agree with you that it works with ADO.NET but not with MS OLEDB provider for Oracle!
Fridtjof
Ah, OK. My apologies, I thought Phillipe was talking about OLE DB. But, what I said is still true. I have this working using an OLE DB Provider with no problems at all.
-Jamie
|||Oh, you're right. I've done some tests with OLEDB for Oracle again and the results are positive.
I don't know what went wrong when I tested this some time ago...
Fridtjof
Subscribe to:
Posts (Atom)