Production Ready Procedures

Hello, I have a couple of "simple" questions.
I have been told, that with rare exception, all my "production procedures
and tables" should all be owned by "dbo". Does this represent reality.
What are some "common" examples of of where there is a need for different
naming conventions.
When Executing a procedure, should it always be qualified with the Database
name.owner? Are there different rules for client applications? I believe
as part of the connection string for a C# or VB application, it requires
the "Catalog", which equates to the database name. So is there any reason
to specify the DataBase name in these situations?
Thanks in advance for your assistance!!!!!!!Whether it's owned by dbo or some other user depends on your requirements.
Unless you have a specific reason to have the objects owned by individual
users keeping them owned by dbo makes life much simpler and cleaner. You
should always preference a call to an object (sp, table etc) by the
owner.object regardless of who the owner happens to be. Never use the
db.owner.object syntax unless you are calling an object in another db. Doing
these helps the compiler to work most efficiently and reduces the work sql
server must do to properly determine which object you are calling.
--
Andrew J. Kelly
SQL Server MVP
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns945A6552D56CAJimHeaveyhotmailcom@.207.46.248.16...
> Hello, I have a couple of "simple" questions.
> I have been told, that with rare exception, all my "production procedures
> and tables" should all be owned by "dbo". Does this represent reality.
> What are some "common" examples of of where there is a need for different
> naming conventions.
> When Executing a procedure, should it always be qualified with the
Database
> name.owner? Are there different rules for client applications? I believe
> as part of the connection string for a C# or VB application, it requires
> the "Catalog", which equates to the database name. So is there any reason
> to specify the DataBase name in these situations?
> Thanks in advance for your assistance!!!!!!!sql

Production DB Modifications

We have a production database that is being used by at least 20 different
applications. Some of the tables need to be modified (column added). Does
anybody have a good way or best method of changing table schemas, objects,
etc. without interrupting or at least minimizing the impact on the current
operations?
Hello,
No. If you need to change the schema it defenetely require a downtime.
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
> We have a production database that is being used by at least 20 different
> applications. Some of the tables need to be modified (column added). Does
> anybody have a good way or best method of changing table schemas, objects,
> etc. without interrupting or at least minimizing the impact on the current
> operations?
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6a39HvKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> Hello,
> No. If you need to change the schema it defenetely require a downtime.
>
Eh, really depends on the size of the schema change.
If the column has no defaults, adding it should be a trivial change. We do
these routinely on a 24x7 database.
If the column has defaults, the larger the table, the longer it will be
locked.
One way to NOT do it is via EM. In almost all cases, this will guarantee
the table will be locked, a new one with the updated schema created, and
then data loaded into it, then the original deleted and the new one renamed.
You really have to do these via a script, not via EM.

> Thanks
> Hari
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
>
sql

Production DB Modifications

We have a production database that is being used by at least 20 different
applications. Some of the tables need to be modified (column added). Does
anybody have a good way or best method of changing table schemas, objects,
etc. without interrupting or at least minimizing the impact on the current
operations?Hello,
No. If you need to change the schema it defenetely require a downtime.
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
> We have a production database that is being used by at least 20 different
> applications. Some of the tables need to be modified (column added). Does
> anybody have a good way or best method of changing table schemas, objects,
> etc. without interrupting or at least minimizing the impact on the current
> operations?|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6a39HvKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> Hello,
> No. If you need to change the schema it defenetely require a downtime.
>
Eh, really depends on the size of the schema change.
If the column has no defaults, adding it should be a trivial change. We do
these routinely on a 24x7 database.
If the column has defaults, the larger the table, the longer it will be
locked.
One way to NOT do it is via EM. In almost all cases, this will guarantee
the table will be locked, a new one with the updated schema created, and
then data loaded into it, then the original deleted and the new one renamed.
You really have to do these via a script, not via EM.
> Thanks
> Hari
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
>> We have a production database that is being used by at least 20 different
>> applications. Some of the tables need to be modified (column added). Does
>> anybody have a good way or best method of changing table schemas,
>> objects,
>> etc. without interrupting or at least minimizing the impact on the
>> current
>> operations?
>

Production DB Modifications

We have a production database that is being used by at least 20 different
applications. Some of the tables need to be modified (column added). Does
anybody have a good way or best method of changing table schemas, objects,
etc. without interrupting or at least minimizing the impact on the current
operations?Hello,
No. If you need to change the schema it defenetely require a downtime.
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
> We have a production database that is being used by at least 20 different
> applications. Some of the tables need to be modified (column added). Does
> anybody have a good way or best method of changing table schemas, objects,
> etc. without interrupting or at least minimizing the impact on the current
> operations?|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e6a39HvKHHA.3936@.TK2MSFTNGP02.phx.gbl...
> Hello,
> No. If you need to change the schema it defenetely require a downtime.
>
Eh, really depends on the size of the schema change.
If the column has no defaults, adding it should be a trivial change. We do
these routinely on a 24x7 database.
If the column has defaults, the larger the table, the longer it will be
locked.
One way to NOT do it is via EM. In almost all cases, this will guarantee
the table will be locked, a new one with the updated schema created, and
then data loaded into it, then the original deleted and the new one renamed.
You really have to do these via a script, not via EM.

> Thanks
> Hari
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:E5B38EC4-31D4-42EE-9C83-A180C04216EF@.microsoft.com...
>

Product qty in a given branch

Hi,
I'm new to queries and I'm stuck with joins...
I have the following tables, and would like to get the total of the
products, given a BranchId:
table Product
ProductId: PK
ProductName
table Branch
BranchId
table Inventory
InventoryId: PK
BranchId: FK to Branch.BranchId
table InventoryDetail
InventoryDetailId: PK
InventoryId: FK to Inventory.InventoryId
ProductId: FK to Product.ProductId
Quantity
Here is what I started:
CREATE PROCEDURE SelectProductByName(@.BranchId INT, @.ProductName
NVARCHAR(100))
AS
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail AS ID ON ID.ProductId = P.ProductId
/*And waht now with*/
RETURNHello, Michael
I guess you want something like this:
SELECT P.ProductId, P.ProductName, X.Qty
FROM Product AS P LEFT JOIN (
SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
FROM InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
WHERE I.BranchId = @.BranchId
GROUP BY ID.ProductId
) X ON P.Product = X.ProductId
WHERE P.ProductName = @.ProductName
This query may also work, but the first query is better:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Razvan|||Hi Razvan,
Thank you very much for the help.
Why is the first first query better than the second one?
Also, I've come up with the following query. Is it correct?
SELECT P.ProductId, P.ProductName,
(SELECT SUM(ID.Quantity) FROM InventoryDetail AS ID
JOIN Inventory AS I ON I.InventoryId = ID.InventoryId
WHERE (I.BranchId = @.BranchId) AND (ID.ProductId = P.ProductId))
AS Qty
FROM Product AS P
WHERE (P.ProductName = @.ProductName)
Regards
Razvan Socol wrote:
> Hello, Michael
> I guess you want something like this:
> SELECT P.ProductId, P.ProductName, X.Qty
> FROM Product AS P LEFT JOIN (
> SELECT ID.ProductId, Sum(ID.Quantity) AS Qty
> FROM InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> WHERE I.BranchId = @.BranchId
> GROUP BY ID.ProductId
> ) X ON P.Product = X.ProductId
> WHERE P.ProductName = @.ProductName
> This query may also work, but the first query is better:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID ON P.ProductId = ID.ProductId
> LEFT JOIN Inventory I ON ID.InventoryId = I.InventoryId
> AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Razvan
>|||Hello, Michael

> Why is the first first query better than the second one?
Before testing the queries (because I had no DDL and sample data, see:
http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
query is better, because the second query seemed unsafe to me. Now that
I've tested it (by translating it for one of my own schemas), I can say
that the second query is plain wrong: it doesn't return the expected
results. I intended to write the second query this way:
SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
FROM Product AS P
LEFT JOIN InventoryDetail ID
INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
WHERE P.ProductName = @.ProductName
GROUP BY P.ProductId, P.ProductName
Regarding the weird placement of the ON clause, see:
http://groups.google.com/group/micr...br />
2579509b

> Also, I've come up with the following query. Is it correct?
Yes, it is correct. Actually, I think it's identical (in terms of
performance) with my first query, but your query is better because is
more readable. The corrected second query (although it produces an
execution plan with the same cost as the other two), seems to me a bit
slower, because the "Stream Aggregate" step (the GROUP BY clause) is
done on a wider row. Also, it's a lot harder to understand, so that's
why I still think it's the worse among the three queries.
Razvan|||Hi Razvan,
Sorry, I didn't provide the DDL. Now that I know how to do it, I'll
provide it next time.
I'll go on with my query as I already understood it, keeping a copy of
yours if needed.
Thanks a lot
Razvan Socol wrote:
> Hello, Michael
>
>
> Before testing the queries (because I had no DDL and sample data, see:
> http://www.aspfaq.com/etiquette.asp?id=5006), I wrote that the first
> query is better, because the second query seemed unsafe to me. Now that
> I've tested it (by translating it for one of my own schemas), I can say
> that the second query is plain wrong: it doesn't return the expected
> results. I intended to write the second query this way:
> SELECT P.ProductId, P.ProductName, Sum(ID.Quantity) AS Qty
> FROM Product AS P
> LEFT JOIN InventoryDetail ID
> INNER JOIN Inventory I ON ID.InventoryId = I.InventoryId
> ON P.ProductId = ID.ProductId AND I.BranchId = @.BranchId
> WHERE P.ProductName = @.ProductName
> GROUP BY P.ProductId, P.ProductName
> Regarding the weird placement of the ON clause, see:
> http://groups.google.com/group/micr... />
492579509b
>
>
> Yes, it is correct. Actually, I think it's identical (in terms of
> performance) with my first query, but your query is better because is
> more readable. The corrected second query (although it produces an
> execution plan with the same cost as the other two), seems to me a bit
> slower, because the "Stream Aggregate" step (the GROUP BY clause) is
> done on a wider row. Also, it's a lot harder to understand, so that's
> why I still think it's the worse among the three queries.
> Razvan
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Since you also did not give any specs, can I assume that inventory is
spread over branches? Can I assume the product is identified by some
industry standard code, such as a UPC? And why did you create that
useless inventory details table? An inventory is not like an invoice.
CREATE TABLE Products
(upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
product_name CHAR(20) NOT NULL,
.);
CREATE TABLE Branches
(branch_id INTEGER NOT NULL PRIMARY KEY,
.);
CREATE TABLE Inventory
(branch_id INTEGER NOT NULL
REFERENCES Branches(branch_id),
upc CHAR(13) NOT NULL
REFERENCES Products(upc),
PRIMARY KEY (branch_id, upc),
qty_on_hand INTEGER DEFAULT 0 NOT NULL
CHECK (qty_on_hand >= 0)
The right design makes it easy:
SELECT P.upc, SUM(I.qty_on_hand)
FROM Products AS P
LEFT OUTER JOIN
Inventory AS I
ON P.upc= I.upc
GROUP BY P.upc;|||Hi Celko,
I already got an answer from Razvan.
I needed the inventory detail table to hold the different quantities
with their respective notes, such as bin location, qty of damaged items
included in the inventory,...
Now that I know how to post DDL, I'll do it next time. As for the sample
data, I don't have any yet. I'm starting to design the database from
scratch.
Thanks for the help
--CELKO-- wrote:
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Since you also did not give any specs, can I assume that inventory is
> spread over branches? Can I assume the product is identified by some
> industry standard code, such as a UPC? And why did you create that
> useless inventory details table? An inventory is not like an invoice.
> CREATE TABLE Products
> (upc CHAR(13) NOT NULL PRIMARY KEY, --upc?
> product_name CHAR(20) NOT NULL,
> ..);
> CREATE TABLE Branches
> (branch_id INTEGER NOT NULL PRIMARY KEY,
> ..);
> CREATE TABLE Inventory
> (branch_id INTEGER NOT NULL
> REFERENCES Branches(branch_id),
> upc CHAR(13) NOT NULL
> REFERENCES Products(upc),
> PRIMARY KEY (branch_id, upc),
> qty_on_hand INTEGER DEFAULT 0 NOT NULL
> CHECK (qty_on_hand >= 0)
>
>
> The right design makes it easy:
> SELECT P.upc, SUM(I.qty_on_hand)
> FROM Products AS P
> LEFT OUTER JOIN
> Inventory AS I
> ON P.upc= I.upc
> GROUP BY P.upc;
>|||>> I needed the inventory detail table to hold the different quantities with
their respective notes, such as bin location, qty of damaged items included
in the inventory,...<<
That is the usual inventory design; several different quantity
attributes (on-hand, on order, on hold, damaged, expired, etc. Since
these are all distinct attributes of an inventory held at a p[articular
branch, you simply extend the table. That data and the needed check
constraints were not shown in your specs (or lack of specs, really).
There is no need to create a separate inventory_id., which is a poor
attempt at a surrogate for a branch_id. You are splitting the actual
primary key (branch_id, upc) across tables. Ask yourself what meaning
an inventory has apart from the goods it holds. Look at your
pseudo-code; what maps yhour magical "InventoryDetailId" to one and
only one product, and assures that it is the RIGHT product? Nothing.
The split key invites disaster.
Please consider a correct design instead.|||Designing a database is not so simple...
I've already changed my design several times. At first because of a lack
of knowledge in database design (this is actually my first database with
SqlServer, before this was 2 simple databases with MS Access)
If you don't mind I'll really need a help. I'll try to make it as simple
as I can.
Our company has 2 branches (I'll generalise this to several branches).
Products can be stocked in either of these branches.
When counting a product, we need the date of the counting, and the
details (quantity + comments, such as damaged, carton no, bin
location,... which are "free-hand" text note). Note that branches count
their products independently of each other.
So, I came up with these tables:
CREATE TABLE Product (
ProductId BIGINT IDENTITY PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Branch (
BranchId INT IDENTITY PRIMARY KEY,
BranchName NVARCHAR(50) NOT NULL,
..
)
CREATE TABLE Inventory (
InventoryId BIGINT IDENTITY PRIMARY KEY,
InventoryDate DATETIME NOT NULL,
BranchId INT NOT NULL REFERENCES Branch(BranchId),
..
)
CREATE TABLE InventoryDetail (
InventoryDetailId BIGINT IDENTITY PRIMARY KEY,
InventoryId BIG INT NOT NULL REFERENCES Inventory(InventoryId),
ProductId BIG INT NOT NULL REFERENCES Product(ProductId),
Quantity INT NOT NULL,
Comments NVARCHAR(100) NULL
)
As for sample data, I don't have any yet, but here is what we want:
Lady coat no 634:
Main branch: counted on 14/02/06
- 60 pcs, ctn 123
- 6 pcs, on the shelf (in the shop)
Second branch: counted on 16/02/06
- 3 pcs, on the shelf
Lady pant no 132:
Main branch: counted on 21/02/06
- 2 pcs, a customer has specially ordered these for himself
Lady shoes no 852:
Main branch: counted on 22/02/06
- 12 pcs, in stock shelf no 3
- 12 pcs, waiting to be sent to branch 2
- 2 pcs, in the shop
Second branch: counted on 20/02/06
- 0 pcs, placed the order from Main branch already
--CELKO-- wrote:
>
> That is the usual inventory design; several different quantity
> attributes (on-hand, on order, on hold, damaged, expired, etc. Since
> these are all distinct attributes of an inventory held at a p[articular
> branch, you simply extend the table. That data and the needed check
> constraints were not shown in your specs (or lack of specs, really).
> There is no need to create a separate inventory_id., which is a poor
> attempt at a surrogate for a branch_id. You are splitting the actual
> primary key (branch_id, upc) across tables. Ask yourself what meaning
> an inventory has apart from the goods it holds. Look at your
> pseudo-code; what maps yhour magical "InventoryDetailId" to one and
> only one product, and assures that it is the RIGHT product? Nothing.
> The split key invites disaster.
> Please consider a correct design instead.
>

Product for Creating Data Dictionary

We have a large SQL Server database that has poorly documented tables and
field names. Is there a product that will create a Word document in table
format from the actual database that can be used to add documentation about
the meaning of each field?
Will
Will
SQL Server 2000 has an option called column description
create table dbo.customer (
customer_id integer not null identity (1, 1)
, trade_name varchar (0255) not null
)
go
alter table dbo.customer add
constraint pk_customer primary key nonclustered (customer_id)
go
/* column description meta info */
execute sp_addextendedproperty N'boolean_property_01', '1', N'user', N'dbo',
N'table', N'customer', N'column', N'trade_name'
execute sp_addextendedproperty N'column_description', 'Customer trading
name', N'user', N'dbo', N'table', N'customer', N'column', N'trade_name'
go
select * from :: fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'customer', 'column', default)
go
drop table dbo.customer
go
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:OOrsHkGUFHA.3636@.TK2MSFTNGP14.phx.gbl...
> We have a large SQL Server database that has poorly documented tables and
> field names. Is there a product that will create a Word document in
table
> format from the actual database that can be used to add documentation
about
> the meaning of each field?
> --
> Will
>
>

Product for Creating Data Dictionary

We have a large SQL Server database that has poorly documented tables and
field names. Is there a product that will create a Word document in table
format from the actual database that can be used to add documentation about
the meaning of each field?
--
WillWill
SQL Server 2000 has an option called column description
create table dbo.customer (
customer_id integer not null identity (1, 1)
, trade_name varchar (0255) not null
)
go
alter table dbo.customer add
constraint pk_customer primary key nonclustered (customer_id)
go
/* column description meta info */
execute sp_addextendedproperty N'boolean_property_01', '1', N'user', N'dbo',
N'table', N'customer', N'column', N'trade_name'
execute sp_addextendedproperty N'column_description', 'Customer trading
name', N'user', N'dbo', N'table', N'customer', N'column', N'trade_name'
go
select * from :: fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'customer', 'column', default)
go
drop table dbo.customer
go
"Will" <DELETE_westes@.earthbroadcast.com> wrote in message
news:OOrsHkGUFHA.3636@.TK2MSFTNGP14.phx.gbl...
> We have a large SQL Server database that has poorly documented tables and
> field names. Is there a product that will create a Word document in
table
> format from the actual database that can be used to add documentation
about
> the meaning of each field?
> --
> Will
>
>

Producing Text File

Hello all,
My sql skills are pretty poor, so please bear with me!

I need to take info from two tables (in Access db) and produce two txt files.

The data in the first table is listed as follows: OrderID, OrderDate, CustomerID, Total
The data in the second table is listed as follows: OrderID, Quantity, Discount, UnitPrice

The user has to be able to specify a time frame (start date and end date).

The major problem I'm having is with setting up the txt files. The header has to have the OrderID listed as "!!OrderID" (this is the way the fake company has their db set up apparently) Also, in the actual data in the txt files the OrderDate has to start w/ "#" and the CustomerID has to start w/ "&".

here's what I mean:

!!OrderID OrderDate CustomerID Total
--- --- ---- --
123456 #456789 &abcdef 987654

If you can help me at all, I'd much appreciate it!!! Thank you!Well, in MS Access you can't use "!" in column name, so you'll have to handle this using VBA coding and replace column name when writing into the text file. You didn't describe tables relationship.

SELECT
[OrderID],
"#" + [OrderDate] AS [OrderDate],
"&" + [CustomerID] AS [CustomerID],
[Total]
from
your_table;

store the result into some VBA object (I don't remember which one to use, it's long time since I used Access for last time). Open text file and read row by row from VBA object and write it into the file. Replace column name OrderID with !!OrderID|||Thanks for the help. The tables are linked through OrderID. Also, I found out that I don't have to use Access to do the project. MySQL is allowed as well. If that makes a difference w/ the !! problem, let me know.

Thanks again!

ProClarity: Is there any way we can use tables as sources of ProClarity views instead of cubes?

Is there any way we can use tables as sources of ProClarity views instead of cubes?

Hello! It is not possible. ProClarity is a SSAS-tool only.

You must build a cube in order to use ProClarity.

HTH

Thomas Ivarsson

sql

Processing methodology

we have 3 large fact tables with combined rec cnts of 32 million. These 3 fact tables share order attribute that we would like to pull out and create a order dimension so that the facts tables don't have to contain the attributes. I've tried update processing of this dimension but the time it takes is not much different than a full process. I would like to know if there is anything like a incremental processing of this dimension and how would I set that up?

I assume that you are using Analysis Services 2005?

Actually, the "Process Update" method is slow than doing a "Process Full" on a dimension. The reason for this is, that "Process Update" reads the entire dimension table and compares the content of this with the existing, processed dimension. Any differences are then updated in the OLAP dimension. "Process Full", on the other hand, does not have to make any comparisons - it simply reads the entire dimension table and builds the OLAP dimension.

Now, the main reason for using "Process Update" is that is does not invalidate your processed measure groups. If you do a "Process Full" on the dimension, you are forced into doing a "Process Full" on any measure group that contains this dimension. Note, that "Process Update" will invalidate the aggregations on any associated measure group, provided that changes have occured to the dimension. This means, that a "Process Update" on the dimension will have to be followed by a "Process Index" on the associated measure groups in order to get aggregations back.

Process SQL Transactions? Easy Question :)

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In SQL 2000, I am sending an XML, which carries data for two tables. Let's say, I am inserting half of the fields in TABLE1 and rest in TABLE2.

Specifically, I want to use Transaction Processing for inserting the rows in both tables. If insertion in one table fails, any inserted data should ROLLBACK and come out of procedure with relevant error code.

Please advice or send me any example links. Thanks

PankajHave you looked at the Begin Tansaction examples in the BOL? That contains an example with the commit transaction but not rollback transaction. I'm assuming you'll do the inserts in one SP. If that's true then you can follow those examples to help do your inserts and then test for errors and rollback if errors are encountered. Something like
SP...

BEGIN TRANSACTION
Insert code here

IF @.@.Error <> 0
ROLLBACK TRANS
ELSE
COMMIT TRANS

That's a rough idea but hopefully will get you started.

process cube but leave transaction open?

We have a massive cube. All but one of the fact tables are by 3am. But the final fact table isn't loaded in the SQL data warehouse until 11am. The users want the cube processed and available as immediately as possible. But the cube can't show ANY updated data until it shows ALL updated data including the fact table from 11am. Any thoughts on making this happen?

One option would be to process the cube at 3am in another database. Then process the final measure group at 11am. Then use the Synchronize command to get those changes to the live database soon after 11am. But there are two drawbacks... 1) This requires twice the amount of disk space as before and 2) I can't figure out a way to synchronize two databases on the same server.

Another option would be to process the cube at 3am but leave that transaction open (so end users can't see the new data) for 8 hours until you process the final measure group and then close the transaction. This doesn't seem like a good plan, but I wanted to hear people's thoughts.

I would certainly experiment with leaving transaction open for 8 hours. I don't see why it doesn't seem like a good plan to you. Note, that you will still have doubled usage of disk space, since two versions will be coexisting for the 8 hours.|||

Well, maybe it's not a dumb idea. I guess the reason I was worried about it is just my relational background and the idea to never leave transactions open. But I guess that doesn't matter in this case.

So my main concern with the technical feasibility is whether the server will clean up the idle session (in so doing cancel the transaction). Am I going to have to run some cheap discover function ever 5 mins for 8 hours to keep the session alive? Any other way?

Thanks for the feedback Mosha.

|||

If server canceling the idle session becomes a problem - you can always bump up the session expiration timeout.

|||

I've got this working. Cube processing is broken up into two parts with the connection being dropped in between. Just like you said, it works fine. Just had to bump up the IdleOrphanSessionTimeout setting.

There is one problem, though. If we get a failure on part 2 of the processing (like if there's an invalid dimension key in a fact table) then that failure rolls back the entire transaction, not just the stuff being processed during part 2. Wondering if there's a way around this. I can walk you through reproducing this against Adventure Works:

First, change the query binding of the Reseller_Sales_2004 partition of the Reseller Sales measure group to be:

SELECT [dbo].[FactResellerSales].[ProductKey],-999 as OrderDateKey,[dbo].[FactResellerSales].[DueDateKey],[dbo].[FactResellerSales].[ShipDateKey],[dbo].[FactResellerSales].[ResellerKey], [dbo].[FactResellerSales].[EmployeeKey],[dbo].[FactResellerSales].[PromotionKey],[dbo].[FactResellerSales].[CurrencyKey],[dbo].[FactResellerSales].[SalesTerritoryKey],[dbo].[FactResellerSales].[SalesOrderNumber],[dbo].[FactResellerSales].[SalesOrderLineNumber],[dbo].[FactResellerSales].[RevisionNumber],[dbo].[FactResellerSales].[OrderQuantity],[dbo].[FactResellerSales].[UnitPrice],[dbo].[FactResellerSales].[ExtendedAmount],[dbo].[FactResellerSales].[UnitPriceDiscountPct],[dbo].[FactResellerSales].[DiscountAmount],[dbo].[FactResellerSales].[ProductStandardCost],[dbo].[FactResellerSales].[TotalProductCost],[dbo].[FactResellerSales].[SalesAmount],[dbo].[FactResellerSales].[TaxAmt],[dbo].[FactResellerSales].[Freight],[dbo].[FactResellerSales].[CarrierTrackingNumber],[dbo].[FactResellerSales].[CustomerPONumber]
FROM [dbo].[FactResellerSales]
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'

(Note that "-999 as OrderDateKey" will cause a failure.)

Second, pull up the directory contains the 2004 partition for Internet Sales. (On your computer it should be something like c:\program files\Microsoft SQL Server\mssql.2\OLAP\data\Adventure Works DW.0.db\Adventure Works DW.18.cub\Fact Internet Sales 1.18.det\Internet_Sales_2004.16.prt) After part 1 completes, you'll see the number of files in this directory double. After part 2 fails, you'll see those new files disappear as the transaction was rolled back.

Then, compile a little VB.NET app, put a breakpoint on the Sleep command so you can see the number of files in that 2004 partition directory double. Then run it:

Public Shared Function Main(ByVal args As String()) As Integer

Dim sServer As String = "localhost"

Dim sDatabase As String = "Adventure Works DW"

ProcessPart1(sServer, sDatabase)

System.Threading.Thread.Sleep(5000) 'simulate a break in the program

ProcessPart2(sServer, sDatabase)

End Function

Public Shared Sub ProcessPart1(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

'save the session ID so we can retrieve it later and connect to this session later

'alternately, it could be saved to a SQL database instead

database.Annotations.SetText("ProcessCubeSessionID", server.SessionID)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.BeginTransaction()

Try

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Internet Sales")

mg.Process(ProcessType.ProcessFull)

'after processing has succeeded, change the orphan session timeout

'to 24 hours so that this session we're about to leave open won't timeout

'before we're done with it

server.ServerProperties("IdleOrphanSessionTimeout").Value = CStr(24 * 60 * 60)

server.Update()

'false means don't end session

'leave the session open so we can connect to it later

'and continue with the open transaction

server.Disconnect(False)

'don't commit the transaction now

'leave it open until we finish the processing later

Catch ex As Exception

server.RollbackTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.Disconnect(True)

Throw

End Try

End Sub

Public Shared Sub ProcessPart2(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

Dim oldSession As String = database.Annotations.GetText("ProcessCubeSessionID")

If String.IsNullOrEmpty(oldSession) Then

Throw New Exception("Was not able to find the existing session which " _

& "processed the other 95% of the cube and has the open transaction! " _

& "The cube will not be processed.")

End If

server.Disconnect(True)

Try

server.Connect(sServer, oldSession)

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Reseller Sales")

mg.Process(ProcessType.ProcessFull) 'BUG?: if this fails, it rolls back the transaction!!!

server.CommitTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

'set this property back to the default now that

'we're done needing orphan sessions to survive

server.ServerProperties("IdleOrphanSessionTimeout").Value = _

server.ServerProperties("IdleOrphanSessionTimeout").DefaultValue

server.Update()

server.Disconnect(True) 'true means to end session

Catch ex As Exception

'there was a problem with the last 5% of the processing...

'so leave the session open so the problem can be corrected and we can

'start with 95% of the processing from earlier in the morning

'being completed already

server.Disconnect(False) 'false means don't end session

Throw

End Try

End Sub

If you search for the line in the code which has a comment saying "BUG?" that's where the processing fails because of our tweak to the partition query. When that processing fails.

|||

I'd still like to hear if anyone knows a workaround, but I've posted this as a bug:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278000

|||I expect the bug that you filed to be resolved "By Design". The definition of the transaction is that it either fully succeed or fully fails. That's the letter "A" in the ACID definition - A for Atomicy. You have very long transaction, but it doesn't matter - if anything fails within transaction, the entire transaction is rolled back.

process cube but leave transaction open?

We have a massive cube. All but one of the fact tables are by 3am. But the final fact table isn't loaded in the SQL data warehouse until 11am. The users want the cube processed and available as immediately as possible. But the cube can't show ANY updated data until it shows ALL updated data including the fact table from 11am. Any thoughts on making this happen?

One option would be to process the cube at 3am in another database. Then process the final measure group at 11am. Then use the Synchronize command to get those changes to the live database soon after 11am. But there are two drawbacks... 1) This requires twice the amount of disk space as before and 2) I can't figure out a way to synchronize two databases on the same server.

Another option would be to process the cube at 3am but leave that transaction open (so end users can't see the new data) for 8 hours until you process the final measure group and then close the transaction. This doesn't seem like a good plan, but I wanted to hear people's thoughts.

I would certainly experiment with leaving transaction open for 8 hours. I don't see why it doesn't seem like a good plan to you. Note, that you will still have doubled usage of disk space, since two versions will be coexisting for the 8 hours.|||

Well, maybe it's not a dumb idea. I guess the reason I was worried about it is just my relational background and the idea to never leave transactions open. But I guess that doesn't matter in this case.

So my main concern with the technical feasibility is whether the server will clean up the idle session (in so doing cancel the transaction). Am I going to have to run some cheap discover function ever 5 mins for 8 hours to keep the session alive? Any other way?

Thanks for the feedback Mosha.

|||

If server canceling the idle session becomes a problem - you can always bump up the session expiration timeout.

|||

I've got this working. Cube processing is broken up into two parts with the connection being dropped in between. Just like you said, it works fine. Just had to bump up the IdleOrphanSessionTimeout setting.

There is one problem, though. If we get a failure on part 2 of the processing (like if there's an invalid dimension key in a fact table) then that failure rolls back the entire transaction, not just the stuff being processed during part 2. Wondering if there's a way around this. I can walk you through reproducing this against Adventure Works:

First, change the query binding of the Reseller_Sales_2004 partition of the Reseller Sales measure group to be:

SELECT [dbo].[FactResellerSales].[ProductKey],-999 as OrderDateKey,[dbo].[FactResellerSales].[DueDateKey],[dbo].[FactResellerSales].[ShipDateKey],[dbo].[FactResellerSales].[ResellerKey], [dbo].[FactResellerSales].[EmployeeKey],[dbo].[FactResellerSales].[PromotionKey],[dbo].[FactResellerSales].[CurrencyKey],[dbo].[FactResellerSales].[SalesTerritoryKey],[dbo].[FactResellerSales].[SalesOrderNumber],[dbo].[FactResellerSales].[SalesOrderLineNumber],[dbo].[FactResellerSales].[RevisionNumber],[dbo].[FactResellerSales].[OrderQuantity],[dbo].[FactResellerSales].[UnitPrice],[dbo].[FactResellerSales].[ExtendedAmount],[dbo].[FactResellerSales].[UnitPriceDiscountPct],[dbo].[FactResellerSales].[DiscountAmount],[dbo].[FactResellerSales].[ProductStandardCost],[dbo].[FactResellerSales].[TotalProductCost],[dbo].[FactResellerSales].[SalesAmount],[dbo].[FactResellerSales].[TaxAmt],[dbo].[FactResellerSales].[Freight],[dbo].[FactResellerSales].[CarrierTrackingNumber],[dbo].[FactResellerSales].[CustomerPONumber]
FROM [dbo].[FactResellerSales]
WHERE OrderDateKey >= '915' AND OrderDateKey <= '1280'

(Note that "-999 as OrderDateKey" will cause a failure.)

Second, pull up the directory contains the 2004 partition for Internet Sales. (On your computer it should be something like c:\program files\Microsoft SQL Server\mssql.2\OLAP\data\Adventure Works DW.0.db\Adventure Works DW.18.cub\Fact Internet Sales 1.18.det\Internet_Sales_2004.16.prt) After part 1 completes, you'll see the number of files in this directory double. After part 2 fails, you'll see those new files disappear as the transaction was rolled back.

Then, compile a little VB.NET app, put a breakpoint on the Sleep command so you can see the number of files in that 2004 partition directory double. Then run it:

Public Shared Function Main(ByVal args As String()) As Integer

Dim sServer As String = "localhost"

Dim sDatabase As String = "Adventure Works DW"

ProcessPart1(sServer, sDatabase)

System.Threading.Thread.Sleep(5000) 'simulate a break in the program

ProcessPart2(sServer, sDatabase)

End Function

Public Shared Sub ProcessPart1(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

'save the session ID so we can retrieve it later and connect to this session later

'alternately, it could be saved to a SQL database instead

database.Annotations.SetText("ProcessCubeSessionID", server.SessionID)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.BeginTransaction()

Try

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Internet Sales")

mg.Process(ProcessType.ProcessFull)

'after processing has succeeded, change the orphan session timeout

'to 24 hours so that this session we're about to leave open won't timeout

'before we're done with it

server.ServerProperties("IdleOrphanSessionTimeout").Value = CStr(24 * 60 * 60)

server.Update()

'false means don't end session

'leave the session open so we can connect to it later

'and continue with the open transaction

server.Disconnect(False)

'don't commit the transaction now

'leave it open until we finish the processing later

Catch ex As Exception

server.RollbackTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

server.Disconnect(True)

Throw

End Try

End Sub

Public Shared Sub ProcessPart2(ByVal sServer As String, ByVal sDatabase As String)

Dim server As New Server()

server.Connect(sServer)

Dim database As Database = server.Databases.FindByName(sDatabase)

Dim oldSession As String = database.Annotations.GetText("ProcessCubeSessionID")

If String.IsNullOrEmpty(oldSession) Then

Throw New Exception("Was not able to find the existing session which " _

& "processed the other 95% of the cube and has the open transaction! " _

& "The cube will not be processed.")

End If

server.Disconnect(True)

Try

server.Connect(sServer, oldSession)

Dim cube As Cube = database.Cubes.FindByName("Adventure Works")

Dim mg As MeasureGroup = cube.MeasureGroups.FindByName("Reseller Sales")

mg.Process(ProcessType.ProcessFull) 'BUG?: if this fails, it rolls back the transaction!!!

server.CommitTransaction()

'blank out the ProcessCubeSessionID annotation

database.Annotations.SetText("ProcessCubeSessionID", Nothing, True)

database.Update(UpdateOptions.Default, UpdateMode.Update)

'set this property back to the default now that

'we're done needing orphan sessions to survive

server.ServerProperties("IdleOrphanSessionTimeout").Value = _

server.ServerProperties("IdleOrphanSessionTimeout").DefaultValue

server.Update()

server.Disconnect(True) 'true means to end session

Catch ex As Exception

'there was a problem with the last 5% of the processing...

'so leave the session open so the problem can be corrected and we can

'start with 95% of the processing from earlier in the morning

'being completed already

server.Disconnect(False) 'false means don't end session

Throw

End Try

End Sub

If you search for the line in the code which has a comment saying "BUG?" that's where the processing fails because of our tweak to the partition query. When that processing fails.

|||

I'd still like to hear if anyone knows a workaround, but I've posted this as a bug:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278000

|||I expect the bug that you filed to be resolved "By Design". The definition of the transaction is that it either fully succeed or fully fails. That's the letter "A" in the ACID definition - A for Atomicy. You have very long transaction, but it doesn't matter - if anything fails within transaction, the entire transaction is rolled back.

Procedure vs Script

I have a complex procedure (300+ lines, but it used to be over 2000). The procedure uses one table variable and three temporary tables with indexes. The temporary tables are joined for the final output.

The problem is that the process takes 24 seconds if I execute the statements from query analyzer, but executing a procedure with same logic causes the process to take nearly six times as long.

Any ideas on what might cause a process to run six times longer as a procedure than as a script? The execution plans appear to be the same, and the same login is being used for both.

blindmanBlindman, just a few questions

Are there parameters passed to the procedure/script?

Just to be absolutely clear, if you cut and paste the meat of the procedure into Query Analyzer the meat will run faster than the procedure itself? I just want to make sure that the same client (and all network hops) are similar for both tests there.

About how much data is being joined in the three tables and returned in the resultset?|||Yes, the procedure takes about a dozen optional parameters. I am supplying three in my test.

Yes, the meat is the same (it tastes like chicken). I just remove the CREATE PROCEDURE header and replace it with explicit variable declarations.

For my test parameters I am only returning five lines of data, though this is culled, calculated, and aggregated from many much larger tables.

The temporarty tables are being loaded with about 30,000 rows.

blindman|||Did you run sp_recompile on the stored procedure ?|||Have you tried using table data type instead ?|||Tried recompiling. No effect.

Bounced the server. This made both executions run slow (so cache may have something to do with it).

Can't use table datatypes for the remaining three temporary data sets because I need to have an indexes on them or the procedure takes forever.

blindman|||I have been experiencing the same myself .. though the difference between the script and proc execution time is not more than 5 secs ...
might have something to do with the executions plans and way they are retrieved for sp and statements|||How about this: Using the meat (lightly basted, of course), put in different values for the parameters, and see if different query plans come out the other side.

I had one case where SQL Server actually chose a poor execution plan for a stored procedure. We had to make sure that the first set of parameters queried by it was the single set of parameters that gave the best overall plan. Could you be seeing a similar effect?|||I tried putting WITH RECOMPILE at the start of the procedure. Wouldn't this accomplish the same thing? It did not affect the execution time.

blindman|||That would regenerate the query plan for each execution (effectively that should have reduced it to just the meat). In my problem, I had 3 different query plans of which one was outlandishly inappropriate even for the parameters it was generated by, one middling plan, and one that was acceptable for all combinations. This only works if the query plan coming out the otherside of the optimizer is significantly different for each set of parameters.

Is it possible that the index statistics on the temp tables are not considered, since they are not available at runtime? As an experiment, what happens if you add permanent tables in place of the temp tables?|||I have to have temporary tables so that it can be multi-user.

Plus, it appeared to be running fine last week.

blindman|||If it was running fine last week, do you happen to know if any significant change has happened to the data? Purge, update or load? You cna check on the last time statistics were updated for each index by running the stats_date function. I think SQL Server automatically runs update statistics on a table if it notices a 10% difference in the number of rows, but maybe not necessarily on a mass update of values in the table you may want to join on.|||Our admin tells me we are running low on drive space. Perhaps that is affecting tempdb or drive cacheing.

blindman|||Low disk space in and of itself should not matter, but it could be a second symptom of a more general problem. It could be you are getting enough data in the database(s) to overwhelm the caching algorithms, and are now seeing more paging of the data. Kindly ask the users to stop putting so many orders into the database, as it could be slowing the system down ;-).

On a more serious note, see if you can get the admin to run a few dbcc showcontig statements on the tables and indexes you are using. You may be able to rebuild the indexes, and get more rows per read of the disk.|||This is a development database on a development server, so no new data is going into it and the table sizes are not changing.

The DBCC Showcontig is a possiblity I'll look into.

In the meantime, I'm goint to try running it on a server with more drive space just to see if it makes a difference.

blindman|||You never told us what show plan is telling you...

Is it one big transaction?

Did you do a trace?

Why not put in

SELECT Datetime() AS systime, 'Process x Starting' As Sysmessage

to find out where the bottleneck is...|||In the meantime, I'm goint to try running it on a server with more drive space just to see if it makes a difference.
That would make sense, since tempdb relies on the speed/size of the drive - as you run out of disk space, I have seen peformance impacts since it takes longer to find free clusters/sectors as tempdb grows ... You might try to alter the tempdb database to increase its initial size. You might have a few bad clusters as well - but I would expect more drastic results if that were the case.|||Update:

One (large) statement in the script uses parallelism when executed as a script, but does not use parallelism when executed as a procedure. This appears to be dragging down the procedure performance.

Anybody seen this before or have any idea what might cause the optimizer to choose a plan without parallelism for a procedure?

blindman|||Can you attach the sproc so we can take a look?

Why would the optimizer not thread out in a sproc?

Are you running on 2 different boxes?|||I think the main requirement to use parallelism is sheer volume of data. We ran into a bug where parallelism on a 3 proc box would hit a self-deadlock. The porblem was "fixed" in SP3a. Now SQL detects the deadlock, and bounces the query with an error. Microsoft's fix was to guess at indexes, until one eliminated parellelism.

What I can not figure out, though, is how they can have two different query plans, when you run the stored proc with recompile.|||You can try to add WITH (MAXDOP <number of CPUs available to your SQL box>).

But I think it may have something to do with recompiles. Try to run Profiler and add SP:Recompile event class.|||That would make the query use fewer processors. What Blindman needs is a MINDOP query hint.

procedure to create log and error files

Hi,

I have a procedure, containing 10 tables, whenever my procedure is executed, i what to know the start and end time of each table and this should be stored in a separate file known as log file and all the error info. should be stored in error file, and then i want to run this procedure through unix shell script(.ksh), can u plz.. help me out in this regard.

Thanks in advance.
mahe.For your loggin problem in oracle database you can use the log4plsql tools
see : http://log4plsql.sourceforge.net/

with this tools you can log in a lot off device.

G.