How to get the SQL statement that caused the trigger fire ?

Hi:
Is it possible to grab the full text of the sql statement that caused a
trigger on a table to fire ?
I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
delete trigger, and in Quary analyzer i'm able to see the DBCC
INPUTBUFFER result, but i don't know how to retrieve this result in my
application code (using ADO and ADO.Net).
Please help.
Thanks
JCVoonHi,
why not look at the trigger definition onthe table...that will tell you
under what conditions the trigger will fire....
"jcvoon" wrote:

> Hi:
> Is it possible to grab the full text of the sql statement that caused a
> trigger on a table to fire ?
> I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
> delete trigger, and in Quary analyzer i'm able to see the DBCC
> INPUTBUFFER result, but i don't know how to retrieve this result in my
> application code (using ADO and ADO.Net).
> Please help.
> Thanks
> JCVoon
>

How to get the SQL statement that caused the trigger fire ?

Hi:
Is it possible to grab the full text of the sql statement that caused a
trigger on a table to fire ?
I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
delete trigger, and in Quary analyzer i'm able to see the DBCC
INPUTBUFFER result, but i don't know how to retrieve this result in my
application code (using ADO and ADO.Net).
Please help.
Thanks
JCVoonHi,
why not look at the trigger definition onthe table...that will tell you
under what conditions the trigger will fire....
"jcvoon" wrote:
> Hi:
> Is it possible to grab the full text of the sql statement that caused a
> trigger on a table to fire ?
> I try to put the DBCC INPUTBUFFER(@.@.SPID) into the 1st line of the
> delete trigger, and in Quary analyzer i'm able to see the DBCC
> INPUTBUFFER result, but i don't know how to retrieve this result in my
> application code (using ADO and ADO.Net).
> Please help.
> Thanks
> JCVoon
>

How to get the sql statement executed from external application?

Hello,

In my database (SQL Server 2005), some data were inserted from a external application.

In order to validate the data , I want to get the SQL statment executed by the application.

Is this possible?

Thanks

Robert

Hi Robert,

The Data Manipulation sql statements are not audited or logged by default.

If you want to capture SQL Statements from the application, you can use SQL Profiler.

If you need this for auditing, You could you use server side traces (its uses the same api as SQL Profiler but runs in the background).

Jag

|||

Hi Jag,

Thanks for your replay.

SQL Profiler is a good tool, I finished my work with it.

Robert

How to get the second row of a recordset?

Here's my SQL Statement (I'm using MS SQL 2000):

SELECT TOP 2 MenuComments, MenuDate, MenuID, MenuIsActive, MenuName
FROM Menu
ORDER BY MenuDate DESC

This orders the data correctly, but the problem is, I need ONLY the SECOND row, not the top row. Also, because I am sorting for menus entered into the system, I cannot use a variable based on real dates (in other words, I can't use the server clock to help filter the results).

Any and all help would be GREATLY appreciated -- I've been banging my head against this one all day!

MikeYou want the last row? Is that correct? Use the MoveLast method of the ADO recordset object. This will take you to the second row using a top 2.

eg:
dim Conn = ".... Your connection string ...."

Set dbAPI = Server.CreateObject("ADODB.Connection")
set rs1 = Set rs1 = Server.CreateObject("ADODB.Recordset")

myCmd = "select ........"

dbAPI.Open Conn

set rs1 = dbAPI.Execute myCmd

if rs1.EOF = false then

rs1.MoveLast

myvar1 = rs1("myCol1")
myvar2 = rs1("myCol2")
... and so on

end if

dbAPI.Close

Hope this helps.|||SELECT TOP 1 * FROM
(SELECT TOP 2 * FROM Menu
ORDER BY MenuDate DESC) Menu
ORDER BY Menu.MenuDate

I hope this will solve your problem.|||Thank you both VERY MUCH! The move last would work (can't believe I didn't think of it). I decided to use Rudra's subquery because it was faster (in other words, less typing for me).

Thanks again for solving a problem that was driving me crazy!

how to Get the result of executed query in file

All ,

Is it possible that i can get the result of executed select statement in a .txt file.

some thing like this

select * from mytablw to <some file name.txt>

Regards,

Ashish

You need to use BCP.

You either invoke it from a cmd window or use xp_cmdshell in SS.

Code Snippet

From cmd:

bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd

From SQL Server:

EXEC master..xp_cmdshell 'bcp "SELECT * FROM MyTable" queryout "c:\My Output File.txt" -c -Smyserver -Umylogin -Pmypswd'

Look up BCP in BOL for more options and parameters.

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.

> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.

> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
add[vbcol=seagreen]
>

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>sql

How to get the record created date?

How do I delete rows that are older than 180 days if the table does not
contain a column of timestamp? The following statement works fine if
parameter startdate is known:
DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
Thanks in advance.
-tcYou don't. Unless you can join with another table that has a datetime
column. (Every table 'should' have such a column: RecordCreated datetime)
And 'timestamp' as the timestamp datatype has nothing to do with TIME.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||SQL Server does not track this information, so you're going to have to add a
column like:
ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
CURRENT_TIMESTAMP;
Of course, you won't be able to take advantage of the values for this
specifc task until 6 more months have passed.
A
"tcw" <tcwangs@.msn.com> wrote in message
news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> How do I delete rows that are older than 180 days if the table does not
> contain a column of timestamp? The following statement works fine if
> parameter startdate is known:
> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> Thanks in advance.
> -tc
>|||> (Every table 'should' have such a column: RecordCreated datetime)
Whoa, that's a pretty broad generalization. Such a column is useful, but in
most applications it isn't necessary on every single table.
A|||Very True, but I prefer to drive home the point for the generalization, and
then once the idea has taken hold, start dealing with the exceptions. And of
course, not needed at all in some databases.
But for general business data ...
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
> Whoa, that's a pretty broad generalization. Such a column is useful, but
> in most applications it isn't necessary on every single table.
> A
>|||It would be better to drive home the fact that each row should take up as
little space as possible and then deal with the exceptions. That way you
encourage efficiency.
--
Best regards
Mark Baldwin
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Very True, but I prefer to drive home the point for the generalization,
> and then once the idea has taken hold, start dealing with the exceptions.
> And of course, not needed at all in some databases.
> But for general business data ...
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful, but
>> in most applications it isn't necessary on every single table.
>> A
>|||I'm less concerned with saving 4 or 8 bytes than with having a 'robust' data
system -I keep hearing storage is inexpensive. Perhaps it's just the segment
of large corporation and government agencies that I work with, but for the
last several years, all projects I've worked with have either required or
benefited from having not only a date column (entered/changed) -but also a
column capturing 'who' was responsible. And updates/deletes from selected
tables may be shadowed to an archive table/server. I tend to work with
VLDB's that must meet requirements set out by Aegis (law enforcement),
SarBox (financial), and/or HIPPA (medical).
Personally, I think that business systems 'should' have date and person
columns on almost every table. They solve so many of the 'problems' that
come up over a project's lifecycle. -Such as the one posted by the OP.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Mark" <swozz_@.hotmail.com> wrote in message
news:OT$O7e$pGHA.5064@.TK2MSFTNGP05.phx.gbl...
> It would be better to drive home the fact that each row should take up as
> little space as possible and then deal with the exceptions. That way you
> encourage efficiency.
> --
> Best regards
> Mark Baldwin
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eiBkZz4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> Very True, but I prefer to drive home the point for the generalization,
>> and then once the idea has taken hold, start dealing with the exceptions.
>> And of course, not needed at all in some databases.
>> But for general business data ...
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:epyLdq4pGHA.4812@.TK2MSFTNGP04.phx.gbl...
>> (Every table 'should' have such a column: RecordCreated datetime)
>> Whoa, that's a pretty broad generalization. Such a column is useful,
>> but in most applications it isn't necessary on every single table.
>> A
>>
>|||> benefited from having not only a date column (entered/changed) -but also
> a column capturing 'who' was responsible.
That kind of data, imho, belongs in a separate auditing table.
Otherwise, you are forced to either have really wide tables or only track
the LAST person who changed it.
> Personally, I think that business systems 'should' have date and person
> columns on almost every table.
I think for higher level entities that's not a bad idea. For complex OLTP
systems with hundreds of tables, you're going to drown yourself in
information overload.
A|||Thank you very much, guys. I think I will add a timestamp column to my table
next time.
-tc
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> SQL Server does not track this information, so you're going to have to add
> a column like:
> ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> CURRENT_TIMESTAMP;
> Of course, you won't be able to take advantage of the values for this
> specifc task until 6 more months have passed.
> A
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
>> How do I delete rows that are older than 180 days if the table does not
>> contain a column of timestamp? The following statement works fine if
>> parameter startdate is known:
>> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
>> Thanks in advance.
>> -tc
>|||"tcw" <tcwangs@.msn.com> wrote in message
news:egpsl9GqGHA.4932@.TK2MSFTNGP05.phx.gbl...
> Thank you very much, guys. I think I will add a timestamp column to my
table
> next time.
Note: To be perfectly clear, you wanta datetime (or smalldatetime) column.
Timestamp is a separate datatype which actually doesn't map to date or time.
> -tc
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eo6cup4pGHA.148@.TK2MSFTNGP04.phx.gbl...
> > SQL Server does not track this information, so you're going to have to
add
> > a column like:
> >
> > ALTER TABLE MyTable ADD CreatedDate SMALLDATETIME NOT NULL DEFAULT
> > CURRENT_TIMESTAMP;
> >
> > Of course, you won't be able to take advantage of the values for this
> > specifc task until 6 more months have passed.
> >
> > A
> >
> >
> > "tcw" <tcwangs@.msn.com> wrote in message
> > news:%23yZt9I4pGHA.4912@.TK2MSFTNGP05.phx.gbl...
> >> How do I delete rows that are older than 180 days if the table does not
> >> contain a column of timestamp? The following statement works fine if
> >> parameter startdate is known:
> >>
> >> DELETE From MyTable Where DATEDIFF (day, startdate, GETDATE()) > 180
> >>
> >> Thanks in advance.
> >>
> >> -tc
> >>
> >
> >
>

How to get the database name dynamically?

I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%'

and (DATABASENAME=?)

Can someone help me out?

Thanks.

Try the DB_NAME() function.

|||

DB_NAME() works but if i have to run this query dynamically in the database as per the database parameter how do i do it?

like

declare @.dbname varchar(100)

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%Master'

and DB_NAME=@.dbname

|||

You need to use dynamic SQL to execute commands in multiple databases at run-time. See example below on how to do it the safe way (without SQL injection risks):

Code Snippet

-- SQL Server 2005 version:

-- Will work with minimal changes in SQL Server 2000 too:

declare @.sp nvarchar(500), @.dbname nvarchar(128);

declare @.dbs cursor;

set @.dbs = cursor fast_forward for

select name from sys.databases

where name not in ('master', 'model', 'msdb', 'tempdb');

open @.dbs;

while(1=1)

begin

fetch @.dbs into @.dbname;

if @.@.fetch_status < 0 break;

set @.sp = quotename(@.dbname) + N'.sys.sp_executesql';

exec @.sp N'

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ''BASE TABLE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N''.'' + QUOTENAME(TABLE_NAME)),

''IsMSShipped'') = 0

'

end;

How to get the @@Identity for multiple Inserts?

Hi All,

Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @.@.Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.

Eg:-
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users

INSERT INTO UserDependent(UserID,OtherFields)
VALUES(@.@.Identity,'SomeOtherValue')

Thanks
Tanveerrewrite to a cursor insert
or
after the hughe insert, determine which are new and update/insert the depending table accordingly.
I'm not entirely sure what you mean by 'this situation' though.|||A better solution would be to create a temporary table .. lets say UserConsolidated like
(Userid,FirstName, SecondName,OtherFields) and then use this table to get data into User and UserDependent.|||At least for now, the @.@.identity values are processed as a block, in other words if you have five different inserts going (on different spids) at the same time, each block of the five will have a contiguous range of @.@.identity values. This means that if you insert 30 rows, the @.@.identity value will be for the first row, and @.@.identity+29 will be the value for the last row in your insert.

Note that this will change at some point in time, probably in the release after Yukon.

-PatP

how to get result from subtracting select statement

maybe my word will be confusing for you all..actually i want to deduct
2 select statement to get a result.this is due to not knowing how to
use coding in mssql...i'm using visual studio 2005...this is the select
statement...
SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, ' ' AS
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
SELECT a.no_akaun, a.nama_penyewa, ' ' AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
how can i deduct those to...let say
<<<if
sum1-sum2>0 then
sum3
else
sum4......>>Select case sum1-sum2>0,sum3,sum4 end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
AS
> sum2
> FROM spr_penyewa a,SPR_Lejer b
> WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
> AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
> sum2
> FROM spr_penyewa a, SPR_Lejer b
> WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa)
wiraperkasa wrote:
> maybe my word will be confusing for you all..actually i want to deduct
> 2 select statement to get a result.this is due to not knowing how to
> use coding in mssql...i'm using visual studio 2005...this is the select
> statement...
> SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, ' ' AS
> sum2
> FROM spr_penyewa a,SPR_Lejer b
> WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil = '76159')
> AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
> SELECT a.no_akaun, a.nama_penyewa, ' ' AS sum1, SUM(b.Amaun*-1) AS
> sum2
> FROM spr_penyewa a, SPR_Lejer b
> WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
> and a.no_akaun=b.no_akaun
> GROUP BY a.No_Akaun, a.nama_penyewa
> how can i deduct those to...let say
> <<<if
> sum1-sum2>0 then
> sum3
> else
> sum4......>>|||Sorry,Some typos in my Previous mail.Please correct that by this query
Select case
when sum1-sum2>0 then sum3
Else sum4
end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
AS
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE (b.ID_Hasil = '76159' AND b.Amaun > 0)-(b.ID_Hasil ='76159')
AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE (b.ID_Hasil = '76159') AND (b.Amaun < 0)
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa)|||i have rewrite the code but still i got and error..this is the select
statement..
Select case
when sum1-sum2>0 then sum3
Else sum4
end as Diff ,Sum1,sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa)
the error said that "incorrect syntax near ')'..."could somebody help
me again with this:)|||Forgot to add Alias
Select case
when A.sum1-A.sum2>0 then A.sum3
Else A.sum4
end as Diff ,A.Sum1,A.sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as
sum2
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa) A|||thank you rajdeep...but now its say "invalid column name 'sum3' and
'sum4'..."|||I mean sum3 and sum4 are the column names which you want to
show.replace with appropriate column names as you wish
I just mentioned them for your reference
wiraperkasa wrote:
> thank you rajdeep...but now its say "invalid column name 'sum3' and
> 'sum4'..."|||i change it to this..
Select case
when A.sum1-A.sum2>0 then A.sum3
Else A.sum4
end as Diff ,A.Sum1,A.sum2 from
(SELECT a.no_akaun, a.nama_penyewa, SUM(b.Amaun) AS sum1, 0 as
sum2, 0 as sum3, 0 as sum4
FROM spr_penyewa a,SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun > 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa
Union
SELECT a.no_akaun, a.nama_penyewa, 0 AS sum1, SUM(b.Amaun*-1) AS
sum2, 0 as sum3, 0 as sum4
FROM spr_penyewa a, SPR_Lejer b
WHERE b.ID_Hasil = '76159' AND b.Amaun < 0
and a.no_akaun=b.no_akaun
GROUP BY a.No_Akaun, a.nama_penyewa) A
and at the end its only show sum1 and sum2...i still lost..plz help
me..rajdeep..what do u mean by the last post?replace it with the column
from the table?i'm confuse..coz sum3 and sum4 should be another
field that are created from the "case" statement right..or am i
wrong...i'm totally lost:(

How to get Recovery Model of a SQL 2000 Database?

Hi ,

Can somebody help with an SQL Statement to list the Recovery models for
all the Databases in a server. I am trying to use the status column from sysdatabases..but i am not able to get the right statement.

Any help/references asap...

Thanks in advance.

Sasi.For SQL 2000

select name, databasepropertyex(name, 'Recovery') as RecoveryModel from master.dbo.sysdatabases order by name

For SQL 2005...

select name, recovery_model, recovery_model_desc from master.sys.databases

Good luck with it...

how to get prepared statement of sp_excute?

Hi All,
In profile,We always see such infomation
"exec sp_execute 1, N'A001', N'A001'"
But who can tell me how to get out the prepared sql for "1",
Thanks.
Look at the stmts before that one, you should be able to see one that says
something like
declare @.P1 int
set @.P1 = 1
exec sp_prepexec @.....
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> In profile,We always see such infomation
> "exec sp_execute 1, N'A001', N'A001'"
> But who can tell me how to get out the prepared sql for "1",
> Thanks.
>
|||The event log is too large,and I can't find the stmt just before that
one,because the stmt may prepare far away from that one.
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Look at the stmts before that one, you should be able to see one that says
> something like
> declare @.P1 int
> set @.P1 = 1
> exec sp_prepexec @.....
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "wy" <wy@.juyee.com> wrote in message
> news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
>
|||How about using the Find feature in Profiler?
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:uB5AyaiSEHA.3872@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> The event log is too large,and I can't find the stmt just before that
> one,because the stmt may prepare far away from that one.
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
says
>

how to get prepared statement of sp_excute?

Hi All,
In profile,We always see such infomation
"exec sp_execute 1, N'A001', N'A001'"
But who can tell me how to get out the prepared sql for "1",
Thanks.Look at the stmts before that one, you should be able to see one that says
something like
declare @.P1 int
set @.P1 = 1
exec sp_prepexec @.....
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> In profile,We always see such infomation
> "exec sp_execute 1, N'A001', N'A001'"
> But who can tell me how to get out the prepared sql for "1",
> Thanks.
>|||The event log is too large,and I can't find the stmt just before that
one,because the stmt may prepare far away from that one.
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Look at the stmts before that one, you should be able to see one that says
> something like
> declare @.P1 int
> set @.P1 = 1
> exec sp_prepexec @.....
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "wy" <wy@.juyee.com> wrote in message
> news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > In profile,We always see such infomation
> > "exec sp_execute 1, N'A001', N'A001'"
> > But who can tell me how to get out the prepared sql for "1",
> > Thanks.
> >
> >
>|||How about using the Find feature in Profiler?
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:uB5AyaiSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> The event log is too large,and I can't find the stmt just before that
> one,because the stmt may prepare far away from that one.
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
> > Look at the stmts before that one, you should be able to see one that
says
> > something like
> >
> > declare @.P1 int
> > set @.P1 = 1
> > exec sp_prepexec @.....
> >
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller backup files? Try MiniSQLBackup
> >
> >
> > "wy" <wy@.juyee.com> wrote in message
> > news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > In profile,We always see such infomation
> > > "exec sp_execute 1, N'A001', N'A001'"
> > > But who can tell me how to get out the prepared sql for "1",
> > > Thanks.
> > >
> > >
> >
> >
>

how to get prepared statement of sp_excute?

Hi All,
In profile,We always see such infomation
"exec sp_execute 1, N'A001', N'A001'"
But who can tell me how to get out the prepared sql for "1",
Thanks.Look at the stmts before that one, you should be able to see one that says
something like
declare @.P1 int
set @.P1 = 1
exec sp_prepexec @.....
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> In profile,We always see such infomation
> "exec sp_execute 1, N'A001', N'A001'"
> But who can tell me how to get out the prepared sql for "1",
> Thanks.
>|||The event log is too large,and I can't find the stmt just before that
one,because the stmt may prepare far away from that one.
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Look at the stmts before that one, you should be able to see one that says
> something like
> declare @.P1 int
> set @.P1 = 1
> exec sp_prepexec @.....
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "wy" <wy@.juyee.com> wrote in message
> news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
>|||How about using the Find feature in Profiler?
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:uB5AyaiSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> The event log is too large,and I can't find the stmt just before that
> one,because the stmt may prepare far away from that one.
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
says[vbcol=seagreen]
>

How to get generated identity value?

Hi,
How can I retrieve generated identity value after insert a row into table in case I can identify inserted row?
(the INSERT statement is dynamic constructed and queried from data access layer, not in stored procedure)
Thanks,In the same batch, you can use @.@.identity (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp) or scope_identity() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp). Once you allow the batch to end, it gets a lot trickier to get the identity value back.

-PatP

How to get from smalldatetime -field only date?

Hi!
I'm using MSDE 2000 SP4. I need to get only date from smalldatetime
using SQL statement like "SELECT MyDate FROM MyTable.".
Like this way I'll get MyDate as "27.11.2005 0:00:00" when using finnish
regional settings, but how to get just "27.11.2005" without that empty time?
I think this is quite easy to solve.
Thanks in advance!
Mika
You have to use the CONVERT Function to dispaly it in the proper way:
Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
HTH, jens Suessmeyer.
|||Jens kirjoitti:
> You have to use the CONVERT Function to dispaly it in the proper way:
> Select CONVERT(VARCHAR(10),'27.11.2005 0:00:00',103)
> HTH, jens Suessmeyer.
>
Nice, thanks Jens!
Is there a link where those different form numbers like 103 is
described? Looks like 104 is like finnish form of date, which I just
guessed.
|||Look int he BOL which is delivered within SQL Server or use the online
BOL:
http://msdn.microsoft.com/library/en...ca-co_2f3o.asp
HTH, Jens Suessmeyer.