|
Hi,
I am having 2 table. I want to delete records having same ID within 1 query.
e.g.
ID name
table1 1 a
table2 1 b
How can I do?
Thanks
|
|
|
|
|
if you want to remove only from one table you can use this
DELETE FROM TABLE_1 WHERE ID IN (SELECT ID FROM TABLE_2)
or
DELETE FROM Table_1
FROM Table_2 AS t2 INNER JOIN
Table_1 as t1 ON t1.ID = t2.ID
but i don't know if it's possible to remove records from 2 different tables by one single query
I Wish the Life Had CTRL-Z
Wizard's First Rule : People are fool,they believe what they want to believe or what they afraid to believe
www.subaitech.blogspot.com
|
|
|
|
|
It you have a relationship setup between the 2 tables on the id column with the cascading delete property set, then deleting the records for one table will delete the corresponding records from the other table
|
|
|
|
|
Write a stored procedure:
Create Proc usp_DeleteFromTables
@Id int
As
Delete From Table1
Where Id = @Id
Delete From Table2
Where Id = @Id
Then execute it. For example, if you wanted to delete id # 2:
Exec usp_DeleteFromTables 2
|
|
|
|
|
hi,
I am creating a report in SQL Reporting Services 2005.
The data is coming from a stored procedure which has multiple select statements (multiple result sets).
When I bind the report with the dataset ,I am getting the data only from the first select statement (1st table), whereas I am getting the data from each select statement in the stored procedure.
Is there any way to get the data while designing the reports?
Thanks in advance.
|
|
|
|
|
You can use the DataSet.Load[^] option and pass in a SqlDataReader and it will fill multiple DataTables with the multiple resultsets.
|
|
|
|
|
Hello friends,
I want to display error message if any error occurs while executing my stored procedure(for debugging purposes). How to display an error message in stored procedure (I am using sql server 2005)
Thanks
|
|
|
|
|
For debuging purposes, you can add after each (SELECT, INSERT, UPDATE, DELETE, EXECUTE ...) command something like this:
IF @@ERROR <> 0
PRINT 'Insert into MyTable failed'
|
|
|
|
|
Raiserror('Message', 16, 1)
The above message will show up like an error thrown by the server. It'll get caught by try/catch blocks.
|
|
|
|
|
hi
can we automate database backup by command line/or by some scripting ?
if yes, please provide how.
thanks
raj
raj
|
|
|
|
|
Try creating a new Maintenance Plan and use SQL server agent to schedule it to run automatically.
The easiest way is to open your management studio -> expand Management -> right click on Maintenance Plan -> choose "Maintenace Plan Wizard". Follow the instructions there.
Regards,
Syed Mehroz Alam
|
|
|
|
|
You can put the code below inside a stored procedure and then create and schedule a job to run whenever you want.
DECLARE @cmd NVARCHAR(100);
SET @cmd = N'C:\BackUp_MyDatabase_'+REPLACE( CONVERT(NVARCHAR,GETDATE(), 121) ,':','.') +'.bak'
BACKUP DATABASE [MyDatabase] TO DISK = @cmd
GO
|
|
|
|
|
HELLO FRIENDS,
I WANT TO UPLOAD A DOC FILE TO DATABASE THAT'S MAX SIZE WILL BE 1024KB
I WANT TO KNOW THAT WHAT DATATYPE DID I TAKE TO STORE THAT FILE IN DATABASE SO THAT AFTER STORING IN DATABASE I CAN RETRIEVE IN THE SAME FORMAT.....
THANKS
|
|
|
|
|
Try a varbinary(MAX)/BLOB field but first make sure your caps lock is off.
modified on Monday, July 14, 2008 7:10 AM
|
|
|
|
|
hii..frnds...i am unable to write a generalized syntax for the update delete insert statments..i mean that if i want to delete a selected row than i am able to do that by specifying the id number of that row,but for remaining ids it doesnt work..so that piece of generalization is what i want..plz help me
santosh
|
|
|
|
|
If you post your code we may be able to understand what you are trying to do, at the moment its rather unclear.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
delete from student where rollno='12';
this is generally how we delete....and in generalized format
delete from student where rollno=@rollno;
but the above line is giving me error stating tht @rollno should be declared as a variable.
i am unable to solve it.....
santosh
|
|
|
|
|
First,
CREATE PROCEDURE spx_Student_Delete (@rollno INT)
AS
delete from student where rollno=@rollno;
GO
Then use it like this
EXECUTE spx_Student_Delete 12
and it should work
modified on Monday, July 14, 2008 1:10 PM
|
|
|
|
|
Hi,
I am asking it again sorry if someone said it right and a got it wrong 
I wrote a application in C# that connects to a sqlserver database.
The login to the application is wrote not in C# but SQL store procedures. I press a buttom that triggers a SP that manages the login.
Ok, but if someone copy and attach my database to other computer he can see everything in there! Because i enter my sqlserver by windows validation and i have no password in sqlserver.
my connection strings have both no password and user.
I want attach to be enabled only if you have user and password.
DO i have to create another login in my sqlserver and create with that new user a database? how i do that? i don ´t know how to create new user in sqlserver rather then the "master"
i spinning here 
|
|
|
|
|
You see we can give you a Solution, but now, your English is not good and you dont get into detail about your problem, you just shoot us with long incomplete statements. take your time and explain your problem and we will step by step help you solve your problem. and lastly you should post this in C# Forum noy here. But as you already done it here you might as well continue
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi
I already answered your first post on this, I'm sorry if you don't understand but here it isagain:
The best option in my opinion is to use windows groups to limit user access. It still uses windows authentication so no password verification etc, and you can then grant explicit permissions to the group. You can deny permissions as well as grant them, so deny select, insert, update, delete on your tables and grant exec on the stored procs.
Its a standard way of doing it and easy to maintain by scripts.
nelsonpaixao wrote: The login to the application is wrote not in C# but SQL store procedures. I press a buttom that triggers a SP that manages the login. my connection strings have both no password and user.
From this I must assume you are alreay using windows authentication, so you need to set the table permissions as I said. This will prevent someone attaching to you database and seeing the tables. I'm not sure how to explain thsi any clearer, but try google for granting permissions in sql server, it may help your understanding.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thank you Bob, i remember your last post!
|
|
|
|
|
Hi all,
I wanna write 2 procedure
1) is when I execute it with parameter its give me the records of that particular table e.g. EXEC sp_Showrecords tablename
2) is when I execute it shown be all tables when I enter datbase name as parameter e.g EXEC sp_ShowTableaList dbname
Can anybody help me.
|
|
|
|
|
hi,
i don´t understand want you want
Maybe i can help you there
What kind of database you have and what do you want your procedure to do?
Didn´t you found anything here in codeproject?
Do you want do match/join contents from diferent tables and display it?
try inner join in search code project.
|
|
|
|
|
I am using SQL server.
e.g.
<br />
Create procedure sp_Fetchrecords<br />
@tbname sysname=NULL<br />
AS<br />
Select * from @tbname<br />
go<br />
gives me error
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
