Database

Re: Optimising stored procedure

21-May-07 22:19

Yes, there is. Optimization with a stored procedure, like all other optimizations, is different for each procedure. Make sure you don't do anything unnessesary and that you don't pull the same information multiple times unless you have to. There might be some useful tools, try a google search on that.

The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.

Ch_Shahzad iqbal21-May-07 23:01

Ch_Shahzad iqbal

21-May-07 23:01

Dear
Used Minimum,Temporary table, Minmum Joins, It will be optimize ur query

SQL Connection Troubles

BadKarma21-May-07 21:23

BadKarma

21-May-07 21:23

Hi,
I'm using VC++ to connect to a SQL Server. At first there was no problem.
But the program has grown and there are now 13 threads accessing the database.
Some too retrieve the data they need, while others put there data in it.
My question is the following: What is the best way to let this work?

a. use a single connection by all threads.
b. let every thread make its own connection.
c. some other solution ...

Can solution (a) give problems when using the same connection pointer in multiple threads.
Solution (b) seems logical but further investigation showed that u must make sure that there are enough concurrent user session on the SQL server. But does that mean that you need 13 CAL's or can you use a single CAL for the whole applications. I can't seem to find any answer to that question?

Thanks in advance

Kurt Pattyn

codito ergo sum

help - Delete Relationship

ekynox21-May-07 18:47

ekynox

21-May-07 18:47

Hi there,

Well I am starting to wade into the world of SQL however, I am stuck with a problem. At the moment I have two tables, product table and supplier table. In the product table I have a foreign key called supplierID which is the primary key of the supplier table. Using MS Access I have created a relationship between these two tables graphically, i.e. one to many relationship.

My problem is that I am trying to delete the supplierID column from the product table using queries.My problem is that I am not being allowed to delete the column supplierID from the product table due to an error:
"Cannont delete field 'supplierID'. It is part of one or more relationships".

My deletion query:
ALTER TABLE product DROP COLUMN supplierID

My question is how do i delete the existing relationship between the two tables I had created graphically ?

Any help will be highly appreciated.

thanks

EDIT:
I am interested in finding out how to delete the relationship programmatically Smile | :)

Dave Herren22-May-07 10:50

Dave Herren

22-May-07 10:50

Try

ALTER TABLE tblname DROP CONSTRAINT constraintname

topcoderjax - Remember, Google is your friend.

ekynox22-May-07 13:26

ekynox

22-May-07 13:26

thanks for your reply. After looking at your response I realised I made Homer mistake in writing up initial post, doh!!!. The query you stated I already know.

What I meant to ask was how do you determine the name of the relationship between two tables programmatically?

ScottM123-May-07 2:46

ScottM1

23-May-07 2:46

Hallo
You can't delete the column because as you said it is part of a foreign key constraint. In MS SQL 2K you can drop this column programmatically by first dropping the foreign key constraint. Access, however, does not appear to give the relationship constraint a name and therefore you can't drop it. As far as I can tell your best option is to use MS SQL 2K instead.

There are 10 types of people in the world, those who understand binary and those who dont.

ekynox25-May-07 15:21

ekynox

25-May-07 15:21

well I managed to workout a dirty hack that does the job. To remove the foreign key constraint from the table we need to determine the name of the relationship this is achieved by viewing the msysrelationship table. If you are in MS Access and enable the ability to view hidden objects and tables you will be able to view the msysrelationship table.

The msysrelationship table maintains relationship names as nameoftable1nameoftable2 i.e. if one table was product and the other was supplier then then relationship will productsupplier.

As long as you can access the msysrelationship table you are pretty much set. Then delete the relationship then drop the column easy!!!

ADo.Net Confuse

Amit Kumar G21-May-07 13:20

Amit Kumar G

21-May-07 13:20

I am new to asp.net 2.0 and i am badly confuse....Any help which can point me in a correct direction...

When we should use SqlDataSource and when we should ado.net datareader etc...What are the criteria to choose.....

Please help...

Amit

Amy

Re: ADo.Net Confuse

Sathesh Sakthivel21-May-07 15:37

Sathesh Sakthivel

21-May-07 15:37

The SqlDataSource control allows a developer to access data stored in any relational database that supports ADO.NET. It can use the System.Data.SqlClient provider to access a SQL Server database, the System.Data.OleDb provider, the System.Data.Odbc provider, or the System.Data.OracleClient provider to access Oracle. Therefore, the SqlDataSource is certainly not only used for accessing data in a SQL Server database.

In order to use the SqlDataSource, you simply provide a value for the ConnectionString property and specify a SQL command or stored procedure. The SqlDataSource control takes care of working with the underlying ADO.NET architecture. It opens the connection, queries the data source or executes the stored procedure, returns the data, and then closes the connection for you.

Regards,

Satips.

Re: ADo.Net Confuse

Amit Kumar G21-May-07 16:10

Amit Kumar G

21-May-07 16:10

Thanks...But when we should go for SQL DataSource option vs command/datareader/dataset in real world...

SQL and Progress Updates

Expert Coming21-May-07 12:54

21-May-07 12:54

I have a very basic upload/download application. The app uses a select statement to download and a insert statement to upload. The reason I include those is because I am not sure how to get a progress report from them, ie completion percentage. I may be uploading upward of 100megs and don't want the user to just sit there waiting. I have tested a 5meg file and it takes about 3 minutes to upload and does so correctly, but the user has no idea what is going on.

How do I get progress of the SQL read/write so I can update my progress bar?

Thanks in advance!

And yes, I did post this in the C# forums, but I realized this belongs here.

The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.

Dave Kreskowiak21-May-07 13:29

Dave Kreskowiak

21-May-07 13:29

Lets see the code you're using to do the INSERT. If you're using what I think you are, there is no progress reporting, and hence no progressbar possible.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Expert Coming21-May-07 20:38

21-May-07 20:38

szSQLCommand1 = "INSERT INTO Files(Name, Size) VALUES(@Name, @Size)";<br />
szSQLCommand2 = "INSERT INTO FileData(Data) VALUES(@Data)";

I am not stuck to this approach though, I am looking for the way to do it...

I have SQL Server 2005 Full Edition up on a remote server that I have full access to if that comes into play.

I don't know too much about SQL or its different statements, procedures, ect. But a point in the right direction of what I need to learn and I can do that.

Thanks for the help, and the quick reply.

The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.

Dave Kreskowiak22-May-07 12:01

Dave Kreskowiak

22-May-07 12:01

This isn't the complete code to do the upload.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Expert Coming22-May-07 20:02

22-May-07 20:02

			string szSQLCommand1;<br />
            string szSQLCommand2;<br />
			Int32 intFileSize;<br />
			byte[] rawData;<br />
			FileStream fsRead;<br />
<br />
			try<br />
			{<br />
				fsRead = new FileStream(lblPath.Text, FileMode.Open, FileAccess.Read);<br />
				intFileSize = Convert.ToInt32(fsRead.Length);<br />
<br />
				rawData = new byte[intFileSize];<br />
				fsRead.Read(rawData, 0, intFileSize);<br />
				fsRead.Close();<br />
<br />
                mySQLConnection.Open();<br />
                <br />
                szSQLCommand1 = "INSERT INTO Files(Name, Size) VALUES(@Name, @Size)";<br />
                szSQLCommand2 = "INSERT INTO FileData(Data) VALUES(@Data)";<br />
<br />
				cmd.Connection = mySQLConnection;<br />
				cmd.CommandText = szSQLCommand1;<br />
				cmd.Parameters.AddWithValue("@Name", txtName.Text);<br />
                cmd.Parameters.AddWithValue("@Size", intFileSize);<br />
<br />
                cmd2.Connection = mySQLConnection;<br />
                cmd2.CommandText = szSQLCommand2;<br />
                cmd2.Parameters.AddWithValue("@Data", rawData);<br />
<br />
				cmd.ExecuteNonQuery();<br />
                cmd2.ExecuteNonQuery();<br />
<br />
				MessageBox.Show("File Inserted into database successfully!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);<br />
<br />
				mySQLConnection.Close();

The only way to speed up a Macintosh computer is at 9.8 m/sec/sec.