|
Thanks i have Fixed it, the Top 1 was supposed to be in
<br />
(SELECT top 1 SUBSTRING(NEW_ATTRIB_CODE,7,2)<br />
FROM SDE.VALUATION V INNER JOIN SDE.PROPERTY PS<br />
ON V.PROPERTY_ID = PS.PROPERTY_ID<br />
) AS CATEGORY_CODE,
Thanks
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
|
|
|
|
|
Great
I Love T-SQL
Don't torture yourself,let the life to do it for you.
|
|
|
|
|
Vuyiswa wrote:
Msg 512, Level 16, State 1, Line 6Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Warning: Null value is eliminated by an aggregate or other SET operation.(0 row(s) affected)
Tells you the problem, your subquery is returning more than 1 row. Eithe rfix it by using SELECT TOP 1 ... or better yet write the query so the subquery can only return 1 value
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hiii..pls help me
1.How can i avoid insertion of null values into a foreign key attribute column....
-thr s a table EMPDETAILS with columns empid,name,age
-empid in EMPDETAILS foreign key references empid in
LOGIN table.
-LOGIN table has some data.
-when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
How can i solve this?????
2.How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
pls help
|
|
|
|
|
Arun Krishnan wrote: -when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
Even though it is a foreign key you still need to insert the data. Foreign keys are for referential integrity only.
Arun Krishnan wrote: 2.How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
Insert into the LOGIN table first, then the EMPDETAILS
Finally I suggest you try google to look for foreign keys as you clearly do not really understand them.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Arun Krishnan wrote: -thr s a table EMPDETAILS with columns empid,name,age
-empid in EMPDETAILS foreign key references empid in
LOGIN table.
-LOGIN table has some data.
-when i insert values into name & age of EMPDETAILS
table, the values r inserted with NULL values for empid
field.
How can i solve this?????
Do you pass the empid? if not, how do you expect the database to know which one do you mean?
Arun Krishnan wrote: How can i insert values into empid of EMPDETAILS table & LOGIN table at the same time,without using IDENTITY constraint????.......
-empid in LOGIN table s d primary key & in EMPDETAILS s d foreign key.
- i have to insert a string value for empid
No problem, insert into EMPDETAILS table the same value of empid that you insert into LOGIN.
By the way, using s d instead of is the does not save you a lot of your time, but makes reading your posts harder.
|
|
|
|
|
|
First, I would make the foreighn key field a NOT NULL field to prevent accidental insert without a non-null value for EMPDETAILS.EMPID.
2nd, make EMPDETAILS.EMPID have a Foreign Key constraint - this would require that the value supplied be a value that exisits in LOGIN.EMPID column
Alter table EMPDETAILS (Alter column EMPID NOT NULL)
ALter table EMPDETAILS ADD CONSTRAINT FK_LOGIN FOREIGN_KEY (EMPID) references LOGIN (EMPID)
Create a stored procedure for the combined insert:
(use appropriate types and sizes for your tables,
this is just an example of how, hot a solution)
Create procedure AddNewEMPID (@Empid nvarchar (20), @name nvarchar(20), @age int, @loginstuff1 varchar(10),...,@loginstuffN int)
as
begin
insert into login (empid, lofinstuff1,...loginstuffN) values (@empid, @loginstuff1,...,@loginstuffN);
insert into EMPDETAILS(empid,name,age) values (@empid, @name,@age);
end
call the stored procedure with values for the parameters either in code (you didn't say what language, but most have a database library like ADO.net, ADO, ODBC etc. that support parameterized query execution)
of in sql query analizer as
exec AddNewEMPID 'empidstring','l1,...,'ln', 'name','age'
|
|
|
|
|
DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. But when i'm using the following T-SQL as given below also doing rolling back with Truncate
Begin Tran
Truncate Table Rough1
Rollback
Plz make me clear.............
|
|
|
|
|
Truncate CAN be rolled back but ONLY when its part of an explicit transaction. This may help explain:
TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists. The deallocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thnx u sir
but Delete can be rolled back plz let me knw how because i just knw how to implement as explicitly not implicitly
|
|
|
|
|
What's wrong with this?
Begin Tran
delete from Rough1
Rollback
Pits fall into Chuck Norris.
|
|
|
|
|
Nothing wrong with it.
Actually it is said tht Delete can be rolled back but truncate cannot be.
//This T-SQL will be rolled back for Delete command
Begin Tran
delete from Rough1
Rollback
//This T-SQL will also be rolled back for Truncate command
Begin Tran
Truncate Rough1
Rollback
So where is the difference between Delete and Truncate command in respect of Rollback
|
|
|
|
|
Within an explicit transaction either can be rolled back. You cannot roll back from the transaction logs for a truncate (outside an explict transaction) as the changes are not inserted into the transaction logs. A delete logs each record deleted to the transaction log so it can be recovered. That is the difference.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Sir,
Can u give me an example.........
Begin Transaction Logs
Truncate Table Rough1
Rollback
Is this T-SQL given abouve ok otherwise plz let me know so tht Truncate cannot be rolled back
|
|
|
|
|
Just do not put the transaction around the truncate
Truncate Table Rough1
Bob
Ashfield Consultants Ltd
|
|
|
|
|
ok Thnx but still i m confused if i dont put the transaction around the truncate then it wont be Rolledback as i want
But if i also dont put the transaction around the delte then it wont be rolled back too.
So how can we say that a Delete can be rolledback but a truncate cannot be......
|
|
|
|
|
.NET- India wrote: So how can we say that a Delete can be rolled back but a truncate cannot be......
You seem to be missing the point, or at least not understanding what others have said.
Both delete and truncate can be rolled back within an explicit transaction, since the physical change does not happen until the transaction (explicit or auto commit) completes. In the case of a committed transaction (explicit or auto commit) only delete can be recovered from the transaction log by restoring the log with backup. It is incorrect to say that delete can be rolled back, but truncated cannot, the difference is not one of ability to rollback, but of recoverability from the transaction log after commit.
|
|
|
|
|
Thanks for trying to explain it better, but I think we are onto a loser here
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Dear Friends,
i m working on an VC++ application and using oracle as a backend. Through my application i m writing some data to Oracle database. After certain number of iterations i m getting Commit error (8197). Can anybody help me with this? What might be the reason for the same.
|
|
|
|
|
It could be deadlocks (row locks by another app).
Try figuring which data it fails on.
|
|
|
|
|
Hi Arsene,
Actually using one application i m putting data in a table and another application is accessing that data to display on the screen.
Can this lead to deadlock situation??/
When i m reading tht data from another appplication i m trying to do some operation as Distinct, SUM and Count...
Can this lead to deadlock situation??/
It will be very helpful if u can provide with some information...
Thanks in Advance
|
|
|
|
|
How would I go about enumerating constraints on a table, logging them, and then dropping them with a view to recreating them again later? I assume something in the Microsoft.SqlServer.Management.Smo namespace would be of use, but a perfunctory Google wasn't too helpful, and I have a 14h00 deadline, and it's now 11h20.
Pits fall into Chuck Norris.
|
|
|
|
|
I don't know about constraints but we have done the following with indexes.
In Enterprise Manage create an index, BEFORE saving the change you can get SQL to script the proposed changes, save to a file or clip. You can then work over the code to meet your requirements. Just like excel macros
And yeah I guess this is way to late for the deadline, but it is Sunday afternoon here now and....
[edit] script icon is next to the primary key icon [edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I appreciate if some one could help me with using InfoMessage Event of ADODB.Connection object in VB6 and SQL server.
If I pass a single SQL Print command to the ADO object, it will fire the Event and works fine, but a print command among other commands doesn't work. Am I missing something??
here is the code
Thanks
Arash
__________________________________________________________________________________________
Private WithEvents cn As ADODB.Connection
Private Sub cn_InfoMessage(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
MsgBox pError.Description
End Sub
Private Sub Comm_UPD_Rec_Click()
SQLAdd = " DECLARE @Key int " & _
"SET @Key=(select (max(task_id)+1) from task) " & _
"insert into task (task_id,...)values (@Key ,...) " & _
" PRINT @Key"
Dim cmd As New ADODB.Command
cmd.CommandType = adCmdText
cmd.CommandText = SQLAdd
Set cmd.ActiveConnection = cn
out = cmd.Execute
End Sub
__________________________________________________________________________________________
|
|
|
|