|
i have to delete data but i have problem found relation between tables so i cant
delete data
how can delete data without drop relation ship between tables???
Rami Abd Alhalim
|
|
|
|
|
If I am understanding you correctly, you can not do this. That is the purpose of the foreign key constaint. It will not allow you to delete the data without dropping the relationship between the tables. The only other options is to delete data from all the tables in the correct sequence.
Hope that helps.
Ben
|
|
|
|
|
Thanks
how correct sequence
|
|
|
|
|
It depends on how your tables are related. Let's say you had:
ParentTable1
ChildTable1
ChildTable2
Both ChildTable1 and ChildTable2 have parenttable1ID which is the key. All of the records in childTable1 and childTable2 that have that parenttable1ID must be deleted before the record in ParentTable1 with parenttable1id can be deleted.
The benifit of this is that you can't orphen the Child table records. They must be deleted before the parent table record can be deleted.
Hope that helps.
Ben
|
|
|
|
|
set particular column's constraint as
Delete Rule as Set null
and
Update Rule as Set null
You can find this option where you define the relationship of tables. it is titled with "Insert and Update Specification" of sql server management studio.
divyesh
|
|
|
|
|
I need some help writing a stored proc. First, I am inserting a range of ZipCodes into a db, ZipBegin and ZipEnd(12345-12346). Although that seems relatively simple, however, I need to check those ranges and make sure that there is no other over lapping range.
for more clarity:
range A 12345-12346 is in the db
range B 12344-12347 is being inserted.
as you can see these ranges overlap, if that occurs I need to use a raiserror and rollback the transaction.
Does anyone have any suggestions on how I could do that?
|
|
|
|
|
What was wrong with my previous answer[^] to your question?
|
|
|
|
|
You want to check before or after inserting?
below logic will help you.
Before Inserting
------------------
check whether there is any ZIPCode exist within the range
sql
---
Select @begin = count(ZIPBegin) from tblZIPCodes where ZIPBegin between 12345 and 12346
Select @end = count(ZIPEnd) from tblZIPCodes where ZIPEnd between 12345 and 12346
If @begin > 0 or @end > 0
-- then there is an entry within that range already exist
else
--perform the insertion
After Inserting
----------------
BEGIN TRANSACTION
--Insert the records
-- then use the same sql stmt written above to get the value of @begin and
--@end
If @begin > 1 or @end > 1
-- multiple entries has been made
ROLLBACK TRANSACTION
RAISERROR
RETURN
else
--perform the insertion
COMMIT TRANSACTION
Regards
John
|
|
|
|
|
I have a script of creating tables and stored procedure.
I want to check particular column is exists or not before dropping it or I just want to catch an exception while dropping particular column.
I cannot do so.
Thanks in advanced
Divyesh
|
|
|
|
|
Hi Mr. divyesh1432
this code to check if column exist or not
Exaple:
-------------------------------------------------------------
if exists(select column_name from information_schema.columns
where table_name='TableName' and column_name='ColumnName')
----------------------------------------------------------------------
Best regard
Rami Abd Alhalim
|
|
|
|
|
|
Mr. divyesh1432
if my answer correct plz tell me
thanks
Rami Abd Alhalim
|
|
|
|
|
|
in my application i use ado.net to connect the db and in ado.net when you connect the db it takes an instance of the relevant table and close the connection . but when the application runs it needs to access the db often and in order to do this do i have to connect the db each and every time the db is needed or keep the connection without closing . but the second option affects on concurrecy.
can you guys do some suggestions to this .
thank you ...
|
|
|
|
|
Hi,
I am createing a report in First.aspx page it may look like this with a report in table format using sql server rs
ID Name Salary
1 xxxx 10000
2 yyyy 20000
3 zzzz 30000
I want The ID Column as Link Column(i.e, values of ID 1,2,3 must reposnd to click events)
I did this By using Navigation Property of that feild, But It is asking another report To jump
In which event I have To right my Code-Behind
|
|
|
|
|
Is it possible to define a parameter for a stored procedure that is both an input and output value, in MS SQL Server 2005?
If so, how?
regards
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
Yes you can used that.
(
@In_Out int out, --this parameter it can set and get data
@In int --this parameter it can set data just
)
Rami Abd Alhalim
|
|
|
|
|
Simple when you know how.
Thanks
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
hi,
I'm having a fixed length file hvaing rows of data and some/all of the records can be grouped together using a rowid.
for ex:
xx1|uiy2jof7giu4|0012000|2007-06-29|AAX
xx3|uiy1jofh3iud|0012000|2007-06-29|AAX
xx4|uiygjofhgiud|0020100|2007-06-29|CUY
xx1|uiyg3vb4giud|0212000|2007-06-30|1HU
xx1|uiygjofhgiud|0002000|2007-06-21|ZXX
xx4|ui2ygj7fhgi9|0052000|2007-07-29|559
Is it possible to read the file into sql as xmldata type and sum a particular value in the row with same id and send to the front end.
Can any one help ?
Thanks and Rgds,
PLS
|
|
|
|
|
I am no expert, but I believe you will need to look at DTS (Data Transformation Services)
Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'
|
|
|
|
|
you want read XML file and saving on SQL server ??
|
|
|
|
|
no the source file is a fixed length text file. the data should be read into sql server as xml datatype and sorted and a particular numeric field should be added there and returned to a dataset.
|
|
|
|
|
There are better methods for diong this, but maybe this will help some.
***********************
declare @cmd varchar(300), @file varchar(255)
set @file = 'C:\temp.txt'
set @cmd = 'type ' + @file
create table #temp (f_All varchar(255))
create table #temp2 (f1 varchar(10), f2 varchar(50), f3 varchar(10), f4 datetime, f5 varchar (10) )
insert into #temp
EXEC master..xp_cmdshell @cmd
insert into #temp2
select
substring(f_all, 1, 3),
substring(f_all, 5, 12),
substring(f_all, 18, 7),
substring(f_all, 26, 10),
substring(f_all, 37, 3)
from #temp
where f_all is not null
select * from #temp2
-- GROUP, SORT, SUMMARIZE, AND USE IT HOWEVER YOU NEED IT.
drop table #temp
drop table #temp2
**********************
Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia
|
|
|
|
|
I am using auto increment for the primary key in a table. For example,
table - (userID, UserName). Now the auto increment of last userId = 10.
If I would like to insert a userId = 111, I found that the next auto increment userId must be in 112, but it is not 11.
Is there any setting to change back the UserId to 11?
Thanks!
|
|
|
|
|
hi mimimimilaw
you must go to design table ,Set Identity Seed = 10 for column auto increment
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
