Database

Indra PR22-Jan-09 21:06

Indra PR

22-Jan-09 21:06

I still don't know about the connection pooling Mika. Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time? Can you give me a little description about that, and what should I do to check it, or maybe a link of reference. Thanks before.

- No Signature Available -

Wendelius22-Jan-09 21:24

22-Jan-09 21:24

Indra PR wrote:
I still don't know about the connection pooling Mika

Connection pooling is quite well decsribed here: OLE DB, ODBC, and Oracle Connection Pooling [^].

Indra PR wrote:
Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time

If you have a lots of users simultaneously (thousands of them) or if you have limited maximum amount of connections, that's possible. You can check the max connection count using:

SELECT @@MAX_CONNECTIONS

You can find the maximum capacity specifications here: http://msdn.microsoft.com/en-us/library/aa933149.aspx[^]

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

Aman Bhullar22-Jan-09 21:17

Aman Bhullar

22-Jan-09 21:17

Indra PR wrote:
I've put the Connection.Close()

although, you have put close connection request after every command. Does your program make sure that connection.close() is executed every time.

I mean to say, If there is a error during the transaction, is close() method called.

try using code as

try
{
   Connection.Open();
   //Your Block
}
catch(Exception EX)
{
   //Exception handling code
}
finally
{
    Connection.Close();
}

One thing you should try checking is
1. When Your application starts giving the error reported, at that instance of time how many users are connected to your server and on the particular DB.

2. How many requests from the application are running in pool of DB

Indra PR22-Jan-09 21:40

Indra PR

22-Jan-09 21:40

Yep, my code is exactly as the same as yours. I still don't know about pool? Before, Mika also said something about DB pooling, or pooling connection. What is that? I've read the reference but still don't understand Laugh | :laugh:

I only know that DB pooling is a feature in ADODB to minimize cost of opening connection, we have to set it first or what? Confused | :confused:

- No Signature Available -

Aman Bhullar23-Jan-09 2:27

Aman Bhullar

23-Jan-09 2:27

What is the Edition of your SQL Server

actually i have a database table have four fields rnkid, rnkartid,rnkuserid and rnk marks i want sum all the marks which have same rnk art id . pls hel tell how

Indra PR23-Jan-09 19:30

Indra PR

23-Jan-09 19:30

SQL Server 2000 Enterprise Edition

- No Signature Available -

anujanujanuj22-Jan-09 17:52

anujanujanuj

22-Jan-09 17:52

actually i have a database table have four fields rnkid, rnkartid,rnkuserid and rnk marks i want sum all the marks which have same rnk art id . pls hel tell how

Re: actually i have a database table have four fields rnkid, rnkartid,rnkuserid and rnk marks i want sum all the marks which have same rnk art id . pls hel tell how

Wendelius22-Jan-09 18:12

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

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