Need to sum columns: nested

Showing posts with label nested. Show all posts

Friday, March 23, 2012

Nesting stored procedure, how to ?

Hello,

I'm trying to use nested stored procedure but can't get it to work.

I've created a stored procedure named 'sousmenu1_parents' which does a simple select, no parameter.

here's what I'm trying to achieve now :

CREATE PROCEDURE SelectAllDroitSpe

-- Add the parameters for the stored procedure here

@.UserId int

EXEC sousmenu1_parents;

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT sousmenu1_parents.menu_id1 AS sousmenu1_id

FROM authorisations_speciales LEFT JOIN sousmenu1_parents ON authorisations_speciales.page_id = sousmenu1_parents.menu_id1

WHERE authorisations_speciales.utilisateur_id=@.UserId

END

I get 'sousmenu1_parents isn't a valid object name'

Where am I going wrong ?

Thanks a lot.

Flip:

If your stored procedure does a simple select then transform the procdure into an function.

Dave

|||

Hi,

you can′t directly select from a strored procedure although the procedure return a table with the results, you will have to EXEC the procedure and redirect the output into a table (like a temp table)

-Create a Table first (can be also a temp table)
-

INSERT INTO YourTable
EXEC procedurename

-Use the table in your query.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

This is a good alternative; the problem with the INSERT INTO ... EXEC PROC method is that you can only use this construct at a single level in a nested series; after that you get execution errors. Don't get me wrong, I use this construct frequently but it is definitely not my first choice when I am dealing with nesting a procedure that "is a simple select". You might be able to make a case for avoiding the function for some other reason but this post is titled "Nesting stored procedure, how to?"

|||

Thanks very much.

I'm now looking up on Functions.

I'm new to Sql Server and I'm migrating an Access database.

I used many nested queries in Access and it looks like converting them is going to be a nightmare.

One thing I don't understand : if I can't "directly select from a stored procedure" what is the point in nesting stored procedure ?

|||

Flip:

Stored procedures will nest; however, it sometims requires judicious use of your alternatives. There are several options to return data from a called stored procedure to a calling stored procedure:

Use of output parameter(s)|||

ignition, I was a big user of access in my past and used many nested access queries to produce result sets. Think of Access queries as Views in SQL.

If in Access you had two queries

select col1, col2 from tablea where some where clause.... (query name is Query1)

select count(*) from Query1 (query name is Query2)

In SQL you could create a View:

Create View vw_Query1 as

select col1, col2 from tableA where....some where clause

Select count(*) from vw_Query 1

Views do not allow you to pass parameters (this is where stored procedures or functions come into play)

Reason you might want to nest stored procedures is to utilize other stored procedures to get a resultset or parameter back to the calling procedure and/or to encapsulate standard code that you cannot execute using functions. Functions have specific limitations (can't do data manipulation (insert, create, drop, can only call extended stored procedures) that you can do in stored procedures.

Create Proc usp_Test1 @.tablename varchar(100)

exec usp_AddMissingColumns @.tablename (does an ALTER which you can't do in functions)

exec usp_SetBlanksToNull @.tablename

return

|||

Great, it works a treat using a View !

This all make sense now ...

So for all the simple SELECT queries I had in Access (no parameters) should I use views in Sql Server ? is it in any way faster than a stored procedure, or any other advantage of this ?

Thanks again everyone, and in case I don't come back here before next year : have a lot of fun to end 2006 !

|||

Ignition, I wouldn't automatically change every simple query you have in Access to a view. If you're migrating to SQL Server, it's a good time to analyze the MS Access queries you currently have to determine if there is a continued need for them. I know that I would create queries (in Access) to do some ad hoc things, and then leave them there (laziness on my part!). When I started looking at the stored queries (for migration), I realized that many of them could be combined into a handful of views (some did basically the same thing but with different columns).

Think of a view as a subset (either just certain columns, an aggregation, and/or with specific where clauses) of a table. If you are querying the same data with the same where clause continually, this is probably a good candidate for a view... If you are continually looking at only certain columns within a table, probably a candidate for a view...

Views, stored procedures, and functions all have their place to do certain types of activities. If you lookup these items in Books Online, they will give you some scenarios on when/where and how to use them...

Good luck and happy New Year...

|||Convert your SPs to table-valued functions or even views if there is no parameterization so that you can reuse them easily.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

SET @.outputXML = (
SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
Its always erroring with the following information
"Msg 1086, Level 15, State 1, Line 16
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

set @.outputXML =
(
select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from
(SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping) as A
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

(SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32) as A
Order by 3,4,5,6 FOR XML EXPLICIT)
SELECT @.outputXML

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

|||I had the exact same error. I solved it in the same way as you did , but my problem is that the query doesnt always run as i expect it to. Every so often Sql server throughs me back this error.
The original query worked without the error. All I did was wrap it in another select, exactly is done in the solution on this thread

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.|||Is it possible because of bad data? Since the error seems to be random, I would check the data or any other query which gets executed before this query that might change the assumptions your current query is making.|||You are getting the error because of ordering of the data.After applying the order by clause, the result set should have the parent tag ID appear before the child tag id.You can verify that by executing the query without the for xml clause.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML asXML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

(SELECT 1 as TAG,NULLas PARENT, BatchID as [Batch!1!id],NULLas [Sequence!2!id],NULLas [Step!3!id],NULLas [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID,NULL,NULLFROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID,NULLFROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32)as A
Orderby 3,4,5,6 FORXMLEXPLICIT)
SELECT @.outputXML

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from(SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] =MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUPBY ObjectType, ParentId

HAVING ObjectType = 3

)as A FORXMLAUTO,TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

|||Is it possible because of bad data? Since the error seems to be random, I would check the data or any other query which gets executed before this query that might change the assumptions your current query is making.|||You are getting the error because of ordering of the data.After applying the order by clause, the result set should have the parent tag ID appear before the child tag id.You can verify that by executing the query without the for xml clause.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

nested XML output with several JOIN statements

First, thanks to the guys for helping me earlier just to get my XML output
into element form. I'm new to XML, so I appreciate all the help and hope I'm
learning.
My next question has to do with nesting the data that comes out as a result
of several JOIN's
Here is my code:
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
[Order].ID,
[Agent].TextID,
[Buyer].TextID,
[Broker].TextID
FROM
@.t AS Root
JOIN psOrder [Order] ON Root.id = 1
LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
WHERE [Order].ID = 12345
FOR XML AUTO, ELEMENTS
The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
However, the output I'm getting is nesting the data.
The output I get (incorrectly) is like this:
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
This is not correct. The Agent, Buyer, and Broker are all children of the
Order, and should not be nested within each other.
The desired output is like this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
I'd would very appreciate some help with this. Perhaps my SQL is not written
properly so that it comes out as desired.
ScottFor each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to get
the correct nesting. For example:
=====
DECLARE @.t TABLE (id INT)
INSERT INTO @.t VALUES (1)
SELECT
Root.id,
authors.au_id, authors.au_lname, authors.au_fname,
titles.title_id, titles.title
FROM
@.t AS Root
JOIN authors ON Root.id = 1
JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
FOR XML AUTO, ELEMENTS
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
> First, thanks to the guys for helping me earlier just to get my XML output
> into element form. I'm new to XML, so I appreciate all the help and hope
> I'm
> learning.
> My next question has to do with nesting the data that comes out as a
> result
> of several JOIN's
> Here is my code:
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> [Order].ID,
> [Agent].TextID,
> [Buyer].TextID,
> [Broker].TextID
> FROM
> @.t AS Root
> JOIN psOrder [Order] ON Root.id = 1
> LEFT JOIN Contact [Agent] ON [Order].agent_id = Agent.ID
> LEFT JOIN Contact [Buyer] ON [Order].buyer_id = Buyer.ID
> LEFT JOIN Contact [Broker] ON [Order].broker_id = Broker.ID
> WHERE [Order].ID = 12345
> FOR XML AUTO, ELEMENTS
> The "Order" table joins to the "Agent", "Buyer", and "Broker" tables.
> However, the output I'm getting is nesting the data.
> The output I get (incorrectly) is like this:
> <Order>
> <Agent>
> <Buyer>
> <Broker>
> </Broker>
> </Buyer>
> </Agent>
> </Order>
> This is not correct. The Agent, Buyer, and Broker are all children of the
> Order, and should not be nested within each other.
> The desired output is like this:
> <Order>
> <Agent>
> </Agent>
> <Buyer>
> </Buyer>
> <Broker>
> </Broker>
> </Order>
> I'd would very appreciate some help with this. Perhaps my SQL is not
> written
> properly so that it comes out as desired.
> Scott
>|||Thanks for the reply SriSamp. I've added the " AND Root.id = 1" at the end
of each LEFT JOIN, but I'm still getting the nesting problem.
The data is still coming out nested like this (incorrectly):
<Order>
<Agent>
<Buyer>
<Broker>
</Broker>
</Buyer>
</Agent>
</Order>
Just to confirm, I do not want the child table data to nest one within each
other.
The desired output is this:
<Order>
<Agent>
</Agent>
<Buyer>
</Buyer>
<Broker>
</Broker>
</Order>
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:OWdNAvA8FHA.1000@.tk2msftngp13.phx.gbl...
> For each of your LEFT JOIN's, towards the end, add "AND Root.id = 1" to
get
> the correct nesting. For example:
> =====
> DECLARE @.t TABLE (id INT)
> INSERT INTO @.t VALUES (1)
> SELECT
> Root.id,
> authors.au_id, authors.au_lname, authors.au_fname,
> titles.title_id, titles.title
> FROM
> @.t AS Root
> JOIN authors ON Root.id = 1
> JOIN titleauthor ON authors.au_id = titleauthor.au_id AND Root.id = 1
> JOIN titles ON titleauthor.title_id = titles.title_id AND Root.id = 1
> FOR XML AUTO, ELEMENTS
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:umPgdiA8FHA.3388@.TK2MSFTNGP11.phx.gbl...
output
the
>

nested while loop

Hi there. I want to avoid a cursur using a quite basing nested while loop. The problem is, that the outer index-variable (i) won't increment at all while the inner loop works perfectly.

This one should be quite easy to solve I guess, I'd be very happy if someone could give me a hint what I should try, though because I don't know what to try. The manual didn't help me much either, using CONTINUE and BREAKs didn't solve this problem for me.

My code:
DECLARE @.i INTEGER
DECLARE @.j INTEGER
SET @.i = 1
SET @.j = 0

WHILE(@.i<= 10) BEGIN
WHILE(@.j <= 100) BEGIN
SELECT @.i, @.j, COUNT(*) as anz FROM mytable WHERE dim1 = @.i AND dim2 = @.j
SET @.j = @.j + 1
END
SET @.i = @.i + 1
END

Thanks a lot for your help :)
Bernhard

corrected typo...[code]
SET i = @.i + 1
/code]

try:

SET @.i = @.i + 1|||try: SET @.i = @.i + 1Hi Kaiowas. Thanks for your reply, but that was just a silly typo in my posting. The original code ist just semantically correct as you suggested. It's not working, though...

Greets, Bernhard|||there might be another thing:
once @.j reaches 100, it is not reset again, so the inner loop runs once.|||there might be another thing:
once @.j reaches 100, it is not reset again, so the inner loop runs once.

Thanks a lot Kaiowas. Silly me :mad: I have to reset the inner counter variable for sure. The correct code:
DECLARE @.i INTEGER
DECLARE @.j INTEGER
SET @.i = 1
SET @.j = 0

WHILE(@.i<= 10) BEGIN
WHILE(@.j <= 100) BEGIN
SELECT @.i, @.j, COUNT(*) as anz FROM mytable WHERE dim1 = @.i AND dim2 = @.j
SET @.j = @.j + 1
END
SET @.j = 0
SET @.i = @.i + 1
END

Thanks a lot :)
Bernhard

Nested Views: How Inefficient?

I've been reading around that nested views can be quite inefficient because:

a) Using views in general involves some overheads (getting info from system tables etc.)

b) The optimiser doesn't do anything intelligent with them but just mixes all the joins from each of the nested views into one big, nasty join

If the only way to get the results I need involves writing this "big, nasty" join anyway, does it matter that I'm not putting it directly into a single view, but breaking it into components so that I can also access parts of the join for other purposes?

If the queries process lots of data, are the system overheads really that noticeable?

THANX!,
Angeloslook into udfs and see if some views can be converted into functions.|||look into udfs and see if some views can be converted into functions.

example, please|||Thanks for the tip. I think I see the point.

This would be faster because each function would perform its join seperately, and only the results would be joined to eachother, right?

Or is it rather a matter of system overheads being reduced?

All the best,

Angelos|||you got it, and overhead will be reduced because of that.

example? of what?|||you got it, and overhead will be reduced because of that.

example? of what?

thanks!

I think the example was requested of *me*...
I'm just experimenting now, so don't have a concrete example yet...

Basically, I'm trying to get my principles correct before choosing a method to use. A main concern is code maintainability. Nested views could easily be avoided by repeating code. But building simpler views and then composing them into more complicated ones allows subsequent use of the simpler ones independently whenever they are sufficient.|||no, the example was requested of ms_sql_dba

an example of a view that can be converted to a udf|||Functions are precompiled, while views are not. Functions are generally more efficient because of that. In your case, however, some experimentation may be required, because it also means that the entire function must be processed before joining it to your other functions/views/tables. It's possible that if you were using views the server would combine all the view and table references to come up with the most efficient plan for that combination. You would suffer some overhead cost in devising the query plan, but you might avoid needless or redundant joins and calculations that are not required in your final dataset.

nested views lock management

Hi, I have a question about nested views. Is there a way for the view not to place any locks on the underlying tables? I tried to re-write my views and its nested views with "with (nolock)" but when I view the enterprise manager, I still see exclusive and share lock on the tables. Any help would be appreciated. Thanks.

I have wanted to do this before and have tried it; however, views do not work that way. If you want to code with the NOLOCK hint you will need to code it with the actual queries. One alternative, although not an efficient one, would be to create a multi-line TVF instead of a view in which the function consists of a query that contains the NOLOCK hint. I have never used this alternative and I guess I don't really recommend it.

|||

Hi , Thanks for the reply.

I was also wondering how about putting the select from view in a store procedure, and set the transaction level to read uncommited. Would that work? This is because I think I am getting a dead lock on a table. for some reason, the select is placing a share lock and an exclusive lock on the underlaying table which make no sense to me, I thought exclusive lock is only for insert, update and delete. Thanks for any response again.

|||

Which SQL Server version do you use?

If you use SQL Server 2005, you could use READ_COMMITTED_SNAPSHOT option for you database.

Using snapshot isolation could decrease number of deadlocks, because transaction isolation was implemented with usage row versions.

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
Venkat
Have you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegr oups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>
|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.
|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.
|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>
|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat
|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat
|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat
sql

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
VenkatHave you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegroups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>|||Uri Dimant wrote:
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
> >I tried running the views seperately and it works . As mentioned the
> > job also does not hang always .. it hangs at times , but the trend is
> > unpredictable.
> >
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Uri Dimant wrote:
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
> >I tried running the views seperately and it works . As mentioned the
> > job also does not hang always .. it hangs at times , but the trend is
> > unpredictable.
> >
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
VenkatHave you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegroups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat

Nested views

When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?

When you say "4 levels deep" do you mean 4 joins? you might want to test it out. There will be an overhead of creating a temp table, inserting data into it, followed by a SELECT from the temp table. If your report will be run by thousands of users simultaneously you might even see a degradation in performance due to tempdb contention.

|||

By 4 levels deep I mean View1 is a query of 6 tables and several joins, then view2 use view1 with some more tables and joins or calculations etc etc.

Basically the end result in view4 is too complex to write in 1 query or 1 view so you go as far as you can in view1, then expand that result using view2 etc.

So any ideas on performance??

|||

Off the top of the head, since its not a simple query, I cant think of any, so your best bet is to test it out quickly. You can fire up profiler or use SET STATS IO ON and some others like TIME etc and see if there is any improvement/degradation in performance.

nested update triggers

HI all,
I have a question about update triggers. I have a update trigger that
updates a 'dupdated' column to the current date and time. I this so that I
have an accurate record of when the row was updated. due to my mistake,
users where changing the dupdated column to cover their sloppiness.
My question is if I have an update command in an update trigger, will it
recursively call itself.
Thanks
RobertRobert Bravery wrote:

> HI all,
> I have a question about update triggers. I have a update trigger that
> updates a 'dupdated' column to the current date and time. I this so that I
> have an accurate record of when the row was updated. due to my mistake,
> users where changing the dupdated column to cover their sloppiness.
> My question is if I have an update command in an update trigger, will it
> recursively call itself.
> Thanks
> Robert
Not if you have set recursive triggers off (which is also the default
state):
ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
If you allow users to update tables directly then I wouldn't expect a
dependable audit trail. Triggers aren't the whole answer - a better
security implementation is the answer.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
Thanks David,
Yes, this table was created durring my learning process, I'm still learning
but was very very green at the time
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139304916.973413.224930@.g44g2000cwa.googlegroups.com...
> Robert Bravery wrote:
>
I
> Not if you have set recursive triggers off (which is also the default
> state):
> ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
> If you allow users to update tables directly then I wouldn't expect a
> dependable audit trail. Triggers aren't the whole answer - a better
> security implementation is the answer.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>sql

nested tree, how to ?

hi, i've problems representing nested tree in sql server strucutre ...
my scenario is :
several process (p1,p2,p3,etc..) makes several operations
(op1,op2,op3,...)
i need to store the combination process,operation,time and this ismy
table
structure (processid,opid,dateop)
now i need to show a tree hystory of the operations in a definited date
range...
here's a sample:
op1
50% makes op2
20% makes op1
60% makes op3
100% makes op4
20% makes op5
50% makes op3
100% makes op6
100% makes op1
the real problem is the amount of data ., about 20 000 000 records ...
how can represent this tree in an efficient way ?!'!!?
thanks in advance for help
andrewI'm guessing you don't want to display all 20M lines at once, so what are yo
u
really trying to 'represent' ?
So, make sure you have an index on processid, and another on dateop.
Populate a temporary table with the processes you're interested in. Then add
to that table the details of the sub-processes, and repeat until there's
nothing more to add. You'll go through the table only once for each level yo
u
need to go down.
To make sure you only ask the 20M row table for the data you need, mark
things as done once you've pulled the data in. So, 3 values. 0 = new, 1 =
processing, 2 = done. Populate with 0. Then increment everything. Then pull
in new values (defaulting to 0), for the records that are marked with 1.
For making sure that it's ordered correctly, try using a string which
indicates the node you're looking at, with something appended to it. After
all, "aba" will come between "ab" and "ac". You might want to use lots of
characters per node though, etc... but string sorting may well work for your
ordering here better than numbers.
Hope this helps... I'm sure other people will have better ideas though.
"andrew" wrote:

> hi, i've problems representing nested tree in sql server strucutre ...
> my scenario is :
> several process (p1,p2,p3,etc..) makes several operations
> (op1,op2,op3,...)
> i need to store the combination process,operation,time and this ismy
> table
> structure (processid,opid,dateop)
> now i need to show a tree hystory of the operations in a definited date
> range...
> here's a sample:
> op1
> 50% makes op2
> 20% makes op1
> 60% makes op3
> 100% makes op4
> 20% makes op5
> 50% makes op3
> 100% makes op6
> 100% makes op1
> the real problem is the amount of data ., about 20 000 000 records ...
> how can represent this tree in an efficient way ?!'!!?
> thanks in advance for help
> andrew
>|||Please post sample data the the tree below is based on.
So far, you don't have anything that sounds like a tree datawise, since you
have not explained any relationships. Trees have parents and children, what
you posted doesn't seem to have either, outside of the formatting of the
results.
Supervisors and employees is a good example of a tree. We all understand
how that relationship works. Explain how yours works and we will be able to
give you much better advice.
"andrew" <cekgroup@.yahoo.com> wrote in message
news:1147337447.998183.307900@.i39g2000cwa.googlegroups.com...
> hi, i've problems representing nested tree in sql server strucutre ...
> my scenario is :
> several process (p1,p2,p3,etc..) makes several operations
> (op1,op2,op3,...)
> i need to store the combination process,operation,time and this ismy
> table
> structure (processid,opid,dateop)
> now i need to show a tree hystory of the operations in a definited date
> range...
> here's a sample:
> op1
> 50% makes op2
> 20% makes op1
> 60% makes op3
> 100% makes op4
> 20% makes op5
> 50% makes op3
> 100% makes op6
> 100% makes op1
> the real problem is the amount of data ., about 20 000 000 records ...
> how can represent this tree in an efficient way ?!'!!?
> thanks in advance for help
> andrew
>|||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. What you posted makes no sense.
You have described a matrix (ops by processes), not a tree. In a tree,
one process might have one or more operations as subordinates.
You show a cycle where OP1 is one of its own subordinates. Trees do
not cycles.
Have you gotten a copy of TREES & HIERARCHIES IN SQL?|||> Have you gotten a copy of TREES & HIERARCHIES IN SQL?
Thanks I think i need it ?!?!? :-)
here is a sample of my table
TABLE : OPERATIONSLOG
--
LogID , <-- PK
CompanyID ,
ProcID ,
OperationID ,
PreviousOperationID ,
OperationDate ,
OperationName ,
PreviousOperationName
SAMPLE DATA
--
1,1, 1, null , 2006-01-01 10:00 <-- ENTRY OPERATION
1,1, 2, 1 , 2006-01-01 10:17
1,1, 5, 2 , 2006-01-01 10:32
1,1, 1, 5 , 2006-01-01 10:36
1,1, 2, 5 , 2006-01-01 10:36 --> EXIT OPERATION
1,2, 3, null , 2006-01-01 10:00 <-- ENTRY OPERATION
1,2, 1, 3 , 2006-01-01 10:06
1,2, 4, 1 , 2006-01-01 10:15 --> EXIT OPERATION
2,3, 6, null , 2006-01-02 10:00 <-- ENTRY OPERATION
2,3, 7, 6 , 2006-01-02 10:26
2,3, 11, 7 , 2006-01-02 10:46
2,3, 1, 11 , 2006-01-02 11:06
2,3, 2, 1 , 2006-01-02 12:06
2,3, 4, 2 , 2006-01-02 13:15 --> EXIT OPERATION
1,4, 1, null , 2006-01-03 10:00 <-- ENTRY OPERATION
1,4, 4, 1 , 2006-01-04 13:15 --> EXIT OPERATION
this is a flat table, with all this data , and other not relevand
fields fot this result
i need a tree result with the history of my operations.
some like is
Give me flow for operation "1" in Company "1" from 2006-01-01 to
2006-01-31
Result :
Operation "1 "
Operations "2" (qty 1 process 50%)
Operations "5" (qty 1 process 100%)
Operations "1" (qty 1 process 100%)
Operations "2" (qty 1 process 100%)
Operations "4" (qty 1 process 50%)
Give me flow for operation "3" in Company "1" from 2006-01-01 to
2006-01-31
Result:
Operations "3" (qty 1 process 100%)
Operations "1" (qty 1 process 100%)
Operations "4" (qty 1 process 100%)
the problem is with 20.000.000 records... (for all companies)
a process always execute operations in the same companyid
about 20 companies ==> 1.000.000 per company :)
a possible idea is with sql 2005 to create a new table with one record
per process ...
my idea is to store in this table :
processid , and an XML Field tha represent the flow of operations.
So when someone request me a tree history i think to use xml native
performance in sql 2005
to create the result xml ( with an xquery or xpath ...)
then i can schedule a job that each night (for eaxmple) can add the new
process stored in the
huge flat table ...
dreams ?
thanks a lot for patience, support and help
thanks in advance !!!!
andrew|||>You show a cycle where OP1 is one of its own subordinates. Trees do
>not cycles.
yes but OP1 in the root level is not the same of the OP1 excecuted
after another
operation, is relevant the operation in a specific level of the tree
...
i hope to explain me, sorry for this !?!? (and for my english too ) ;-)|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147365462.119015.291560@.u72g2000cwu.googlegroups.com...
> ...Have you gotten a copy of TREES & HIERARCHIES IN SQL?
>
It should be "...a copy of MY BOOK, TREES & HIERARCHIES IN SQL.
When you recommend your own book, you should have the moral obligation to
send a copy to the poster.
Free of charge (OK, the poster can pay transport) and autographed, of
course.|||If they buy a copy, then I promise NOT to autograph it. You cannot
return a book with an auto graph nto the distributor. I used to own
some bookstore.
.

Friday, March 23, 2012

Nesting stored procedure, how to ?

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

nested XML output with several JOIN statements

nested while loop

Nested Views: How Inefficient?

nested views lock management

Nested Views are not getting executed.

Nested Views are not getting executed.

Nested Views are not getting executed.

Nested views

nested update triggers

nested tree, how to ?

Need to sum columns

Blog Archive

About Me