Getting the right grouping

I have the following view statement
SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
NULL THEN
'0' ELSE Orig_miles END AS total_miles
FROM dbo.Engine INNER JOIN
dbo.tblEventInfo ON dbo.Engine.ID =
dbo.tblEventInfo.ID LEFT OUTER JOIN
dbo.orig_miles ON dbo.Engine.EngineNumber =
dbo.orig_miles.EngineNumber
GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
dbo.tblEventInfo.EventDate
HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
(dbo.tblEventInfo.EventDate <
CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
The problem I am having is that when there are more than two records
in the data set that meet the having criteria it spits out two
records. I am looking for it to spit out a single record that is a
sum of all the records available. If I take away the second part of
the having statement it works just fine except without the date
filter(which i need). Any help is greatly appreciated.Plamen responded in .programming.
"pyrahna" <pltaylor3@.gmail.com> wrote in message
news:1180014589.891961.116170@.g4g2000hsf.googlegroups.com...
> I have the following view statement
> SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
> NULL THEN
> '0' ELSE Orig_miles END AS total_miles
> FROM dbo.Engine INNER JOIN
> dbo.tblEventInfo ON dbo.Engine.ID =
> dbo.tblEventInfo.ID LEFT OUTER JOIN
> dbo.orig_miles ON dbo.Engine.EngineNumber =
> dbo.orig_miles.EngineNumber
> GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
> dbo.tblEventInfo.EventDate
> HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
> (dbo.tblEventInfo.EventDate <
> CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
> The problem I am having is that when there are more than two records
> in the data set that meet the having criteria it spits out two
> records. I am looking for it to spit out a single record that is a
> sum of all the records available. If I take away the second part of
> the having statement it works just fine except without the date
> filter(which i need). Any help is greatly appreciated.
>

Getting the right grouping

I have the following view statement
SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
NULL THEN
'0' ELSE Orig_miles END AS total_miles
FROM dbo.Engine INNER JOIN
dbo.tblEventInfo ON dbo.Engine.ID = dbo.tblEventInfo.ID LEFT OUTER JOIN
dbo.orig_miles ON dbo.Engine.EngineNumber = dbo.orig_miles.EngineNumber
GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
dbo.tblEventInfo.EventDate
HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
(dbo.tblEventInfo.EventDate <
CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
The problem I am having is that when there are more than two records
in the data set that meet the having criteria it spits out two
records. I am looking for it to spit out a single record that is a
sum of all the records available. If I take away the second part of
the having statement it works just fine except without the date
filter(which i need). Any help is greatly appreciated.Plamen responded in .programming.
"pyrahna" <pltaylor3@.gmail.com> wrote in message
news:1180014589.891961.116170@.g4g2000hsf.googlegroups.com...
> I have the following view statement
> SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
> NULL THEN
> '0' ELSE Orig_miles END AS total_miles
> FROM dbo.Engine INNER JOIN
> dbo.tblEventInfo ON dbo.Engine.ID => dbo.tblEventInfo.ID LEFT OUTER JOIN
> dbo.orig_miles ON dbo.Engine.EngineNumber => dbo.orig_miles.EngineNumber
> GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
> dbo.tblEventInfo.EventDate
> HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
> (dbo.tblEventInfo.EventDate <
> CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
> The problem I am having is that when there are more than two records
> in the data set that meet the having criteria it spits out two
> records. I am looking for it to spit out a single record that is a
> sum of all the records available. If I take away the second part of
> the having statement it works just fine except without the date
> filter(which i need). Any help is greatly appreciated.
>

Getting the right grouping

I have the following view statement
SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
NULL THEN
'0' ELSE Orig_miles END AS total_miles
FROM dbo.Engine INNER JOIN
dbo.tblEventInfo ON dbo.Engine.ID =
dbo.tblEventInfo.ID LEFT OUTER JOIN
dbo.orig_miles ON dbo.Engine.EngineNumber =
dbo.orig_miles.EngineNumber
GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
dbo.tblEventInfo.EventDate
HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
(dbo.tblEventInfo.EventDate <
CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
The problem I am having is that when there are more than two records
in the data set that meet the having criteria it spits out two
records. I am looking for it to spit out a single record that is a
sum of all the records available. If I take away the second part of
the having statement it works just fine except without the date
filter(which i need). Any help is greatly appreciated.
Plamen responded in .programming.
"pyrahna" <pltaylor3@.gmail.com> wrote in message
news:1180014589.891961.116170@.g4g2000hsf.googlegro ups.com...
> I have the following view statement
> SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
> NULL THEN
> '0' ELSE Orig_miles END AS total_miles
> FROM dbo.Engine INNER JOIN
> dbo.tblEventInfo ON dbo.Engine.ID =
> dbo.tblEventInfo.ID LEFT OUTER JOIN
> dbo.orig_miles ON dbo.Engine.EngineNumber =
> dbo.orig_miles.EngineNumber
> GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
> dbo.tblEventInfo.EventDate
> HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
> (dbo.tblEventInfo.EventDate <
> CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
> The problem I am having is that when there are more than two records
> in the data set that meet the having criteria it spits out two
> records. I am looking for it to spit out a single record that is a
> sum of all the records available. If I take away the second part of
> the having statement it works just fine except without the date
> filter(which i need). Any help is greatly appreciated.
>

Getting the return value

Hello everyone -

Please advise on why the output value is an empty

declare
@.DynamicSQL3 NVARCHAR(4000

SET @.DynamicSQL3 = 'SELECT Security_Level_ID ' +
'FROM ' + @.DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '

exec sp_executesql @.DynamicSQL3, N'@.i int output', @.AdministratorSecurityID output

I run the query and see the grid being populated with the proper
security_level_id in the query analyzer,

but when i execute a print @.AdministratorSecurityID
there is a space character in the results window

please advise on what i am doing wrong

thanks
tonydeclare @.DynamicSQL3 NVARCHAR(4000

SET @.DynamicSQL3 = 'SELECT @.i = Security_Level_ID ' +
'FROM ' + @.DatabaseName + '.dbo.Security_Level_Master ' +
'WHERE Security_Level_Name = ''Administrator'' '

exec sp_executesql @.DynamicSQL3, N'@.i int output'
, @.AdministratorSecurityID output
-PatP|||that was the issue - many thanks!!!sql

Getting the return from a stored procedure from within a stored

Im trying to get stp1 to get the return value from stp2. stp2 returns
a int. Here is the code that I used in stp1
delcare @.return as in
set @.return = (dbo.stp2 (PARAM1, PARAM2))
I have tried different way to execute this but I get a error:
"Msg 4121, Level 16, State 1, Procedure stp1, Line 58 Cannot find
either column "dbo" or the user-defined function or aggregate
"dbo.stp2", or the name is ambiguous."
Is there a way to get the return value of a stored procedure from
within a stored procedure?Try :
declare @.return int
EXEC stp2 @.ValuePassed, @.return OUTPUT
PRINT @.return
Also , you need to make sure stp2 , is set up appropriately
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"TheVillageCodingIdiot" <whosyodaddy1019@.hotmail.com> wrote in message
news:6d0de7c3-e56c-4fba-8886-8cf70a60d646@.i72g2000hsd.googlegroups.com...
> Im trying to get stp1 to get the return value from stp2. stp2 returns
> a int. Here is the code that I used in stp1
> delcare @.return as in
> set @.return = (dbo.stp2 (PARAM1, PARAM2))
> I have tried different way to execute this but I get a error:
> "Msg 4121, Level 16, State 1, Procedure stp1, Line 58 Cannot find
> either column "dbo" or the user-defined function or aggregate
> "dbo.stp2", or the name is ambiguous."
> Is there a way to get the return value of a stored procedure from
> within a stored procedure?|||declare @.return int
exec @.return =3D dbo.stp2 @.PARAM1, @.PARAM2
select @.return
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
On Jan 24, 9:44=A0am, TheVillageCodingIdiot
<whosyodaddy1...@.hotmail.com> wrote:
> Im trying to get stp1 to get the return value from stp2. stp2 returns
> a int. Here is the code that I used in stp1
> delcare @.return as in
> set @.return =3D (dbo.stp2 (PARAM1, PARAM2))
> I have tried different way to execute this but I get a error:
> "Msg 4121, Level 16, State 1, Procedure stp1, Line 58 Cannot find
> either column "dbo" or the user-defined function or aggregate
> "dbo.stp2", or the name is ambiguous."
> Is there a way to get the return value of a stored procedure from
> within a stored procedure?

Getting the results for month and year by a single SQL query

Hi,

I've to get the results from a single table for month and year-to-date by a single query. I've two columns monthstartdate and monthenddate. If it is only for month I can assign the values and execute the query. Vice versa for year. But If I want to get results for month and year at once like below:

Reportingdate # of Views per month # of view per year

Please suggest me on writing this query.I am confused. Please give me data sample and desired result.

Thank you.|||Current Result:

PageName Month-To-Date # of views
SqlHelp.aspx 15

Expected Result: Along with the above result it should fetch result for year to date( Jan 1st 2007 to current date)

PageName Month-#-views Year-to-Date # Views
SqlHelp.aspx 5 300

Current query is like as below:
SELECT TOP(10)
PageName,
SUM([Number of Views])[Number of Views],
FROM Pages (NOLOCK)
WHERE MonthStartDt >= @.StartDt
AND MonthEndDt < DATEADD(d, 1, @.EndDt)
GROUP BY
PageName
ORDER BY SUM([Number of Views]) DESC

How do I write query to get the results for month as well as year in a single query, please suggest me.|||I need table structure like what columns are in a table and what parameters you pass to the query to get a result.
Give me something like this:
Table name [Pages]

DATA
PageName , Number of Views, MonthStartDt, MonthEndDt
Page 1 1000 01/01/2006 31/03/2006
Page 1 500 01/05/2006 31/07/2006
Page 2 7000 01/01/2006 31/03/2006

Parameters @.StartDtMonth = ?
Parameters @.EndDtMonth = ?
Parameters @.StartDtYear = ?
Parameters @.EndDtYear = ?

What result do you expect to get
PageName, Count per Month, Count per Year.

Getting The report server cannot open a connection to the report server database error

Hi

I went to my comp ->rt cick->manage-IIS->default website->rt click->properties->directory security->annoymouse access…EDIT->under Default domain it was blank, and i added a domain there.

And i was getting error opening up the reports from localhost.

I changed everything to same as it was previosly, and I am getting the error:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) (rsRPCError) Get Online Help

Cannot open database "ReportServer$SQLExpress" requested by the login. The login failed. Login failed for user 'NGES\Computer name$'.

Hi,

Maybe you should change the databse connection credential. You can do it on the SSRS Configuration manager on the Database Setup page.

change the credential type from Service Credential to Windows or SQL server credential and type a user name and password with the appropiate permnission in the SSRS Report Server and ReportServerTemp database. that user must be a member of the RSExecRole role in the database.

Another option, to cange the SSRS service credential to a windows account and add the same permission (also if you do it through the SQL Server Config manager, it assigns the appropiate permisson to that user.

regards,

Janos

Getting the primary keyy

I'm inserting a sort of data from a formview into the database. The primary key is set to increment automatically.

After the insertion I'm trying to get the primary key in order to use in another formview (same page) to insert data on different table.

The code:

asp:SqlDataSourceID="newSchemaSqlDataSource"runat="server"ConnectionString="<%$ ConnectionStrings:logprocConnectionString1 %>"

InsertCommand="INSERT INTO [LogSchema] ([Title], [Type], [InitPattern], [EndPattern], [SetupDate]) VALUES (@.Title, @.Type, @.InitPattern, @.EndPattern, @.SetupDate); SELECT @.NewID = SCOPE_IDENTITY()" OnInserted="newSchemaSqlDataSource_Inserted">

<InsertParameters>

<asp:ParameterName="Title"Type="String"/>

<asp:ParameterName="Type"Type="String"/>

<asp:ParameterName="InitPattern"Type="String"/>

<asp:ParameterName="EndPattern"Type="String"/>

<asp:ParameterName="SetupDate"Type="DateTime"/>

<asp:ParameterName="NewID"Type="Int32"/>

</InsertParameters>

Backend:

protectedvoid newSchemaSqlDataSource_Inserted(object sender,SqlDataSourceStatusEventArgs e)

{

int newid = (int)e.Command.Parameters["@.NewID"].Value;

Response.Write(newid.ToString());

}

The problem is the e.Command.Parameters["@.NewID"].Value is returning NULL as I could see at the debug. What am I missing?

Try setting the Direction property of your "NewID" Parameter to "Output".

<asp:parameter direction="Output" name="NewID" type="Int32" />

|||Thanks a lot... it works!!!!

Getting the place number for a specifik ID, from a select list

Heres the thing

Im making a booking application where it is possible to be put on standby. When you book more than there is room fore, you will be put on standby. At the same time there should be a field in the databaserow that will be set to true if you start out with being put on standby. The order of the bookings is being set by the logdate, the one who books first gets the higher place.

When you book, my idea was to first insert the data, then check to see if the place of the booking has exceeded the maximum number

Im coding in vb2005 and can easily make it by coding with the sql:

Sql= "Select nr from booking where date=@.date and ClassID=" & theID

And then make a loop with a reader ( I havent inserted any of basic code in this example!)

while reader.read

X+= 1

If reader.item("Nr") = Thenumber then exit while

End while

If x> Max number then do the Update where Standby=true

But i would like to make it simpler with just making a simple call to database and not making a list to read. Ive put in the vb example to just explain what i would like to do..

If anybody have a better idea it is very welcome!!

Dan, you might try something like this...

insert into Booking
select flight_num, reservation_num,
case
when
(
Select Count(*)
From Booking
Where New.flight_num = Booking.flight_num
) >= MaximumSeats then 'Standby'
else 'Reserved'
end as reservation_status
From NewReservations New

The subquery in the case-when-else construct will determine the number of seats already on the Booking table, and insert the record by placing Standy or Reserved in the reservation_status column . The MaximumSeats for the flight needs to be known, also...

Not sure if you store the new apps in a separate table before inserting into your reservation table, but should give you an idea...

|||

You can use the following Logic...

Update/Insert query .. Where @.RequestedNumber <= (Select Count(Room) From booking Where date=@.date and ClassID=@.ClassID);

Select Case When @.@.RowCount <> 0 Then 'Updated' Else 'StandBy' End as Status

sql

Getting the physical directory that the log file is located

I am trying to programatically determine the directory of the log files in
SQL Server.
I am using
SELECT Filename FROM master.dbo.sysdatabases
to get the data directory, now I need to find the log files.
Thanks,
RSHmaster..sysaltfiles or dbname..sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RSH" <way_beyond_oops@.yahoo.com> wrote in message news:eFWU7uc%23FHA.3064@.TK2MSFTNGP10.phx
.gbl...
>I am trying to programatically determine the directory of the log files in
> SQL Server.
> I am using
> SELECT Filename FROM master.dbo.sysdatabases
> to get the data directory, now I need to find the log files.
>
> Thanks,
> RSH
>|||There are some issues with this - like suppose you multiple data files
and/or multiple log files.
However, assuming one data file and one log file, then the file that is
not the data file is the log file...
SELECT filename
FROM sysfiles
WHERE filename NOT IN
(SELECT filename FROM master..sysdatabases WHERE name = db_name())
Payson
RSH wrote:
> I am trying to programatically determine the directory of the log files in
> SQL Server.
> I am using
> SELECT Filename FROM master.dbo.sysdatabases
> to get the data directory, now I need to find the log files.
>
> Thanks,
> RSH|||In SQL Server 2000, you can use master.dbo.sysaltfiles to return both the
data and log file directories.
In SQL Server 2005, you should use the sys.master_files catalog view
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:eFWU7uc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...
>I am trying to programatically determine the directory of the log files in
>SQL Server.
> I am using
> SELECT Filename FROM master.dbo.sysdatabases
> to get the data directory, now I need to find the log files.
>
> Thanks,
> RSH
>|||Thanks...I just realized however that when our SA installed SQL Server he
set it up so the Log files were stored in the same directory as the
Datafiles. he then later changed his mind and set it up to store the
datafiles seperate from the logfiles. The problem is that in doing so the
Master database (which I was using to find the directories of the files to
use in several stored procs) still has both files in the same directory. Is
there a way to find out where the Log files and where the Datafiles are set
to be created in programatically? In other words can I query a sys table to
find where SQL Server will by default place the Log and the Data files when
a new database is being created?
Thanks,
RSH
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23gcUrzc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...
> master..sysaltfiles or dbname..sysfiles
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:eFWU7uc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...|||I realize that but our SA has set it up so all of the log files are written
to directory A and the data files are written to direcory B.
I have the query below which indeed returns the location of the Log and Data
file for the Master DB which is not the problem. The problem is that the
Master database was created when we installed SQL Server. Since then we
went back and set it up so that the MDFs are written in one directory and
the LDFs are written to another. ALL of the databases that exist on the
server have both their LDF and MDF in the same directory. The problem is
that I have a conversion application that is about to convert thousands of
databases and I need to get the data files in the data directory and the log
files in the log directory but since all of the databases on that server
have both the logs and datafiles in the same directory i need a way to find
out the preference setting for SQL server to find out which directory to
correctly write the appropriate filetype.
Thanks,
Ron
"Payson" <payson_b@.hotmail.com> wrote in message
news:1133808837.723905.12280@.g44g2000cwa.googlegroups.com...
> There are some issues with this - like suppose you multiple data files
> and/or multiple log files.
> However, assuming one data file and one log file, then the file that is
> not the data file is the log file...
> SELECT filename
> FROM sysfiles
> WHERE filename NOT IN
> (SELECT filename FROM master..sysdatabases WHERE name = db_name())
> Payson
>
> RSH wrote:
>|||Not sure I understand your problem completely, but maybe this will
help.
The undocumented procedure sp_msforeachdb will loop through the
databases
sp_msforeachdb 'use ? SELECT ''?'' as dbname, filename FROM
sysfiles'
The status column in sysfiles will, when decoded, tell you whether the
file is a data file or a log file - see sysfiles in BOL.
Good luck.
Payson
RSH wrote:
> I realize that but our SA has set it up so all of the log files are writte
n
> to directory A and the data files are written to direcory B.
> I have the query below which indeed returns the location of the Log and Da
ta
> file for the Master DB which is not the problem. The problem is that the
> Master database was created when we installed SQL Server. Since then we
> went back and set it up so that the MDFs are written in one directory and
> the LDFs are written to another. ALL of the databases that exist on the
> server have both their LDF and MDF in the same directory. The problem is
> that I have a conversion application that is about to convert thousands of
> databases and I need to get the data files in the data directory and the l
og
> files in the log directory but since all of the databases on that server
> have both the logs and datafiles in the same directory i need a way to fin
d
> out the preference setting for SQL server to find out which directory to
> correctly write the appropriate filetype.
> Thanks,
> Ron
>
> "Payson" <payson_b@.hotmail.com> wrote in message
> news:1133808837.723905.12280@.g44g2000cwa.googlegroups.com...|||Let me word this a bit differently...Is there anyway to query the
preferences to find out where SQL Server is setup to write the Log files,
and datafiles.
I can't simply query the tables because the system databases were setup when
SQL Server was setup to write both filetypes to the same directory. Now he
has changed the directories so that each is in its own directory but since
all of the databases on the server already exist, querying the table brings
back the same data directory.
EXAMPLE:
Preferences:
Physical Directories When SQL Server was installed:
DATA: C:\Datafiles
LOGS: C:\Datafiles
The systable query returns this value when pointed at the master database:
datafile location: C:\Datafiles\master.mdf
log file location: C:\Datafiles\masterlog.ldf
NOW... The Sys Admin changed the SQL Server preferences to write datafiles
to a seperate directory than the log files:
Preferences:
DATA: L:\Datafiles
LOGS: M:\Logfiles
BUT... because SQL was installed initially with the datafiles and log files
written to the same directory, all of the databases still have their files
in the original locations which is a problem...my data conversion
application queries the systables to find the directories where the log and
data files are so I can pass that information in the create database query
when setting up the database.
The systable query returns this value when pointed at the master
database: --
datafile location: C:\Datafiles\master.mdf
log file location: C:\Datafiles\masterlog.ldf
Since none of the databases reflect the preferences setting in SQL Server
they are being written to the C:\Datafiles directory instead of being split
to the L:\Datafiles, and M:\Logfiles respectively.
"Payson" <payson_b@.hotmail.com> wrote in message
news:1133812823.499247.50450@.g43g2000cwa.googlegroups.com...
> Not sure I understand your problem completely, but maybe this will
> help.
> The undocumented procedure sp_msforeachdb will loop through the
> databases
> sp_msforeachdb 'use ? SELECT ''?'' as dbname, filename FROM
> sysfiles'
> The status column in sysfiles will, when decoded, tell you whether the
> file is a data file or a log file - see sysfiles in BOL.
> Good luck.
> Payson
> RSH wrote:
>|||Gonna try once more. I am using sysfiles in my query, which is local
to each database - hence the USE. I think sysfiles (the one in a given
database, not the one in master) will tell you the actual files for
that database. And I think the status column, when anded with 64, will
tell you whether or not this particular file is a log file.
I think.
If not, I don't have a clue.
Best of luck.
Payson
RSH wrote:
> Let me word this a bit differently...Is there anyway to query the
> preferences to find out where SQL Server is setup to write the Log files,
> and datafiles.
> I can't simply query the tables because the system databases were setup wh
en
> SQL Server was setup to write both filetypes to the same directory. Now h
e
> has changed the directories so that each is in its own directory but since
> all of the databases on the server already exist, querying the table bring
s
> back the same data directory.
> EXAMPLE:
> Preferences:
> Physical Directories When SQL Server was installed:
> DATA: C:\Datafiles
> LOGS: C:\Datafiles
> The systable query returns this value when pointed at the master database:
> datafile location: C:\Datafiles\master.mdf
> log file location: C:\Datafiles\masterlog.ldf
>
> NOW... The Sys Admin changed the SQL Server preferences to write datafiles
> to a seperate directory than the log files:
> Preferences:
> DATA: L:\Datafiles
> LOGS: M:\Logfiles
>
> BUT... because SQL was installed initially with the datafiles and log file
s
> written to the same directory, all of the databases still have their files
> in the original locations which is a problem...my data conversion
> application queries the systables to find the directories where the log an
d
> data files are so I can pass that information in the create database query
> when setting up the database.
> The systable query returns this value when pointed at the master
> database: --
> datafile location: C:\Datafiles\master.mdf
> log file location: C:\Datafiles\masterlog.ldf
> Since none of the databases reflect the preferences setting in SQL Server
> they are being written to the C:\Datafiles directory instead of being spli
t
> to the L:\Datafiles, and M:\Logfiles respectively.
>
>
> "Payson" <payson_b@.hotmail.com> wrote in message
> news:1133812823.499247.50450@.g43g2000cwa.googlegroups.com...|||> In other words can I query a sys table to find where SQL Server will by default place the
Log and
> the Data files when a new database is being created?
It is a bit more involved than that. You can define in EM default path for d
ata and log files. This
is stored in the registry at (for a default instance):
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer
In the entries "DefaultData" and "DefaultLog".
However, if you didn't set these, I believe that the path where the master d
atabase is will act as
the default path (you need to do some testing on this).
You can read the registry with TSQL using xp_instance_regread. I don't think
it is documented so use
at own risk, Google for usage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RSH" <way_beyond_oops@.yahoo.com> wrote in message news:%23LzIp3c%23FHA.600@.tk2msftngp13.ph
x.gbl...
> Thanks...I just realized however that when our SA installed SQL Server he
set it up so the Log
> files were stored in the same directory as the Datafiles. he then later c
hanged his mind and set
> it up to store the datafiles seperate from the logfiles. The problem is t
hat in doing so the
> Master database (which I was using to find the directories of the files to
use in several stored
> procs) still has both files in the same directory. Is there a way to find
out where the Log files
> and where the Datafiles are set to be created in programatically? In othe
r words can I query a
> sys table to find where SQL Server will by default place the Log and the D
ata files when a new
> database is being created?
> Thanks,
> RSH
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23gcUrzc%23FHA.3064@.TK2MSFTNGP10.phx.gbl...
>

Getting the past 24 hours worth of data?

I have a need to query my data and return only the last 24 hours (versus last day) worth of data.

My date and time fields are called:

calc_date = the date
Calc_time= the time
RealTime = Calc_date+Calc_time as realtime

I know if I use getdate()-1 that will get me the last day, but I need the last 24 hours. Any ideas on how would I do this?

I think what you're after is the DATEADD() function, so to get the start of the last 24 hours you could use:

DATEADD(hh,-24,GetDate())

e.g. SELECT DATEADD(hh,-24,GetDate()) AS [24HoursAgo]

hh = hours

|||

Hi Jim,

Follow this example, I hope it'll help you.

declare @.date nvarchar(10),

@.time nvarchar(8),

@.lastday datetime

select

@.date = '2007-07-08',

@.time = '15:45:20',

@.lastday = convert(datetime, (@.date + ' ' + @.time))

select

'Before:' as [Info],

@.date as [Date],

@.time as [Time],

@.lastday as [DateTime]

select

'After:' as [Info],

@.date as [Date],

@.time as [Time],

dateadd(hh, -24, @.lastday) as [DateTime]

|||

Actually, Jim, if you want the last 24 hours GETDATE() - 1 should work just fine. Look at this:

Code Snippet

select getdate() as now,
getdate()-1 as [24 hours ago]

/*
now 24 hours ago
-
2007-08-16 07:22:42.340 2007-08-15 07:22:42.340

|||

Your query might be like this..

Code Snippet

select

*

From

<Table Name>

Where

Cast(Calc_date+Calc_timeas datetime) between Getdate() and Getdate() -1

Getting the OUT parameter of stored proc in .cmd file.

Hi,
Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
the output parameter of stored proc in .cmd command. Based on the value i
need to terminate the program. Below are the details.
In the .cmd file we use the below syntax for truncating a table.
isql -E -S%1 -d%2 -Q"truncate table tb_mast"
Right now this needs to be replaced by calling a stored procedure by passing
the table name and get the return value to check sucess/failure through the
output parameter. If any error the program (.cmd) should terminate.
Store procedure is ready and it is working fine. Procedure will be like
sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
parameter2 will be set as 0, else 1.
How do I call the procedure in the .cmd file and check the success/failure b
y
getting the output parameter?
Please help me in this regard. Hope my explanation is clear.
Thanks in advance.
Vivek.
rHi,
May not be possible directly. Why do you want to have a command file to do
this?
You could truncate table inside the stored procedure by implementing a
condition right.If there is any technical difficulty please write back.
Thanks
Hari
SQL Server MVP
"rvivek2000" wrote:

> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to acces
s
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passi
ng
> the table name and get the return value to check sucess/failure through th
e
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure
by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
> --
> r
>|||rvivek2000 wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to acces
s
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passi
ng
> the table name and get the return value to check sucess/failure through th
e
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure
by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
>
Have a look at the EXIT command that can be used with isql. You can use
it to set the ERRORLEVEL based the results of a query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Getting the OUT parameter of stored proc in .cmd file.

Hi,
Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
the output parameter of stored proc in .cmd command. Based on the value i
need to terminate the program. Below are the details.
In the .cmd file we use the below syntax for truncating a table.
isql -E -S%1 -d%2 -Q"truncate table tb_mast"
Right now this needs to be replaced by calling a stored procedure by passing
the table name and get the return value to check sucess/failure through the
output parameter. If any error the program (.cmd) should terminate.
Store procedure is ready and it is working fine. Procedure will be like
sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
parameter2 will be set as 0, else 1.
How do I call the procedure in the .cmd file and check the success/failure by
getting the output parameter?
Please help me in this regard. Hope my explanation is clear.
Thanks in advance.
Vivek.
--
rHi,
May not be possible directly. Why do you want to have a command file to do
this?
You could truncate table inside the stored procedure by implementing a
condition right.If there is any technical difficulty please write back.
Thanks
Hari
SQL Server MVP
"rvivek2000" wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passing
> the table name and get the return value to check sucess/failure through the
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
> --
> r
>|||rvivek2000 wrote:
> Hi,
> Iam new to BCP and .cmd commands in sqlserver. I want to know how to access
> the output parameter of stored proc in .cmd command. Based on the value i
> need to terminate the program. Below are the details.
> In the .cmd file we use the below syntax for truncating a table.
> isql -E -S%1 -d%2 -Q"truncate table tb_mast"
> Right now this needs to be replaced by calling a stored procedure by passing
> the table name and get the return value to check sucess/failure through the
> output parameter. If any error the program (.cmd) should terminate.
> Store procedure is ready and it is working fine. Procedure will be like
> sp_truncate_table (paramerer1 IN, parameter2 OUTPUT). If successful
> parameter2 will be set as 0, else 1.
> How do I call the procedure in the .cmd file and check the success/failure by
> getting the output parameter?
> Please help me in this regard. Hope my explanation is clear.
> Thanks in advance.
> Vivek.
>
Have a look at the EXIT command that can be used with isql. You can use
it to set the ERRORLEVEL based the results of a query.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

getting the number pf rows in a query result

My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:

"In the forums there is X results for the word X
In the articles there is X results..."

And when the user click one of those lines, the list shows the specific results in that section.

My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…

Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)

Great thanksyou can do a

select count(*) from table where <condition>

to get the # of records matching your condition.

hthsql

getting the number of records with like values

I have a resultset that looks something like this:

Anzahl users_statdata_hobbies

499 Andere
266 Essen
60 Essen,Andere
127 Essen,Musik
10 Essen,Musik,Party,Andere
30 Essen,Party
4 Essen,Party,Andere
51 Kunst
4 Kunst,Andere
13 Kunst,Essen
4 Kunst,Essen,Andere

I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies

Of course this is not normalized but I can't change this.

Nevertheless I need to get the full number of each Hobby and not only the combination of them.

So instead or in addition to the existing recordset I need e.g

357 Essen which ist the sum of all records containing 'Essen' in the above example

The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.

Any ideas someone?

You could perform the initial select into a temp table, then count on that table.

For example:

SELECT into #temp COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies

Select sum(Anzahl), substring(users_statdata_hobbies, 1, 5)

from #temp

group by substring(users_statdata_hobbies, 1, 5)

drop table #temp

You may need to play with conversion or cast on the first column if implicit conversion won't use it as an integer.

Martin

|||

I think your best bet is writing a Table-valued user-defined function that receives in two variables, a delimited list and the delimiter character. Then split the values (i.e. Essen,Musik,Party,Andere) into a returned table. You can then either insert all your results into a temp table and count or you can use relationships with your Master Hobby table to get your counts. The benifit is you get exact counts for each of the Hobbies, not just Essen, which in my mind is like planning ahead for what you might need later.

Good luck.

|||

Hello,

I would consider splitting up the Hobby table. You should create one record per Hobby. This will make selecting and joining those records MUCH more efficient. It might look like a lot of work at first, but such a design would also allow comparisons across languages if you think about multi-language websites later on. And it will also make it possible to extract a exact number of "matching hobbies" in a single querry.

Another "problem" with storing the hobbies in a string like that would be indexing.Also is "Wein,Weib,Gesang" the same as "Gesang,Wein,Weib"? Any querry using a "like '%bla%'" wont be able to use an index on the table. The result would be that you have to scan ALL records for every time someone is searching for a "match"... And since that is most likely one of the main functions of your site, you should try to keep it as efficient as possible.

Getting the number of hours in a month

Hi,
I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.
Any ideas?
Thanks,
Skip.Hi Skip, this calculates the number of hours in the month containing the date 20040101.

select 24 * datepart("d", dateadd("d", -1, dateadd("m", 1, '20040101')))

It goes 1 month forward, then 1 day back to get the last day of the month. The 24 * converts days to hours.

This does not take into account daylight savings :-(|||declare @.month datetime
set @.month = getdate()

select datediff(hour, convert(char(7), @.month, 120)+'-01', dateadd(month, 1, convert(char(7), @.month, 120)+'-01'))|||At least for the United States, you could use:CREATE FUNCTION fHoursInMonth(@.pd1 DATETIME) RETURNS INT AS
BEGIN
DECLARE
@.dWork DATETIME

SET @.dWork = Convert(CHAR(8), @.pd1, 121) + '01'
RETURN 24 * DateDiff(day, @.dWork, DateAdd(month, 1, @.dWork))
+ CASE Month(@.dWork)
WHEN 4 THEN -1 -- Lose an hour to "Spring forward"
WHEN 10 THEN 1 -- Gain an hour from "Fall back"
ELSE 0
END
END-PatP|||I would think that datediff(hour...) would account for leap years and daylight savings.|||Not according to:SELECT a.d, dbo.fHoursInMonth(a.d), DateDiff(hour, a.d, DateAdd(month, 1, a.d))
FROM (
SELECT '2004-01-15' AS d
UNION SELECT '2004-02-15'
UNION SELECT '2004-03-15'
UNION SELECT '2004-04-15'
UNION SELECT '2004-05-15'
UNION SELECT '2004-06-15'
UNION SELECT '2004-07-15'
UNION SELECT '2004-08-15'
UNION SELECT '2004-09-15'
UNION SELECT '2004-10-15'
UNION SELECT '2004-11-15'
UNION SELECT '2004-12-15') AS aThe biggest problem is that the observance of Daylight Savings time, the dates of the changes, and even the amount of change (not everyone uses one hour) are location dependant.

-PatP|||Humph! :( :(

Getting the next or previous id

Hi there,
I need a sql query to get the next or previous sequential id where
status >= 1 or status <= 3 given a current id.
current id = 145
note: the gaps between where status is = (1, 2, 3) could be tens,
hundreds, or thousands of record.
example below:
table structure and data:
id status
10 -1
11 -1
12 1
13 ... 144 status are all = -1
145 1
146 ... 250 status are all = -1
251 1
252 ... 300 status are all = -1
1. For the next, the query should return 251
2. For the previous, the query should return 12
Your help and response is greatly appreciated.
Thanks
*** Sent via Developersdex http://www.examnotes.net ***Jun,
For a specific answer, give DDL (table definitions as CREATE TABLE
statements, sample data as INSERT statements, and desired output).
Perhaps you need something like
declare @.current int
set @.current = 145
select min(id) as nextID
from T
where T.id > @.current
and T.status between 1 and 3
select max(id) as previousID
from T
where T.id < @.current
and T.status between 1 and 3
An index on (status, id) or (id, status), depending on
the distribution of status values, may make queries like these
more efficient.
Steve Kass
Drew University
Jun Victorio wrote:

>Hi there,
>I need a sql query to get the next or previous sequential id where
>status >= 1 or status <= 3 given a current id.
>current id = 145
>note: the gaps between where status is = (1, 2, 3) could be tens,
>hundreds, or thousands of record.
>example below:
>table structure and data:
>id status
>10 -1
>11 -1
>12 1
>13 ... 144 status are all = -1
>145 1
>146 ... 250 status are all = -1
>251 1
>252 ... 300 status are all = -1
>1. For the next, the query should return 251
>2. For the previous, the query should return 12
>Your help and response is greatly appreciated.
>Thanks
>
>
>*** Sent via Developersdex http://www.examnotes.net ***
>

Getting the name of the updated table

I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide.

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

I don't know the answer, however I would strongly suggest that you would be better off scripting a trigger for each table that did the auditing. Having that level of data access in your CLR trigger is likely to perform worse than a pure TSQL trigger.

This is not a definitive statement just a word of warning.

|||

Thanks for the tip... I didn't think the performance would be that much worse. We were trying to create an auditing solution generic enough to handle all auditing, rather than writing a trigger for each table.

Thanks, again, for the response!

Craig

|||

I believe you can do this with the eventdata() function. You may need to do some XQuery to get the specific value you want because this will return an XML document describing the event. I think it's a good idea to have this one trigger to catch all your audited updates. Simple single object to manage. Simple = good!

|||

EVENTDATA returns data only when referenced directly inside of a DDL trigger.

The requirements here are for Insert Actions, thus its a DML trigger not DDL.

Actually, this is not an easy question, but I believe the answer lies in the fact that DML triggers are table specific objects for this reason. What do I mean by this? Consider this...

[Microsoft.SqlServer.Server.SqlTrigger(Name = "tri_InsertAudit", Target = "Test", Event = "FOR INSERT")]

The target attribute can only accept 1 table name (to my knowledge). And even before CLR triggers were around, even in TSQL a trigger was always declared such as...

CREATE TRIGGER trigger_name

ON <schema_name, sysname, Sales>

So DML triggers have always been thought of as a table-level entity. I believe it is this manner of thinking that is the reason there is no obvious way to extract the affected tables name, because the creators of DML triggers assume you will know the table name. So what is my answer, that to meet your auditing requirements with a DML trigger you must make it specific per Target.

I have thought up some "off the wall" solutions before for similiar tasks which usually end up involving heavy tsql usage, information schemas, and system table queries but to be honest if you have to go to this extent its probably not a good idea in the first place :)

At the least do this:

TSQL DML Trigger:

Create Trigger dbo.testtrig
On test
For Insert
As
Begin
Insert LogTable
Select I.*, 'testTable' As [Table] From inserted I
End

|||

Really then, in review, the anser is No it cannot be done.

Why? Because you cannot create one trigger for multiple tables. Thus this violates your whole intention which was to have one object for all auditing purposes.

probably not the answer you were hoping for, but I hope this helps,

Derek

|||You could have the same core function that is called by a wrapper. Having a wrapper for each table and being attached the relevant table.|||

I created a generic AuditTrigger in C# that is not Table Specific. And about half way down it has a way to retireve the TableName using SQL. ;-)

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class Triggers

{

//A Generic Trigger for Insert, Update and Delete Actions on any Table

[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()

{

SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context

string TName; //Where we store the Altered Table's Name

string User; //Where we will store the Database Username

DataRow iRow; //DataRow to hold the inserted values

DataRow dRow; //DataRow to how the deleted/overwritten values

DataRow aRow; //Audit DataRow to build our Audit entry with

string PKString; //Will temporarily store the Primary Key Column Names and Values here

using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection

{

conn.Open();//Open the Connection

//Build the AuditAdapter and Mathcing Table

SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM TestTableAudit WHERE 1=0", conn);

DataTable AuditTable = new DataTable();

AuditAdapter.FillSchema(AuditTable, SchemaType.Source);

SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert Command for us

//Get the inserted values

SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);

DataTable inserted = new DataTable();

Loader.Fill(inserted);

//Get the deleted and/or overwritten values

Loader.SelectCommand.CommandText = "SELECT * from DELETED";

DataTable deleted = new DataTable();

Loader.Fill(deleted);

//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)

SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'", conn);

TName = cmd.ExecuteScalar().ToString();

//Retrieve the UserName of the current Database User

SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);

User = curUserCommand.ExecuteScalar().ToString();

//Adapted the following command from a T-SQL audit trigger by Nigel Rivett

//http://www.nigelrivett.net/AuditTrailTrigger.html

SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@."SELECT c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = '" + TName + @."'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);

DataTable PKTable = new DataTable();

PKTableAdapter.Fill(PKTable);

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table

{

case TriggerAction.Update:

iRow = inserted.Rows[0];//Get the inserted values in row form

dRow = deleted.Rows[0];//Get the overwritten values in row form

PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string

foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns

{

if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "U";//U for Update

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry

}

}

break;

case TriggerAction.Insert:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "I";//I for Insert

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = null;

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

case TriggerAction.Delete:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "D";//D for Delete

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = null;

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

default:

//Do Nothing

break;

}

AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable

conn.Close(); //Close the Connection

}

}

//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values

//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"

public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)

{

string temp = String.Empty;

foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed

{

temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString()].ToString(), ">,"));

}

return temp;

}

}

Hope this helps. Enjoy!!!!!!!

|||

Man! I really thought you had it with:

SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'"

This works great unless the darn tables have cascade delete on them. If cascade delete is on, you'll get the last table in the delete chain. Shoot!!!

Any other ideas?

|||Chris,

have you actually tried to deploy the trigger m_shane_tx posted? As the trigger doesn't have a target, the deployment will fail (at least it does it for me). I.e AFAIK when you create a DML trigger, you have to have a target, so you can not hav a generic trigger for all tables.

Niels
|||

I'm not sure of the difference between your deployment process and mine, but I can deploy that trigger just fine using Visual Studio (without a target). Maybe you have extra constraint for triggers on your server or something, but for me that trigger works as is.

In answer to ckimmel, I am not sure why it doesn't work as is. Since the cascade should cause the trigger to fire on the next table which should do what you want.

Does the trigger not fire at each level of your cascading delete?

|||

On further thought I better understand what is most likely happening for ckimmel the transaction is probably locking multiple tables at once ( i.e all tables the cascading delete touches). So "SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'" is really going to return a list of tablenames and the ExexcuteScalar call only shows you 1 of them. You need to further constrain the 'where' clause which I am not sure is even possible. Do a google search for sys.dm_trans_locks and see what of value you can filter by to get a single value returned each time the trigger fires.

Like I said though I am not sure it can be done.

MShaneHorn

|||

nielsb try following the deployment process for the VB based generic audit trigger at the following address. http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

This was the original article that I got my inspiration from for the C# trigger I wrote. I then modified it to automatically retrieve the table name instead of requiring a specific table naming structure.

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

|||OK, I misunderstood what you were doing. I thought you somehow manged to create one generic trigger in the database, without associating the trigger with a specific table. Having read the article and the following paragraph:

<<<<<<<<<<<<<<<<<<
Now associate the CLR trigger routine with the "ADDRESS" table. With the generic trigger, this is all the code you'll need to audit a table (you can stick this into your standard template for table creation):
>>>>>>>>>>>>>>>>>

and the following code-snippet:

<<<<<<<<<<<<<<<<<<
create trigger Audit_ADDRESS
on ADDRESS for insert, update, delete
as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon
>>>>>>>>>>>>>>>>>>

I see that you actually are associating the trigger with table(s).

Niels

Getting the name of the updated table

I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide.

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

I don't know the answer, however I would strongly suggest that you would be better off scripting a trigger for each table that did the auditing. Having that level of data access in your CLR trigger is likely to perform worse than a pure TSQL trigger.

This is not a definitive statement just a word of warning.

|||

Thanks for the tip... I didn't think the performance would be that much worse. We were trying to create an auditing solution generic enough to handle all auditing, rather than writing a trigger for each table.

Thanks, again, for the response!

Craig

|||

I believe you can do this with the eventdata() function. You may need to do some XQuery to get the specific value you want because this will return an XML document describing the event. I think it's a good idea to have this one trigger to catch all your audited updates. Simple single object to manage. Simple = good!

|||

EVENTDATA returns data only when referenced directly inside of a DDL trigger.

The requirements here are for Insert Actions, thus its a DML trigger not DDL.

Actually, this is not an easy question, but I believe the answer lies in the fact that DML triggers are table specific objects for this reason. What do I mean by this? Consider this...

[Microsoft.SqlServer.Server.SqlTrigger(Name = "tri_InsertAudit", Target = "Test", Event = "FOR INSERT")]

The target attribute can only accept 1 table name (to my knowledge). And even before CLR triggers were around, even in TSQL a trigger was always declared such as...

CREATE TRIGGER trigger_name

ON <schema_name, sysname, Sales>

So DML triggers have always been thought of as a table-level entity. I believe it is this manner of thinking that is the reason there is no obvious way to extract the affected tables name, because the creators of DML triggers assume you will know the table name. So what is my answer, that to meet your auditing requirements with a DML trigger you must make it specific per Target.

I have thought up some "off the wall" solutions before for similiar tasks which usually end up involving heavy tsql usage, information schemas, and system table queries but to be honest if you have to go to this extent its probably not a good idea in the first place :)

At the least do this:

TSQL DML Trigger:

Create Trigger dbo.testtrig
On test
For Insert
As
Begin
Insert LogTable
Select I.*, 'testTable' As [Table] From inserted I
End

|||

Really then, in review, the anser is No it cannot be done.

Why? Because you cannot create one trigger for multiple tables. Thus this violates your whole intention which was to have one object for all auditing purposes.

probably not the answer you were hoping for, but I hope this helps,

Derek

|||You could have the same core function that is called by a wrapper. Having a wrapper for each table and being attached the relevant table.|||

I created a generic AuditTrigger in C# that is not Table Specific. And about half way down it has a way to retireve the TableName using SQL. ;-)

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class Triggers

{

//A Generic Trigger for Insert, Update and Delete Actions on any Table

[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()

{

SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context

string TName; //Where we store the Altered Table's Name

string User; //Where we will store the Database Username

DataRow iRow; //DataRow to hold the inserted values

DataRow dRow; //DataRow to how the deleted/overwritten values

DataRow aRow; //Audit DataRow to build our Audit entry with

string PKString; //Will temporarily store the Primary Key Column Names and Values here

using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection

{

conn.Open();//Open the Connection

//Build the AuditAdapter and Mathcing Table

SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM TestTableAudit WHERE 1=0", conn);

DataTable AuditTable = new DataTable();

AuditAdapter.FillSchema(AuditTable, SchemaType.Source);

SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert Command for us

//Get the inserted values

SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);

DataTable inserted = new DataTable();

Loader.Fill(inserted);

//Get the deleted and/or overwritten values

Loader.SelectCommand.CommandText = "SELECT * from DELETED";

DataTable deleted = new DataTable();

Loader.Fill(deleted);

//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)

SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'", conn);

TName = cmd.ExecuteScalar().ToString();

//Retrieve the UserName of the current Database User

SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);

User = curUserCommand.ExecuteScalar().ToString();

//Adapted the following command from a T-SQL audit trigger by Nigel Rivett

//http://www.nigelrivett.net/AuditTrailTrigger.html

SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@."SELECT c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = '" + TName + @."'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);

DataTable PKTable = new DataTable();

PKTableAdapter.Fill(PKTable);

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table

{

case TriggerAction.Update:

iRow = inserted.Rows[0];//Get the inserted values in row form

dRow = deleted.Rows[0];//Get the overwritten values in row form

PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string

foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns

{

if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "U";//U for Update

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry

}

}

break;

case TriggerAction.Insert:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "I";//I for Insert

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = null;

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

case TriggerAction.Delete:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "D";//D for Delete

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = null;

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

default:

//Do Nothing

break;

}

AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable

conn.Close(); //Close the Connection

}

}

//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values

//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"

public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)

{

string temp = String.Empty;

foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed

{

temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString()].ToString(), ">,"));

}

return temp;

}

}

Hope this helps. Enjoy!!!!!!!

|||

Man! I really thought you had it with:

SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'"

This works great unless the darn tables have cascade delete on them. If cascade delete is on, you'll get the last table in the delete chain. Shoot!!!

Any other ideas?

|||Chris,

have you actually tried to deploy the trigger m_shane_tx posted? As the trigger doesn't have a target, the deployment will fail (at least it does it for me). I.e AFAIK when you create a DML trigger, you have to have a target, so you can not hav a generic trigger for all tables.

Niels
|||

I'm not sure of the difference between your deployment process and mine, but I can deploy that trigger just fine using Visual Studio (without a target). Maybe you have extra constraint for triggers on your server or something, but for me that trigger works as is.

In answer to ckimmel, I am not sure why it doesn't work as is. Since the cascade should cause the trigger to fire on the next table which should do what you want.

Does the trigger not fire at each level of your cascading delete?

|||

On further thought I better understand what is most likely happening for ckimmel the transaction is probably locking multiple tables at once ( i.e all tables the cascading delete touches). So "SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'" is really going to return a list of tablenames and the ExexcuteScalar call only shows you 1 of them. You need to further constrain the 'where' clause which I am not sure is even possible. Do a google search for sys.dm_trans_locks and see what of value you can filter by to get a single value returned each time the trigger fires.

Like I said though I am not sure it can be done.

MShaneHorn

|||

nielsb try following the deployment process for the VB based generic audit trigger at the following address. http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

This was the original article that I got my inspiration from for the C# trigger I wrote. I then modified it to automatically retrieve the table name instead of requiring a specific table naming structure.

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

|||OK, I misunderstood what you were doing. I thought you somehow manged to create one generic trigger in the database, without associating the trigger with a specific table. Having read the article and the following paragraph:

<<<<<<<<<<<<<<<<<<
Now associate the CLR trigger routine with the "ADDRESS" table. With the generic trigger, this is all the code you'll need to audit a table (you can stick this into your standard template for table creation):
>>>>>>>>>>>>>>>>>

and the following code-snippet:

<<<<<<<<<<<<<<<<<<
create trigger Audit_ADDRESS
on ADDRESS for insert, update, delete
as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon
>>>>>>>>>>>>>>>>>>

I see that you actually are associating the trigger with table(s).

Niels
sql

Getting the name of the updated table

I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide.

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

I don't know the answer, however I would strongly suggest that you would be better off scripting a trigger for each table that did the auditing. Having that level of data access in your CLR trigger is likely to perform worse than a pure TSQL trigger.

This is not a definitive statement just a word of warning.

|||

Thanks for the tip... I didn't think the performance would be that much worse. We were trying to create an auditing solution generic enough to handle all auditing, rather than writing a trigger for each table.

Thanks, again, for the response!

Craig

|||

I believe you can do this with the eventdata() function. You may need to do some XQuery to get the specific value you want because this will return an XML document describing the event. I think it's a good idea to have this one trigger to catch all your audited updates. Simple single object to manage. Simple = good!

|||

EVENTDATA returns data only when referenced directly inside of a DDL trigger.

The requirements here are for Insert Actions, thus its a DML trigger not DDL.

Actually, this is not an easy question, but I believe the answer lies in the fact that DML triggers are table specific objects for this reason. What do I mean by this? Consider this...

[Microsoft.SqlServer.Server.SqlTrigger(Name = "tri_InsertAudit", Target = "Test", Event = "FOR INSERT")]

The target attribute can only accept 1 table name (to my knowledge). And even before CLR triggers were around, even in TSQL a trigger was always declared such as...

CREATE TRIGGER trigger_name

ON <schema_name, sysname, Sales>

So DML triggers have always been thought of as a table-level entity. I believe it is this manner of thinking that is the reason there is no obvious way to extract the affected tables name, because the creators of DML triggers assume you will know the table name. So what is my answer, that to meet your auditing requirements with a DML trigger you must make it specific per Target.

I have thought up some "off the wall" solutions before for similiar tasks which usually end up involving heavy tsql usage, information schemas, and system table queries but to be honest if you have to go to this extent its probably not a good idea in the first place :)

At the least do this:

TSQL DML Trigger:

Create Trigger dbo.testtrig
On test
For Insert
As
Begin
Insert LogTable
Select I.*, 'testTable' As [Table] From inserted I
End

|||

Really then, in review, the anser is No it cannot be done.

Why? Because you cannot create one trigger for multiple tables. Thus this violates your whole intention which was to have one object for all auditing purposes.

probably not the answer you were hoping for, but I hope this helps,

Derek

|||You could have the same core function that is called by a wrapper. Having a wrapper for each table and being attached the relevant table.|||

I created a generic AuditTrigger in C# that is not Table Specific. And about half way down it has a way to retireve the TableName using SQL. ;-)

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class Triggers

{

//A Generic Trigger for Insert, Update and Delete Actions on any Table

[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()

{

SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context

string TName; //Where we store the Altered Table's Name

string User; //Where we will store the Database Username

DataRow iRow; //DataRow to hold the inserted values

DataRow dRow; //DataRow to how the deleted/overwritten values

DataRow aRow; //Audit DataRow to build our Audit entry with

string PKString; //Will temporarily store the Primary Key Column Names and Values here

using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection

{

conn.Open();//Open the Connection

//Build the AuditAdapter and Mathcing Table

SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM TestTableAudit WHERE 1=0", conn);

DataTable AuditTable = new DataTable();

AuditAdapter.FillSchema(AuditTable, SchemaType.Source);

SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert Command for us

//Get the inserted values

SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);

DataTable inserted = new DataTable();

Loader.Fill(inserted);

//Get the deleted and/or overwritten values

Loader.SelectCommand.CommandText = "SELECT * from DELETED";

DataTable deleted = new DataTable();

Loader.Fill(deleted);

//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)

SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'", conn);

TName = cmd.ExecuteScalar().ToString();

//Retrieve the UserName of the current Database User

SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);

User = curUserCommand.ExecuteScalar().ToString();

//Adapted the following command from a T-SQL audit trigger by Nigel Rivett

//http://www.nigelrivett.net/AuditTrailTrigger.html

SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@."SELECT c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = '" + TName + @."'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);

DataTable PKTable = new DataTable();

PKTableAdapter.Fill(PKTable);

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table

{

case TriggerAction.Update:

iRow = inserted.Rows[0];//Get the inserted values in row form

dRow = deleted.Rows[0];//Get the overwritten values in row form

PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string

foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns

{

if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "U";//U for Update

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry

}

}

break;

case TriggerAction.Insert:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "I";//I for Insert

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = null;

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

case TriggerAction.Delete:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "D";//D for Delete

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = null;

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

default:

//Do Nothing

break;

}

AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable

conn.Close(); //Close the Connection

}

}

//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values

//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"

public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)

{

string temp = String.Empty;

foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed

{

temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString()].ToString(), ">,"));

}

return temp;

}

}

Hope this helps. Enjoy!!!!!!!

|||

Man! I really thought you had it with:

SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'"

This works great unless the darn tables have cascade delete on them. If cascade delete is on, you'll get the last table in the delete chain. Shoot!!!

Any other ideas?

|||Chris,

have you actually tried to deploy the trigger m_shane_tx posted? As the trigger doesn't have a target, the deployment will fail (at least it does it for me). I.e AFAIK when you create a DML trigger, you have to have a target, so you can not hav a generic trigger for all tables.

Niels
|||

I'm not sure of the difference between your deployment process and mine, but I can deploy that trigger just fine using Visual Studio (without a target). Maybe you have extra constraint for triggers on your server or something, but for me that trigger works as is.

In answer to ckimmel, I am not sure why it doesn't work as is. Since the cascade should cause the trigger to fire on the next table which should do what you want.

Does the trigger not fire at each level of your cascading delete?

|||

On further thought I better understand what is most likely happening for ckimmel the transaction is probably locking multiple tables at once ( i.e all tables the cascading delete touches). So "SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'" is really going to return a list of tablenames and the ExexcuteScalar call only shows you 1 of them. You need to further constrain the 'where' clause which I am not sure is even possible. Do a google search for sys.dm_trans_locks and see what of value you can filter by to get a single value returned each time the trigger fires.

Like I said though I am not sure it can be done.

MShaneHorn

|||

nielsb try following the deployment process for the VB based generic audit trigger at the following address. http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

This was the original article that I got my inspiration from for the C# trigger I wrote. I then modified it to automatically retrieve the table name instead of requiring a specific table naming structure.

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

|||OK, I misunderstood what you were doing. I thought you somehow manged to create one generic trigger in the database, without associating the trigger with a specific table. Having read the article and the following paragraph:

<<<<<<<<<<<<<<<<<<
Now associate the CLR trigger routine with the "ADDRESS" table. With the generic trigger, this is all the code you'll need to audit a table (you can stick this into your standard template for table creation):
>>>>>>>>>>>>>>>>>

and the following code-snippet:

<<<<<<<<<<<<<<<<<<
create trigger Audit_ADDRESS
on ADDRESS for insert, update, delete
as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon
>>>>>>>>>>>>>>>>>>

I see that you actually are associating the trigger with table(s).

Niels

Getting the name of the updated table

I am writing a generic trigger in VS 2005 that selects records from the inserted table, and updates an audit table. I am, however, unable to retrieve the name of the table that the insert occurred on. I am using the following code to select the records, and obtain the name.. Can anyone offer any alternatives to accomplishing this task? Thanks in advnace for any help you can provide.

Craig

SqlDataAdapter tableLoader = new SqlDataAdapter("SELECT * FROM inserted", connection);

DataTable insertedTable = new DataTable();

tableLoader.Fill(insertedTable);

string insertedTableName = insertedTable.TableName;

I don't know the answer, however I would strongly suggest that you would be better off scripting a trigger for each table that did the auditing. Having that level of data access in your CLR trigger is likely to perform worse than a pure TSQL trigger.

This is not a definitive statement just a word of warning.

|||

Thanks for the tip... I didn't think the performance would be that much worse. We were trying to create an auditing solution generic enough to handle all auditing, rather than writing a trigger for each table.

Thanks, again, for the response!

Craig

|||

I believe you can do this with the eventdata() function. You may need to do some XQuery to get the specific value you want because this will return an XML document describing the event. I think it's a good idea to have this one trigger to catch all your audited updates. Simple single object to manage. Simple = good!

|||

EVENTDATA returns data only when referenced directly inside of a DDL trigger.

The requirements here are for Insert Actions, thus its a DML trigger not DDL.

Actually, this is not an easy question, but I believe the answer lies in the fact that DML triggers are table specific objects for this reason. What do I mean by this? Consider this...

[Microsoft.SqlServer.Server.SqlTrigger(Name = "tri_InsertAudit", Target = "Test", Event = "FOR INSERT")]

The target attribute can only accept 1 table name (to my knowledge). And even before CLR triggers were around, even in TSQL a trigger was always declared such as...

CREATE TRIGGER trigger_name

ON <schema_name, sysname, Sales>

So DML triggers have always been thought of as a table-level entity. I believe it is this manner of thinking that is the reason there is no obvious way to extract the affected tables name, because the creators of DML triggers assume you will know the table name. So what is my answer, that to meet your auditing requirements with a DML trigger you must make it specific per Target.

I have thought up some "off the wall" solutions before for similiar tasks which usually end up involving heavy tsql usage, information schemas, and system table queries but to be honest if you have to go to this extent its probably not a good idea in the first place :)

At the least do this:

TSQL DML Trigger:

Create Trigger dbo.testtrig
On test
For Insert
As
Begin
Insert LogTable
Select I.*, 'testTable' As [Table] From inserted I
End

|||

Really then, in review, the anser is No it cannot be done.

Why? Because you cannot create one trigger for multiple tables. Thus this violates your whole intention which was to have one object for all auditing purposes.

probably not the answer you were hoping for, but I hope this helps,

Derek

|||You could have the same core function that is called by a wrapper. Having a wrapper for each table and being attached the relevant table.|||

I created a generic AuditTrigger in C# that is not Table Specific. And about half way down it has a way to retireve the TableName using SQL. ;-)

using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

public partial class Triggers

{

//A Generic Trigger for Insert, Update and Delete Actions on any Table

[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]

public static void AuditTrigger()

{

SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context

string TName; //Where we store the Altered Table's Name

string User; //Where we will store the Database Username

DataRow iRow; //DataRow to hold the inserted values

DataRow dRow; //DataRow to how the deleted/overwritten values

DataRow aRow; //Audit DataRow to build our Audit entry with

string PKString; //Will temporarily store the Primary Key Column Names and Values here

using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection

{

conn.Open();//Open the Connection

//Build the AuditAdapter and Mathcing Table

SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM TestTableAudit WHERE 1=0", conn);

DataTable AuditTable = new DataTable();

AuditAdapter.FillSchema(AuditTable, SchemaType.Source);

SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert Command for us

//Get the inserted values

SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);

DataTable inserted = new DataTable();

Loader.Fill(inserted);

//Get the deleted and/or overwritten values

Loader.SelectCommand.CommandText = "SELECT * from DELETED";

DataTable deleted = new DataTable();

Loader.Fill(deleted);

//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)

SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'", conn);

TName = cmd.ExecuteScalar().ToString();

//Retrieve the UserName of the current Database User

SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);

User = curUserCommand.ExecuteScalar().ToString();

//Adapted the following command from a T-SQL audit trigger by Nigel Rivett

//http://www.nigelrivett.net/AuditTrailTrigger.html

SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@."SELECT c.COLUMN_NAME

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = '" + TName + @."'

and CONSTRAINT_TYPE = 'PRIMARY KEY'

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);

DataTable PKTable = new DataTable();

PKTableAdapter.Fill(PKTable);

switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table

{

case TriggerAction.Update:

iRow = inserted.Rows[0];//Get the inserted values in row form

dRow = deleted.Rows[0];//Get the overwritten values in row form

PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string

foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns

{

if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "U";//U for Update

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry

}

}

break;

case TriggerAction.Insert:

iRow = inserted.Rows[0];

PKString = PKStringBuilder(PKTable, iRow);

foreach (DataColumn column in inserted.Columns)

{

//Build an Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "I";//I for Insert

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = null;

aRow["NewValue"] = iRow[column.Ordinal].ToString();

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

case TriggerAction.Delete:

dRow = deleted.Rows[0];

PKString = PKStringBuilder(PKTable, dRow);

foreach (DataColumn column in inserted.Columns)

{

//Build and Audit Entry

aRow = AuditTable.NewRow();

aRow["ActionType"] = "D";//D for Delete

aRow["TableName"] = TName;

aRow["PK"] = PKString;

aRow["FieldName"] = column.ColumnName;

aRow["OldValue"] = dRow[column.Ordinal].ToString();

aRow["NewValue"] = null;

aRow["ChangeDateTime"] = DateTime.Now.ToString();

aRow["ChangedBy"] = User;

AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry

}

break;

default:

//Do Nothing

break;

}

AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable

conn.Close(); //Close the Connection

}

}

//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values

//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"

public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)

{

string temp = String.Empty;

foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed

{

temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString()].ToString(), ">,"));

}

return temp;

}

}

Hope this helps. Enjoy!!!!!!!

|||

Man! I really thought you had it with:

SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'"

This works great unless the darn tables have cascade delete on them. If cascade delete is on, you'll get the last table in the delete chain. Shoot!!!

Any other ideas?

|||Chris,

have you actually tried to deploy the trigger m_shane_tx posted? As the trigger doesn't have a target, the deployment will fail (at least it does it for me). I.e AFAIK when you create a DML trigger, you have to have a target, so you can not hav a generic trigger for all tables.

Niels
|||

I'm not sure of the difference between your deployment process and mine, but I can deploy that trigger just fine using Visual Studio (without a target). Maybe you have extra constraint for triggers on your server or something, but for me that trigger works as is.

In answer to ckimmel, I am not sure why it doesn't work as is. Since the cascade should cause the trigger to fire on the next table which should do what you want.

Does the trigger not fire at each level of your cascading delete?

|||

On further thought I better understand what is most likely happening for ckimmel the transaction is probably locking multiple tables at once ( i.e all tables the cascading delete touches). So "SELECT object_name(resource_associated_entity_id) FROM sys.dm_tran_locks WHERE request_session_id = @.@.spid and resource_type = 'OBJECT'" is really going to return a list of tablenames and the ExexcuteScalar call only shows you 1 of them. You need to further constrain the 'where' clause which I am not sure is even possible. Do a google search for sys.dm_trans_locks and see what of value you can filter by to get a single value returned each time the trigger fires.

Like I said though I am not sure it can be done.

MShaneHorn

|||

nielsb try following the deployment process for the VB based generic audit trigger at the following address. http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

This was the original article that I got my inspiration from for the C# trigger I wrote. I then modified it to automatically retrieve the table name instead of requiring a specific table naming structure.

Trying using the TSQL they use to deploy the VB trigger to deploy my modified C# version.

mshanehorn

|||OK, I misunderstood what you were doing. I thought you somehow manged to create one generic trigger in the database, without associating the trigger with a specific table. Having read the article and the following paragraph:

<<<<<<<<<<<<<<<<<<
Now associate the CLR trigger routine with the "ADDRESS" table. With the generic trigger, this is all the code you'll need to audit a table (you can stick this into your standard template for table creation):
>>>>>>>>>>>>>>>>>

and the following code-snippet:

<<<<<<<<<<<<<<<<<<
create trigger Audit_ADDRESS
on ADDRESS for insert, update, delete
as external name [AuditCommon].[AuditCommon.Triggers].AuditCommon
>>>>>>>>>>>>>>>>>>

I see that you actually are associating the trigger with table(s).

Niels