|
Hallo;
I would Like to know how to select last insert id from one table so as i can insert it to another table on the same event holder using SQL SERVER 2000 /2005
I am able to do the same using Mysql using the last insert Id function but cannot do the same for SQL server.
briogene
|
|
|
|
|
with the help of
@@identity
and
u can use this query as well
select max(id) from tablename
After the insertion of values
I hope this will work for u
Regards
Avesh
|
|
|
|
|
thanks for this avesh it works
briogene
|
|
|
|
|
Your Welcome...
Regards
Avesh
|
|
|
|
|
You really shouldn't do either of these, as they are both pretty bad practice. If you do a select on @@identity, you get the last inserted identity column regardless of scope. So, suppose you insert a record into your table and there is a trigger behind there that inserts into another table with an identity column on it, @@identity returns the id of the trigger insert and not the main table.
SELECT max(id) only works if there aren't multiple people posting into your tables at the same time.
The simple solution is to use:
SELECT @ID = SCOPE_IDENTITY() This returns the identity of the inserted item on the main table, even if you have lots and lots of trigger inserts going on.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Select scope_identity()
-- modified at 5:31 Monday 22nd October, 2007
Best Regards,
Chetan Patel
|
|
|
|
|
Hi Everyone,
I have a file named as "File1.txt" from "MachineA" that is under shared folder only. But my SQL SERVER 2000 is in "Machine B". I am doing sending from SQL Server 2k using Jmail.
I want to access the network file "File1.txt" from "MachineA".
If, I give machine name or ip. it doesn't take. simply it skips the attachment file and it sending only mail. If i stored the "File1.txt" in "MachineB" then its sending correct.
Please give me some idea.....
Thanks & Regards
Kumaran
|
|
|
|
|
Remember that your JMail job is running as the user that runs the SQLAgent service. I'd check to make sure this user can access the UNC you are referring to.
Try logging into the console as your SQLAgent service account and running the job interactively.
Hope it hlps.
/*
Ghazi Hadi Al Wadi, PMP, ASQ SSGB, DBA
*/
|
|
|
|
|
Hi all!
I am wondering how can I do this; here's my situation...
"I would only want to retrieve the first 5 records from the query that was executed... so lets say there is 100 records matching the query expression, but i wouldnt want them all but only retrieve the first 5..."
Thanks in advance!
Regards,
Jensen
|
|
|
|
|
SELECT TOP 5 *
FROM YourTable
|
|
|
|
|
Works like a gem!!
This make me wonder, is it possible then to display the next 5? after the first 5?
Thanks in advance!
Regards,
Jensen
|
|
|
|
|
Here is a "quick and dirty" offer of a solution:
<br />
select top 5 productid into #temp1<br />
from table1<br />
<br />
select top 10 productid <br />
from table1<br />
where productid not in<br />
(select productid from #temp1)<br />
-- modified at 9:29 Monday 22nd October, 2007
You always pass failure on the way to success.
|
|
|
|
|
If they've got identity columns and you've ordered them based on the identity then:
SELECT TOP 5 ... FROM MyTable WHERE <<Criteria>> AND ID > @ID If you pass in 0 to @ID the first time, you'll get the first 5 records. If you pass in the ID of the last record from the first select, you'll get the next 5, and so on.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi all .
I am going to consult you about a security concept .
In my database I have a set of tables (eg. City , Country , ...)
and I have a corresponding View for each Table (eg. V_City , V_Country , ...)
and There are a set of Add/Delete/Update/List stored procedures for each object , the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security? Not that I access SQL Server Database from a .Net App.
Thanks in advance.
|
|
|
|
|
DotNetWWW wrote: the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security?
Adding the rights to the views increases your attack surface. The attack surface is the amount of your system that is potentially open to abuse.
You also say that you have tables, and corresponding views. If the view is defined as SELECT * FROM CorrespondingTable then I don't see any advantage in that.
My personal opinion is that the best solution in most cases is to allow access only to the stored procedures that are required. That way SQL Server has the ability to verify the data before modifying the database, it can also veto and request for information. Whereas access to tables and views gives much wider scope for an application to abuse the database.
DotNetWWW wrote: Not that I access SQL Server Database from a .Net App.
I wouldn't think the type of application would make much difference.
|
|
|
|
|
Hi,
I am using VB in MS ACCESS and is not able to update a date field in the database, I am taking the value from a textbox in the form.
This is the query I have been using...
s = "Update Table1 set DOB = """ + Text2.Value + """ where name = """ + Text0.Value + """ "
DoCmd.RunSQL (s)
It is showing the type mismatch error.
Can somebody please help me with this.
Thanks
|
|
|
|
|
try to use formating if the field is DateTime type
example:
Update Table1 set format('dd.mm.yyyy',DOB) = '" + Text2.Value+"' where [Name]='"+Text0.Value+"'"
I Love SQL
|
|
|
|
|
Hi All the Following Statement in SQL gives me an Error, the Primary_ID is the Primary key for both tables
Ambiguous column name 'Primary_ID'.
<br />
select E1.Primary_ID, E2.Primary_ID,<br />
IsNull(E1.Lis_key, E2.Lis_key) AS Liskey, <br />
IsNull(E1.Attrib_code, E2.Attrib_code) AS Attributecode<br />
from Property_Mass E1<br />
full outer join Property_Home_Match E2<br />
on E1.Lis_key = E2.Lis_key<br />
and E1.Attrib_code = E2.Attrib_code<br />
where (E1.Primary_ID is null or Primary_ID is null)<br />
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
This is because Primary_ID is contained in Property_Home_Match and Property_Mass.
You need to prefix Primary_ID with E1 or E2 to make it clear which Primary_ID you are referring to.
You always pass failure on the way to success.
|
|
|
|
|
In your WHERE clause you reference Primary_ID twice but don't say which table it comes from the second time. Since it exists in more than one table it is ambiguous.
|
|
|
|
|
Ohh
Sorry Guys, my mistake, it will never happen again.
Am Sorry
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sudden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Don't worry about it. About 10-15 years ago I once sat for two hours trying to figure out why my C++ application wouldn't compile. I'd missed a semi-colon but the compiler had thrown up 100 completely unrelated errors.
|
|
|
|
|
what is the basic difference between @a and @@a type parameter?
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
|
You may like to make a quick reference from here[^]
|
|
|
|
|
thanQ
Soniagupta1@yahoo.co.in
Yahoo Messenger Id = soniagupta1
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
