an sql command that doest work in a page

Hello,

I have a sequense of sql commands in order to recursively update a table that has parents/childs

After I create a temporary table, I need to run an sql command that for some reason is not working. No errors, the command is actually excecuted, but I beieve the rowcount is 0 from the beggining

Here is the command:

Dim

InsertConnectionAs Data.SqlClient.SqlConnection =New System.Data.SqlClient.SqlConnection("Server=myServer;User ID=myUser;pwd=myPSW;Database=myDatabase")Dim SqlInsertCommandAs Data.SqlClient.SqlCommand =New Data.SqlClient.SqlCommand("while @.@.rowcount > 0 " _

&

"begin INSERT INTO submenu" _

& uid &

" (pageid,parentid) SELECT y.pageid , y.parentid FROM submenu" & uid _

&

" i INNER JOIN page y ON y.ParentId = i.pageID LEFT OUTER JOIN subMenu" _

& uid &

" i1 ON i1.pageId = y.pageId WHERE(i1.pageID Is NULL) " _

&

"end", InsertConnection)

InsertConnection.Open()

SqlInsertCommand.ExecuteNonQuery()

InsertConnection.Close()

SqlInsertCommand =

Nothing

If I insert any other SQLcommand there it is excecuted normally.

The command I have is excecuted fine using sql server manager.

Is there any way that a command is excecuted in the SQL manager but not in a page...??

Any ideas would be great...

Thank you

Hello my friend,

I would not use @.@.rowcount outside of Enterprise Manager. Could you describe your database structure and what you are trying to insert. No need to send vb code, just the SQL or some comments on the steps and I can send you the correct SQL that will work from wherever it is used.

Kind regards

Scotty

|||

You use @.@.RowCount in first line of your query but this returns number of rows affected by last select statement in current SQL thread, but your thread is starting so it returns always 0 so your loop is never executed.

You should populate your temporary table in the same select statement to work correctly. The best solution is to create SQL stored procedure which will do all your work at one shot if you can do it.

Thanks

JPazgier

|||

Hi,

The software is a sitebuilder. The particular table holds the page stucrure of each site.

The table is this one:

----

pageid int identify

siteid int

pagename nvarchar(200)

parentid int

----

I need to update / delete all of the tree when the user wants to update or delete a top element. The number of levels is not limited.

I managed to do it, using a variable. The "problem" is that I set it to 1000 times. So if someone has more than 1000 pages under the parent, if will fail. And it's not right in the first place.

The thing is, that this worked fine when I was on an other server that used MS SQL 2000. I didn;t find any differences searching the web from 2000 to 2005

jpazgier, why does it work then when I excecute it using SQL manager..? Isn't this weird? I mean, if the rowcount is 0 from the beggining in the application, should't it be 0 in the SQL manager too?

Thank you

|||

Hello my friend,

I realize now what you are trying to do and I have the answer for you and this will work no matter how many levels you have (no 1000 limit). Run the following SQL, but change tblTree to the name of your table (I did not know what you have called it): -

CREATE FUNCTION dbo.fnGetPages
(
@.PageID AS INT
)

RETURNS @.ChildPageIDs TABLE(PageID INT)

AS

BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM tblTree WHERE ParentID = @.PageID

DECLARE @.TempChildPageIDs TABLE(PageID INT)
INSERT INTO @.TempChildPageIDs (PageID)
SELECT PageID FROM @.ChildPageIDs ORDER BY PageID

DECLARE @.ChildPageID AS INT
SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)

WHILE (@.ChildPageID IS NOT NULL)
BEGIN
INSERT INTO @.ChildPageIDs (PageID)
SELECT PageID FROM dbo.fnGetPages(@.ChildPageID)
DELETE FROM @.TempChildPageIDs WHERE PageID = @.ChildPageID

SET @.ChildPageID = (SELECT TOP 1 PageID FROM @.TempChildPageIDs)
END
RETURN
END

Now to get all child IDs of page 1 (either direct children of 1, and also children of ones that are children of 1, and so on) I run the following: -

select PageID from dbo.fnGetPages(1)

To delete the page and all of its children I run the following 2 commands: -

DELETE FROM tblTree WHERE PageID IN (SELECT PageID FROM dbo.fnGetPages(1))

DELETE FROM tblTree WHERE PageID = 1

Kind regards

Scotty

|||

Thanks Scotty,

The code to create the function is only run once right?

Then I just select update or do whatever I need to do using the function right?

Thanks

|||

Yes that is correct. You only run the function SQL once. You only need to run this again if you decide to use this functionality within a new database.

You just need to run the commands that use the function and you should be fine.

Kind regards

Scotty

AMO Class library: MdxScript Commands

Hi,

I have a question regarding the AMO Class MDX script.

As desribed here http://msdn2.microsoft.com/en-us/library/microsoft.analysisservices.mdxscriptcollection_members.aspx there seems to be a collection with several MDX scripts and a marker for the active script collection...

Does anyone know if BIDS always uses collectionitem(o) from the enumeration?
or is it possible to handle more than one item of the collection in BIDS? and if it is possible - where is the button to change the shown item in BIDS?

Thanks in advice!

Cheers,
Markus

I'm not sure about the first part of your question, but I do know that it isn't possible to see more than one item from the collection in BIDS. Sorry...

Chris

|||

Hello Chris,

thanks for the answer - hope that someone could clarify, the usage of the collection...

Have you seen this?

http://www.european-pass-conference.com/

Hope that we will meet there...

Cheers,

Markus

|||

hello Markus,

if in your cube, you have an MDXScript object with DefaultScript property set to true, then Calculations tab in BIDS's Cube Editor, should pick up that script. In case there is no such script, then then first script in the collection, would be chosen. It is not possible to switch to a different script in BIDS. BIDS will never create multiple scripts itself.

hope this helps,

|||

No, I hadn't seen that - thanks! I'm pleased to see that PASS Europe is back, and I'll definitely try to be there.

Chris

Am I being role happy?

I'm working on the security portion of this vb app. In the application the
user is presented with a list of commands that can be executed on a piece of
data.

My client wants to be able to block certain users from seeing certain
commands on their screen.

The commands are loaded into a listview from a table.

I've created a role for each combination of command/province(A user may be
able to execute a command on an Ontario piece of data but not a Quebec
piece).

I just modified the query that loads the list box so that it includes an
IS_Member with the command/province combination.

The roles were easy to create(sql query to create the sp_addrole commands).
And the query modifications were easy as well. Is_Member barely affects
query times(very fast).

I've also created roles that contain these roles so a user only has to be
added to one role to get access to large groups of commands.

I may have one problem though. I've created hundreds of roles on the
server. Is that a problem? Can anyone tell me if there's a better way of
doing this?

Thanks in advance.
J
P.S. The application has just under 500 users and is going to hit 1000 in a
couple of years.Me (me@.here.com) writes:
> I've created a role for each combination of command/province(A user may be
> able to execute a command on an Ontario piece of data but not a Quebec
> piece).
> I just modified the query that loads the list box so that it includes an
> IS_Member with the command/province combination.
> The roles were easy to create(sql query to create the sp_addrole
> commands).
> And the query modifications were easy as well. Is_Member barely affects
> query times(very fast).
> I've also created roles that contain these roles so a user only has to be
> added to one role to get access to large groups of commands.
> I may have one problem though. I've created hundreds of roles on the
> server. Is that a problem? Can anyone tell me if there's a better way of
> doing this?

The one thing that I don't really like this design is that you use
SQL Server's own mechanisms to control data access within the
application. I would prefer to have my own authorization tables.
This design can confuse a DBA who thinks roles are for control access
to tables and T-SQL commands.

Then again, if it ain't broke...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp