How To Audit DML Table Changes w/o Triggers?

Whenever any DML activity occurs in a database I need to audit the following:

1. The table that was changed (INS, UPD, DEL)
2. The data value of the primary key of the changed row

For example, if someone executes:

UPDATE payroll SET Salary = 50000 WHERE empid = 123

I need: "payroll" and "123"

For various business reasons I can't use triggers. It is just one of those things...

I've looked at various options on SQL Profiler and while it looks like I can tell there was an action on the "payroll" table it doesn't look likely that I'll be able to figure out that it was on primary key 123. This seems to especially be the case on the execution of a stored proc where data values are passed as @.parameters.

I know there are third-party products that analyze transaction logs with a GUI and let you export these results to a CSV file or Excel. The concern here is that I need something which is an on-going process, so the GUI and human interaction necessary to generate the file doesn't quite cut it. If I'm wrong here and there's a suggestion I'd certainly look at it.

Thanks so much!

Doug

The options are pretty much what you described. Or you could do this in your code by logging the parameters to the SP that does the modification for example. Allowing ad-hoc insert/update/delete to tables directly is not a good thing to do. In SQL Server 2005, you can use event notifications to do this easily.|||Thanks for the feedback...but I'm not sure I understand you ... I cannot use a trigger ... and the log reading tools don't seem to offer a constant flow. So the items I mention in my post won't work.

Is there any other approach?

Thanks again...I appreciate any suggestions.

Doug|||My point was that your options are limited in SQL Server 2000. I am not aware of the fulll capabilities of various 3rd party tools that work with log files directly. Did you check web site of companies like Lumigent? Another idea I was thinking about was to use replication. You could configure log reader agent to output verbose information which will include commands that are being replicated. Maybe this will help. It is hard to tell. And it almost seems like you have to modify your application if existing tools or methodologies do not meet your requirements.|||OK, this gives me a couple of leads to follow. Thank you very much for thinking this over with me, I appreciate your feedback.

All the best!

Doug

how to asssign string variable to SqlDbType

i have string variable as,

String str="Int";

now, while assigning sql parameters, i want

param.SqlDBType=SqlDBType.Int;

but, value of Int is dynamic. it may be string or double so,

i want it to be as,

param.SqlDBType=(SqlDBType)str;

but its not acceptable(its invalid cast).

in any way can i do it and how?

regards--

The SqlDBType is not the value you are passing to the database, rather it is the data type. Therefore this must match that of your table column data type.

If your column is a varchar you would assign SqlDBType.Varchar or of it is an Int you would assign SqlDBType.Int.

To assign the actual value to the parameter you use the Value property.

e.g. param.Value = <the value you want to assign to the parameter>

How to assign values to variables in a procedure in with sele

I tried that but get an error for syntax check
Error141 - A select statement that assigns a value to a variable must not be
combined with data retrieval operation.
I am using SQL Server 7 .
Any help will be greatly appreciated.
Thanks,
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT @.name = name, @.sex = sex
> FROM table1
> WHERE (something that guarantees exactly one row)
>
> "JS" <JS@.discussions.microsoft.com> wrote in message
> news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
>
>> Error141 - A select statement that assigns a value to a variable must not
> be
> combined with data retrieval operation.
Then you didn't run exactly what I posted. Could you show exactly what you
tried to run?
You can't SELECT and assign in the same statement. So, if you are sure that
the WHERE clause will always limit to one row, *and* for some reason you
need to SELECT the data *and* return it in output parameters (why would you
need to do both?), you can do this:
-- assign variable values from table
SELECT @.name = name, @.sex = sex FROM table WHERE ...
-- return *variables* to client
SELECT name = @.name, sex = @.sex|||JS wrote:
> I tried that
I doubt that you tried exactly what he said. It would help if you showed us
the revised code, but I suspect your statement now looks like:
SELECT NAME,SEX,@.name = name, @.sex = sex
from table1 where .....
Right?

> but get an error for syntax check
> Error141 - A select statement that assigns a value to a variable must
> not be combined with data retrieval operation.
That's a pretty self-explanatory error message: in a single sql statement
you can either return data to the client or assign values to variables. You
cannot do both in a single statement.
I suspect what you are trying to to do is:
SELECT name = name, @.sex = sex
from table1 where .....
SELECT @.name, @.sex

> I am using SQL Server 7 .
>
Doesn't matter
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Here it is:
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Then you didn't run exactly what I posted. Could you show exactly what yo
u
> tried to run?
> You can't SELECT and assign in the same statement. So, if you are sure th
at
> the WHERE clause will always limit to one row, *and* for some reason you
> need to SELECT the data *and* return it in output parameters (why would yo
u
> need to do both?), you can do this:
> -- assign variable values from table
> SELECT @.name = name, @.sex = sex FROM table WHERE ...
> -- return *variables* to client
> SELECT name = @.name, sex = @.sex
>
>|||Here is my code
CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.sex
VARCHAR(1) OUTPUT
AS
set rowcount 1
SELECT @.name1=nt.NAME, @.sex=ot.SEX
from Name_Table nt,Other_table ot where
nt.ssn = ot._ssn AND
nt.ssn = @.ssn
What I am trying to do is assign the values to the OUTPUT variables, so that
the client can see the values of the OUTPUT field.
Please help
"Bob Barrows [MVP]" wrote:

> JS wrote:
> I doubt that you tried exactly what he said. It would help if you showed u
s
> the revised code, but I suspect your statement now looks like:
> SELECT NAME,SEX,@.name = name, @.sex = sex
> from table1 where .....
> Right?
>
> That's a pretty self-explanatory error message: in a single sql statement
> you can either return data to the client or assign values to variables. Yo
u
> cannot do both in a single statement.
> I suspect what you are trying to to do is:
> SELECT name = name, @.sex = sex
> from table1 where .....
> SELECT @.name, @.sex
>
> Doesn't matter
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>|||> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30)
> OUTPUT,@.sex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
(a) do you not have a primary key on SSN? If so, there is no need to set
rowcount 1, since there will only ever be a maximum of one match. If there
is no primary key, why not?
(b) sorry, but there is no way that the procedure above yields the error you
mentioned earlier. Either you transcribed it wrong or you are looking at
the wrong code.|||Also,
(a) can you come up with a more useless and generic name for your procedure
than GET_DATA? At least you use the dbo prefix...
(b) I recommend better formatting so your procedure is readable.
(c) I strongly recommend against these implicit, non-ANSI join syntaxes.
How about:
CREATE PROCEDURE dbo.GetNameSexData
@.ssn VARCHAR(9),
@.name VARCHAR(30) OUTPUT,
@.sex VARCHAR(1) OUTPUT
AS
BEGIN
SET NOCOUNT ON
SET ROWCOUNT 1
SELECT
@.name = nt.Name,
@.sex = ot.Sex
FROM
Name_Table nt
INNER JOIN Other_Table ot
ON nt.ssn = ot.ssn
WHERE nt.ssn = @.ssn
END
GO
Now, if that produces an error when you *call* it, show the method you used
to *call* it.|||Got it I was declaring the variables but assigining all the values in the
select statement for the output type. Thanks very much for all the help
"JS" wrote:
> Here is my code
> CREATE PROCEDURE dbo.GET_DATA @.ssn VARCHAR(9),@.name1 VARCHAR(30) OUTPUT,@.s
ex
> VARCHAR(1) OUTPUT
> AS
> set rowcount 1
> SELECT @.name1=nt.NAME, @.sex=ot.SEX
> from Name_Table nt,Other_table ot where
> nt.ssn = ot._ssn AND
> nt.ssn = @.ssn
> What I am trying to do is assign the values to the OUTPUT variables, so th
at
> the client can see the values of the OUTPUT field.
> Please help
> "Bob Barrows [MVP]" wrote:
>

How to assign values to a variable from a xls sheet?

I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?

select Proyecto from [Carga$]

TIA,

I'm so sorry it's solved!!

I promise you that from now on I'll try not be so impatient..

How to assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.sql

How to assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.

How to assign user variable value to the Derived Column, in Data Flow Task

Hi:

In the derived column transformation editor, I have a Derived column name called FileGroupID. I would like to pass in a value for this column from a variable that I have set earlier in the scope. Can someone let me know, how to write the expression that does that and where do I specifiy that expression. I am thinking its the expression field in the derived column transformation editor. My main question is how to actually write the expression, what is the syntax to pull the variable value? Thanks.

MA2005

@.[User::YourVariableName]

Actually from the top left side you can expand the variables folder and drag-and-dorp it on the expression field; so you avoid the typing.

How to assign the @@IDENTITY to a variable

Hi,

HOW can I assign the value of @.@.IDENTITY to the any variable in SQL SERVER .

Thanks,

DECLARE @.var int
INSERT INTO <table> VALUES <...> SELECT @.var= @.@.IDENTITY

On another note, I'd recommend using SCOPE_IDENTITY() instead of @.@.IDENTITY. check out books online for some info. ScopeIdentity is more accurate in returning the autonumber id.

How to assign string value to TEXT output parameter of a stored procedure?

Hello,

I am currently trying to assign some string to a TEXT output parameter
of a stored procedure.

The basic structure of the stored procedure looks like this:

-- 8< --
CREATE PROCEDURE owner.StoredProc
(
@.blob_data image,
@.clob_data text OUTPUT
)
AS
INSERT INTO Table (blob_data, clob_data) VALUES \
(@.blob_data, @.clob_data);
GO
-- 8< --

My previous attempts include using the convert function to convert a
string into a TEXT data type:
SET @.clob_data = CONVERT(text, 'This is a test');

Unfortunately, this leads to the following error: "Error 409: The
assignment operator operation cannot take a text data type as an argument."

Is there any alternative available to make an assignment to a TEXT
output parameter?

Regards,
ThiloIs there a reason you can't just do a select on it?

How to assign OR get Time value to a colum

How can i store time value in a column. And also i need to compare the values. Another problem is , how can i get the Current time of the server.(something like getdate() ?)

Happy Coding

hi,

there's no other function aside form getdate().

if you want it to be stored automatically to a column you have to enforce a "default constraint"

thanks,

joey

|||

MasterG wrote:

How can i store time value in a column. And also i need to compare the values. Another problem is , how can i get the Current time of the server.(something like getdate() ?)

Happy Coding

Hi,

Please see answer; http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1000324&SiteID=1

Most people wont like you double posting a question here, but it looks like your new so I guess everyone will let you off, just the once

Happy Coding!

hth
Pace|||

What a shame but sorry for duplicate post. But it is caused from my web browser. Sorry for that , and also thanks for your solution.

Very Happy Coding now ha .....

how to assign null in derived column transformation editor

Dear friends, can any one tell me how to assign null to the expression value in derived column transfromation editor?

thanks,

In the editor dialog, expand the Null Functions folder, and drag the appropriate NULL(your data type here) item to the expression box. For example, you'd use NULL(DT_I4) to create a null integer.

How to assign a value to a parameter?

Hello everyone,

i have the parameter in my stored procedure that i am using as a sqldatasource.

Now in one of the events, i need to assign a value to the parameter. How can i do that?

Microsoft is changing the syntax so often, all solutions i found on this forum just don't work anymore, like:

SqlDataSource1.SelectParameters[

"@.CompareInteger"].value="1"

OR

SqlDataSource1.SelectParameters["@.CompareInteger"].DefaultValue="1"

I guess the SelectParameter - became 'ReadOnly'..But how to assign value to a parameter now?!?

Thanks for any help

They aren't changing the syntax.. Hasn't ever changed, but those methods you mentioned above only work in certain circumstances, because they are "hacks".

What you want to do is capture the SqlDataSource1_Selecting event. From within that event, you have access to the underlying command (and parameters collection). Your code from within that event would looks something like:

e.Parameters["@.CompareInteger"].Value="1;

Or

e.parameters("@.compareinteger").value="1" if you are using VB.NET

|||

it does not work, Motley. It does not work.

for SqlDataSource1_Selecting event, e does not have such an option - e.parameters - check it for yourself...:(

So, the question still remains - HOW TO ASSIGN VALUE TO A PARAMETER?

|||IS there any way to assign the value to a parameter?!?!? in any event procedure?|||

If it's not e.parameters, then it's one of the following:

e.SelectCommand.Parameters

or

e.Command.Parameters

|||

thank you, Motley ! Will remember it now.

How to assign a column's value to another column's default

I got a table. i m using SQL 2005.

tblMy
--
col1 varchar(10) NotNull
col2 varchar(10) AllowNulls
col3 varchar(10) AllowNulls

Question: How to assign the col1's value to col3's default value. If user enters a data to col3 , its ok , but if don't SQL will automatically insert the value of col1 to col3.

Happy Coding....

I don't think a default constraint will work for this. You could use a trigger though.

Alteratively, you could mimic this behavior with a view, something like:

create view myview as select col1, col2, coalesce(col3,col1) from mytable

|||

View may be solution to another problem, but not mine. But trigger is a good idea, i thought before , but im inexprienced on triggers. I'll try to learn as quick as i can. Thanks for your post.

Happy Coding...

|||

I found the answer of my own question:

To do this, i use "Computed Column Specification" (Formula Section). Obviously it is surprising no one answered this simple question except 1 person. Interesting

Happy Coding...

sql

how to assgin the null value

How to assgin null value for a variable or any value
for example
if a=1 then
customerid
else
in else part i need null but default it is taking 0
how to assign valuefor example
if a=1 then
customerid
else

try this

if a=1 then
ToText(customerid)
else
''|||this is not a NULL value, it's a blank string with is not the same. would matter if he is doing counts or anything.

to trully create a null, make a formula called @.NULL and leave it completely BLANK. then reference the formula in the IF-Statement like

IF _______
THEN _______
ELSE {@.NULL}

How to alter(add) a Table with a default value and by allowing Nulls?

Hi

I am using this query to alter a table

ALTER TABLE myTable ADD age int NULL DEFAULT(0)

But above query is adding age field by storing Nulls but not with default values

So I need to add age field to the table by storing default value as 0 and by allowing Nulls

Please advice

Thanks

Use the below query,

Code Snippet

Create table #Mytable

(

Id int,

Name varchar(100)

)

Insert Into #Mytable values(1,'One');

Insert Into #Mytable values(2,'Two');

Insert Into #Mytable values(3,'Three');

Alter table #MyTable Add Age int NOT NULL Default(0)

Alter table #MyTable Alter Column Age int NULL;

select * from #MyTable

Insert Into #Mytable(Id,Name) values(4,'Four');

select * from #MyTable

|||

Thanks Mani

I got my answer

and

Can't we do it in a single step in Sql Server?

|||

Yes, we have it,

Code Snippet

Alter table #MyTable Add Age int NULL Default(0) WITH VALUES

|||

Thanks a lot Mani, That's what I'm talking about.

With Regards

Vijay

how to Alter (add) a Table with default value

Hi

In SQL Server 2000

How to add a New column (c1) to the existing Table (T1) with default value as 0 and by allowing NULLs.

Please advice

Thanks

See this thread from a few days ago:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1890218&SiteID=1

The 'search' function is a very good thing.

|||

Thanks

I found the answer. i.e.,

Code Snippet

ALTER TABLE T1 ADD C1 int NOT NULL DEFAULT (0)

How to allow use to exec xp_sendmail

I have a stored proc that assigns a value to a field based on user input from an Access front end.

The last part of the stored proc sends an email if certain conditions are met.

It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?

The users are getting this message:

EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)You can grant a user to execute xp_sendmail by going to properties of xp_sendmail in master database and grant the user permission to execute.

How to address cells in a calculation

I have a calculation where I need to get the value of a certain cell in my cube. It looks like this:

([Measures].[Amount],

[Table].[Table].&[{327365F8-F148-4C34-A749-D3F56FD3B8F6}],

[Line].[Line].&[{2155F898-47EA-4269-B167-BE940C79E9F6}],

[Column].[Column].&[{83435F85-93AB-48D1-B2F3-B5E0BAF62642}],

[Currency].[Currency].&[{2FA606E1-0A20-4A21-B360-643A530C297B}],

[Maturity].[Maturity].&[{188601ED-2E02-4883-9336-45BC831C5EE8}],

[Region].[Region].&[{0712BAAF-FEE4-479E-8287-E75DAD405B40}],

[Sector].[Sector].&[{E52C493D-6528-490E-BD63-4A0857F8B53F}],

[PastDue].[PastDue].&[{524942A0-A1ED-4BF5-86D2-F8EF393F0004}],

[Custom].[Custom].&[{7FCAB9D1-4D7B-48CD-8EEC-1AA47F5D9CD1}],

[Situation].[Situation].&[{7187DCEE-11C9-4EC2-A2CB-BC2534716D8B}])

The problem here are the uniqueidentifiers or as you like the ID's of the members. Is there another way to select a certain cell in my cube by using other properties of the members ? I would prefer something like

(

[Measures].[Amount],

[Table].[Table].&["First Table"],

[Line].[Line].&["First Line"],

[Column].[Column].&["First Column"],

[Currency].[Currency].&["EUR"],

[Maturity].[Maturity].&["TotalMaturity"],

[Region].[Region].&["TotalRegion"],

[Sector].[Sector].&["TotalSector"],

[PastDue].[PastDue].&["TotalPastDue"],

[Custom].[Custom].&["TotalCustom"],

[Situation].[Situation].&["TotalSituation"]

)

When referencing a member you can use the "Key" value or the "Name" value. In your example:

This referes to a "Key" value

[Currency].[Currency].&[{2FA606E1-0A20-4A21-B360-643A530C297B}],

You can refer to the "Name" value using:

[Currency].[Currency].[EUR],

Notice that the "&" symbol is removed when referencing a member name.

HTH,

- Steve

|||

Absolutely.

You can use names instead of the keys.
In the simpliest case you just to drop the "&" character and make sure you provide a full path to your member.

In form of :

[Dimension].[Hierarchy].[Level].[Member]

For instance ; [Product].[Products].[Product Family].[Drink]

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you both for your answer.

I have a dimension where multiple members have the same name ... how can I address these ?

thanx in advance

|||

Answer is that it depends.

If you have two members with the same parent then:

[Dimension].[Hierarchy].[Parent Level Name].[Parent Member Name].[Member Name]

will return the value for the first member matching the "Member Name"

If the two members have different parents then:

[Dimension].[Hierarchy].[Parent Level Name].[Parent Member Name 1].[Member Name]

[Dimension].[Hierarchy].[Parent Level Name].[Parent Member Name 2].[Member Name]

can be used to reference each member individually.

HTH,

Steve

how to add the values in a column

Display The total value of all the orders put together
try sum, ie select sum(OrderValue) from orders
You may have to group the orders though, here from northwind I get a sum for each customer
select customerId,sum(freight) from orders group by customerid
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"qqqqqqqqqqqqqqqqqqqqqqq" wrote:

> Display The total value of all the orders put together
>
>

How To add the value of a variable to column in a DataSet

Hi,

i'm working on a Data Flow which uses a "Flat Sile Source" to read a CSV-file and then sends the transformed data to a "OLE DB Destination".

What i need is a way to add a column to my transformed data which contains a value from a User-Variable.

My User-Variable contains the key for the data, and this one value shall replicate to all Rows in the DataSet.

So anybody know of an existing Data Flow component, which can do this?

Regards, Martin

How about the Derived Column component? Or have I completely misunderstood the point?

Ed

|||

The Derived Column did exactly what i needed - thanks for the help!