Re: Insert query and Cursor in same stored procedure - Database Discussion Boards

Re: How do I Backup all the Databases on Server 1 and Append Restore the Backup on Server 2 so it gets all the changes in the First Server, I will do this with 7 Servers in Progression so they Loop Every 15 Minutes.

Wendelius22-Jan-09 4:35

Wendelius

22-Jan-09 4:35

So I guess you have 7 databases on each machine.

Anyhow, the backup is taken using Backup[^] command and then it's restored using Restore[^] command. You can schedule these using for example Windows Task Scheduler and a command script.

The need to optimize rises from a bad design.My articles[^]

BobClarkSQL22-Jan-09 4:40

BobClarkSQL

22-Jan-09 4:40

The backup must be done automatically on Server A and then Restored Immediately Afterwards on Server B until all 7 Servers have the Previous Servers Information. When Server B completes its Restore from Server A it must Start a Backup and complete the Sequence chain until all are updated automatically. There are 10 databases on Each Server that are identical in nature.

Regards
BC

BobClarkSQL22-Jan-09 4:52

BobClarkSQL

22-Jan-09 4:52

Only the information and new records from the Previous Server in the Change need to be Restored into the Next Servers Databases.

Wendelius22-Jan-09 5:06

Wendelius

22-Jan-09 5:06

Then you have to use --log-bin option and mysqlbinlog [^] utility.

What comes to the other question about the sequence of operations on different server, there's no native support for that in MySQL so you can for example create one centralized script which does all the backups and restore operations on all servers. Or if you want to create a script on each server you must create a mechanism how to synchronize the operations (using for example your master database to store information what is being done and what's next).

The need to optimize rises from a bad design.My articles[^]

BobClarkSQL22-Jan-09 5:42

BobClarkSQL

22-Jan-09 5:42

I am using a log-bin file on each of the Servers and Each Server is a Slave of the Previous Server with the Last One in the Chain is the the Sever for the Save Number 1. any Topology will be fine as long as all the data is the same in all the Servers at the end of the day.

Insert query and Cursor in same stored procedure

Jay Royall21-Jan-09 23:28

Jay Royall

21-Jan-09 23:28

Hi,

I have a stored procedure which firstly inserts a record into a table and then uses a cursor to update all the records in the table (there is a field called sequence, which specifies the order that the records are retrived, that needs to be updating).

The problem I seem to have is that the cursor updates all the records except for the one that has been newly inserted. Although my Insert statement comes before the cursor, it seems as though the record is being inserted after the cursor has finished.

Any ideas anyone?

(I am using SQL Server 2005)

Re: Insert query and Cursor in same stored procedure

Wendelius21-Jan-09 23:33

Wendelius

21-Jan-09 23:33

If you could post the code, it would greately help.

Perhaps you open the cursor before the insert statement or yu have where condition which eliminates the inserted row.

Another thing, I believe it would be much better, in terms of speed, if you would do the updating without cursors. Perhaps the cursor operation could be transformed to a set based update statemnt.

The need to optimize rises from a bad design.My articles[^]

Re: Insert query and Cursor in same stored procedure

Jay Royall21-Jan-09 23:49

Jay Royall

21-Jan-09 23:49

Thanks for your time.

I understand what you're saying about cursors being a performance hog, but there are going to be at most 30 records in this table so I didn't think using a cursor would have too much of an impact. Am I wrong in thinkign this?

Anyway, here is my procedure:

ALTER PROCEDURE [dbo].[spReportTabINSERT] 
	-- Add the parameters for the stored procedure here
	@prmName as varchar(30),
	@prmSequence as int
AS
BEGIN
	SET NOCOUNT ON;

	INSERT INTO
		tblReportTabs
		(
			[name],
			[Sequence]
		)
	VALUES
		(
			@prmName,
			@prmSequence
		)	

	-- update all  sequences
	DECLARE @ID uniqueidentifier
	DECLARE @Sequence int
	DECLARE @NewSequence int

	SET @NewSequence = 10

	DECLARE crsTabs CURSOR
	FOR
		SELECT
			tblReportTabs.[ID],
			tblReportTabs.[Sequence]
		FROM
			tblReportTabs
		ORDER BY
			tblReportTabs.[Sequence]

	OPEN crsTabs

	FETCH NEXT FROM crsTabs INTO @ID, @Sequence

	WHILE @@FETCH_STATUS = 0
	BEGIN
		UPDATE
			tblReportTabs
		SET
			tblReportTabs.[Sequence] = @NewSequence
		WHERE
			tblReportTabs.[ID] = @ID

		SET @NewSequence = @NewSequence + 10

		FETCH NEXT FROM crsTabs 
	END
END

CLOSE crsTabs
DEALLOCATE crsTabs

Re: Insert query and Cursor in same stored procedure

Wendelius22-Jan-09 0:00

Wendelius

22-Jan-09 0:00

The first observation is that in the end of the loop, you should have:

FETCH NEXT FROM crsTabs INTO @ID, @Sequence

instead of

FETCH NEXT FROM crsTabs

The need to optimize rises from a bad design.My articles[^]

Re: Insert query and Cursor in same stored procedure

Jay Royall22-Jan-09 0:05

Jay Royall

22-Jan-09 0:05

Great, fixed!! School boy error aye?!

Thanks a lot Smile | :)

Re: Insert query and Cursor in same stored procedure

Wendelius22-Jan-09 0:13

Wendelius

22-Jan-09 0:13

No problem.

Liqz wrote:
School boy error aye

It happens to everyone Smile | :)

Another thing, I didn't quite get the logic in your loop. You fetched the sequence but you didn't use it anywhere, was that intentional?

The need to optimize rises from a bad design.My articles[^]

Re: Insert query and Cursor in same stored procedure

Jay Royall22-Jan-09 0:21

Jay Royall

22-Jan-09 0:21

Again, you're right. I am updating the sequence with @NewSequence, but of course, I do not need to fetch it in order to do this. I will amend it.

Thanks again Smile | :)

Re: Insert query and Cursor in same stored procedure

Wendelius22-Jan-09 0:29

Wendelius

22-Jan-09 0:29

You're welcome Smile | :)

The need to optimize rises from a bad design.My articles[^]

Re: Insert query and Cursor in same stored procedure

Ennis Ray Lynch, Jr.22-Jan-09 6:23

Ennis Ray Lynch, Jr.

22-Jan-09 6:23

I must be off my rocker but I can't see why you are using a cursor for this logic.

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

If you don't ask questions the answers won't stand in your way.

Most of this sig is for Google, not ego.

Re: Insert query and Cursor in same stored procedure

Jay Royall22-Jan-09 6:31

Jay Royall

22-Jan-09 6:31

Because I need to loop through each record in a table and update a field based on an incrementing integer.

I'm failry new to all this so if you have any other ideas then I'm all ears!

Re: Insert query and Cursor in same stored procedure

Ennis Ray Lynch, Jr.22-Jan-09 6:36

Ennis Ray Lynch, Jr.

22-Jan-09 6:36

Well autonumbers are the way to do incrementing integers in Sql Server and sequences in Oracle (look-up either SCOPE_IDENTITY() or sequence NextVal depending on your poison of choice)

However, to get your code to work it seems to me:
1) Begin Transaction
2) Insert
3) UPDATE set sequence = sequence + 10 WHERE sequence > @sequence
4) UPDATE set sequence = sequence + 10 where id = @id
5) COMMIT

CURSORS are evil and should be avoided.

Re: Insert query and Cursor in same stored procedure

Jay Royall22-Jan-09 9:38

Jay Royall

22-Jan-09 9:38

Thanks for your response. I understand your concern about cursors but unfortunately I don't think your idea is going to work. The reason is because the insert statement is inserting a record with a sequence which isn't a multiple of 10, i.e. 15, 25, 35 etc. This then means that the newly inserted record is in the right place accoring to sequence number. The cursor then gets all the records ordered by sequence, and updates them back to numbers divisible by 10, including the newly inserted record (obviously, the sequence doesn't have to be multiples of 10, it could of easily have been 2, but still).

Again, I realise that cursors are not a good idea but I don't see any other way around it (other than doing it from my VB.NET application with multiple database calls which definately isn't a good idea). Also, there are going to be at most 30 records in this table so I thought that using a cursor wouldn't be too much of a problem.

Like I said, I am fairly new to this so any other suggestions you may have are welcomed.

Re: Insert query and Cursor in same stored procedure

Ennis Ray Lynch, Jr.22-Jan-09 9:46

Ennis Ray Lynch, Jr.

22-Jan-09 9:46

I highly recommend you reevaluate your logic in your application and database. I think you will find the answer should come to you. While I do not know what you are doing, I have a pretty good idea.

Re: Insert query and Cursor in same stored procedure

Jay Royall22-Jan-09 9:52

Jay Royall

22-Jan-09 9:52

Ok Thanks. I have just looked at your example again and now understand a little more where you're coming from and i think that it would acually work for my insert statement by changing one of the lines from

UPDATE set sequence = sequence + 10 where id = @id

UPDATE set sequence = sequence + 5 where id = @id

which is great and I will use this, thanks Smile | :)

But I have a similar stored procedure for doing an update on an existing record, that is, updating the sequence of one of the records already in the table. Maybe I will look into my logic further for this too.

Thanks again Smile | :)

Re: Insert query and Cursor in same stored procedure

Wendelius22-Jan-09 9:50

Wendelius

22-Jan-09 9:50

I understood that you're trying to do ordering. For example you have ordinals 1,2,3,4 and 5 then you add a new record which has the ordinal 3 so old ordinals 3,4 and 5 will be replaced with 4,5 and 6 respectively. If that's the case, you could simply:
- update all rows, set ordinal = ordinal + 1 where ordinal >= 3
- insert the new row with ordinal 3

The need to optimize rises from a bad design.My articles[^]

Re: Insert query and Cursor in same stored procedure

Jay Royall22-Jan-09 9:56

Jay Royall

22-Jan-09 9:56

That is actually a good and very simple idea too, why didn't I think of that!

Will try that when I get to work tomorrow!

Thanks Smile | :)

Re: Insert query and Cursor in same stored procedure

Wendelius22-Jan-09 10:05

Wendelius

22-Jan-09 10:05

No problem.

The need to optimize rises from a bad design.My articles[^]

Re: Insert query and Cursor in same stored procedure

Jay Royall22-Jan-09 21:31

Jay Royall

22-Jan-09 21:31

This works great when inserting a new record and then updating each sequence but not when changing an existing record's sequence and then updating all the other sequences. But, with my new found knowledge, I'm sure that I'll work it out!

Thanks again all for your help.

Re: Insert query and Cursor in same stored procedure

Wendelius22-Jan-09 21:36

Wendelius

22-Jan-09 21:36