|
Thanks! After ironing out a few type mismatches, that worked like a dream!
I was a hesitant to use a cursor because I've heard bad things about them, but this technique was ordained by those higher up the ladder than myself, so I figured now is as good of a time as any to learn.
Thanks again!
-------------------
abort, retry, fail?
|
|
|
|
|
jszpila wrote:
I was a hesitant to use a cursor because I've heard bad things about them
Yes, I would say that cursors are useful in a small number of situations. It is something that once learned is a difficult habit to get out of because people are used to procedural approaches that are used in more traditional languages such as C/C++/C#/VB/Pascal/Java and so on. SQL is a set-based language and so it operates on sets of data more efficiently and if you can keep it that way then all the better.
Now that you know how to use cursors, my advice is to put that knowledge away and not touch it unless you really must use it as there is no other option. Just about every resource I've read on the subject has said how awful and inefficient they are - and I don't mean gaining a few milliseconds here or there. I mean stored procedures going from taking hours with cursors to a few seconds to do the same thing without.
Like I said, I think your problem can be solved without using cursors - but it would probably take a little bit longer to think through. However, the benefits at the other end are so much better.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
I'm searching for a good forum to ask questions, exchange ideas etc regarding the creation on data providers. Not using them, making/creating/developing them..
I'm responsible for creating the API-layers for my employers IMDBMS, so far we have written a JDBC-driver, OLEDB-driver and a .NET Data Provider for .NET 1.1.
Problem is that our .net driver that is working using VS2003 and .net 1.1, but will not work in VS2005 .net 2.0. Thanks Bill, appriciate it..not! 
So I'm desperatly looking for a nice place/forum to ask questions, getting ideas etc concerning this.
I've tried the ".NET Framework Data Access and Storage" forum at forums.microsoft.com/msdn, but they seem to think I'm trying to use the SQL Servers data provider, not my own.
Has anyone seen such a place?
TIA/
Michael Hulthin
|
|
|
|
|
Hi I have reports created in ms Access database. Do you know
how to use code in VB.NET so that I can launch the access database and then open up the reports?
Could you give me some sample codes please? thanks
dw1928
|
|
|
|
|
I have what I hope is a simple question. I'm trying a product called RapTier from Sharp Power that generates a data access layer. One of the options it has is to produce stored procedures from my database. I've looked through the generated stored procedures, and they seem pretty good.
My problem is this, I have the stored procedures as a .sql file, and I have to figure out how to actually attach/add them to the database that I have (I'm using MSDE 2000).
Can anyone give me a hand on how to do this?
Thanks.
The ends can never justify the means. It is the means that determine the ends.
|
|
|
|
|
You can actually hurl any SQL (including a code-generated script) through a regular SqlCommand object.
A few months ago I wrote a simple application to demonstrate that - It picks up a file and dumps the contents to a SQL Server via the SqlCommand object in .NET
You can find the download here[^]
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
OK, I don't really understand your response, to tell the truth. I'll go to your site and take a look at the utility, maybe it can help.
What I'm really looking for (I think) is just the command I need to feed OSQL to get it to go find my .sql file and add it to the database. I'm not sure if that is equivalent to "hurl[ing] any SQL through a regular SqlCommand object" or not.
Thanks for the reply though!
The ends can never justify the means. It is the means that determine the ends.
|
|
|
|
|
I think what he meant was "why don't you just go read the .sql file into the commandtext property of a sql command object and executenonquery". The only thing you need to be careful of is "go", but I also read in another post that colin wrote a utility to help with sql containing "go" in it.
|
|
|
|
|
If it's a local instance of MSDE, use the following command:
osql -E -i filename.sql The -E switch indicates to use Windows authentication, and the -i filename.sql indicates which file contains the statements to run. Run osql /? to see other options.
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005
|
|
|
|
|
I use ADO.NET to execute sql. But it takes a lot of time, so after 30 seconds program show error message "Timeout expired!" (althrough I declare "Connect Timeout=600" in connect string). How to use SqlConnection without Timeout or with a long time?
Please help me!!!
Thanks!
|
|
|
|
|
There are two timeouts. The one you are using is for the connection. In other words, how long should the .NET application wait to connect (not do any work, just connect) to the database.
The other timeout it on the command object, CommandTimeout. This timeout is the amount of time that the .NET application will wait for it to perform what ever command you sent it.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hi,
I want to learn SQL Server and use it in C# Application.
Who know about it,please help me install it.
Thanks.
|
|
|
|
|
Tran Ngoc Minh wrote:
Who know about it
Lots of people do.
Tran Ngoc Minh wrote:
please help me install it
What problems are you having while installing SQL Server?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Hi,
I want to access sql store procedure.It has 3 input parameter and 8 output parameters. store procedure also retun the result set. Can you please give me reference of sample code to access store procedure. I am totally new to DB.
Thanks
|
|
|
|
|
Hi
I've got a login Proc that returns some data upon successful login.
I've just added some checking code before this data to verify that the user may log in etc, but the resultsets from these checks are being returned in the dataset. Is there some way to clear the results before doing my final select statement?
Thanks
Russell
|
|
|
|
|
Not too sure if this will help but when I have a structure like that I use a reader. I can then write the following:
if(r.Read()){
// they are valid, get user info
if(r.NextResultSet()){
while(r.Read()){
// return additional data for that person
}
}
}
r.close();
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
-- modified at 12:45 Monday 3rd October, 2005
|
|
|
|
|
I'm trying to get SQL to not return the recordsets that the SP uses internally and just return the data i really want.
There's quite a bit of branching in the checking code which means i don't know exactly how many select statements will be executed prior to the select that i want executed.
|
|
|
|
|
The easiest way around this is to not execute queries that return rows.
Instead of:
SELECT *
FROM Users
WHERE ID = "Tom"
Do:
DELCARE @i INTEGER
SELECT @i = COUNT(*)
FROM Users
WHERE ID = "Tom"
You can use logic to determine if @i > 0 then process the rest of the login. You can use any number of variables in the SELECT. Just make sure your query only returns 1 row.
You can also use EXISTS:
IF EXISTS(SELECT * FROM Users WHERE ID = "Tom")
BEGIN
-- Do your work here
END
|
|
|
|
|
Hey all, I've got data structured like this:
ID - Contact - Company - Email
1 - Jeff - Acme - Jeff@Acme.com
2 - Hester - Acme - Hester@Acme.com
3 - Mort - Acme - Mort@Acme.com
4 - Jason - Widget - Jason@Widget.com
5 - Harriet - Widget - Harriet@Widget.com
I need to write a query that will return only the first contact for each company, in this case it should return:
----------------------------------------
1 - Jeff - Acme - Jeff@Acme.com
4 - Jason - Widget - Jason@Widget.com
Thanks in advance!
*->>Always working on my game, teach me
*->>something new.
cout << "dav1d\n";
|
|
|
|
|
Not Tested but give it a try.
<br />
SELECT ID, Contact, Company, Email<br />
FROM [table]<br />
GROUP BY Company<br />
HAVING MAX(ID) = ID<br />
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Just a little correction
SELECT ID, Contact, Company, Email
FROM [table]
GROUP BY Company
HAVING MIN(ID) = ID
|
|
|
|
|
Nope
something like
select * from [table] t where id=(select min(id) from [table] tt where t.company=tt.company)
HTH
|
|
|
|
|
Check this
SELECT *
FROM DeleteMe
GROUP BY Company
HAVING MIN(ID) = ID
|
|
|
|
|
It will nerver work. Read BOL about GROUP BY first before posting.
|
|
|
|
|
Why dont you try it first then reply. I have already tested it and it works fine.
I think you need some lesson on Group By.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
