SQL Server 2014 :: Dynamic Trigger Logging
Oct 13, 2015
I would like to have a dynamic trigger on INSERT, UPDATE and DELETE. (each their own trigger).
What i want, is the trigger to log what has happened with the record. For the insert trigger, i would like to select all columns and put them in 1 column to my logging table. For the update trigger, i need the same, however for both of the old and new items.
For example:
CREATE TABLE [dbo].[TRIGGER_TEST](
[COLUMNA] [int] IDENTITY(1,1) NOT NULL,
[COLUMNB] [nvarchar](50) NOT NULL,
[COLUMNC] [nvarchar](20) NULL,
[COLUMND] [date] NULL,
[COLUME] [decimal](2, 2) NULL,
[Code] ....
When I insert an item in TRIGGER_TEST, i would like to see what is inserted in the TRIGGER_LOGGING table.
INSERT INTO [dbo].[TRIGGER_TEST]
([COLUMNB]
,[COLUMNC]
,[COLUMND]
,[COLUME])
VALUES
('test'
,'hello'
,getdate()
,0.1)
GO
I would like to see in my TRIGGER_LOGGING table in NEW:
COLUMNB='test'|COLUMNC='hello'|COLUMND='2015-10-13 16:04'|COLUME=0,10
View 0 Replies
ADVERTISEMENT
Sep 8, 2015
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
View 8 Replies
View Related
Aug 14, 2015
Is it possible to capture the contents of THROW within a CATCH into a variable so that it can be written to a logging table?
I can capture ERROR_MESSAGE() to a variable but unfortunately it only writes the last error rather than the previous one that is informational.
I realize that THROW can be multiple lines so it may not be possible to do through TSQL but thought I should ask.
View 2 Replies
View Related
May 12, 2015
The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.
Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;
Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
adding or moving ')' or '(' are not working.......
select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value
select * from #temp order by .........
versus
select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value
select * from #temp order by .....
View 3 Replies
View Related
Feb 7, 2015
I have this command :
Exec 'update .... insert ..... delete ..... insert ...'
I Execute these command in one execution.
exec ('...')
Are these commands act as a transaction? If one of them create error , another commands run or rull backed?
View 1 Replies
View Related
Oct 18, 2015
I have a report that gets a Customer_Number parameter and sends a mail with that customer's data.I want my users to be able to get this report's results by sending an email to a certain email address with a customer number in the topic.
View 1 Replies
View Related
Jun 9, 2014
I want to generate dynamic temp table so, from one strored procedure am getting an some feilds as shown below
CM_id,CM_Name,[Transaction_Month],[Transaction_Year],''[Invoice raised date],''[Payment Received date],''[Payout date],''[Payroll lock date]
for i want to generate table for the above feilds with datatype
View 5 Replies
View Related
Jul 21, 2015
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
[Code] ....
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
View 3 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Jan 6, 2014
We have created a DDL trigger on SQL server 2005 database for DB audit purpose. Following is the script used for trigger creation
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ChangeLog](
[Code] ....
After the DDL trigger creation. Application team started reporting following error while executing a stored procedure.
*********************************
Error 1:
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
Error2:
[Execute SQL Task] Error: Executing the query "exec sp_drop_indexes_EnhLeaseData delete from dbo.leases where vin_num='XXX' and lease_acct_num='XXXX' delete from dbo.leases where vin_num='XXX' and lease_acct_num='080066225' " failed with the following error: "INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
View 1 Replies
View Related
Feb 4, 2015
I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..
Is there anyway to capture the record either in the master table or in a separate table.
View 6 Replies
View Related
Feb 7, 2007
Since I can't seem tofind the Microsoft SQL 2000 forum, I will post this here:
I currently have logging enable on several of my packages.
However, we are still in development of our packages and are reaching upwards
of 100 and logging will eventually need to be active on all of them. In
production, there will still be a development server and a production server,
both with different server names and user id/pwd.
I am looking for a way to dynamically change the logon information for the
logging so that we do not have to have someone go through and manually change
the options. I have tried using Dynamic Properties Task, but this only works on
the 2nd run of the package.
-----
As a second question: can anyone explain to me why the errordescription field
in sysdtssteplog is cut short?
View 1 Replies
View Related
Oct 24, 2006
I am trying to use the idea as mentioned by Jamie at:
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx
which is to build dynamic SQL using a variable evaluated as an expression.
Set Expression="SELECT * FROM MyTable WHERE MyColumn = " + @[VariableContainingFilterValue]
Everything works fine. The entire package works.
My next step is to log the variable so that I know, after package execution, exactly what SQL statement the package is executing.
I tried to do it by a couple of ways in a Script task:
1) Dts.Events.FireInformation(0, String.Empty, String.Format("SQL: {0}", Dts.Variables("SourceSQL").Expression), String.Empty, 0, False)
Gives me just an expression without actually evaluating it
2) Dts.Events.FireInformation(0, String.Empty, String.Format("SQL: {0}", Dts.Variables("SourceSQL").Value), String.Empty, 0, False)
Produces an error:
The expression for variable €œSourceSQL€? failed evaluation. There was an error in the expression.
Regards,
Yitzhak
View 7 Replies
View Related
Dec 2, 2015
I'm trying to get the application logging to work, but nothing is happening. I'm using SQLServer2014.
I've followed the instructions on [URL] .... , even copying and pasting the snippet into web.config, but nothing happens, no log is created in the default directory.
Is there something version specific? Does 2014 behave differently?
View 2 Replies
View Related
Jun 18, 2007
Greetings,
I am developing a package on my local workstation. I have defined two logging service providers. One is for SQL Server and the other is for the Windows Event Log. I am using the Dts.Log method in a script task to write log entries.
Logging is working properly with the SQL Server provider and rows are being inserted into the sysdtslog90 table. However, the only events that are being logged in the Windows Event Log are the package start and end events which I believe SSIS is doing automatically anyway.
Is there something I need to do to enable WIndows Event Log logging other than defining a log provider and making sure it is checked active? Won't SSIS write to two different logs with one Dts.Log call? Any ideas on what might be going wrong with my approach?
Thanks,
BCB
View 3 Replies
View Related
Jun 18, 2014
I have two tables called ECASE and PROJECT
In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .
When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.
When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.
I need to prevent the deadlock .
View 3 Replies
View Related
Jan 30, 2015
I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
DECLARE @Body NVARCHAR(2000),
[code]....
View 0 Replies
View Related
Apr 21, 2015
I have a situation where I have Table A, Table B.
View C is created by joining table A and table B.
I have written a instead of trigger D on view C.
I do not insert/update/delete on the view directly.
For every insert/update in table A /B the values should get insert/update in the view respectively. This insert/update on view should invoke the trigger.
And I am unable to see this trigger work on the view if any insert/update occurs on base table level.
Trigger is working only if any operation is done directly on the view.
View 2 Replies
View Related
May 5, 2008
Hi,
Does anyone know if there's a way to log batch updates done using SQL queries without using a trigger or a cursor?
Thanks in advance,
Vinod
View 6 Replies
View Related
Nov 4, 2015
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.
Declare @NewID varchar(50) = Replace(convert(Varchar(50),NEWID()),'-','')
Declare @SQLStr varchar(8000)
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from inserted'
Exec (@SQLStr)
I get the following error: Invalid object name 'inserted'
I know I can do:
Select * into #inserted from inserted
Set @SQLStr= 'Select * into [TMPIns' + @newID + '] from #inserted'
Exec (@SQLStr)
But I don't want to use TempDB as these tables can become big and I also feel that it is redundant. Is there a way to avoid the creation of #inserted?
View 2 Replies
View Related
May 10, 2008
How to create a dynamic trigger for a dynamic table?
View 3 Replies
View Related
Jul 23, 2005
Hi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
View 1 Replies
View Related
Feb 13, 2008
Hi!
I have an integration code write in T-SQL. It´s a TRIGGER that when some data is INSERTED on a specific table, verify the first caracter of a nvarchar on the column named "idCli", and depending on their value, call one specific stored procedure that will execute some data modifications to fit on other table on a diferent database.
Each client of mine can have only one table that start the trigger on APP1, but can have many instances of SQL for different codes.
Until now, what we do is:
Find how many different databases (and their names) a specific client have to APP2 and write a specific stored procedure for each database, using the names (that are always different...). We use a template of course, but this don't change the fact the we must correct many times the database name on the different stored procedures.
This increse the time and chance of errors on installing the system.
The first way we think for solve this question is using dinamic sql, like this code:
Code Snippet
CREATE TRIGGER T01
ON [dbo].[table1]
FOR INSERT, UPDATE
AS
-- some code that put values in @v1 and @V2...
IF @v1 = 1
EXEC fct ('DB1..Tabela1', @V2)
ELSE
EXEC fct ('DB2..Tabela1', @V2)
GO
CREATE PROCEDURE fct (@table_name nvarchar(50), @valor int)
AS
EXEC ('INSERT INTO '+@table_name+' (valor) VALUES ('+@valor+')')
GO
This type of code has the advantege (we think) to permit us change only the TRIGGER, and use always the same number of procedures on install.
Is there any security problem to do this type of code?
Even if the @table_name and @valor are determined by the program?
In case of yes, how can I do something like this, or, if this is not possible, how can I "automate" the creation of the procedures with a variable number of choices (like 2 different tables for client A, 5 for client B, etc)?
Thanks in advance
View 8 Replies
View Related
Oct 17, 2007
Hi,
I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes.
I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging.
How do I configure the package to always log the package information into the table, too?
Best regards,
Stefoon
View 5 Replies
View Related
Feb 16, 2005
Need to parsing serverName and databaseName to run a dynamic query to get serverName and databaseName and employee_ID via a accountID parameter.
-----------------------------
declare @stringSQL varchar(200)
select @stringSQL=
'insert into temp1 select '+@AccountID+' accountID, employee_ID from ' + @serverName +'.dbo.'+@databaseName+'.tblEmployee where inactive=0'
print @stringSQL_GetUserName
exec (@stringSQL_GetUserName)
select * from temp1
------------------------------
above dynamic query works fine.
Howevery, this should be run only under insertion event. When I put it in a proc to run within the insertion trigger or put the whole sql statement within the trigger:
1. when ran at a MSDE server
MSDTC on server is unavailable.
2. when ran at a SQL2000 developer testing server with the distributed transaction coordinator on, the insertion a record in the isql/w hang there. Could not even to kill this query, and have to stop and restart the SQL server.
Then I just want to return the dynamic query result without 'insert into temp1 ', the result is still hang...
Is there a way to let the insert trigger to run a dyanamic query which linked to around 10 servers?
thanks
David
View 4 Replies
View Related
Sep 12, 2005
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View 4 Replies
View Related
Feb 8, 2015
Is there a way to subscribe SSRS report using dynamic parameters for email and trigger the report from autosys job so that report should generate the exact time the job is triggered.Let me describe, my SSRS report should be triggered on success of one autosys job. i need to send email parameter and time of report schedule from this autosys job.
View 3 Replies
View Related
Apr 24, 2007
Hi Guys hope someone can help with this one, I am working on SQL 2000 and having problem with logging a new server instance to be run in query analyzer let me explain,
I have the default sql server and then also an instance called sqlone on the same machine for study reasons.
However after loading the new instance on and then registering it in enterprise manager it appears fine on there, but I am having problem only in query analyzer the sqlone instance does not appear in the drop down menu so that I can select too work on that server
Only reason I need this is as I am practicing adding linked servers and without the second instance being visible it won't work correctly.
For other infomation the instance ok in the enterprise manager, server manager and then in the computer management in the administrative tools in the control panel.
Any advice please people
Thanks
Neil G
View 2 Replies
View Related
Nov 21, 2007
How do I set up EL to log information to a sql server 2005 DB? I have created the the Database Trace Listener through the configuration utility, but do I need to run some script on the DB to create the tables, sprocs etc? I can't find any script though...
Thanks
View 1 Replies
View Related
Jan 23, 2005
I've got a problem here logging on mssql server
It seems to me that the problem is in ConnectionString
When I add new connection to the project via Microsoft OLEDB provider for SQL Server
I use login 'sa' and password 'pwd'. Then if I check 'Allow saving password' (or something like that - i use other language) everything works fine, but if i don't at last I get an error message
Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'sa'.
Source Error:
...
Line 47: Me.SqlCommand1.Connection.Open()
...
Can I use connection string with no explicit inclusion of password?
TIA
View 2 Replies
View Related
Jan 25, 2008
Hi Guys;
I have written a software which tries to connect a sql server in LAN. Here is the connection string:
Data Source= 192.168.2.2,1433; Initial Catalog='mastercoiffeur';Network Library=DBMSSOCN;User Id=MBCA;Password='123456'
I can't figure out why I cant connect to the server? What do you recommend to me to do?
Thanks for help...
Extra Information:
I have created a login called "MBCA" and password is "123456". Server works as windows authentication and SQL server.
View 7 Replies
View Related
Apr 21, 2008
Hi pals,
I am using sql server 2005.
Is there any means to check/see the log if i add a new job category has been added or deleted .
I just want to know this for Auditing purpose.
i just want to keep a track what new job categories have been deleted or new Job categories have been added on so and so dates.
Hint:
-----
Management Studio-->Jobs-->Right Click-->Manage Job Categories-->Add/Delete.
Any Thoughts????
View 3 Replies
View Related
May 15, 2006
After creating a linked server to a remote server, I needed to log in usingsp_addlinkedsrvlogin to get my stored procedure to work. However, I noticedthat after stopping SQL Server and the DTC and then restarting both, that mystored procedure worked without having to execute sp_addlinkedsrvlogin.Is the log-in information stored in the machine, such that if SQL Server isstopped or the server is rebooted, on does not have to executesp_addlinkedsrvlogin again? Or is there a point at which one would have tore-log-in to a linked server?Thanks.
View 6 Replies
View Related