Showing posts with label names. Show all posts
Showing posts with label names. Show all posts

Tuesday, March 27, 2012

CURSOR is driving me insane!

We have a tree structure containing section names. Each node is asection name and each section can have subsections. I have to copy thetree structure but need to maintain the parent-child relationshipestablished within the id / parent_id fields. How do i acheive this?

For example i have the tree
Section 1
|-Section 1.1
Section 2
|-Section 2.1

The"Section" table contains 3 fields: id, parent_id, and caption. ID isthe identity of the section record and parent_id contains NULL or theID of this record's parent to create a child. So "Section 1" (id=1,parent_id=null), "Section 2" (id=2, parent_id=null), "Section 1.1"(id=3, parent_id=1), "Section 2.1" (id=4,parent_id=2).

I wouldlike to copy this sucture to create 4 new sections but they need tomaintain their id/parent_id relationships BUT with new IDs. For this icreated the following stored procedure:
------
CREATE PROCEDURE [dbo].[CopySection]
AS
-- Declare a temporary variable table for storing the sections
DECLARE @.tblSection TABLE
(
id int,
parent_id int,
caption varchar(max),
)

DECLARE @.newAgendaID int, @.newSectionID int;
DECLARE @.tid int, @.tparent_id int, @.tcaption varchar(max);

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Copy the desired sections into the local temp variable table
INSERT INTO @.tblSection SELECT id, parent_id, caption FROM tblSection ORDER BY parent_id;

-- Using a cursor, step through all temp sections and add them to the tblSection but note its new ID
DECLARE c1 CURSOR FOR SELECT * FROM @.tblSection ORDER BY parent_id FORUPDATE OF parent_id;
OPEN c1;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
WHILE @.@.FETCH_STATUS = 0
BEGIN

-- Insert the new Section and record the identity
INSERT INTO tblSection (agenda_id, parent_id, caption) VALUES (@.tparent_id, @.tcaption);
SET @.newSectionID = SCOPE_IDENTITY();

-- Update the temp variable table with the new identity from the newly created real section in tblSection
-- Update all temp variable records to point to the new parent_id
UPDATE @.tblSection SET parent_id = @.newSectionID WHERE parent_id = @.tid;

FETCH NEXT FROM c1 INTO @.tid, @.tparent_id, @.tcaption;
END

CLOSE c1
DEALLOCATE c1

END
------

Thecritical "UPDATE @.tblSection" part doesnt seem to update the tempvariable table with the @.newSectionID (the actual section identityobtained after inserting a real record into the tblSection table). Soin the end the inserted records into tblSection still point to theincorrect parent_id instead of the copied record's parent_id.

Maybe I'm using CURSOR incorrectly or not setting a parameter so that it refreshes its recordset? I've tried using both table variables and temp tables but no luck.

I've only quickly scanned your code, but it makes no sense that the UPDATE statement does not work. It should. And since it's not, that means your WHERE condition is not being met.

So, backtracking and looking at your INSERT statement, you have 3 columns listed yet are inserting only 2 values. I am guessing that this is the cause of your problem. You are assigning the @.tcaption value to the parent_id column:

INSERT INTO tblSection (agenda_id,parent_id, caption) VALUES (@.tparent_id,@.tcaption);


|||Great stuff. Thanks Terri. All is good.

Sunday, March 25, 2012

Cursor from diff table -challenge

I have 17 tables with names as Breaktable1, Breaktable2,... till
Breaktable17

i make a query like this

set nocount on

Declare @.sEventtable varchar(20)
Declare @.sstartzoneid varchar(20)
Declare @.ssttopzoneid varchar(20)

Select @.sstartzoneid ='1'
Select @.ssttopzoneid = '17'

select @.sstartzoneid = convert(smallint,@.sstartzoneid)

if @.sstartzoneid<> 0
DECLARE Tablecursor FOR <---> ( error here )
select status, sum(cost)
FROM "breaktable " + @.sstartzoneid
where breakdate=DATEDIFF(day,'08/12/1960','03/29/2003')
group by status

CLOSE tableCursor
DEALLOCATE tableCursor

can somebody help ??You can't use a variable that way in the DECLARE CURSOR. You may have to declare the table dynamically.

Is now a good time to ask why you've got 17 tables when it seems that 1 would do quite nicely?

-PatP|||What do you want to do with the data accessed by the cursor? You can create a view comprised of all 17 tables with an artificially created field that would identify which table the data is coming from, and then do your cursor definition based on the value of that field. And of course, I agree with Pat 100%.|||I just want to read it for each day
so that i can just change the date everyday i run the query

Trust me it needed the 17 tables

I am from oracle so a little tizzy in sql|||Sorry, I don't trust anybody. I've seen 17-table implementation here, made by Anderson Consulting, and the company was paying them $180/hour. Just by looking at the names I can tell you that the design was not done by the person who understands either business or db design. How can I trust?|||I still can't see for the life of me any reason to have either 17 tables or a cursor. If those make you more comfortable, I guess that works Ok for me. I just can't see any reason for them.

-PatP|||I agree , but i did not designed it i just have to use it

so how do i make a query that will query all 17 tables and give me sum of cost and status

cursor or no cursor ?

table name break1 to break17|||I'd use:CREATE VIEW dbo.BigBreak AS
SELECT 1 AS table_id, * FROM dbo.breaktable1
UNION ALL SELECT 2, * FROM dbo.breaktable2
UNION ALL SELECT 3, * FROM dbo.breaktable3
UNION ALL SELECT 4, * FROM dbo.breaktable4
UNION ALL SELECT 5, * FROM dbo.breaktable5
UNION ALL SELECT 6, * FROM dbo.breaktable6
UNION ALL SELECT 7, * FROM dbo.breaktable7
UNION ALL SELECT 8, * FROM dbo.breaktable8
UNION ALL SELECT 9, * FROM dbo.breaktable9
UNION ALL SELECT 10, * FROM dbo.breaktable10
UNION ALL SELECT 11, * FROM dbo.breaktable11
UNION ALL SELECT 12, * FROM dbo.breaktable12
UNION ALL SELECT 13, * FROM dbo.breaktable13
UNION ALL SELECT 14, * FROM dbo.breaktable14
UNION ALL SELECT 15, * FROM dbo.breaktable15
UNION ALL SELECT 16, * FROM dbo.breaktable16
UNION ALL SELECT 17, * FROM dbo.breaktable17
GOI'm not sure how well the optimizer will process it, but I think that it will do Ok. You'll definitely need to test it first.

-PatP|||i went like this but takes about 30 mins to run

select status, sum(cost) as zone1
FROM event1
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status

select status, sum(cost) as zone2
FROM event2
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by status

TILL 18

But takes about 35 mins for each run and slows all other user

and then i have to do shut down restart to make it work nicely ??

and the reason for 18 tables is that an event occurs and needs to be inserted(recorded) at 18 times at that exact moment (millisecond)|||How did you jump from 17 to 18 tables? Why on earth do you need to record extremely time sensitive information 18 different times?

-PatP|||we are an adsales company that insert ads on cable

it has to insert those breaks in 18 zones at the same exact time

whether its successfull or not it does an insert in each table.|||I am sure you can have a cursor within a cursor to do that|||A cursor within a cursor ...

I swear by the Holy book (guys here know what I mean) ... I would never even think of ever doing that ...|||we are an adsales company that insert ads on cable

it has to insert those breaks in 18 zones at the same exact time

whether its successfull or not it does an insert in each table.

Pat we got a CROSS THREAD JOIN going on here...

In any case a Timezone type column is all you needed...

Anderson Consulting...scrubs....

had to babysit everyone of them...

I saw one created a 7 level nested cursor...wondered why it ran 7 hours...|||besides the time their are about 15 other fields that needs to be inserted and they are different !!!!|||Are the structure (column type and order) the same for all (1..N) of the breakpoint tables? If not, could they be made the same?

-PatP|||the structure is the same

But about 40 people may be reading or writing
beside the auto writing these zones (18) do

so i taught its quiet busy

Tell me if some one is writing to a table lets say different rows does it locks the whole table

I understood as if they write to same rows than it will

Am I correct???|||No, you can have a practical infinity of inserts all going on at the same time without a problem in SQL-2000. This was a severe problem prior to SQL 6.5, it got much better with the IRL code in SQL 7.0, and it is a non-issue with SQL 2000.

In SQL-2000, you can only have one spid doing an UPDATE or a DELETE on any given row at any given time, but that is true of almost every database. We typically have 300-500 users simultaneously updating some of our heavily used tables at period end (but that is on pretty powerful hardware).

-PatP|||thanks but yes this is 6.5 i am working on
Perhaps then the designer was right ||||

I am not a fan of microsoft

For oracle
i would do a cursor very nicely

declare
i number
for i in 1 .. 17
loop
select *
from break + "i"|||As long as you aren't running 6.0 or earlier, you should be Ok. The problems were fixed (at least for the most part) in 6.5 as long as you don't explicitly turn IRL off!

This is just a suggestion, but 6.5 is at least five years old. That would be like using Oracle 7.0 (yes, there really was something before 7.3)! You really need to encourage your management to upgrade to current versions.

-PatP|||I don't understand what's wrong with this:

select table_id, status, sum(cost) as zone1
FROM dbo.BigBreak --event1 <--WHAT IS THIS?
where breakdate=DATEDIFF(day,'08/12/1960','04/15/2004')
group by table_id, status|||Nothing wrong actually
It was just a curiosity

I am about to upgrade to sql 2000

it usually takes about 4 months to get the things passed through accounts

thanks

Thursday, March 22, 2012

Cursor

Hi
I am creating a cursor which runs through a table of database names and
execute a script using xp_cmdshell on every database in that table.
Declare @.DataBaseName varchar(255)
Declare DBcursor CURSOR FOR
Select dbname From master..upgradedb
Declare @.command varchar(255)
OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @.DataBaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Upgrading.... ' + @.DataBaseName
set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
exec master..xp_cmdshell @.command
FETCH NEXT FROM DBCursor INTO @.DataBaseName
END
CLOSE DBCursor
DEALLOCATE DBCursor
I get the followinf results - Can anyone guide me in the right direction
please.
Upgrading.... QFM161R23AMEYAA
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)
Upgrading.... QFM161R23GLSCHOOLS
output
----
----
----
--
usage: osql [-U login id] [-P password]
[-S server] [-H hostname] [-E trusted connection]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w columnwidth]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-L list servers] [-c cmdend] [-D ODBC DSN name]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-n remove numbering] [-m errorlevel]
[-r msgs to stderr] [-V severitylevel]
[-i inputfile] [-o outputfile]
[-p print statistics] [-b On error batch abort]
[-X[1] disable commands [and exit with warning]]
[-O use Old ISQL behavior disables the following]
<EOF> batch processing
Auto console width scaling
Wide messages
default errorlevel is -1 vs 1
[-? show syntax summary]
NULL
(19 row(s) affected)> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
c:\temp\Complete(7-9).sql'
If you had printed out the command before trying to execute it for the first
time, you'd have noticed that there's a syntax error in it. A space is
missing before the -i switch.
But I'm just guessing here.
ML|||Check if sp_MSforeachdb System Stored Procedure can be used.
This will simplify the code. U may not require cursor at all.
Rakesh
"Jaco" wrote:

> Hi
> I am creating a cursor which runs through a table of database names and
> execute a script using xp_cmdshell on every database in that table.
> Declare @.DataBaseName varchar(255)
> Declare DBcursor CURSOR FOR
> Select dbname From master..upgradedb
> Declare @.command varchar(255)
>
> OPEN DBCursor
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Upgrading.... ' + @.DataBaseName
> set @.command = 'osql -E -S jaco_ -d '+ @.DataBaseName + '-i
> c:\temp\Complete(7-9).sql'
> exec master..xp_cmdshell @.command
> FETCH NEXT FROM DBCursor INTO @.DataBaseName
> END
> CLOSE DBCursor
> DEALLOCATE DBCursor
> I get the followinf results - Can anyone guide me in the right direction
> please.
> Upgrading.... QFM161R23AMEYAA
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
> Upgrading.... QFM161R23GLSCHOOLS
> output
>
> ----
----
----
--
--
> usage: osql [-U login id] [-P password]
> [-S server] [-H hostname] [-E trusted connection]
> [-d use database name] [-l login timeout] [-t query timeout]
> [-h headers] [-s colseparator] [-w columnwidth]
> [-a packetsize] [-e echo input] [-I Enable Quoted Identifi
ers]
> [-L list servers] [-c cmdend] [-D ODBC DSN name]
> [-q "cmdline query"] [-Q "cmdline query" and exit]
> [-n remove numbering] [-m errorlevel]
> [-r msgs to stderr] [-V severitylevel]
> [-i inputfile] [-o outputfile]
> [-p print statistics] [-b On error batch abort]
> [-X[1] disable commands [and exit with warning]]
> [-O use Old ISQL behavior disables the following]
> <EOF> batch processing
> Auto console width scaling
> Wide messages
> default errorlevel is -1 vs 1
> [-? show syntax summary]
> NULL
> (19 row(s) affected)
>sql