|
hi! i have a table 'Categories' whose schema is:
CategoryId(Primary Key) int
Name nvarchar(25)
i have another table 'Products' whose schema is:
ProductId(Primary Key) int
CategoryId int
Description nvarchar(250)
ProductImage nvarchar(50)
UnitCost money
now i want whenever i delete an category from Categories, then all the data related to this Category from the table Products should be deleted automaticaly from Products table. How is possible?
Thanks & Regards,
SAMir Nigam,
Software Engineer,
STPL, Lucknow, India.
|
|
|
|
|
You have to do cascading deletes. Google Cascading Delete[^] has some good results that can help you out 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
First delete rows in Products table with categoryid whichyou want tobe deleted then delete category from categories table.
example
delete from products where categoryid='1'<br />
delete from categories where categoryid='1'
I Love SQL
|
|
|
|
|
He could always have that in a stored procedure, but cascading delete is easier 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I have four tables (t1, t2, t3, t4) in my DB. Given t1 and t4, i want to find what tables(relationship) are between t1 and t4. I want to do this using SQL and/or .NET. Any suggestions?
t1 t2
--------- ---------
t1Id (pkey) t2Id (pkey)
t2Id (fkey) t3Id (fkey)
<br />
t3 t4
--------- ---------
t3Id (pkey) t4Id (pkey)
t4Id (fkey)
The result in this example would be t2, t3
|
|
|
|
|
Start by looking at your primary keys.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi all,
I am in a fix for which i really need some input with this problem
colA-------- --COLB
1000-------- --,,,--EXCESS OF ,,,
1000A------- --A,,
100AB------- --A,B,,
100ABC------ --A,B,C,
This result was derived to achive the table Below
DESIRED RESULT
colA-------- --COLB
1000-------- --NULL
1000A------- --A
100AB------- --A
100AB------- --B
100ABC------ --A
100ABC------ --B
100ABC------ --C
BUT THIS IS THE RESULT I GET
colA-------- --COLB
1000-------- --NULL
1000A------- --A
1000A------- --NULL
1000AB------ ---A
1000AB------ ---B
1000AB------ --NULL
100ABC------ --A
100ABC------ --B
100ABC------ --C
THIS IS BECAUSE THE FIRST RESULT CONTAINS ESCESS OS ,,, I WANT TO
REMOVE THOSE AND HAVE ONLY THE VALID LETTERS,
IS THERE A WAY TO REMOVE ONLY THE EXCESS COMMAS
ANY INPUT BE VERY HELPFUL
THANKS
SQL DEVELOPER
|
|
|
|
|
I recommend a regular expression that looks for 1 or more contiguous commas and replaces with a single comma. I can't give you the regex straight away, but maybe you can find one on a regex site somewhere.
|
|
|
|
|
I am using MS Access to make a simple database and i used a lookup field to another table so that the user can update the lookup as needed. But when i copy the records to another table i want them to copy as text data not lookup data. I would like to know how to copy it to another table and get the actual value instead of the foriegn key.
|
|
|
|
|
How can I get more control over generating SQL scripts? I would like to optionally include drops where SSMS only allows drops OR creates etc. I would like to script as alter not create etc.
|
|
|
|
|
Perhaps Red Gate SQL Compare? We use it for generating scripts to update a schema, rather than drop/recreate.
|
|
|
|
|
Hai,
I have Windows XP SP2 and I have installed SQL Server 2005. while i'm opening SQL sever Management studio i get a pop message indicating that "SSPI Error Context". Can anyone sort out this problem.
aanryn
|
|
|
|
|
Hello!
I am trying to retirve data from almost five tables simultaneously, but having problems in use of a single query!!!
Here is the sample of thouse five tables
students: stuid, classID, sectionID, termID, sessionID, stuName, other fields
Classes: classID, className
Sections: sectionID, sectionName
Terms: termID, termName
Sessions: sessionID, sessionName
NOw if i want to extract data with a single query in somewhat this form
stuName, className, sectionName, termName, sessionName
Can someone help me in this, as to how the best query can be formed?
thank you!!
-- modified at 1:28 Monday 30th July, 2007
|
|
|
|
|
SELECT stuName = s.stuName,
className = c.className,
sectionName = sc.sectionName,
termName = t.termName,
sessionName = ss.sessionName
FROM students s
LEFT OUTER JOIN Classes c ON s.classID = c.classID
LEFT OUTER JOIN Sections sc ON s.sectionID = sc.sectionID
LEFT OUTER JOIN Terms t ON s.termID = t.termID
LEFT OUTER JOIN Sessions ss ON s.sessionID = ss.sessionID
Eliz.K
<awww.oin1.com< a="">
|
|
|
|
|
Thank You Eliz!
One thing more, also if i want to get the record of a single student, can i used the where clause after joins like
where s.stuid = '2222'
???
thanks!!!
|
|
|
|
|
|
I am calling a store procedure on sql server 2000 table as below::
return SqlHelper.ExecuteDataset(cn, "abc",54);
It is giving the following exception...
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding error.
can anyone suggest me the solution plz..
hiiiiii
|
|
|
|
|
hope procedure execution is take more time.
set connectiontimeout property of connection object (cn)
Regards
KP
|
|
|
|
|
Thanks,
But it is not working. can you tell me the way to attach timeout with sqlhelper??
hiiiiii
|
|
|
|
|
Your problem is not with the connectiontimeout. It is the command timeout. Somewhere in your sqlhelper there should be a command timeout. The default is 30 seconds. You will need to increase it to something larger. You may also want to look at your sql statements and consider creating indexes to help with the speed.
Hope that helps.
Ben
|
|
|
|
|
Increase your timeout time and/or profile your stored procedure and see what is taking up time.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I want to use stored procedure in place of select statement for cursor.
e.g.
DECLARE ProfInd_CURSOR CURSOR for
dbo.EM_GET_WORKHOURS 88,1,'01/01/2007','08/08/2007'
OPEN ProfInd_CURSOR
..............................
any idea
Do good and have good.
|
|
|
|
|
Modest Bird wrote: I want to use stored procedure in place of select statement for cursor.
I don't think that you can do this. What you can do is create a temporary table in your current procedure and fill it from nested procedure. select from this temporary table in cursor
|
|
|
|
|
Hello Modest,
You can try to use User Definied Function for your cursor instead of stored procedure.
Like this:
DECLARE cur CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT * FROM dbo.fn_GetTestData(1)
Please get the sample from --> http://www.oin1.com/Technical/SQL/FunctionInCursor.htm[^]
Eliz.K
|
|
|
|
|
You can't. You have to provide a SELECT statement, and you cannot directly SELECT from a stored procedure's results.
You can replace the stored procedure with a user-defined function - either an inline table-valued function, which simply contains a single SELECT statement, or a multistatement table-valued function.
Alternatively, the INSERT statement can insert the results of a stored procedure into a table (e.g. INSERT table (columns) EXECUTE stored_proc). You would need to create a temporary table or table variable to store the results, with the appropriate schema, insert into the temporary table/table variable, then declare the cursor to select from that.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
