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

SQL Server Admin 2014 :: Server Level Trigger To Log Activity And Rollback After Logging Information

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

SQL Server 2014 :: Capture THROW For Logging

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

SQL Server 2014 :: Pivot IN Clause - Dynamic Columns

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

SQL Server Admin 2014 :: Execute Dynamic Query 1 As A Transaction

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

SQL Server 2014 :: Trigger A Report By Sending Email

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

SQL Server Admin 2014 :: Create Dynamic Columns In Temp Table?

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

SQL Server 2014 :: Synchronize Table On Remote Server Via Update Trigger Failing

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

SQL Server 2014 :: How To Call Dynamic Query Stored Procedure In Select Statement

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

SQL Server Admin 2014 :: INSERT Failure After DDL Trigger Creation

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

SQL Server Admin 2014 :: Master Table With Insert Trigger

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

Dynamic Logging Properties In MS SQL DTS Packages

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

Logging Dynamic SQL Variable When EvaluateAsExpression=TRUE

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

Master Data Services :: Unable To Get Trace Logging To Work In MDS 2014

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

Logging To Event Viewer Fails But SQL Server Logging Works OK - Why?

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

SQL Server 2014 :: Transaction Rollback When Multiple Threads Are Inserting Records Into Table Because Of Trigger

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

SQL Server Admin 2014 :: Logon Trigger Executing Multiple Times For Single Connection?

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

SQL Server 2014 :: Trigger On A View If Any Insert / Update Occurs On Base Table Level

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

Logging Batch Updates Without Using A Trigger Or A Cursor

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

SQL Server 2012 :: In Trigger - Building Dynamic Table With Inserted Data

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

Dynamic Trigger

May 10, 2008

How to create a dynamic trigger for a dynamic table?

View 3 Replies View Related

Dynamic If Update() In Trigger - Urgent!

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

Using Dynamic SQL With Trigger And Stored Procedures

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

Logging Package Name For Any Event In Sysdtslog90 Logging Table

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

Proc With Dynamic Query Hang When Insert Trigger Fires....

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

Tie Together Custom Logging And SSIS Logging?

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

Reporting Services :: Way To Subscribe SSRS Report Using Dynamic Parameters For Email And Trigger Report

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

Logging Server

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

EL 3.1 And Logging To Sql Server 2005

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

Troubles With Logging On SQL Server

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

Problem With Logging Into SQL Server Via LAN

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

SQL Server Job Agent Logging !!

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

Logging Into Linked Server

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







Copyrights 2005-15 www.BigResource.com, All rights reserved