Re: CTE to Build XML - Database Discussion Boards

Dear All,

Iam new to MS SQL Server 2000 Administration. I have a small problem.

I want to Configure Auto Database Replication. I have 2 databases

1) Database 1
2) Database 2

The data should be copied from Database 1 to Database 2 when ever a insert, delete, update happened in the Database 1.Any changes in the Database 2 Should not affect to Database 1.

Overall if any thing chages in the Database 1 ,Same changes should
happen in Database 2.

Please help me on this.

Thanks & Regards
Veeresh

i want to join this group

You'll get a good starting point from this article: How Transactional Replication Works[^]

The need to optimize rises from a bad design.

My articles[^]

In Visual Studio, from the server explorer I am right clicking on stored procedure and click Step into Stored Procedure on the menu pad. It comes back to allow me to enter in the parameters but I cannot step through the program starting at the red dot. The dot says: "Break point will not currently be hit. Unable to bind sql break points at this time. Object contain break point not loaded." How can I clear this up so I can debug the procedure?

But I use the following rule: If I can't debug a stored procedure using pen and paper it is too complicated. This rule has served me well.

Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.

Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway

Most of this sig is for Google, not ego.

I faced some time ago some known problems with server explorer. So try following:
- delete all connections permanently from server explorer
- restart visual studio (better yet the whole machine)
- add new connection in server explorer to your database and try debugging again

This may solve the problem. At least it helped in my case.

Mika

The need to optimize rises from a bad design.

My articles[^]

Hi
I have been experimenting with ways of extracting relational data in forms of xml from SQL Server 2005. I have created some views containg and ID integer field and a XML field. The ID field is what i filter on when selecting data, and the XML field contains the actual data.

I came across a situation where the XML data was recursive in its nature. I have a product Hierarchy where products consists of a list of SubProducts. I solved this by creating a SQL Function which is called recursively in my view, retreiving all products that have the current product as parent. Something Like this:

SELECT
ProductID,
ProductName,
ParentID,
dbo.GetSubProducts(ProductID)
FROM
view_Product

This worked fine with small amount of data, but ended up being very slow when there was a medium amount of data.

I tried refactoring my view to use CTE, cause it seems the way to go.
The problem is i get a folder like structure, and not a XML like structure.

Like this:
Product1
Product2
Product1/Product2
Product1/Product2/Product3
Product1/Product2/Product4
Product3
Product4

What I wanted was like this:
Product1/Product2/Product3+Product4
Product2/Product3+Product4
Product3
Product4

I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.

I have not posted any sql as i have no real solution to the issue.
I hope you understand the problm anyhow.

Michael

Perhaps if you provide the CTE SQL you used to generate the incorrect result, it would be easier to see the problem.

The need to optimize rises from a bad design.

My articles[^]

I created a full example including tables, views and data required to test the SQL.

-------------------------------------------------------------------------------------------------
---- DATA STRUCTURE PREPERATION -------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-- Create a Test Schema
CREATE SCHEMA TEST
GO

-- Create a table with parent/child relationship.
CREATE TABLE [TEST].[Product](
[ID] [int] NOT NULL,
[Name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentID] [int] NOT NULL,
[ParentName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [PK_TEST_Product_1] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[ParentID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-- Populate table with sample data.
INSERT INTO [TEST].[Product] VALUES (26,'TV',0, NULL)
INSERT INTO [TEST].[Product] VALUES (43,'Buy Settopbox',47,'Settopbox Group')
INSERT INTO [TEST].[Product] VALUES (47,'Settopbox Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (48,'Rent Settopbox',47,'Settopbox Group')
INSERT INTO [TEST].[Product] VALUES (82,'DR1',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (83,'DR2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (84,'Sverige TV1',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (85,'Sverige TV2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (88,'TV2',98,'Plus TV')
INSERT INTO [TEST].[Product] VALUES (97,'Basic TV',100,'Standard TV Group')
INSERT INTO [TEST].[Product] VALUES (98,'Plus TV',100,'Standard TV Group')
INSERT INTO [TEST].[Product] VALUES (100,'Standard TV Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (124,'Special TV Group',26,'TV')
INSERT INTO [TEST].[Product] VALUES (152,'Oprettelse TV',26,'TV')
INSERT INTO [TEST].[Product] VALUES (698,'SmileSport pakke',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (699,'HD Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (701,'Discovery Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (702,'Kids Package',124,'Special TV Group')
INSERT INTO [TEST].[Product] VALUES (82,'DR1', 97, 'Basic TV')
INSERT INTO [TEST].[Product] VALUES (83,'DR2', 97, 'Basic TV')

-- Create view that selects from the table and defines an xml column.
CREATE VIEW [TEST].[view_Xml_Product]
AS
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName,
(
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName
FOR XML PATH('Product'), TYPE
)
AS [xml]
FROM [TEST].[Product] P WITH (NOLOCK)
GO
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------
---- THE RECUSIVE CTE THAT CREATES RECURSIVE XML DOCUMENTS ------------------------------
-------------------------------------------------------------------------------------------------
WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml])
AS
(
SELECT
P.ID,
P.[Name],
P.ParentID,
P.ParentName,
CAST(P.ID AS Varchar(200)) AS [Path],
P.[xml] AS [xml]
FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)

UNION ALL

SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
CAST(RTRIM(P2.ID) + '->' + P3.[Path] AS varchar(200)) AS [Path],
(
SELECT
P2.ID,
P2.[Name],
P2.ParentID,
P2.ParentName,
P3.[xml] AS SubProductList
FOR XML PATH('Product'), TYPE
) AS SubProductList
FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)
INNER JOIN ProductTree AS P3 ON P2.ID = P3.ParentID
)
SELECT * FROM ProductTree ORDER BY 1
OPTION (MAXRECURSION 50)
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------

When executing the CTE you recieve a "folder-like" structure as shown in the Path field. Actually the XML column has the same structure as the path column.

[b]Like this:[/b]
Product1
Product2
Product1->Product2
Product1->Product2->Product3
Product1->Product2->Product4
Product3
Product4

and the xml:
<Product>
<ID>26</ID>
<Name>TV</Name>
<ParentID>0</ParentID>
<SubProductList>
<Product>
<ID>124</ID>
<Name>Special TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>701</ID>
<Name>Discovery Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
</SubProductList>
</Product>
</SubProductList>
</Product>

[b]What I wanted was like this:[/b]
Product1/Product2/Product3+Product4
Product2/Product3+Product4
Product3
Product4

and the xml (not complete, but show the idea):
<Product>
<ID>26</ID>
<Name>TV</Name>
<ParentID>0</ParentID>
<SubProductList>
<Product>
<ID>124</ID>
<Name>Special TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>698</ID>
<Name>SmileSport pakke</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>699</ID>
<Name>HD Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>702</ID>
<Name>Kids Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
<Product>
<ID>701</ID>
<Name>Discovery Package</Name>
<ParentID>124</ParentID>
<ParentName>Special TV Group</ParentName>
</Product>
</SubProductList>
</Product>
<Product>
<ID>100</ID>
<Name>Standard TV Group</Name>
<ParentID>26</ParentID>
<ParentName>TV</ParentName>
<SubProductList>
<Product>
<ID>97</ID>
<Name>Basic TV</Name>
<ParentID>100</ParentID>
<ParentName>Standard TV Group</ParentName>
</Product>
<Product>
<ID>98</ID>
<Name>Plus TV</Name>
<ParentID>100</ParentID>
<ParentName>Standard TV Group</ParentName>
</Product>
</SubProductList>
</Product>
</SubProductList>
</Product>

I want one row per product having all subproducts beneath it. A complete tree when looking at a specific product no matter the level in the hierarchy.

I hope you understand what I intend to do.

Michael Vivet

Hi,

First of all, you did a great job in creating this example! You've really put effort into it.

I've investigated this a bit and so far I haven't used the xml portion in any way. I think it's in this stage easier to play only with the path.

What I've done:
- created a test view based on the recursive query (it's easier to handle it in the next stage)
- in the CTE I locked the root to those records having parentid 0. In the previous version all the rows acted as parents
- changed the path to build the string in reverse order to gain correct path
- changed the join in second part of the cte (it was in wrong order)

So now the path is showing better results and also the parentid is giving the previous parentid, not the original parentid.

The view looks like this:

CREATE VIEW vw_test AS
WITH ProductTree (ID, [Name], ParentID, ParentName, [Path], [xml]) AS
(
SELECT P.ID,
       P.[Name],
       P.ParentID,
       P.ParentName,
       CAST(P.ID AS Varchar(200)) AS [Path],
       P.[xml] AS [xml]
FROM [TEST].[view_Xml_Product] AS P WITH (NOLOCK)
WHERE  p.parentid=0
UNION ALL
SELECT P2.ID,
       P2.[Name],
       P2.ParentID,
       P2.ParentName,
       CAST(RTRIM(P3.[Path]) + '->' + cast(P2.ID as varchar(200)) AS varchar(200)) AS [Path],
       (SELECT P2.ID,
               P2.[Name],
               P2.ParentID,
               P2.ParentName,
               P3.[xml] AS SubProductList
               FOR XML PATH('Product'), TYPE) AS SubProductList
FROM [TEST].[view_Xml_Product] AS P2 WITH (NOLOCK)
INNER JOIN ProductTree AS P3 ON P3.ID = P2.ParentID
)
SELECT * FROM ProductTree

The next phase was to remove the rows that have 'children'. I did it in a quick and dirty way but here's the query:

SELECT * 
FROM  vw_test a 
WHERE NOT EXISTS( SELECT 1 
                  FROM  vw_test b
                  WHERE b.[Path] LIKE a.[Path] + '%' 
                  AND   b.[Path] != a.[Path]) 
ORDER BY 5

The next step would be to eliminate the rows that are sub level row and have the same parentid. In this process those rows should be combined so that the id part should be added to the previous row. For example rows:

ID  Name         ParentID ParentName       Path
698 SmileSport pakke  124      Special TV Group	26->124->698
699 HD Package        124      Special TV Group	26->124->699
701 Discovery Package 124      Special TV Group	26->124->701
702 Kids Package      124      Special TV Group	26->124->702

should be converted to single row having path:

26->124->698 + 699 + 701 + 702

One way to do this is to create a stored proc which uses this select and then using cursor combine rows.

One thing though. I'm quite pessimistic with the efficiency. So basically your original idea of using a function may be better when bottlenecks in that function are eliminated.

Also few observations.
- You shouldn't use 0 for the root parent id. Use NULL instead.
- Consider creating a foreign key between parentid and id.
- Don't save the parentname in the table. Fetch it when needed so you won't have possible mismatches in data and you don't have to update several places when name is changed.

Does this help you forward?

Mika

The need to optimize rises from a bad design.

My articles[^]

Thanks for the reply.

My objective was too implement a solution which enables me to end up with a view, returning the required xml. Your suggestion about creating a stored procedure that uses a cursor, doesnt comply with that. Further the performance would be really bad.

I thought that my original idea of using a recursive function, and select that in a view might be the fastest solution. I merely tried to investigate whether CTE could be used to increase the performance. So for now i will stay with the recursive function.

If you find a better solution please post in this thread

Once again thanks for the effort.

Yes, as I mentioned, I was worried about the performance too.

I wouldn't believe that the performance of a recursive function is a problem as long as it's constructed properly. I've used this techique for several years without any problems.

If something comes in my mind, I'll post it. Also if you have any questions about recursive proc implementation, open a new thread and we'll try to help.

Mika

The need to optimize rises from a bad design.

My articles[^]

Whats the best sql Statement to check if a table is empty?

Thanks ONeil

select top 1 from ... is one way. Select count(*) from is another - could be slow though if the re are a lot of records.

Bob
Ashfield Consultants Ltd

In sql server 2k i've actually seen Count(*) return a 1 when counting an empty table, had it happen several times, cant remember why it happened anymore.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

Never seen it myself, must have been fixed in an early service pack - after all, count is one of the fundamental functions.

Bob
Ashfield Consultants Ltd

I saw it happen in SP4, wish i could remmeber how and why it showed up.

Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.

hi all,
i have a small code of piece which create backup of Sql server database. It works well but in some cases(when i select target path to desktop folder or documents and settings folder or program files folder) it gives following error
"Can not open backup device" & Target Path & . "Operating system error 5(Access is denied.).Backup database is terminating abnormally"

Please suggest for this error ?

Are these folders located on the server, or are you selecting a folder in your local desktop? Have you checked whether you got write-permissions on the folder?

eddyvluggen wrote:
Are these folders located on the server, or are you selecting a folder in your local desktop?

This folder is located on local desktop(or program files or documents and settings).I have no write permission on this folder. Whether there are any method to check the permission of the folder and change the permission of the folder using vb.net ?

sunil goyalG wrote:
This folder is located on local desktop

In that case you'll need to backup on the server, and copy the backup from the server-machine to your local machine. Can you make a backup to the root of C: on the servermachine?

sunil goyalG wrote:
I have no write permission on this folder.

You'll need permission. Ask your network-administrator if you can't change the permissions.

Hope this helps,

eddyvluggen wrote:
In that case you'll need to backup on the server, and copy the backup from the server-machine to your local machine

in my present case, Server and client is same

eddyvluggen wrote:
You'll need permission. Ask your network-administrator if you can't change the permissions.

How can i do this thing programmatically ? It is a stand alone computer

sunil goyalG wrote:
in my present case, Server and client is same

In that case, you should be able to backup to C:\.. Have you tried whether you can backup there? It's important because it rules out any other problems, like a full harddisk and the likes.

sunil goyalG wrote:
How can i do this thing programmatically ?

You can't "grant" yourself permissions from code that easily, that would be a security-hole. If you can't copy a text-file to the appropriate folder, then you also can't backup there. Can you check whether you can write a textfile to the folder that you want to backup to?

There should be a local administrator-account on the machine. That's the one that's used to set/check the permissions.

eddyvluggen wrote:
you should be able to backup to C:\.. Have you tried whether you can backup there?

i am able to take backup on root(c:\) or any other folder except(desktop,program files & documents and Settings folder)

eddyvluggen wrote:
Can you check whether you can write a textfile to the folder that you want to backup to?

i am able to create any text file to above specified folder but whenever i try to create backup file then it gives error.

any suggestion ?

Sounds like you do have write-permissions then Smile | :)

It might be that the path is too long, or the spaces in the path might cause problems. Can you backup to "C:\PROGRA~1"?

eddyvluggen wrote:
Can you backup to "C:\PROGRA~1"?

No. i can not backup to this path. Also not on desktop and documents and settings folder. Except these folder i can take backup in any folder programmatically.