|
This is where you pay the price for BAD data design, If you were good you would create another table called Subject and link it to your student table in the correct foreign key data structure. Now you're screwed, you have to invent a work around for your crappy design.
Options Are:
Redesign you data structure correctly - this is the recommended solution.
Play around with the text structure (', Science') will get most except where it is the first subject.
Try and NOT like "Social Science"
Create a split function based on the "," and filter on the required value (most sql devs have a split function floating around in their tools set)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No doubt DB structure has million dollar worth butt still you can use
you can use In
"Select Col1 ,Col2 from tbl Where Col2 not In (SELECT Col2 FROM tbl ) "
and can also use the XML as now is supported in SQL SERVER
|
|
|
|
|
Using In won't work, you still need to split col2 into it's parts. Doing it with XML or a table var is the same thing, you still need a function to do the splitting of the course column.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yup I agree with you that Split function has to be used but if that data exists in any other table then we can use in
|
|
|
|
|
Hello
I am trying to retrieve all the records form an oracle database table that have a start_date greater than or = Jan 1, 2008, have an end_date greater than May 1, 2009 or the value is null.
There are three columns in the table: start_date, end_date and id.
I need all the id's that have started before April 1, 2008 have an end date that is greater than or = May 1, 2009 or the end_date value is null.
So far I have
select * from table t
WHERE Start_date <= '01-APR-2008'
AND END_DATE >= '01-MAY-2009'
OR END_DATE IS NULL
Any help would be appreciated.
|
|
|
|
|
If your start_date and end_date are defined as type "date", I believe you need to do a TRUNC(start_date) to compare the date. The datatype may actually contain both date and time.
Also, my preference is to use parenthesis around my logical conditions so that I know that they will be evaluated properly.
For example:
WHERE (Start_date <= '01-APR-2008' AND END_DATE >= '01-MAY-2009') OR END_DATE IS NULL
|
|
|
|
|
|
"select max(centreid) from regionmaster where centreid like '" & intBranchPrefix & "%'", DBConnection, adOpenKeyset, adLockOptimistic
If rsMain.RecordCount > 0 Then
intID = Mid(rsMain(0), 3, 13)
End If
intCentreID = intBranchPrefix & (intID + 1)
this is my sql command in vb6 aplication.here i have to create a storeprocedure of this command which create centreid by taking BranchPrefix from application.i have created a storeprocedure but iam getting an error sayingArgument data type decimal is invalid for argument 1 of substring function.in below storeprocedure Note:i have centreid in table in binary(13,2)
create proc GenerateCentreID @intBranchPrefix varchar(20)
as
declare @MaxCentreID bigint,@intID varchar(20)
set @MaxCentreID=(select convert(bigint,max(substring(centreid,3,13)))
from regionmaster
where centreid like @intBranchPrefix+'%'
)
set @intID=@intBranchPrefix + convert(varchar(13),@MaxCentreID+1)
select @intID
go
|
|
|
|
|
I have got a stored procedure which executes very fast in Sql Server Management Studio, but when I call it with the same parameters from C#, it executes slower and sometimes even throws timeout exception.
Does anybody know the reason?
hint: I have noticed that when I comment out one of the conditions in where clause, the query executes very fast both on SSMS and C#.
Another point is that when I remove the comment and put the condition back in the query (which causes the stored procedure to compile again) the query execution becomes a little faster in C#, but after a while it becomes slow again (all with the same parameters)
|
|
|
|
|
I think that for anybody to stand a chance of answering your question, you should edit your post and include the stored procedure and the c# code that calls it.
Please do NOT reply to me, this is just some advice to help you get an answer. 
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
May I suggest ...
In SQL Server Studio Manager, execute your stored procedure, but under the menu "Query" choose the option to "Display Esitmated Execution Plan". This will give you great insight into how the SQL server is going to retrieve your data. You want to avoid large "scans" of tables, try to take advantage of indexes.
Also, you may want to look into SQL Profiler and watch what is going on while your code executes.
|
|
|
|
|
Thanks for your advice,
All the indexes are being used (index seek) and no table scan is happening. That's why the query gets executed very fast in Sql Server. That's why I wonder what the difference is when I execute it from code with the same parameters.
|
|
|
|
|
I would now look into the SQL Profiler, because you can now monitor how your application is interacting with the database. Things to check on the client side would be network traffic, memory usage and CPU usage while this is executing.
Sounds like a fun challenge. 
|
|
|
|
|
I found something recently that discussed arith_abort (I think, the link is at work) which is set on by default, setting it to off apparently made a difference to some procs that showed this behavior.
I have seen this behavior and after doing all the optimising possible the proc was still slow, that's when I started chasing down the more esoteric suggestions.
If I find the link I will post it Monday....
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi there,
how can you call a stored proc with OUT param in MySQL?
CREATE PROCEDURE spListUser (
...
PrimaryEmailFilter nvarchar(255),
..
OUT NumItemsSelected int
)
BEGIN
...
END;
I tried different things none work.
<br />
CALL spListUser (<br />
...<br />
@NumItemsSelected<br />
);<br />
<br />
CALL spListUser (<br />
...<br />
OUT @NumItemsSelected<br />
);<br />
<br />
CALL spListUser (<br />
...<br />
@NumItemsSelected OUT<br />
);<br />
In the end, I figured that it worked with Command line client but not with TOAD. What the hell is this? Anybody come across this before?
Thanks
dev
|
|
|
|
|
How can I go about building a database for say, a toy company? Using the microsoft SQL 2008? I know SQL commands whcih Ihave used to retrieve data using VB script from an already built website.
But I want to build my own DB and then connect it to my .NET framework.
|
|
|
|
|
Well one way to start would be by building ER diagrams.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Yeah Henry, that would be a good start. But then, how do I start up SQL 2008 and how do I get the Microsoft SQL editor? I mean, i see a downloaded link that says SQL 2008 on my desktop after I installed Visual Developer. But, I am unanle to figure out where do i go from there to build my DB??
|
|
|
|
|
Buy a book on SQL, your question is unanswerable in a forum, you are asking someone to teach you how to build a database, this can take years to perfect and certainly weeks/months to get the basics.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the Curt reply Mycroft.
|
|
|
|
|
Hi
The system table sysmergesubscriptions has changed from SQL Server 2000 to SQL Server 2005. The columns distributor and srvid are no more available. I have a stored procedure that used this columns that is not working when it should be used on an SQL Server 2005 or 2008.
Are there any replacements for this columns and is it possible to use them also on SQL Server 2000?
Here is the query that uses this columns:
DECLARE repl_cur CURSOR FOR
SELECT
mp.name,
me.srvname,
mp.publisher,
mp.publisher_db,
ms.db_name,
ms.subscription_type,
ms.distributor
FROM
sysmergepublications mp
INNER JOIN
sysmergesubscriptions ms
ON ms.pubid = mp.pubid
INNER JOIN
master.dbo.sysservers me
ON ms.srvid = me.srvid
INNER JOIN
sysmergearticles ma
ON ma.pubid = ms.pubid
WHERE
ma.name = 'PasswordCounter'
AND ms.subscription_type = 1
Thanks for your help
Greets
Roland
|
|
|
|
|
Hello,
I got a SQL 2005 database back up file which i restored in my VS2005 SQL studio express to use in my code. I found that some of the stored procedure are using linked server. I created all required linked server object in studio express but still when i try to execute the stored procedure, i get this error:
"The OLE DB provider "MSDASQL" for linked server "LinkedServerName" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Procedure rpStoredProcedureName, Line 18
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName"."
Any idea to resolve this problem? what else i need to do?
Thanks in Advance.
|
|
|
|
|
gajesh wrote: I created all required linked server object in studio express
Does Express support linked servers - I would be surprised if it does.
Are all the linked servers accessible to your system?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi!
I have a table Validation:
Id_student --- - id_semestre ----- id_validation
102----------- - 1 ---------------- 4
102----------- - 1 ---------------- 3 --
102----------- - 2 ---------------- 4
103----------- - 1 ---------------- 4
104----------- - 2 ---------------- 3
104----------- - 2 ---------------- 4
I want to select students who have in semestre 2 id_validation = 4 only .
in the example i want have :
102 students, not 102 and 104
thank you.
|
|
|
|
|
This works (using a correlated subquery), but I'm sure it's not the only way to get the correct results:
select v1.id_student from validation v1
where v1.id_semestre = 2 and v1.id_validation=4
and not exists (select id_student from validation v2
where id_semestre = 2 and id_validation<>4
and V1.ID_STUDENT = V2.ID_STUDENT )
Scott
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
