Case Statement Within A Select Where 2 or More Instances Of The Record Exist.

Ok,
I have a data warehouse that I am pulling records from using Oracle
SQL. I have a select statement that looks like the one below. Now what
I need to do is where the astrics are **** create a case statement or
whatever it is in Oracle to say that for this record if a 1/19/2005
record exists then End_Date needs to be=1/19/2005 else get
End_Date=12/31/9999. Keep in mind that a record could have both a
1/19/2005 and 12/31/9999 instance of that account record. If 1/19
exists that takes presedent if it doesnt then 12/31/9999. The problem
is that the fields I pull from the table where the end_date is in
question change based on which date I pull(12/31/9999 being the most
recient which in some cases as you see I dont want.) so they are not
identical. This is tricky.
Please let me know if you can help.

SELECT
COLLECTOR_RESULTS.USER_ID,
COLLECTOR_RESULTS.LETTER_CODE,
COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,
COLLECTOR_RESULTS.ACTIVITY_DATE,
COLLECTOR_RESULTS.BEGIN_DATE,
COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,
COLLECTOR_RESULTS.PLACE_CALLED,
COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,
COLLECTOR_RESULTS.ORIG_FUNC_AREA,
COLLECTOR_RESULTS.ORIG_STATE_NUMBER,
COLLECTOR_RESULTS.CACS_FUNCTION_CODE,
COLLECTOR_RESULTS.CACS_STATE_NUMBER,
COLLECTOR_RESULTS.STATE_POSITION,
COLLECTOR_RESULTS.TIME_OBTAINED,
COLLECTOR_RESULTS.TIME_RELEASED,
COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,
sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,
FROM
COLLECTOR_RESULTS,
COLLECT_ACCT_SYS_DATA,
COLLECT_ACCOUNT
WHERE
COLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)
AND
COLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)
AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)
AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)
AND COLLECTOR_RESULTS.ACTIVITY_DATE =
to_date(''01/19/2005'',''mm/dd/yyyy'')
AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')
AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************On 20 Jan 2005 11:13:31 -0800, philipdm@.msn.com wrote:

>Ok,
>I have a data warehouse that I am pulling records from using Oracle
>SQL.

Hi philipdm,

You posted this question in a newsgroup for MS SQL Server. I doubt you'll
get any specific Oracle help here.

But if I understand your requirements correctly, I think you can solve it
using only ANSI-standard SQL: correlated subquery, group by and aggregate
functions. I assume Oracle will have no trouble running those! I'm not
sure if the NULL handling requires sppecial attention (see notes down
below).

First, let me check if I correctly understand your requirements:

> Keep in mind that a record could have both a
>1/19/2005 and 12/31/9999 instance of that account record. If 1/19
>exists that takes presedent if it doesnt then 12/31/9999.

The way I read this is: check collect_acct_sys_data for a particular
acct_num / location_code combinations. If there's a row for 1/19/2005, use
that. If there's a row for 12/31/9999 but no row for 1/19/2005, use the
12/31/9999 row. If there's no row for 1/19/2005 and no row for 12/31/9999,
use no row at all - the join will fail and the rows for the other tables
that use this acct_num / location_code combination won't be included in
the result set either.

I'd use something like the following. Note: I've used table aliasses and
converted the table and column names to lower case to improve readability;
I kept the (+) symbols you included and didn't change the format of the
to_date function calls - neither of these are known in MS SQL Server, so I
have no idea if they're right or wrong.

SELECT CR.User_ID,
... (lots of other columns)
FROM Collector_Results AS CR,
Collector_Acct_Sys_Date AS CASD,
Collect_Account AS CA
WHERE CA.Acct_Num = CASD.Acct_Num(+)
AND CA.Location_Code = CASD.Location_Code(+)
AND CA.Acct_Num = CR.Acct_Num(+)
AND CA.Location_Code = CR.Location_Code(+)
AND CR.Activity_Date = to_date(''01/19/2005'',''mm/dd/yyyy'')
AND CA.End_Date = to_date(''12/31/9999'',''mm/dd/yyyy'')
AND CASD.End_Date =
(SELECT MIN(CASD2.End_Date)
FROM Collector_Acct_Sys_Date AS CASD2
WHERE CASD2.Acct_Num = CASD.Acct_Num(+)-- Do you need the
(+) here?
AND CASD2.Location_Code = CASD.Location_Code(+)-- Do you
need the (+) here?
AND ( CASD2.End_Date = to_date(''01/19/2005'',''mm/dd/yyyy'')
OR CASD2.End_Date = to_date(''12/31/9999'',''mm/dd/yyyy'')))

Final note: if the possibility exists that no row in CASD for a given
acct_num / location_code with either of the two dates, the subquery should
return NULL; the clause "AND CASD.End_Date = (subquery)" will evaluate to
"AND CASD.End_Date = NULL", which should not be true for any value of
CASD.End_Date (not even if CASD.End_Date is NULL!!). This is how NULLS
should be treated according to ANSI standard. If Oracle treats the result
of an empty subquery or comparison to NULL differently, then you should
tweak the query to get the correct results.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Carrage Returns, Stored Procedures

Question:
What would be the best way to add carrage returns to a record, and would my
method create alot of overhead and wasted space. What would be the best
method to minimize overhead and wasted space.
Scenario:
Server MS SQL 2000
Table name= mitTickets
Fields= problem,details, created, lastupdate
UPDATE [mitTickets] SET [mitTickets].details= Now() &
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
n], [mitTickets].lastupdate = Now()
WHERE (((mitTickets.ID)=1));
Current Text in record [Data Example for mitTickets.Details]
Backup failed. Backup failure investigated and found tape ejected.
I would like to create a stored procedure that will append the current Date
and Time to each update that is being submitted via a web form. I only want
the web form to add text and have the stored procedure append the input text
the the existing record so that the data looks like the following. and have
it also update the field [lastupdate]
10/4/2003 9:10:32 AM
Tape inserted, backup completed successfully.
10/4/2003 7:15:02 AM
Backup failure investigated and found tape ejected.
10/4/2003 6:27:08 AM
Backup failed.
Thank You,
DaveAssuming it is a VARCHAR(8000),
CREATE PROCEDURE dbo.addDetails
(
@.mitTicketID INT,
@.detail VARCHAR(255)
)
AS
BEGIN
DECLARE @.append VARCHAR(512)
SET @.append = CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ CONVERT(CHAR(10), GETDATE(), 101) + ' '
+ STUFF(RIGHT(CONVERT(VARCHAR(255), GETDATE(), 109), 14), 9, 4, ' ')
+ CHAR(13) + CHAR(10) + @.detail
UPDATE mitTickets
SET
details = details + @.append,
lastUpdate = GETDATE()
WHERE ID = @.mitTicketID
END
GO
You can change all that formatting to this, if you're happy with MMM DD
YYYY HH:MMAM format:
CONVERT(VARCHAR(26), GETDATE())
I don't know what you mean by overhead and wasted space; if you want a
carriage return in the column, there isn't a more efficient way than putting
a carriage return in the column. Not sure what kind of magic you might be
expecting there?
In any case, I think this should be redesigned. Why do you want to store
all that detail in a huge column? That column almost looks like a table.
Why don't you store detail rows in a relational table... it will make the
handling of adding rows much easier, it will make reporting easier, it will
certainly make *removal* of older data easier, it will avoid the problems
you'll have when you have to make it TEXT because all of your data reaches
8000 characters at some point, and it will completely eliminate the
redundancy of having a lastupdate column.
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lastupdate
> UPDATE [mitTickets] SET [mitTickets].details= Now() &
>
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
> n], [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following. and
have
> it also update the field [lastupdate]
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.
>
> Thank You,
> Dave
>|||Hi
I am not sure why you wish to add this to the database as a single field.
What would happend if you wanted to query the data e.g all activity on a
certain day?
It would be better to add the carriage returned on output, either when
selecting the data or probably more preferably through the user interface.
You can use the getdate() function to get the current system date and time.
John
"new" <dduryea@.inetmicro.com> wrote in message
news:C0Xfb.50792$nU6.8338649@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lastupdate
> UPDATE [mitTickets] SET [mitTickets].details= Now() &
>
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
> n], [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following. and
have
> it also update the field [lastupdate]
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.
>
> Thank You,
> Dave
>

Carrage Returns, Stored Procedures

Question:
What would be the best way to add carrage returns to a record, and would my
method create alot of overhead and wasted space. What would be the best
method to minimize overhead and wasted space.

Scenario:
Server MS SQL 2000
Table name= mitTickets
Fields= problem,details, created, lupdate

Current Text in record [Data Example for mitTickets.Details]
Backup failed. Backup failure investigated and found tape ejected.

I would like to create a stored procedure that will append the current Date
and Time to each update that is being submitted via a web form. I only want
the web form to add text and have the stored procedure append the input text
the the existing record so that the data looks like the following.

10/4/2003 9:10:32 AM
Tape inserted, backup completed successfully.

10/4/2003 7:15:02 AM
Backup failure investigated and found tape ejected.

10/4/2003 6:27:08 AM
Backup failed.Sorry, I meant to post my Stored Procedure

UPDATE [mitTickets] SET [mitTickets].details= Now() &
Chr(13)+Chr(10)+[@.detailstxtbox]+Chr(13)+Chr(10)+Chr(13)+Chr(10)+[descriptio
n], [mitTickets].lastupdate = Now()
WHERE (((mitTickets.ID)=1));

"new" <dduryea@.inetmicro.com> wrote in message
news:sXWfb.50789$nU6.8336240@.twister.nyc.rr.com...
> Question:
> What would be the best way to add carrage returns to a record, and would
my
> method create alot of overhead and wasted space. What would be the best
> method to minimize overhead and wasted space.
> Scenario:
> Server MS SQL 2000
> Table name= mitTickets
> Fields= problem,details, created, lupdate
>
> Current Text in record [Data Example for mitTickets.Details]
> Backup failed. Backup failure investigated and found tape ejected.
>
> I would like to create a stored procedure that will append the current
Date
> and Time to each update that is being submitted via a web form. I only
want
> the web form to add text and have the stored procedure append the input
text
> the the existing record so that the data looks like the following.
> 10/4/2003 9:10:32 AM
> Tape inserted, backup completed successfully.
> 10/4/2003 7:15:02 AM
> Backup failure investigated and found tape ejected.
> 10/4/2003 6:27:08 AM
> Backup failed.|||new (dduryea@.inetmicro.com) writes:
> Sorry, I meant to post my Stored Procedure
> UPDATE [mitTickets]
> SET [mitTickets].details = Now() & Chr(13) + Chr(10) + [@.detailstxtbox] +
> Chr(13) + Chr(10) + Chr(13) + Chr(10) +
> [description],
> [mitTickets].lastupdate = Now()
> WHERE (((mitTickets.ID)=1));

The CRLF are alright, but there are a coupld of other errors:

o There is no Now() in SQL Server. Use
convert(varchar(20), getdate, 121) to get the date.
o & is an operator for bitwise and. You want + for string concatenation.
o [@.detailslistbox] will resolve to a column with the name
@.detailslistbox. If you want refer to a variable, remove the brackets.
o While legal here, it is best to leave out the table name on the left-
hand side of the SET clause. You can only update the columns of one
table at a time, so the name is redundant here.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Use a trigger like the following to do this.

CREATE TRIGGER mitTickets_inserted_time ON mitTickets
FOR insert
AS UPDATE mitTickets
SET lupdate= GETDATE()
WHERE problem in (SELECT problem FROM INSERTED)

That will take care of it.

Regards,
-Manoj

Capturing record count for a table in Oracle and saving it in a table in SQL Server

I would like to find out how to capture record count for a table in oracle using SSIS and then writing that value in a SQL Server table.

I understand that I can use a variable to accomplish this task. Well first issue I run into is that what import statement do I need to use in the design script section of Script Task. I see that in many examples following statement is used for SQL Server databases:

Imports System.Data.SqlClient

Which Import statement I need to use to for Oracle database. I am using a OLE DB Connection.

any idea?

thanks

Why are you trying to use a Script Task? This can be achieved very easily using an Execute SQL Task.

-Jamie

|||

I'd concur with Jamie, use an Execute SQL task.

If you have to use the script task, then you need to reference the System.Data.OleDb namespace for OLEDB connections, or the System.Data.OracleClient for the Oracle client.

|||well how do I use sql task to accomplish this?|||select count(*) from table

Store the contents of that SQL statement into a single result set. Variable mapped to 0 in the Result tab.|||the result tab is frozen. how do I enable it? do I need to define an expression?|||

Set ResultSet='Single Row'

-Jamie

|||is that an expression?|||

No. ResultSet is a property of the Execute SQL Task.

-Jamie

|||gotcha.. i did set it single row. Now how do I get the value from that resultset and use it to update a column.|||On the Result Set tab, add a result, make the name 0 and specify the variable to put the count in. Then use another Execute SQL task, with an Insert statement, to write the variable to your table.|||

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?

also what would the query look like in the second sql task to get value from the variable?

is there any example I can follow?

|||No, 0 refers to the first column of the resultset (it's a zero-based index).|||

Shahab03 wrote:

just wondering why would you name result set in result name tab to zero? is that initial value of the variable?

also what would the query look like in the second sql task to get value from the variable?

is there any example I can follow?

INSERT INTO MyTable (RecordCountCol)

VALUES (?)

On the Parameter Mapping tab, add a parameter, set the variable to the same one you populated earlier, make the name 0, and set the data type appropriately.

See this for a really good overview of using the Execute SQL task. http://www.sqlis.com/58.aspx

|||

well I have a sqltask now with following properties:

query: select count(*) as EmpCompRC from empcomp

I have also defined a variable EmpCompRC. Result name in result set tab is set to 0 and variable is EmpCompRC.

Now as I understand I have setup another sql task after the defined above. Correct?

and what would that task look like? I dont see how would I pass the variable value from previous task to this one. I tried following statement but it failed:

update detail

set sourcerecordcount = @.EmpCompRC

Go

does anyone know how to setup this sqltask? query above still leaves the variable name out.

Capture the ID of the last record entered and use in an Update

I'm entering a Selection record for a partiuclar lotID,

Once entered, I need to obtain its SelectionID then use it to update a another field within that record.

Here's what I've been doing...

--insert values into a testchangeorders table

INSERT

INTO testchangeordersVALUES(2,3,3,3,1,'red',0,5)

--Find the SelectionsID of the last record created for that partiuclar LotID

SELECT

MAX(SelectionsID)

FROM

testchangeorders

WHERE

LotID= 2

--Once located, I was trying to update a field called uniqueID with a contantination of '3-' & the record's SelectionsID

UPDATE

testchangeorders

SET

UniqueID=('3-'& SelectionID

WHERE

SelectionsID=SELECTMAX(SelectionsID)AND LotID= 2)

If your SelectionsID is a identity field Use

select@.@.identity

after your insert statement.It returns the last identity value created in the cureent scope.

Have this code in a stored procedure along with the insert statement

Hope this will help you. Let me Know if you need any further help

|||Actually, you'll want to use SELECT SCOPE_IDENTITY() if you want it limited to the current scope. @.@.IDENTITY is only limited to the current session, not the current scope.|||

SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

See Examples for an illustration.

Examples

This example creates two tables,TZ andTY, and an INSERT trigger onTZ. When a row is inserted to tableTZ, the trigger (Ztrig) fires and inserts a row inTY.

USE tempdb
GO
CREATE TABLE TZ (
 Z_id int IDENTITY(1,1)PRIMARY KEY,
 Z_name varchar(20) NOT NULL)
INSERT TZ
 VALUES ('Lisa')
INSERT TZ
 VALUES ('Mike')
INSERT TZ
 VALUES ('Carla')
SELECT * FROM TZ
--Result set: This is how table TZ looks
Z_id Z_name
----
1 Lisa
2 Mike
3 Carla
CREATE TABLE TY (
 Y_id int IDENTITY(100,5)PRIMARY KEY,
 Y_name varchar(20) NULL)
INSERT TY (Y_name)
 VALUES ('boathouse')
INSERT TY (Y_name)
 VALUES ('rocks')
INSERT TY (Y_name)
 VALUES ('elevator')
SELECT * FROM TY
--Result set: This is how TY looks:
Y_id Y_name
-----
100 boathouse
105 rocks
110 elevator
/*Create the trigger that inserts a row in table TY
when a row is inserted in table TZ*/
CREATE TRIGGER Ztrig
ON TZ
FOR INSERT AS
 BEGIN
 INSERT TY VALUES ('')
 END
/*FIRE the trigger and find out what identity values you get
with the @.@.IDENTITY and SCOPE_IDENTITY functions*/
INSERT TZ VALUES ('Rosalie')
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @.@.IDENTITY AS [@.@.IDENTITY]
GO
--Here is the result set.
SCOPE_IDENTITY
4
/*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/
@.@.IDENTITY
115
/*@.@.IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on TZ*/

SQL Server Books Online

HTH 

|||

Thanks-each of you who responded.

I read and reread the last post explaining @.@.IDENTITY and SELECT SCOPE_IDENTITY(). I'm a little fuzzy as to why one would even use @.@.IDENTITY, unless in my case it doesn't matter.

I'm using the following code and if I use @.@.IDENTITY is seems to work just as well what I have below

INSERT

INTO testchangeordersVALUES(2,3,3,3,1,'red',0,5)

UPDATE

testchangeordersSET UniqueID='3-'+CONVERT(varchar,SCOPE_IDENTITY())

WHERE

SelectionsID=SCOPE_IDENTITY()

It updates the UniqueID with a "3-#" (PK) just as I want it to ie., 3-71

I guess my question is, in my case many users will be hitting this at the same time:

If user A fires it and creates ID# 22, the UniqueID should be 3-22

If user B fires it and creates ID#23, the UniqueID should be 3-23

By going with Scope_Identity() does that guarantee that records are being updated correctly? I understand why to use scope if you are updating two tables but don't know if it matters if you are working in the same table (which may be why I was advised to use @.@.IDENTITY in the first place).

I think I'm ok because the condition is WHERE SelectionsID = SCOPE_IDENTITY and that should make sure the correct one is updated.

|||

I'd say 99.9% of the time when someone wants to get the last identity created, they want to be using SCOPE_IDENTITY(). You don't need to worry about either of them getting confused by concurrent users, though, since they are both per-session. The difference generally comes into play when you have triggers on tables that will automatically create records in other tables after an action in your sproc. In that case @.@.IDENTITY will get the last identity created by the session *in any table*.

|||

Have a look into this.

http://dlanouette.blogspot.com/2005/05/sql-identity-vs-scopeidentity.html

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10434

Hope this may help you

|||

I'm clear now thanks.

AshokRaja - thanks too. That first link in particular was good.

Capture MAC address in trigger

I have a simple trigger that writes out records to another table when a user
deletes a record. I am looking for a way to capture the Network Address valu
e
that is associated with the process. Is that possible ?
Here is the trigger. I want to insert the Network Address value into the
Lastuser coulumn in the Contact1del table
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
ThanksHOST_NAME() maybe?|||jenks wrote:
> I have a simple trigger that writes out records to another table when
> a user deletes a record. I am looking for a way to capture the
> Network Address value that is associated with the process. Is that
> possible ?
> Here is the trigger. I want to insert the Network Address value into
> the Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT, TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2,
> EXT3, EXT4, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY,
> DEAR, SOURCE, KEY1, KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES,
> CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1,
> KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid
This will give you the mac address for the current spid. In your case,
you could use an embedded SELECT or grab the mac address before the
insert. For example:
Insert into #ttt (col1, col2)
Select 1, (
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid )
David Gugick - SQL Server MVP
Quest Software|||this might help:
select net_address from master..sysprocesses where spid=@.@.spid
dean
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:8B260AE5-DAF4-43CD-8A29-9232573A39EE@.microsoft.com...
>I have a simple trigger that writes out records to another table when a
>user
> deletes a record. I am looking for a way to capture the Network Address
> value
> that is associated with the process. Is that possible ?
> Here is the trigger. I want to insert the Network Address value into the
> Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
>|||Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
problem with that ? Thanks again!!
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
where spid=@.@.spid), GETDATE(), LASTTIME, RECID
from deleted|||I ordinarily advise against using WITH(NOLOCK), because it can cause queries
to return incorrect answers, but this is a special case in which the row in
sysprocesses tied to @.@.SPID cannot be changed by activity on any another
spid, so it makes better sense to eliminate the extra shared lock.
I would save off net_address and GETDATE() in local variables before
executing the INSERT. That minimizes the number of database accesses and
could make it possible to identify what was changed by the statement that
caused the trigger to fire. Of course, that would work only if you stored
@.@.SPID, too.
DECLARE @.net_address nchar(12), @.getdate DATETIME
SELECT @.net_address = net_address, @.getdate = GETDATE() from
master..sysprocesses WITH(NOLOCK) WHERE spid = @.@.SPID
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:F382F996-E346-4815-ACCF-42AD3845008F@.microsoft.com...
> Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
> problem with that ? Thanks again!!
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
> where spid=@.@.spid), GETDATE(), LASTTIME, RECID
> from deleted
>
>

Capture Before/After data on Multirow Updates

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data in
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
END
You can't assign multiple row values to a single variable. SQL just returns
the first row values -as you noticed.
For your test, change the code to
SELECT * FROM inserted
SELECT * FROM deleted
to see what is in the tables.
Provide more detail about what you are hoping to accomplish and we can help
you better.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:CD971923-195E-464C-A51B-E447526EF7ED@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>

Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data i
n
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR R
EPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
ENDYmerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I d
o
> this? I am using sp_trace_generateevent to capture before and after data
in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + '
-
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegroups.com...
> Ymerejtrebor wrote:
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>|||Dan Guzman wrote:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data in
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
ENDYmerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I do
> this? I am using sp_trace_generateevent to capture before and after data in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegroups.com...
> Ymerejtrebor wrote:
>> Problem: I only get one record of before and after data when performing
>> multirow updates from a single update statement. I want to get before
>> and
>> after data for ALL updated records from the update statement. How can I
>> do
>> this? I am using sp_trace_generateevent to capture before and after
>> data in
>> a trace when performing updates using the following code:
>> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
>> REPLICATION AS
>> BEGIN
>> PRINT 'HERE'
>> Declare @.mval nvarchar(256)
>> Declare @.mval2 nvarchar(256)
>> Declare @.mvalall nvarchar(512)
>> SELECT
>> @.mval = ' UPDATE: Before First Name: ' + au_fname +
>> ' -
>> ' + ' Last Name: ' + au_lname + ' | '
>> FROM DELETED
>> SELECT
>> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
>> Last Name: ' + au_lname + ' | '
>> FROM INSERTED
>> Set @.mvalall=@.mval + @.mval2
>> EXEC sp_trace_generateevent
>> @.event_class = 82, @.userinfo=@.mvalall
>> END
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>|||Dan Guzman wrote:
> > How about inserting direct to a table. This example assumes no nullable
> > columns are involved and that the key is key_col:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data in
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
END
Ymerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I do
> this? I am using sp_trace_generateevent to capture before and after data in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegr oups.com...
> Ymerejtrebor wrote:
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>
|||Dan Guzman wrote:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Cant write longer string in the record

I have a field which was varchar before but I changed it to text.

But i can't write in it enough text as i wish. This field is important becouse it holds SQL senences which i parse latter in my application and than i execute it.

it says <Long Text> and i can't enter any more characters.You got more than 8000 bytes of data?

Look up READTEXT WRITETEXT.

Welcome to a whole new world of hurt.

should've stayed with varchar(8000)|||Each "statement" should easily fit into an 8000-character field. Add a table that would organize the statements into batches, then restructure your original table by adding FK from your BatchMaster, and a Statement Sequence field (what statement comes first, second, etc. in each batch) Ability to read/write text fields is good, but when there is no need...there is no need ;)

Cant update a record using a DetailsView and SqlDataSource

Hi,

I'm trying to create a registration page that I've divided into multiple pages (first page for basic details, next page for address, etc.). I insert the record in the first page, and update it in the other pages. I pass the newly created ID to the other pages using the Page.PreviousPage property.

In the second page, I have the SqlDataSource configured as "SELECT * FROM [Table] WHERE ID = ?", and the UpdateCommand is "UPDATE ... WHERE ID = ?".

In Page_Load, I am updating the SelectCommand to "SELECT ... WHERE ID = " & intID, and the UpdateCommand similarly. The I do a dtlsvw.Databind()

But when I go to the next page (the newly created ID is being passed properly), the update doesn't do anything. The new record doesn't contain the values in the detailsview. Can somebody help me out?

Thanks,

Wild Thing

Does your ASP page looks like in this example from .Net help:

This section contains two code examples. The first code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a Microsoft SQL Server database using theGridView control. The second code example demonstrates how to update data in an ODBC database using theGridView control.

The following code example demonstrates how to set theUpdateCommand property of theSqlDataSource control and update data in a SQL Server database using theGridView control. TheGridView automatically populates theUpdateParameters collection, inferring the parameters from theBoundField objects, and calls theUpdate method when theUpdate link on the editableGridView is selected. This example also includes some post-processing: after a record is updated, a notification e-mail message is sent.

<%@.Page Language="VB" %><%@.Import Namespace="System.Web.Mail" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><SCRIPT runat="server"> Sub OnDSUpdatedHandler(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs) If e.AffectedRows > 0 Then ' Perform any additional processing, ' such as setting a status label after the operation. Label1.Text = Request.LogonUserIdentity.Name & _ " changed user information successfully!" Else Label1.Text = "No data updated!" End If End Sub 'OnDSUpdatedHandler</SCRIPT><HTML> <BODY> <FORM runat="server"> <asp:SqlDataSource id="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:MyNorthwind%>" SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees" UpdateCommand="Update Employees SET FirstName=@.FirstName,LastName=@.LastName,Title=@.Title WHERE EmployeeID=@.EmployeeID" OnUpdated="OnDSUpdatedHandler"> </asp:SqlDataSource> <asp:GridView id="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeID" AutoGenerateEditButton="True" DataSourceID="SqlDataSource1"> <columns> <asp:BoundField HeaderText="First Name" DataField="FirstName" /> <asp:BoundField HeaderText="Last Name" DataField="LastName" /> <asp:BoundField HeaderText="Title" DataField="Title" /> </columns> </asp:GridView> <asp:Label id="Label1" runat="server"> </asp:Label> </FORM> </BODY></HTML>

|||

Hi,

Thanks for your help! I'll go through the code you've attached.

I managed to solve the problem by binding the detailsview to the data source from scratch, and asking it to retrieve the ID parameter for the select statement from the Session Field. I don't know if it's the best alternative, but it's working!

Thanks!

Wild Thing