|
Post your tables structure here.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
ok, i have 5 tables:
Studenti(student):
fields -> CodiceStud(PK),NomeStud,CognStud,DataNascita,Classe,
CodiceIstituto(FK)
Istituti(school):
fields -> CodiceIstituto(PK),Denominazione,Indirizzo,Telefono
Professori(teachers):
fields -> CodiceProf(PK),CognProf,NomeProf,CodiceIstituto(FK),
CodiceManif(FK)
Manifestazioni(Competition):
fields -> CodiceManif(PK),Descrizione,Luogo,DataInizio
Iscrizioni(inscriptions):
fields -> CodiceStud(FK),CodiceManif(FK),DataInizio
The "inscriptions" table result from the N:N relation between student and competition.
The tables i need for the query are student and school only.
With this query i want to have as result the list of the school with the number of students who take part in one of the competition.
PS: Without the Distinct function, if a student take part in more then one competition he will be counted more time...
VentoEngine corp.
Program your life ^^
|
|
|
|
|
You could start with this rather not-so-elegant query and then fine tune it to your taste.
Select i.Denominazione, count(s.CodiceStud)
From Istituti i INNER JOIN Studenti s ON i.CodiceIstituto = s.CodiceIstituto
Where Exists
(
Select DISTINCT Iscrizioni.CodiceStud
From Iscrizioni
Where Iscrizioni.CodiceStud = s.CodiceStud
)
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
what are the letters "i" and "s" ?
VentoEngine corp.
Program your life ^^
|
|
|
|
|
ventomito wrote: what are the letters "i" and "s" ?
Aliases for table names.
You can define as alias like this:
SELECT * FROM MyTable t
INNER JOIN OtherTable o ON ....
where the alias comes immediately after the occurrance of the table name in the FROM or JOIN clause. I prefer to make this more explicit by writing it like this:
SELECT * FROM MyTable AS t
INNER JOIN OtherTable AS o ON ....
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Colin Angus Mackay wrote: I prefer to make this more explicit by writing it like this
In my opinion, it makes it easier to read when using aliases 
|
|
|
|
|
Im a beginner so im going to ask a stupid question that i did not find an anserw to in the documentation.
Is it possible to connect to sql 2005 over internet? So far i only done it with intranet.
If it is possible, could somebody tell me how it is done, or show me to an article concerning the subject?
(would also apreciate some security aspects that need to be considered)
R
Johan
|
|
|
|
|
john1113334 wrote: Is it possible to connect to sql 2005 over internet?
I should be. I don't see why not, it is just TCP/IP traffic. A firewall might block it, of course.
john1113334 wrote: would also apreciate some security aspects that need to be considered
SQL Injection attacks and tips on how to prevent it[^]
Also, exposing your SQL Server over the internet is a security risk. Normally it should sit behind an propertly configured firewall so that it can only be used on an internal network. If you must expose it over the internet you should consider some sort of VPN (Virtual Private Network).
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
hello all i have a problem and want to ask for solution
which is
i have make a webservice and i have connect the database with it and i have try it and its working but on THE LOCAL HOST , the problem that i reserve a domian its free ar somee.com and i dont know how to make the datasoure of the connections on that server i.e domain so any body help me please.
thanx
tedy
|
|
|
|
|
Read their support docs at http://somee.com/Support.aspx[^]. There is section dedicated to database related FAQs.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
I need to include a column in an SQL Server table, which can hyperlink to some text stored somewhere inside or outside the same database.
Please help.
-- modified at 1:18 Wednesday 30th November, 2005
|
|
|
|
|
R. A. Abbasi wrote: I need to include a column in an SQL Server table, which can hyperlink to some text stored somewhere inside or outside the same database.
In case of the text store in the database you should scan through the table and search for the text that you need by using SELECT statement. Check here for the SELECT statment information.[^]
In case of the text outside the database you should know the file path. The field that you are create must store the file path and point to the specific file that you need to open it. Check here for the information about read and write to text file.[^]
|
|
|
|
|
Comparing only Time in Sql?
Hi Dears!
I have two Different DateTime Values i want to Compare only their times independent of Dates How to do it?
let
DateTime @dt1='2004-10-30 19:30:00.917'
DateTime @dt2='2005-11-29 15:00:00.917'
accordint to my requirements
@dt1>=@dt2 should be true as 19:30>=15:00
because i have to compare only the time not the Date how to achieve this in sql .Function required.
|
|
|
|
|
You can change your times to milliseconds since midnight and compare those.
CREATE FUNCTION MillisecondsSinceMidnight
(
@in DATETIME
)
RETURNS INTEGER
AS
BEGIN
DECLARE @retval INTEGER
SET @retval =
DATEPART(ms,@in) +
DATEPART(ss,@in) * 1000 +
DATEPART(mi,@in) * 60000 +
DATEPART(hh,@in) * 3600000
RETURN @retval
END
|
|
|
|
|
Hi, I have a problem while trying to install SQL server 2000 sp4. There are 3 component that i need to install:
- sql server sp4
- analysis serverice sp4
- msde sp4
The first 2 option could be install successfully. But when i try to install the last option (msde sp4) it display a message "The instance name is invalid" after i double click on the file setup.exe. Does any one challenge this problem before? If you have please let me know about the solution how to fix it.
|
|
|
|
|
You don't need to install 'MSDE SP4' on a regular instance of SQL Server: Developer Edition, Standard Edition, Workgroup Edition, Enterprise Edition or Personal Edition. 'MSDE SP4' is only for updating the SQL Server 2000 Desktop Engine, also known as MSDE 2000.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you very much for your comment. But in technically, if you want install it and meet the problem like i post in the previous thread, what is the best solution to solve it?
|
|
|
|
|
Hello
I seriously need help on this. Please take time listen to my problem.
We have a SQL 2000 database running for a year. the main table called 'datatable' has grown to 1.8GB. Now we want to change the old database name to a different name without losing all the data in it. We made full backup of the old database and create a new empty database and restore the old database to it.
After I done that the program that populates the "datatable" in the newly restored database always give me error (IDispatch error #3121: [Microsoft][ODBC SQL Server Driver]Timeout expired :Microsoft OLE DB Provider for ODBC Drivers)
when rs->Open("DataTable", _variant_t((IDispatch *)conn,true), adOpenKeyset, adLockOptimistic,adCmdTable); excuted.
I have tried to recreate table index. It still doesn't work.
The datatable has first column "ID" as integer AutoNumber.
If I truncate the table, the program works fine. If I delete most of the data
for example only keep last a few hundred rows, the program also works.
If I don't use rs->open and rs->addNew() method to insert data, instead of using a simple insert SQL query, it works fine.
I believe the problem is that when we do restore something got messed up, but
how? If the datatable is too big and need more time to process when we call rs->open, but why the old database doesn't have this problem. The same program are running on those two databases. Any ideas?
Please help me out.
|
|
|
|
|
Opening a 1.8GB table is not a good idea either. USe WHERE clause to filter the records you need.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
hello Farhan
I don't quite understand.
If I use a where clause for example select * from datatable
where id = (select max(ID) from datatable), the rs->Open method will be okay. However, later on I do
rs->addNew() after addNew method, I do "if (FAILED(rs->Update()))" to commite the insertion. It will return me false means that it cannot update to datatable.
I don't know how to put a where clause there in order to do a insertion. Any ideas?
Thank you
|
|
|
|
|
AddNew()/Update() is a very inefficient way of adding new records, specially in a big table. I suggest you use INSERT query to insert new records.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
I understand AddNew() and Update is not a good way to
insert into a large table.
However, my question is why this method works fine with
the original table.
For the restored new table it refused to work.
|
|
|
|
|
Look for physical and logical fragmentation. Physical fragmentation can be fixed by using a good defragmenter. Database files fragmentation can be viewed and fixed using DBCC commands. For example
DBCC SHOWCONTIG(DataTable)
Also, check if antivirus or malware is blocking access to data files (very rare possibility).
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hello Farhan
I tried
DBCC DBREINDEX (datatable)
DBCC SHOWCONTIG(datatable)
It does reduce some fragmentation of the database file.
This is the message I got
DBCC SHOWCONTIG scanning 'DataTable' table...
Table: 'DataTable' (607341228); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 4100
- Extents Scanned..............................: 514
- Extent Switches..............................: 513
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.81% [513:514]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.39%
- Avg. Bytes Free per Page.....................: 703.8
- Avg. Page Density (full).....................: 91.31%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It seems even better than the production database, but still
the program works on the production database only, and give me the same timeout expired error when tried on the new database which is restored from the production database.
Any ideas ?
Thank you very much
|
|
|
|
|
Please post connection strings for both database.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
