COPY SQL QUERY

Question

Post reply

COPY SQL QUERY

Ram:)

SSCrazy

Points: 2246
More actions
July 16, 2010 at 11:24 am

#226845

Hi,
We have a database to store all the data which is updated or deleted in other databases(around 50) inculding system name, ip address, updated time etc.. I am using triggers for this..
I would like to add one more column to store the query which is used to update or delete.
Can anyone help me on this..
Ram...
🙂

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

seth delconte SSCertifiable Points: 6388 More actions · Answer 1

ALTER TABLE changed_data ADD query_run VARCHAR(255) NULL ;

GO

http://msdn.microsoft.com/en-us/library/ms190273.aspx

_________________________________
seth delconte
http://sqlkeys.com

Ram:) SSCrazy Points: 2246 More actions · Answer 2

Seth,

I am sorry for not giving my question cleary..

I would like to store the query into that column..

🙂

seth delconte SSCertifiable Points: 6388 More actions · Answer 3

I think there's a way to do that other than running Profiler to record the queries...

_________________________________
seth delconte
http://sqlkeys.com

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 4

seth delconte (7/16/2010)
I think there's a way to do that other than running Profiler to record the queries...

Cool... what is it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

seth delconte SSCertifiable Points: 6388 More actions · Answer 5

Jeff Moden (7/16/2010)
seth delconte (7/16/2010)
I think there's a way to do that other than running Profiler to record the queries...
Cool... what is it?

Typo...I meant I DON'T think there's a way...

_________________________________
seth delconte
http://sqlkeys.com

Ram:) SSCrazy Points: 2246 More actions · Answer 6

I think still I didn't give much information about my problem..

Here is the clear information..

I have around 50 database with same schema..

I have one additional database to store the data which is updated or deleted in main databases. This database also has the same schema.. In addition to remaining databases.. this database tables have extra columns like-- database_name, action(updated , deleted), modified_date, modified by(IP address), sysname

I am using insert, update triggers in main databases..what ever the data modified, deleted in main databases, stores in this database..

My interest is to add another column and want to store the query to know how this updation have been done..

Please Suggest

🙂

tim.hansen SSC Veteran Points: 296 More actions · Answer 7

If it were me, I'd modify your table to add the column that you need.

I would then modify all of your triggers to include the information you want to populate to the new column.

Living in Paradise: N 34°16'07.99" W 119°12'17.58"

Ram:) SSCrazy Points: 2246 More actions · Answer 8

Tim.

Thanks for reply..

I will do all modifications what you have given... But I want to know how to get the particular query that effected the updation or deletion.. Here I want to store that query into the new column which causes to raise the trigger..

Thanks

🙂

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 9

seth delconte (7/16/2010)
Jeff Moden (7/16/2010)
seth delconte (7/16/2010)
I think there's a way to do that other than running Profiler to record the queries...
Cool... what is it?
Typo...I meant I DON'T think there's a way...

Heh... Damn! I thought you were on to something. Thanks anyway, Seth.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003933 More actions · Answer 10

Ram:) (7/16/2010)
I think still I didn't give much information about my problem..
Here is the clear information..
I have around 50 database with same schema..
I have one additional database to store the data which is updated or deleted in main databases. This database also has the same schema.. In addition to remaining databases.. this database tables have extra columns like-- database_name, action(updated , deleted), modified_date, modified by(IP address), sysname
I am using insert, update triggers in main databases..what ever the data modified, deleted in main databases, stores in this database..
My interest is to add another column and want to store the query to know how this updation have been done..
Please Suggest

I'm not sure if you'll find it useful or not but check out the "default trace". No sense reinventing the wheel.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ram:) SSCrazy Points: 2246 More actions · Answer 11

Ram:)

SSCrazy

Points: 2246

July 16, 2010 at 4:58 pm

#1194435

Thanks all

🙂

David Webb-CDS SSCoach Points: 17398 More actions · Answer 12

Are you trying to record the text of the dml that did the update? If the updates all come through procs, you might look at this:

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/2765/

Other than that, I don't know of any way to actually capture the text of executing sql from the sql itself.

And then again, I might be wrong ...
David Webb