Database

T-Sql programming in Sql server 2005

22-Jan-09 18:12

If I understood you correctly, your query could be something like:

select sum(rnkmarks)
from your table
group by rnkartid

See SUM[^] and GROUP BY[^] for more details.

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

maheesh22-Jan-09 17:33

maheesh

22-Jan-09 17:33

Hi,
I'm new to T-Sql programming in Sql server with C++ Application.
Plz provides some examples.

With Regards
Mahesh

Re: T-Sql programming in Sql server 2005

Wendelius22-Jan-09 18:14

Transfer the data from xls sheet to sqlserver using SSIS

22-Jan-09 18:14

You could start for example here: Connecting to a Data Source (ADO.NET)[^]

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

bruze22-Jan-09 3:20

bruze

22-Jan-09 3:20

Hi,

I need to transfer the data from xls sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise I have many xls files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution .

thanks

Re: Transfer the data from xls sheet to sqlserver using SSIS

Wendelius22-Jan-09 3:30

Re: Transfer the data from xls sheet to sqlserver using SSIS

22-Jan-09 3:30

AFAIK you would have to do this sheet by sheet. For more info: Excel Source[^]

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

Jon_Boy22-Jan-09 3:56

Jon_Boy

22-Jan-09 3:56

Are you kidding or serious?

Any suggestions, ideas, or 'constructive criticism' are always welcome.

"There's no such thing as a stupid question, only stupid people." - Mr. Garrison

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.

BobClarkSQL22-Jan-09 3:15

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. [modified]

22-Jan-09 3:15

I am using MySQL version 5.1 on each of the Networked PC's. There are 7 i all. How do I Backup all the Databases on Server 1 and Append Restore the Backup on Server 2 so it gets all the changes from the First Server, I will do this with 7 Servers in Progression so they Loop Every 15 Minutes. I am using Windows XP professional on each of the 7 Servers and the all have the same Databases. Each Server would have all the New Records in the Tables from the Previous Server. I have tried using Replication on them all but it does not update whena recors is added or changed on any of the 7 Servers. When records are added or changed on any of the 7 Servers, the other 6 must be Updated with the change or addition. I appreciate any suggestions or help or Topology changes.

Regards
BC

Wendelius22-Jan-09 3:27

22-Jan-09 3:27

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

Not sure what you're trying to achieve with this. If you constantly restore modifications using backups from one database to another, you'll have a stand-by server. However, you're unable to modify anything on the stand-by (or 7 stand-by's in this case). Is this really what you want?

BobClarkSQL wrote:
I have tried using Replication on them all but it does not update whena recors is added or changed on any of the 7 Servers

AFAIK MySQL doesn't support multi-master replication (or in other words merge replication). The only thing that comes close is clustering[^], but that's intended for distributed scenarios.

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

modified on Thursday, January 22, 2009 9:39 AM

BobClarkSQL22-Jan-09 4:00

22-Jan-09 4:00

Yes. I have to have a solution that does not require any licensing or cost. The Information has to be updated automatically with some frequency (say every 5 to 15 minutes) so that when all 7 Stand Alone Servers have the same information and new records and record changes in all of there databases which would be identical in nature. It can be accomplished with any free tool or package that works with MySQL 5.1. They all communicate with each other and I granted access for each of them for Replication and set up Relay and Binary Log Files as is required with Replication but none of the servers get updated when new records are entered in any server or changed. If a Backup is done on any Server it takes less than a minute. If it can be Restored on the Next Server in the Change and Vice Versa, it should be OK as long as it can be deployed automatically and it is staggered so there is no Writing Conflicts.

Regards
BC

Wendelius22-Jan-09 4:08

22-Jan-09 4:08

I'm sorry, but I don't get it. Consider for example following timeline:

Server A   Server B
--------   ---------
insert 1     |
   |       insert 2
   |         |
Backup 1     |
   |       insert 3
   |         |
   |       Restore 1

When Backup 1 is restored in Restore 1, inserts 2 and 3 are lost.

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

BobClarkSQL22-Jan-09 4:19

22-Jan-09 4:19

Data Loss is not critical. If Migration is used every 5 minutes they all synchronize with each other fine. Each server is in a stand alone world. The other servers information is not critical as long as it gets there eventually.

Regards
BC

Wendelius22-Jan-09 4:35

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

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

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

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

Insert query and Cursor in same stored procedure

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.

Jay Royall21-Jan-09 23:28

Re: Insert query and Cursor in same stored procedure

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)

Wendelius21-Jan-09 23:33

Re: Insert query and Cursor in same stored procedure

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[^]

Jay Royall21-Jan-09 23:49

Re: Insert query and Cursor in same stored procedure

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

Wendelius22-Jan-09 0:00

Re: Insert query and Cursor in same stored procedure

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[^]

Jay Royall22-Jan-09 0:05

Re: Insert query and Cursor in same stored procedure

22-Jan-09 0:05

Great, fixed!! School boy error aye?!

Thanks a lot Smile | :)

Wendelius22-Jan-09 0:13

Re: Insert query and Cursor in same stored procedure

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[^]

Jay Royall22-Jan-09 0:21