Showing posts with label update. Show all posts
Showing posts with label update. Show all posts

Wednesday, March 28, 2012

how to get the status of update sucessing?

Dear Experts,

I have a stored procudure which can be successful called from application and update against tables, the question is how do I get the status which can indicate how many records were updated or no any record was updated in the action?

Thanks in advance.

Hi,

@.@.ERROR returns the error number for the last T-SQL statement executed while
@.@.ROWCOUNT returns the number of rows affected by the last SQL statement.

Wednesday, March 21, 2012

How to get the count of source records doing UPDATE with joined tables.

I have an sql like this in my stored procedure.
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
So, COL1 of T1 table is modified when T1 finds matching records in T2
table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
there are multiple matching records from T2. I want to make T1 table
be updated when there is exactly one matching record in T2.
Of course I can check the count of the matching records in T2 before
doing the above but T2 is actually from openquery interface(so dynamic
sql) to a remote server and I don't know exactly how I can get a
cursor with a dynamic sql.(I guess I should search this soon.)
If there is a way to find out the count of matching source records for
the above sql, it will help me a lot. Thanks..I think this should work
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
And (Select Count(*) From T2 Where T2.COL2=T1.COL2)=1
Dmitriy
"Yi, Dong-ryon" <feeva@.hanmail.net> wrote in message
news:bf8bb96c.0503131958.770f80ea@.posting.google.com...
>I have an sql like this in my stored procedure.
> UPDATE T1
> SET COL1 = T2.COL1
> FROM T1, T2
> WHERE T1.COL2=T2.COL2
> So, COL1 of T1 table is modified when T1 finds matching records in T2
> table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
> there are multiple matching records from T2. I want to make T1 table
> be updated when there is exactly one matching record in T2.
> Of course I can check the count of the matching records in T2 before
> doing the above but T2 is actually from openquery interface(so dynamic
> sql) to a remote server and I don't know exactly how I can get a
> cursor with a dynamic sql.(I guess I should search this soon.)
> If there is a way to find out the count of matching source records for
> the above sql, it will help me a lot. Thanks..|||On 13 Mar 2005 19:58:20 -0800, Yi, Dong-ryon wrote:

>I have an sql like this in my stored procedure.
>UPDATE T1
>SET COL1 = T2.COL1
>FROM T1, T2
>WHERE T1.COL2=T2.COL2
>So, COL1 of T1 table is modified when T1 finds matching records in T2
>table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
>there are multiple matching records from T2. I want to make T1 table
>be updated when there is exactly one matching record in T2.
>Of course I can check the count of the matching records in T2 before
>doing the above but T2 is actually from openquery interface(so dynamic
>sql) to a remote server and I don't know exactly how I can get a
>cursor with a dynamic sql.(I guess I should search this soon.)
>If there is a way to find out the count of matching source records for
>the above sql, it will help me a lot. Thanks..
Hi Yi,
As an alternative to the suggestion made by Dmitriy, here's a version
that refers to the T2 table in only one place:
UPDATE T1
SET Col1 = T2a.Col1
FROM T1
INNER JOIN (SELECT Col2, MIN(Col1) AS Col1
FROM T2
GROUP BY Col2
HAVING COUNT(*) = 1) AS T2a
ON T2a.Col2 = T1.Col2
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to get the client IP Address in T-SQL ?

Hi,

My problem is -
I have a trigger for auditing the changes(insert/update/delete) in the database table.That is done and is working fine. But I need to have the client's IP address from where the changes are done. That I need in T-SQL, that means, not in any web form but in the SQL/T-SQL.

As I have checked many forums, I got that there is extended stored procedure in master database named xp_cmdshell which has xplog70.dll and when we execute this stored procedure with 'ipconfig' we can get the IP Address. But I do not need that in master database. I need that in my database say myDB.

So how to proceed further. I don't know whether to create extended SP which contains DLL or is there any other option.

Pls help
Thanks in advanceHave you tried calling the stored procedure* from within your "myDB"? :)|||See this the stored procedure code and call,

create Procedure sp_get_ip_address (@.ip varchar(40) out)
as
begin
Declare @.ipLine varchar(200)
Declare @.pos int
set nocount on
set @.ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @.ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@.ipLine,'***') != '***')
begin
set @.pos = CharIndex (':',@.ipLine,1);
set @.ip = rtrim(ltrim(substring (@.ipLine ,
@.pos + 1 ,
len (@.ipLine) - @.pos)))
end
drop table #temp
set nocount off
end
go

declare @.ip varchar(40)
exec sp_get_ip_address @.ip out
print @.ip

But this is in the master Database.If I do this same thing in my database say myDB it gives error that it do not have extended SP|||Try

Exec dbo.sp_get_ip_address @.ip out|||If we exec this stored procedure in the trigger, How to store the output in the table or in some variable. Since I have tried this way - Insert #temp exec master..xp_cmdshell 'ipconfig' which gives error saying that -' insert and execute statments cannot be nested'. And if I first create the table with a column and then write - select * into #temp from exec sp_get_my_ip_address @.ip out, it gives error as incorrect syntax near exec|||Let's just confirm the DBMS that we're using...
I'm guessing SQL Server 2000, but please correct me if I'm wrong.

I'll then move the thread to the appropriate topic.|||I am using SQL Server 2005|||This works in 2000 and 2005 and might be of some use to you - let me know how you get on.

DECLARE @.host varchar(255)
SET @.host = host_name()

CREATE TABLE #Results (
Results varchar(255)
)

DECLARE @.cmd varchar(260)
SET @.cmd = 'ping ' + @.host

INSERT INTO #Results
EXEC master..xp_cmdshell @.cmd

SELECT Replace(Left(Results, CharIndex(']', Results)), 'Pinging ', '') As [client]
, host_name() As [host_name()]
FROM #Results
WHERE Results LIKE 'Pinging%'

DROP TABLE #Results|||Now my problem is - if the client machine do not have host name assigned in that case how can we ping and get the IP ?|||You can't...
Back to your method it is then...

Try this

--DROP trigger and/or table if they exist
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'TR' AND name = 'myTable_InsertUpdate') BEGIN
DROP TRIGGER myTable_InsertUpdate
END
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'myTable') BEGIN
DROP TABLE myTable
END

--Create out table; note the audit fields
CREATE TABLE myTable (
id int PRIMARY KEY NOT NULL IDENTITY(1,1)
, field1 char(1)
, changed_by_ip char(15)
, changed_by_host char(15)
, datetime_changed datetime
)
GO

--Create trgger for update and insert
CREATE TRIGGER myTable_InsertUpdate
ON myTable
FOR insert, update
AS
DECLARE @.ipLine varchar(255)
DECLARE @.pos int
DECLARE @.ip char(15)

--temporary table creation
CREATE TABLE #ip (
ipLine varchar(255)
)

--Insert the return of ipconfig into the temp table
INSERT #ip EXEC master..xp_cmdshell 'ipconfig'

--find the line which contains the IP and assign it to a variable
SET @.ipLine = (
SELECT ipLine
FROM #ip
WHERE ipLine LIKE '%IP Address%'
)

--If the IP is known
IF Coalesce(@.ipLine, '***') <> '***' BEGIN
--Find the index of the colon from the END of the string
SET @.pos = CharIndex(':', Reverse(@.ipLine), 1) - 1
--Trim the IP off the end of the string
SET @.ip = Right(@.ipLine, @.pos)
--Remove any trailing or leading white space
SET @.ip = RTrim(LTrim(@.ip))
END

--Drop the temp table
DROP TABLE #ip

--Update the audit fields based on the value being updated
UPDATE myTable
SET changed_by_ip = @.ip
, datetime_changed = GetDate()
, changed_by_host = host_name()
WHERE id IN (SELECT id FROM inserted)
GO

--Insert some test values
INSERT INTO myTable (field1) VALUES ('a')
INSERT INTO myTable (field1) VALUES ('a')
--Display initial values
SELECT * FROM myTable

--Update one of the fields
UPDATE myTable
SET field1 = 'b'
WHERE id = 2
--Display changed values.
SELECT * FROM myTable

--Notice the change in datetime_changed where id = 2
GO

--And finally; clean up after ourselves
DROP TRIGGER myTable_InsertUpdate
DROP TABLE myTable

This works on my install of 2000 and 2005.|||Let me see if I understand what the original poster (neetu bhagtani) was looking for, because this line of reasoning doesn't sound correct to me.

You have at least a SQL 2005 server, and a web server in a data center. You have clients that connect to the web server using HTTP, but those clients do not log in to the SQL Server directly.

If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.

-PatP|||See as I told you before I get an error at this line -

INSERT #ip EXEC master..xp_cmdshell 'ipconfig' (as per the code given by you)

Insert #temp exec master..xp_cmdshell 'ipconfig' (and as per the code written by me)

which gives error saying that -' insert and execute statments cannot be nested'.

Also, I would like to tell that I was working on testing environment means on local server but not on live server since it this works on testing server and only we can upload and test on live server. Live Server is SQL Server 2003

Now when I uploaded your pinging version of Stored Procedure I get an error telling that master DB owner is someone else and when I give '[dbo].master' for executing the xp_cmdshell it gives another error saying that server is not the sysservers list use 'sp_addlinkedserver' Stored Procedure to add the server in the sysservers list

Pls help|||Yes, as described below is very much true

If I've described the configuration that you've currently got, then your SQL Server can only get the client machine's IP address from the web server, because the SQL Server only "sees" the web server via TCP/IP, it never deals directly with the client so the SQL Server won't know the IP address of the client.|||So all users will appear to have the same IP... (which kinda ruins what you're trying to achieve, no?)|||Some users will access the application on web server from UK, some from US, and some from India. So all will connect to same web server on which the application resides but will have different IP addresses of their machines.|||You do realize that the IIS logs keep all of the information you are after. Right?|||You should be handling this at the application level and/or analyzing logs as MCrowley suggests. It is not physically possible to do at the database level per Pat's assertion.

Also, if you enabled the ability to fire xp_cmdshell under the privileges that your web app uses, I suggest you disable it right now.|||But how to exactly do that ? how to get it from IIS log ?|||Let's go back to the original requirement for a second. You need to get the webserver client's IP address, so you can put that into an audit trail for any updates. Is this about what you need?|||See I am inserting the data in my Audit table in the trigger while insert/update/delete and I have the log file created by IIS which has all IP Addresses. My problem is how to get the IP from log file and insert in the table since insert in the table is done in the trigger while insert/update/delete|||This sounds like it should be done by passing the variable from the client-side.
For example, if you're users are accessing the databse through a web front end then pick up the value from the client's workstation and pass it on submit of a query as one of the values.

Make sense?|||Why would this need to be done by a trigger? Why can't the webserver pass this information to you? Are there users accessing this data by methods other than the webserver?|||Hi there,

Does it have to be IP address? will hostname do? with a lookup table for IP address or something.

If you want the hostname then do this:

select hostname from sysprocesses
where spid = @.@.spid|||True, but in an IIS implementation both the web server's address and hostname ought to be constants. The original poster wants the IP address of the client, which is not available to the SQL Server.

-PatP

Monday, March 19, 2012

How to get statistics on DML statements per table

Can I get statistics on which type of DML statements (e.g. insert, delete, update) that are executed by users on a table without creating triggers? I want to be able to show the number of executed statements per statement type. I have tried the 2005 Profiler but it outputs the entire batch statement which makes it a bit more difficult to create statistics.

Rgds

Bob

There is no easy way to obtain this information. You could also take a look at 3rd party tools that can get this sort of information from the transaction logs.

Monday, March 12, 2012

How to get rid of a corrupt record

I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?Are you getting any errors? Did you try running DBCC CHECKTABLE on that
table?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
I have a corrupt record in my database that I can't query on. Nor can I
delete or update. Running any of the Select, Update or Delete statements in
QA using the WHERE clause with ID = <the corrupt record ID> causes the
system to hang until it eventually times out.
How can I get rid of this record ?|||Rakhi
You could try running DBCC Checktable. You can run this
with parameters that can try to repair errors. See BOL for
details.
Hope this helps
John|||Have you tried:
DBCC CHECKTABLE ('tablename', REPAIR_ALLOW_DATA_LOSS)
...Most likely the problem is NOT a corrupt record, but rather a hanging
connection that has the record locked.
Run your query and check the "management" section to see if your connection
is blocked.
Bruce
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Hi,
Try running dbcc checktable on that table with Repair options after taking a
database backup or table backup (Select * into bckuptable from table). If it
fails again u can try the below steps:
1. Create a new table same as the corrupted table
2. Write a script to copy the data in batches excluding the corrupted data.
3. Rename the corrupted table to table_corrupt
4. Rename the new table to original name
5. Create all indexes for this new table
Thanks
Hari
MCDBA
"RB" <rakhi.bhatia@.verizon.net> wrote in message
news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements
in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>|||Thanks everyone. Will try out all these steps and report the results.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eq#M#3WnDHA.644@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try running dbcc checktable on that table with Repair options after taking
a
> database backup or table backup (Select * into bckuptable from table). If
it
> fails again u can try the below steps:
> 1. Create a new table same as the corrupted table
> 2. Write a script to copy the data in batches excluding the corrupted
data.
> 3. Rename the corrupted table to table_corrupt
> 4. Rename the new table to original name
> 5. Create all indexes for this new table
> Thanks
> Hari
> MCDBA
>
> "RB" <rakhi.bhatia@.verizon.net> wrote in message
> news:#O8P#jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > I have a corrupt record in my database that I can't query on. Nor can I
> > delete or update. Running any of the Select, Update or Delete statements
> in
> > QA using the WHERE clause with ID = <the corrupt record ID> causes the
> > system to hang until it eventually times out.
> >
> > How can I get rid of this record ?
> >
> >
>|||To add to the other posts:
If this indeed is a corrupted row, you want to find out why it became corrupted in the first place.
If you have a hw problem, you want to fix that!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"RB" <rakhi.bhatia@.verizon.net> wrote in message news:%23O8P%23jWnDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I have a corrupt record in my database that I can't query on. Nor can I
> delete or update. Running any of the Select, Update or Delete statements in
> QA using the WHERE clause with ID = <the corrupt record ID> causes the
> system to hang until it eventually times out.
> How can I get rid of this record ?
>

How to get return value for the number of rows affected by update command

Hi,

i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?

Below is the normal way i did in vb.net, but how to check for the return value. Please help.

========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========

Thank you.you can add either of these statements to the SQL being called
[BOL} @.@.rowcount
[BOL] Rowcount_big

the difference is in the datatypes rowcount _big returns a bigint
and @.@.rowcount returns int

if you have over 2 billion rows user rowcount_big|||Hi Ruprect, thanks for your reply. My sql statement is a very simple insert query without using any parameters just like the one below:

sql = "INSERT INTO [Subscriber] ([SubID], [SubName], [SubEmail], [Status], [MailID], [SubscribeDate]) VALUES (SubID, SubName, SubEmail, 'Pending', MailID ,getDate())"

I'm unsure of how to include the " [BOL} @.@.rowcount ". Do you mean that i should add a parameter to return @.@.rowcount or there is other way to do it? I'm new to this, would you please give me an example.

Thanks for your time.|||@.@.Rowcount stored the number of records affected by the immediately prior statement. The value is lost as soon as another statement is executed, so you must either use it immediately or store it in a procedure variable:

declare @.RecordsAffected Int
.
.
.
.
.
Update/Select/Delete some records from somewhere...
set @.RecordsAffected = @.@.RowCount

Look up @.@.Rowcount in Books Online for more details.|||thanks BLIND MAN
i didnt getthis until late
[BOL] stands for Books Online it's the sql server help file
i was giving you the article title

and since blindman got it exactly i've no need to reiterate
good luck.|||see if there is something like mycommand.rowsaffected property.|||Thanks Blindman and Thanks Ruprect. I'll study BOL ;) for details of @.@.rowcount.|||You should also follow ms_sql_dba's suggestion to see if there is a method to return the value via VB.

It might be more appropriate if you are going to use the value in your VB code.|||Hi ms_sql_dba, there isn't any rowsaffected property, however there is this UpdatedRowSource and others ..

Thanks for your suggestion, although i'm unsure of their usage, i'll look into it and see if i can find something which stores the value of number of rows affected!|||Sure Blindman, i'll study both ways and see which one is more applicable for my situation. You have a great day.|||Hi Everyone,

I managed to find another solution to my question. Just simply assign the value like this line:-

rowsAffected = myCommand.ExecuteNonQuery()|||see, it was simple!|||Yea. Lesson learned! Cheers!!!

Friday, February 24, 2012

How to get info in SELECT directly instead of UPDATE? Thanks.

Greetings!

I have the 3 raw data tables below, and would like to extract some
data. My current query is really cumbersome, does anyone know a better
way to get all the info (attach the right state to the lowest zip code)
in one step? Thanks a lot! Here are the details.

- Ideal Output, for each person , keep
(1) person_id (From Table A)
(2) The earliest open_date of accounts starting with 2 (From Table A)
(3) ssn (From Table B)
(4) Zip and state info (From Table C), but only keep the lowest zip and
state.

-Table A -
person_idaccountopen_date
10001220000015/15/2003
10001220000026/20/2004
10001300000012/2/2002
10002220000038/12/2004
10002220000049/15/2004
10002300000022/16/2005

-Table B -
person_idzip state
1000111111AA
1000122222CC
1000233333BB

--Table C -
person_idssn
100011234567
100022345678

-Ideal Output-
person_idmin_openssnzipstate
100015/15/2003123456711111AA
100028/12/2004234567833333BB

Here is what I did:
Select a.person_id, min(a.open_date), b.ssn, min(c.zip) as zip
,cast (0 as varchar) as state
Into output
>From TableA as a
Join TableB as b
on a.person_id=b.person_id
Join TableC as c
on a.person_id=c.person_id
Where a.account like '2%'
Group by a.person_id, b.ssn
Order by a.person_id

Update output
Set output.state=b.state
>From output as a
Join TableC as b
on a.person_id=b.person_id
and a.zip=b.zip(rong.guo@.gmail.com) writes:
> I have the 3 raw data tables below, and would like to extract some
> data. My current query is really cumbersome, does anyone know a better
> way to get all the info (attach the right state to the lowest zip code)
> in one step? Thanks a lot! Here are the details.
> - Ideal Output, for each person , keep
> (1) person_id (From Table A)
> (2) The earliest open_date of accounts starting with 2 (From Table A)
> (3) ssn (From Table B)
> (4) Zip and state info (From Table C), but only keep the lowest zip and
> state.

For these kind of these questions, it's always recommendable to post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it possible to easily copy and paste into Query Analyzer to
develop a tested query. Since your sample data was not in this form,
this query is untested:

SELECT a1.person_id, a1.min_open, c.ssn, a1.min_zip, b.state
FROM (SELECT a.person_id, min_open = MIN(a.open_date),
min_zip = MIN(b.zip)
FROM table_a a
JOIN table_b b ON a.person_id = b_person_id
GROUP BY a.person_id) AS a1
JOIN table_b b ON a1.person_id = b.person_id
AND a1.min_zip = b.zip
JOIN table_c c ON a1.person_id = c.person_id

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland.

Because I am not familiar with CREATE and INSERT, I didn't use them
this time. Sorry for the inconvenience. I will certainly try it next
time.

Thanks again!

Sunday, February 19, 2012

How to get identity field value after recordset update

I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work.

This is what I have:

rsRec.AddNew
rsRec("RequisitionID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
intItemId = rsRec("ReqChildID")
rsRec.Close

Forgive me if this has already been posted, but I couldn't find it.

[edit to add:]

rsRec is an ADODB.Recordset in case that wasn't clear.Look at @.@.IDENTITY and SCOPE_IDENTITY in BOL.|||1 intItemId set identity for replication
2 use keyset recordset type|||I appreciate the quick replies.

I am using @.@.Identity and it works well enough for my purposes.

[edit] I found the BOL. Never even looked at it before. Thanks for pointing it out

Thanks again all.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.|||Look at SCOPE_IDENTITY... @.@.IDENTITY is global to all transactions on the table, where SCOPE_IDENTITY is limited to the scope of a single transaction. You could potentially get the ID for the wrong transaction with @.@.IDENTITY.

Agree. But it's good to know what differs those cases. Let the user choose what he needs. Give him fishing rod, not fish ;) .|||Due to time constraints I went with @.@.Identity. The database hits aren't very common so I will assume a certain small probability that there will be an error from time to time.

That being said, I would prefer Scope_Identity for 100% accuracy. I am continuing to work with SCOPE_IDENTITY when I can to try to make it work for me. As of this morning I got it to supply the correct ID using enterprise manager to test. I'll eventually get it to work in my code.

Thank you guys for helping me solve this problem.|||@.@.identity may acquire a value of anything else only when the target table contains a trigger that performs an insert into another table with identity field. if this is the case then scope_identity() needs to be used.

on another thought, you should put dml statements into a stored procedure and call it from your front end.|||I inherited this app. I would love to convert it to using stored procedures. I just need to get this section done so I can have a completed app, and a happy boss. Then I can convert as I go to make it all easier to work with.

Grrrr... I can't seem to get SCOPE_IDENTITY() to happen on the page.

I have:

set cn = Server.CreateObject("ADODB.Connection")
set rsRec = Server.CreateObject("ADODB.Recordset")
set rsIdent = Server.CreateObject("ADODB.Recordset")
cn.Open dbConnection

rsRec.Open sSQL,cn, 3 ,3
rsRec.AddNew
rsRec("ReqID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
rsRec.Close

sSQL = "SELECT SCOPE_IDENTITY() as Ident"
rsIdent.Open sSQL,cn, 3 ,3
intItemId = rsIdent("Ident")
rsIdent.Close

I have no idea why this doesn't work. I thought as long as I keep the connection(cn) open then this would work. Guess I'm wrong? Do I need to use the same recordset?|||sSQL="insert tbl (...) values (...) select ident=scope_identity()"
set rsRec=cn.execute(sSQL)|||I have simplified the statements that are actually in the code. Although that suggestion would likely make it work(as it works when I test it in the Query Analyzer), there are quite a few conditions that would affect the SQL statement and at this point I don't have the time to switch it to that style.

Its' done for the time being. I pushed it out for beta.

I'm only sorry I couldn't get it all to work fast enough, but that's what updates are for.

Thanks again for the help and pointing me in the right direction.