Is it possible to create a stored procedure which will create a text file (containing information from some tables) and send it via e-mail to a list of user.
I know that I will have to configure the SQL Mail.
If it is possible, can someone give me sample code.
I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.
I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:myprog20060913 and move my processed file there.
Hello,I need a permanent synchronization between the "Active Directory" and the UserTable in my WebApp.I think a good solution for my interface can I realize with the "Stored Procedures" in SQL Server 2005. Is this the right way?I can create a Stored Procedure and a AD-Reader separately, that's no problem. But how I can import the "System.DirectoryServices"-Assembly in my Stored Procedure?Or is there an other solution?Thanks a lot!NussyStar*
Is there a way to create a text file (such as a Windows Notepad file)by using a trigger on a table? What I want to do is to send a row ofinformation to a table where the table: tblFileData has only onecolumn: txtOutputI want to use the DB front end (MS Access) to send the text string tothe SQL backend, then have the SQL Server create a file to a path,such as F:/myfiledate.txt that holds the text in txtOutput, then thetrigger deletes the row in tblFileData.Can this be done easily?Any help is appreciated
I am situation, where we have a table named as Project, columns for the table as follows:
Code: ------------------------------------------ ID | ClientCode | ProjectName ------------------------------------------ 1 | AAA | Dubai Airport Phase I 2 | AAA | Dubai Airport Phase II 3 | ARC | Salala 4 | MIZ | UMBC Building ------------------------------------------
Now my task was, whenever a project name and other details being created, then a Folder will be created in a server itself in the path E:ProjectFolder in following way:
You can see here Folder and sub-folder is being created with that following project - client code & ID
I used following trigger to do the same:
Code: CREATE TRIGGER [dbo].[CreateFolderName] ON [dbo].[Project] after INSERT AS SET NOCOUNT ON BEGIN declare @chkdirectory as nvarchar(4000), @folderName varchar(100), @mainfolderName varchar(100) declare @folder_exists as int SET @mainfolderName = (SELECT ClientCode AS Project FROM INSERTED) SET @folderName = (SELECT (ClientCode + cast(ID as varchar(10))) AS Project FROM INSERTED) set @chkdirectory = 'E:ProjectFolder' + @mainfolderName + '' + @folderName
[code].....
This worked like a charm, now my next task is using same trigger, I have to create a BAT file inside that SubFolder - T-SQL for creation of BAT File as follows:
Code: DECLARE @FileName varchar(50), @bcpCommand varchar(2000) SET @FileName = REPLACE('E:ProjectFolder[red](select ClientCode from INSERTED)[/red][red](select ClientCode + cast(ID as varchar(10)) from INSERTED)[/red]xcopy_'+ (SELECT cast(ID as varchar(10)) FROM INSERTED) +'.bat','/','-') SET @bcpCommand = 'bcp "[red]SELECT 'xcopy "E:ProjectFolder' + clientCode + '" "10.0.0.35ProjectFolder" /T /E /I' FROM INSERTED[/red]" queryout "' SET @bcpCommand = @bcpCommand + @FileName + '" -U SQLServerUsername -P SQLServerPassword -c' EXEC master..xp_cmdshell @bcpCommand
Here I am not understanding how to insert the above T-SQL in the Trigger as well as the above T-SQL is not right, what's wrong in this?
Last query that will be included in the trigger is to execute the newly created bat file.
I am trying to create a trigger that fires after a user logon and logoff and does the following:
creates a new user deletes data from old temp table
Then I need to create a stored procedure that executes dynamically to drop old users remove the old user account access
We are running SQL Server 2000. Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?
HiThis problem involves 2 columns in my Product table - ReleaseDate(varchar) and ReleaseClass(varchar)When a new product is Entered, the current date is inserted into ReleaseDate & ReleaseClass = NewWhen product is a month old, I want ReleaseClass to = RecentWhen product is a month 3 mths old, I want ReleaseClass to = NormalWhen the product is a month old, is there a method to automatically run a Stored procedure say to change the ReleaseClass field??any code or links on how i go about this would be appreciated + i've never used a triggerCheers!!
Hi, Please help me to find this answer. We know Trigger are a type of stored procedure,and can be activated whether by a insert ,update or delete event of a table. We also know that stored procedure are quick due to their execution plan which are already stored in the memory once complied. But what about triggers? what is the mechanism of triggers?How they work? And how fast they are from Tsql queries?Is there any mechanism to calculate or measure the efficiency of triggers? Please comment if anybody knows the answers. Thanks!! Joydeep
Hello there, I searched for answers to the above topic, but could not find what I want. My stored procedures and triggers are returning a message based on the result, mostly error messages. How can I get that message using ASP.Net? Should I use an output parameter? Thank you for your help.
I have a trigger that checks if a particular field in an application is being messed with. I am only allowing users to update this field when it is empty. So, I am not allowing them to change the value stored in the field (they will receive an email notification) because a backend operation is going on that could screw things up.
Aside from that, I want the application to refresh the page when the email goes out to go back and display the original contents of the field.
Now, I know this obviously cannot be done from a trigger since it is only dealing with the database. But, does someone have a quick and dirty way of accomplishing this?
Persons Table PersonID int NOT NULL PRIMARY KEY PersonFatherID int NULL FOREIGN KEY Persons(PersonID) PersonMotherID int NULL FOREIGN KEY Persons(PersonID) PersonGeneration int NULL PersonFirstName nchar(20) NOT NULL PersonLastName nchar(20) NOT NULL
Spouses Table SpouseID int NOT NULL PRIMARY KEY HusbandID int NOT NULL FOREIGN KEY Persons(PersonID) WifeID int NOT NULL FOREIGN KEY Persons(PersonID)
Persons Table Data PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName 1 1 1 1 Adam Smith 2 2 2 1 Evelyn Smith 3 1 2 2 Caleb Smith 4 NULL NULL 0 Sara Jones
I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration column (0) with Caleb's PersonGeneration (2) in Persons table. So, the rule is if PersonFatherID=NULL and PersonMotherID=NULL and PersonGeneration=0, then update this person's PersonGeneration with his/her spouse's PersonGeneration. I am thinking about a trigger or a stored procedure or both. Any help would be greately appreciated.
I have a requirement to audit tables in a SQL Server database. The tables are dynamically created when the application creates a form and the table holds the form data. So my plan is this, I have worked out the audit table (static) and the trigger. What i'm having issues with is getting the trigger to create from within the stored procedure. So just to recap: the user creates a form in the app, this creates a table and should call this stored procedure. The stored procedure creates the trigger on that table (which begins auditing that table, inserting to the static audit table based on the table name being passed into the stored procedure).
Where im at: I can create the stored procedure. When i go to run the stored procedure, I get the errors after passing the table as a value.
In my opinion it's an error with the correct number of single ticks, but not sure.
The Code: USE [AdventureWorks] GO /****** Object: StoredProcedure [dbo].[spReplaceAuditTrigger] Script Date: 12/06/2011 15:28:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROC spReplaceAuditTrigger( @PassedTableName as NVarchar(255) ) AS
I have a problem calling stored procedure in trigger..
When no exception occures stored procedure returns the value but if any exception occures executing that stored procedure then stored procedure will not return any value..
I have handled exception by returning values in case if any..
Here is the stored procedure
CREATE PROCEDURE BidAllDestinations ( @ITSPID int, @DestinationID int, @BidAmount decimal (18,4), @BidTime datetime, @intErrorCode int out )
AS DECLARE @GatewayID int DECLARE @GatewayExist int SET @GatewayID = 0 SET @GatewayExist = 0 SET @intErrorCode = 0
UPDATE BID FOR CORRESPONDING GATEWAY DECLARE GatewayList CURSOR FOR
SELECT Gateways.GatewayID FROM Gateways INNER JOIN GatewayDestinations ON Gateways.GatewayID = GatewayDestinations.GatewayID INNER JOIN ITSPs ON Gateways.ITSPID = ITSPs.ITSPID Where Gateways.ITSPID = @ITSPID AND DestinationID = @DestinationID
OPEN GatewayList
FETCH NEXT FROM GatewayList INTO @GatewayID
IF (@GatewayID = 0)
SET @intErrorCode = 1 ELSE BEGIN -- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH WHILE @@FETCH_STATUS = 0
UPDATE TerminationBids SET BidAmount = @BidAmount, BidTime = getdate()
WHERE GatewayID = @Gatewayid AND DestinationID = @DestinationID
ELSE
INSERT INTO TerminationBids (GatewayID, DestinationID, BidAmount) VALUES (@GatewayID,@DestinationID,@BidAmount)
IF @@ERROR <> 0 BEGIN GOTO PROBLEM CLOSE GatewayList DEALLOCATE GatewayList END
FETCH NEXT FROM GatewayList INTO @GatewayID
END CLOSE GatewayList DEALLOCATE GatewayList
END PROBLEM: BEGIN
SET @intErrorCode = 100
END RETURN @intErrorCode GO
TRIGGER CODE:::
CREATE TRIGGER TR_TerminationBid ON dbo.TerminatorBidHistory FOR INSERT
AS
DECLARE @ITSPID int DECLARE @DestinationID int DECLARE @BidAmount decimal (18,4) DECLARE @BidTime datetime DECLARE @intErrorCode INT DECLARE @DistinationList varchar (8000) DECLARE @DestinationLevel varchar (100) SET @intErrorCode = 0 SET @ITSPID = 0 SET @DistinationList = '' -- CHECK ITPSID' S VALIDITY
SELECT@ITSPID = i.ITSPID, @DestinationID= i.DestinationID, @BidAmount = i.BidAmount, @BidTime = i.BidTime FROM Inserted i INNER JOIN ITSPS ON ITSPS.ITSPID = i.ITSPID INNER JOIN Destinations ON Destinations.DestinationID = i.DestinationID
EXEC BidAllDestinations @ITSPID,@DestinationID,@BidAmount,@BidTime, @intErrorCode = @intErrorCode output SELECT @intErrorCode Following should return value for @intErrorCode if any exception occures
I'm trying to come up with a Stored Procedure or a Trigger to Sum up monthly =Expenses to the YTDExpenses Column. Can Someone help please.
I have a Monthlyexpense column. How do I Sum up this column and put the Total in my ytdexpenses column. Do I use a stored procedure, because I want the monthlyExpenses to SUm up every time I submit a monthly expense to the database and siplay in the ytdExpenses Column. When I Write a Query all of the rows in the ytdExpenses shows the same amount and do not total up every time I submit to the database. Help please.
Hi AllI need opinions on how to approach my task.I currently have 3 tables: the master table, the archive and a temptable.MASTER: has 3 fields ProductID and ProductNo and ReleasedARCHIVE: Has 3 ProductID, ProductNo, SoldDateTEMP: ProductID, ProductNo, SoldDateI have a trigger on the master table upon deletion to archive. This istriggered from a seperate routine from a vb app to delete a recordreal time.CREATE Trigger Archive_Proc On dbo.MASTERFor DeleteAsDeclare @iDate As DateTimeSet @iDate = GetDate()If @@RowCount = 0 Returnset Nocount onInsert Into ARCHIVE(ProductID, ProductNo, SoldDate)Select ProductID, ProductNo, @iDate from deletedMy problem is that I have a temp table that gets filled from aseperate transaction.It needs to be matched against the master tablethen deleted at both master and temp. but the issue is that the temptable contains its own SoldDate value that needs to be archived.Q 1: if I use a stored proc. how do i pass the SoldDate value to thetrigger as Triggers dont use GVs.Q 2: How do I set up the stored procedure to delete with multipletables. I can get it to UPDATE but not delete....CREATE PROCEDURE COMPARESOLD@Pool SmallintASSet NoCount onUpdate MASTERSet Released = 2From TEMP, MASTERWhere TEMP.ProductNo = MASTER.ProductNoAND TEMP.ProductID = MASTER.ProductIDAND INVENTORY.Released = 1hopefully someone can lead me to the right direction...Thanks
I am trying to test a simple trigger on insert and it does not work when I call EXEC sp_send_cdosysmail. However, the stored procedures does work if I right-click on it and select Execute Stored Procedure.
Below is a simple version of the trigger I am trying to implement. I know it works in SQL Server 2000 and 2005 but can't seem to get it to work in SQL Server 2005 Express. Any help is greatly appreciated!
ALTER TRIGGER [dbo].[trig_Tableinsert] ON [dbo].[Table] FOR INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
Print 'Hello'
-- Insert statements for trigger here EXEC sp_send_cdosysmail some@one.com', 'notify@me.com','New Insert', 'test'
Persons Table PersonID int NOT NULL PRIMARY KEY PersonFatherID int NULL FOREIGN KEY Persons(PersonID) PersonMotherID int NULL FOREIGN KEY Persons(PersonID) PersonGeneration int NULL PersonFirstName nchar(20) NOT NULL PersonLastName nchar(20) NOT NULL
Spouses Table SpouseID int NOT NULL PRIMARY KEY HusbandID int NOT NULL FOREIGN KEY Persons(PersonID) WifeID int NOT NULL FOREIGN KEY Persons(PersonID)
Persons Table Data PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName 1 1 1 1 Adam Smith 2 2 2 1 Evelyn Smith 3 1 2 2 Caleb Smith 4 NULL NULL 0 Sara Jones
I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration column (0) with Caleb's PersonGeneration (2) in Persons table. So, the rule is if PersonFatherID=NULL and PersonMotherID=NULL and PersonGeneration=0, then update this person's PersonGeneration with his/her spouse's PersonGeneration. I am thinking about a trigger or a stored procedure or both. Any help would be greately appreciated.
We have a requirement in the project to do auditing for some of the tables. We have come across different approaches of implementing auditing.
The following are the approaches:
Create a generic stored procedure to do auditing. Call this procedure whenever any tables that require auditing have insert/update/delete operation performed on it. Auditing and the DML operation should be part of a single transaction. Create a generic CLR trigger to do the auditing. The CLR trigger can be attached to the tables that require auditing. Create separate audit tables and triggers for each table which require auditing.
I would like to know whether there are any disadvantages of using triggers in production server. Could anyone please help me in identifying the best approach for implementing auditing?
Will there be any situation when the DML statements execute and the corresponding trigger fails?
Is there any performance degradation on using triggers for auditing compared to including the auditing logic implemented in the stored procedures? We have been advised not to use triggers in production environment. But we are not clear about the reason for this.
PLEASE NOTE THAT I DO NOT WANT TO REPLACE STORED PROCEDURE WITH TRIGGERS TO IMPLEMENT BUSINESS LOGIC. BUT IN THE SCENARIO THAT I DISCUSSED, I NEED TO CAPTURE THE LOG INFORMATION WHENEVE A DML STATMENT IS EXECUTED AGAINST THE TABLES. WHAT IS THE PREFERRED APPROACH HERE...USING TRIGGERS OR STORED PROCEDURES?
In general, is there any disadvantage in using triggers for auditing? Has anyone faced any issues with triggers? (Triggers not invoked during DML operations or any performance related issues) Please let us know.
