|
|
Increment the connection timeout of SQL server Connection properties.
Knock out 't' from can't,
You can if you think you can
|
|
|
|
|
use CommandTimeout property of dbCommand to increase timeout time
DbCommand cmd = new DbCommand();
cmd.CommandTimeout = 60; // 60 secs timeout
|
|
|
|
|
Hi
I hava stored procedure that runs three different queries. Two of these queries involve selecting aggregate data. i have to join these three resultsets on a common field. How can i do this inside a stored procedure and then output the result of join to the caller.
Amna Ahmad
|
|
|
|
|
If all three queries have the same data layout, just use the UNION command.
(SELECT * FROM table WHERE [somefield] = 0)<br />
UNION<br />
(SELECT * FROM table WHERE [somefield] <> 0)
Visual Studio can't evaluate this, can you?
public object moo<br />
{<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}
|
|
|
|
|
JOIN and UNION mean different things. The original poster did not supply enough information to decide that he really meant UNION. He also indicated the possibility of only one common column so UNION would not be the the answer in that situation.
|
|
|
|
|
amnaahmad wrote: i have to join these three resultsets on a common field
You could put the results of the three individual queries in a temp table, or table variable. You can then join on the three tables.
It is possible to do everything that you want in one query, but the query optimiser doesn't seem to be too good at joining subqueries - especially if there is a lot of source data before the aggregation is done.
If you need help with the JOIN itself then can you supply more information about how the result sets are related.
|
|
|
|
|
two of the subqueries return aggregate data. while the third query returns data without aggregating. the columns returned by the threee queries are not same so we cannot UNION. i need to join the table on a common field. I can join this with the help of derived table or CTE ( common table expressions) . But CTE cannot be used in CLR stored procedures? Can it be? - Since my table are pretty large, i need an optimal solution.
|
|
|
|
|
amnaahmad wrote: But CTE cannot be used in CLR stored procedures? Can it be?
Why are you using CLR stored procedure here? As far as I can see everything can be done in SQL. CLR stored procedures should only be used when an action cannot be completed in SQL as CLR stored procedures are slower.
Run your two aggregating queries and store their result in a temp table or table variable. Then join as normal
SELECT a.column1, a.column2, b.column3, b.column4, c.column5, c.column6
FROM a
INNER JOIN b ON a.pk = b.fk
INNER JOIN c ON a.pk = c.fk
|
|
|
|
|
I am using CLR stored procedures coz i am dynamically constructing queries. The table name is passed as an argument as well as columns and the where condition. If i do it in TSQL it would become too cumbersome.
|
|
|
|
|
I don't know if this is the appropriate forum for this.
If I have a long Sql query executing that fetches a recordSet what is the best mode
to show a progress to the user? As long as the query is executed on the server-side
I don't have any ideea how long it will take nor what percent is completed of the query.
|
|
|
|
|
Zoltan Balazs wrote: If I have a long Sql query executing that fetches a recordSet what is the best mode
to show a progress to the user?
There is no progress information comming back from the server, no does it even know in advance how many records are going to be affected, a ProgressBar that shows 0 to 100% is impossible. The only thing you can do is make a ProgressBar that doesn't progress. That means making a kind of static scrolling image that just shows something is going on, but nothing about how far along the process is.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
You could first do a Count Query,
SELECT Count(*) FROM table
and then write your code so that you fetch 100 records at a time (or 42 or whatever number) and link that to your progress bar. This would be cumbersome, but progressbars are very important to end-users so it might be considerable.
Visual Studio can't evaluate this, can you?
public object moo<br />
{<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}
|
|
|
|
|
joon_ wrote: This would be cumbersome
And also very very inefficient.
|
|
|
|
|
What would be your suggestion, if the progress bar was a requirement?
Visual Studio can't evaluate this, can you?
public object moo<br />
{<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}
|
|
|
|
|
You know the CommandTimeout - the point at which the query will automatically fail if it takes too long. Progress along time. It might be that you often don't get the progress bar going fully to 100%, but it does tell people that it is progressing and they will know that their wait will be over by the time it does reach 100%.
|
|
|
|
|
For one, it's two trips to the database. Second, this query will return the number of records in the entire table, NOT the number of records that's actually going to get returned. Not to mention this query, if the table has enough records, could actually take longer than the query he really wants to run against this table.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
shriku24 wrote: i have problem to store audio file in database
If you are using SQL Server 2000 use an image column type (you can store upto 2Gb in there). If you are using SQL Server 2005 you can also use varbinary(max) which also has a 2Gb limit and is easier to use.
To get the data in and out use a byte array
byte[] audioData = SomeMethodForGettingTheAudioData();<br />
SqlCommand cmd = new SqlCommand();<br />
cmd.CommandText = "INSERT INTO SomeTable(AudioDataColumn) VALUES (@audioData)";<br />
cmd.Parameters.Add("@audioData", audioData);<br />
cmd.ExecuteNonQuery();
To get it out again just select the relevant column
SqlCommand cmd = new SqlCommand();<br />
cmd.CommandText = "SELECT AudioDataColumn FROM SomeTable WHERE ID = @id";<br />
cmd.Parameters.Add("@id", someIdValue);<br />
byte[] audioData = (byte[])cmd.ExecuteScalar();
|
|
|
|
|
can u give some examples for sql server 2000. I am tried to store audio file using image data type it store data successfully. But in table it looks as some code(number, hex or binary) format not as actual name of that file. and also i can't retrive(play) this file through my vb.net form which is just like media player.please help me.it's very urgent.
shrikant
|
|
|
|
|
Colin Angus Mackay wrote: byte[] audioData = SomeMethodForGettingTheAudioData();
well.. i wanted it for images.. though ia m sure the procedure wud be same..
now the somemethodforinputimages.. how do i go bout it.. i`l have a fileupload control to browse to the file..
how do i link it to the method to input images..
i hope i am clear..
Kunal
|
|
|
|
|
Kunal P wrote: well.. i wanted it for images.. though ia m sure the procedure wud be same..
Yes, you can put up to 2Gb of anything you like in there.
Kunal P wrote: now the somemethodforinputimages.. how do i go bout it..
Get your image file and return it as a byte array: byte[]
Kunal P wrote: i`l have a fileupload control to browse to the file..
I've not used the file upload control. I'd imaging you'd get some sort of stream object or perhaps it has a method that supplies you with a byte array.
|
|
|
|
|
Colin Angus Mackay wrote: I've not used the file upload contro
ok.. lets chuck out the file upload control, i thought that was the only wat to go bout getting the file.
Colin Angus Mackay wrote: Get your image file and return it as a byte array
now.. on the form.. wat kind of control should be created, that returns the file as a byte array..
how did u get the image from the user to be uploaded..
Kunal
|
|
|
|
|
I have already created the tables in access.I want to get the create table queries for the existing tables.
plz help me
|
|
|
|
|
I looked into this, and asked my local ACCESS Guru, and he said that this is not possible.
The only thing that comes remotely close is using Tools > Analyse > Documenter...
From this data you could quickly write create scripts yourself.
Other options are copying your MDB file and just removing all of the data. What exactly do you need it for?
I also found that the CREATE TABLE sql statements are not really supported anymore since Access 1997, so you won't find proper syntax of CREATE TABLE in JET SQL in the Help files of Office, look on Google instead.
Visual Studio can't evaluate this, can you?
public object moo<br />
{<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}
|
|
|
|
|
Does anybody know how to get List of Databases in Oracle?
-- modified at 7:53 Tuesday 20th March, 2007
Nana
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
