|
A VB.NET console application (requires the database that Roger linked to above):
Imports System.Data.OleDb
Module Module1
Sub Main()
Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Dim param As OleDbParameter
Dim rdr As OleDbDataReader
Try
conn = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=""courses.mdb"";" & _
"Persist Security Info=False")
conn.Open()
cmd = New OleDbCommand("qryStudentsOnCourse", conn)
cmd.CommandType = CommandType.StoredProcedure
param = cmd.Parameters.Add("@courseID", OleDbType.VarChar, 10)
param.Value = "C0450"
rdr = cmd.ExecuteReader()
While rdr.Read()
Console.WriteLine("{0} {1}", rdr("firstName"), rdr("lastName"))
End While
Finally
If Not rdr Is Nothing Then
rdr.Close()
End If
If Not cmd Is Nothing Then
cmd.Dispose()
End If
If Not conn Is Nothing Then
conn.Close()
End If
End Try
End Sub
End ModuleHope this helps. Some people have reported that using the ODBC classes rather than OLE DB is faster in the current version of the framework (probably due to lower interop overhead for 'flat' APIs versus COM APIs).
|
|
|
|
|
This is exactly what I was looking for, thank you very much.
|
|
|
|
|
did any body know any equvalent to dbms_pipe (Oracle Pack) in SQL Server ??
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
It depends what you want to do with it.
If you want to return arbitrary out-of-band messages to the client, use PRINT. If you want to communicate between two server processes, you'll have to write that functionality yourself. You could do it as a set of extended stored procedures, but I would recommend using SQL Server just as a database server, and perform any required computations on the client.
This is basically a difference of philosophy between Oracle and SQL Server; Oracle says 'I am the master server and you are all my slaves', while SQL Server says 'Why can't we all work together?' 
But seriously, SQL Server offers less support for building everything into the database. While it supports background operations, the only way 'foreground' operations (explicitly requested by a client) talk to background operations is through data stored in the database, unless you use some method of extending SQL Server.
|
|
|
|
|
Dear Mike,
Thank you for your replay, you have toled me to use print command is i would like to send out of band messages but the qustion is who can the cline catch this message , as if you like to send an alert to one of the clients after adding new data row in a table so you will create a triiger which will use print statement and how can the client (or many Clients catch it)?
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
Ah, I see: you're trying to notify clients that some data has changed.
This can't be done in the current versions of SQL Server. The next version, codenamed 'Yukon', will support this feature.
At present, I would recommend periodically polling the table from the client. This technique will apparently be used by the ASP.NET cache management code in the next version of ASP.NET, codename 'Whidbey', due to be released next year (IIRC), for SQL Server 7.0 and 2000.
A PRINT statement is only received by the client whose commands caused the PRINT to be executed. In ADO.NET, this message is treated as an informational message of severity 0 and can be handled by handling the InfoMessage event of your SqlConnection. The same is true for classic ADO (the event is on the Connection object).
It looks like (and I'll admit I'm just trawling the documentation here) ODBC will return SQL_SUCCESS_WITH_INFO (as opposed to SQL_SUCCESS) if a PRINT or a warning RAISERROR (severity 10 or below) occurs - you can then retrieve the message from SQLError. See also KB article 280109[^].
|
|
|
|
|
So Mr.Mike,
In this case i have create a table for sessions where any client can open a session by adding row to that table then i had create an extended exteneded procedure in c++ for SQL server which will send an alert on the TCP socket to all clients (executed on trigger) which had opened session to monitor a table so it is the best way to do it in this verssion of sql server 2000
Mhmoud Rawas
------------
Software Eng.
|
|
|
|
|
SELECT Sum(Amount) as "Total Expenditures", Month(eDate) as "Month"
FROM Expenditure
GROUP BY Month(eDate)
ORDER BY Month(eDate)
Above is my current SQL statement. I would like to change it so that it would generate a table that shows zeros for months that have zero "Total Expenditures". Currently it only shows amounts for months that have
total > 0. I think I could do it once I get it into my datatable but the query would be nicer.
Thanks
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
1 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=1
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
2 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=2
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
3 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=3
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
4 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=4
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
5 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=5
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
6 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=6
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
7 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=7
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
8 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=8
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
9 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=9
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
10 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=10
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
11 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=11
<br><br>
<font color="#0000FF">UNION</font>
<br><br>
<font color="#0000FF">SELECT</font>
<font color="#0000FF">ISNULL</font>(<font color="#0000FF">SUM</font>(Amount), 0) as [Total Expenditures],
12 as [Month]
<font color="#0000FF">FROM</font> Expenditure
<font color="#0000FF">WHERE</font> <font color="#FF00FF">MONTH</font>(eDate)=12
It's not the nicest way in the world, I admit. I'll let you know if I come up with a better solution. HTH.
Jeff Varszegi
P.S. You should join up-- membership's free, you know. That way you can get an email when somebody answers one of your posts. JKV
|
|
|
|
|
Yeah--- I see where that should work but when I run it in QTADO it tells me that there are :
Wrong number of arguments used with function in query expression 'ISNULL(SUM(Amount), 0)'.
From my understanding it works like this ISNULL(expression, value if null) so it should work. Maybe it is something with MS JET or something.
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
It must be the MS JET Engine. It only wants one arguement for the ISNULL function. Back to the drawing board.
"It has become appallingly obvious that our technology has exceeded our humanity."
- Albert Einstein (1879-1955)
"I think there is a world market for maybe five computers."
- Thomas Watson (1874-1956), Chairman of IBM, 1943
"640K ought to be enough for anybody."
- Bill Gates (1955-), in 1981
"Half this game is ninety percent mental."
- Yogi Berra
|
|
|
|
|
I think jet has a different ISNULL. The syntax is ISNULL(value).
Try using IIF to check the condition and set it to 0 if it is null.
e.g. IIF(ISNULL(value),0, value).
|
|
|
|
|
Good one. I shouldn't have blindly assumed that he was using SQL Server, I guess.
Regards,
Jeff Varszegi
|
|
|
|
|
Yes this works:
SELECT IIF(ISNULL(Sum(Amount)),'0', Sum(Amount)) as 'Total Expenditures', '1' as 'Month'
FROM Expenditure
WHERE MONTH(eDate)=1
I suppose I will write it as Jeff suggested using the above.
Thanks for the help guys! THE CODE PROJECT IS AWESOME!
|
|
|
|
|
I would recommend just setting up a months table with 1 columns (month number). Then join that to your query (something like ... month(a.edate) = b.month_nm ...)
|
|
|
|
|
Hi!
Could you show me the difference between ADO and DAO?
Thanks!
|
|
|
|
|
DAO = Data Access Objects
ADO = ActiveX Data Objects
in brief .. use ADO .. don't use DAO
ADO is more general .. you can access many data sorces : XML,Active Directory,File System + SQL server , oracle , MySQL etc.. using OLEDB provides or ODBC..
DAO is more dedicated for access..ADO can work perfectly with Access.
|
|
|
|
|
|
Everytime I spoke with other Sun/Java flavoured developer they sneered at M$ SQL Server as if it's not a real database server. Is Oracle really so much faster than SQL? I also heard Oracle has a more comprehensive set of reporting tools. Personally, I have no experience with Oracle...
|
|
|
|
|
The current TPC-C[^] raw performance benchmarks suggest that Oracle on HP/UX on outlandish hardware (64-way Itanium2 1.5GHz, 512Gb of memory) does currently outperform SQL Server 2000 (64-bit) on the same outlandish hardware (look at the second and third rows, the first had 1024Gb of RAM).
However, most of us are lucky to be using 512 megabytes rather than 512 gigabytes.
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
Basically, you need to decide what you're going to use your system for (transaction processing versus ad-hoc queries), decide what performance level you actually need (i.e. how many transactions per second you're planning to handle). The performance of your system will also depend very much on how you write your queries and implement your indexes.
For most general purpose applications, SQL Server is fine.
|
|
|
|
|
Mike Dimmick wrote:
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
I looked at the URL you specified... SQL Server isn't doing so bad at all.. But I remember I read some performance reports from Oracle site. According to their report, SQL performance is just a small fraction of Oracle.. I didn't read into the details however.
|
|
|
|
|
Mike Dimmick wrote:
Frankly, SQL Server usually performs well enough on almost any hardware. Oracle really requires massive amounts of memory - 9i couldn't create a database on our test system with 192Mb (taking 6 hours and then the client crashed), while SQL Server 2000 creates a new database in about 5 seconds on the same hardware.
Not to mention, SQL Server is organized in such a way as to make it easy for "non-sql" people to use. With the different flavors (MSDE, Standard, Developer, and Enterprise), you can really get just what you need without the tons of overhead that Oracle requires.
Also, with Oracle, from around 7.0.4 and above, you basically need at least a week of classes just to figure out the whole Scott/Tiger thing and how to get the DB up and running. That does not include performance tuning, backups or how and why the data dictionary works.
Mike Dimmick wrote:
Basically, you need to decide what you're going to use your system for (transaction processing versus ad-hoc queries), decide what performance level you actually need (i.e. how many transactions per second you're planning to handle). The performance of your system will also depend very much on how you write your queries and implement your indexes.
We did a lot of performance testing on both Oracle 9i and Sql Server 2K. We found that when it came to data warehousing (large queries, mostly static indexes and a lot of tables!), Oracle was best. When it came to more transactional queries, Sql Server was top notch. Plus, given that .NET is tailored to Sql Server (go figure!), there is an added performance boost... 
Ok, enough of my rambling... 
Bill P.
Oakland, CA
-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCM/MU/B dpu s--:-- a32 C++++$ ULH+++ P+++ L++ E+ W+++$ N++ o K? w++++$ O-- M V-- PS+ PE+ Y++ PGP++ t++@ 5++@ X++ R+@ tv b++ DI++ D+++>++++ G++ e++ h---- r+++ y++++
-----END GEEK CODE BLOCK-----
|
|
|
|
|
As others have said, it all depends on your available hardware and the type of DB you want to create. I have found SQL Server to be much easier to use (in general), easier to manage for most day-to-day taks, has better management tools and performs well on updates/deletes and good-enough on selects. Oracle has poorer management tools, but has many more configuration options readily accessible and performs better for querying, but unless configured to the Nth degree, has poor performance on inserts/updates/deletes.
If you know how to configure Oracle, I think you can squeeze better performance out of it on an apples-for-apples hardware comparison. Also, Oracle IMHO is more scalable to multiple servers, especially in geographically seperated situations.
Ultimately, I choose SQL Server because I just don't know how to configure all the options and SQL Server sets most things automatically and does a good enough job. About all you need to know specific to SQL server (beyond basic DB design) is how to use file groups. Also, the management tools for SQL Server are much better than the comporable Oracle tools.
|
|
|
|
|
Hi everyone,
I have not been in touch with SQL for a long time. I had to start using it again since a few weeks and it has been quite a struggle.
I have a question and I would be really grateful if someone can help me with it. Consider the following table structure:
ID TESTNUMBER STATE VALUE
1 100 1 0
2 100 1 1
3 100 2 1
4 100 2 0
5 101 1 1
6 101 1 0
7 101 2 1
Now, what I want to do is get the DISTINCZ count of TESTNUMBER, say where (State = 1, Value = 0) and (State = 2, Value = 1). So basically this should give me a count of 2 because TestNumber 100 has an entry for (State = 1, Value = 0) and (State = 2, Value = 1). Also, entry 101 has (State = 1, Value = 0) and (State = 2, Value = 1) entries. However, I am unable to write this using a query. Please help!
Thanks,
Sincerely,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Try this:
SELECT COUNT(*), state, value
FROM table
GROUP BY state, value
The statement above will group your result based on state and value, e.g.
Count State Value
2 1 0
2 1 1
2 2 1
1 2 0
If you need to add a condition, add HAVING state = 1 AND value = 0 (for example).
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
