I am a VoIP phone system using SQL on the back end. I am trying to get a Trigger to fire and email me when a certain number has been dialed.
Create Trigger trg_Emergency_Calls on dbo.CallDetailRecord for Insert
IF @@ROWCOUNT=0 RETURN ---NO rows affected exit proc
IF (SELECT finalcalledpartynumber FROM inserted)='95593684' BEGIN
--RAISERROR ('Call Stored Procedure Here',16,10)
EXEC WEB_SRVR03.master.dbo.sp_SMTPMail @body='This is a test Email'
END
Return
GO
The problem is I have to execute the actually email SP is on another server and has to be that way. The trigger actually runs each time but if the IF statement becomes true then the trigger hangs and never completes. Watching the other server(WEB_SRVR03) there is never a request to execute the sp_SMTPMail. I have been trying to troubleshoot this with profiler but I never see any locks or anything that would give me a problem. Also the insert statement that caused the trigger to fire also never finishes and so the record isn't written to the db. If anyone has any suggestions I would appreciate it.
Thanks
I am having a problem importing data into SQL 7 from any type of source. I go through the whole import process no problem. When I click the finish button to start the import, nothing at all happens. Enterprise Manager and the DTS just hang and I must use crtl+alt+delete to end the program. Can anyone give me any suggestions as to what might be happening. Big Thanks in advance, I've been working on this for days.
I want to finsih the execution of a DTS package from an ActiveX task. If a condition is ok, the package would continue as normal. If not, I want to finish the package without any error. Just without executing the next tasks. Do you have any idea?
I have a Stored Procedure which Ideally should run when a Customer Logs in, the Procedure will check the available stock and create a Temp Table for the Information, which allows many other Queries in the site to run a lot faster, (due to no joins). The Query has taken as much as 30seconds (Lots of Records and 1/2 dozen Joins) to run upon log in and causing a Timeout for the web application.
I want the procedure to run as it is, but for the login method to not be dependent on the Process tried this in .NET cmd.BeginExecuteNonQuery() (cmd=SQLCOmmand) which doesn't do what I want it just allows me to run heaps of QUeries at the same time.
Can anyone help me with getting this procedure to run and not hold up the Web application? Not sure whether I need to do this in .NET, or whether I can get .NET to run a Batch File or something, but someone must have had a similiar problem, please help.
I have a scheduled job to run daily at 4 am. the job imports data from client side from text files and puts data in our sql table.It takes around 1 hour. the problem is it doesnt stops after the import process completes.so after one hour i can see the data is imported into my sql table so thats fine but the job keeps running. I tried observing that job's spid in activity monitor in sql 2005 but after one hour i cant even see that spid but still job runs. its weird.and after that when i stop the job manually then it stops saying job completed succcessfully. that step is a last step and it uses windows cmd.my understanding is the job step doesnt understands that it got finished. what should i do in here?? any ideas r appreciated we are running the same job for another servers and its fine.
No Error but Export to Excel does not finish When the report has 2 pages with total 500 rows exporting to Excel is not a problem. If it has 100 pages 5000 rows exporting to excel does not end and it does not return any error but the process does not end either. What might the problem be?
I have a database A include five tables, and have more than 1,500,000 rows. There is a replica database of A. First of all, there is no data in the two dbs. When I finish inserting data into A, the replica db seems still work, the log file size still changes. How can I know the replication finished or not? How long it will take to finish replicate 1,500,000 rows of data?
I have been working with this for about a month now, and no similar problems to date. Today I am trying to introduce 4 configuration flags that control whether optional ETL stage feeds are executed. I did this by adding a do-nothing script component. The precedent and constraint is used, and it checks the boolean variable flag. The first package executes fine. But it never returns from there. This precedent has nothing fancy on it either. It simply does not run any more of the package, make any more conditional checks, nor the common completion tasks. It just seems to think it is done.
The optionals all fire execute package tasks. One thing that might be tripping it up is that I attempt to run one package twice, each time with varying parent package variable set to control it to use a different destination database for each run. Should this not be OK to do?
Here's my dilema, I want to run a stored procedure that starts another stored procedure running, but does not wait for the stored procedure to complete execution.
The stored procedure should execute immediately, and leave the other procedure to complete running in the background. Is there any way to do this?
We recently upgraded out SQL version from SQL2008R2 to SQL2014. As such, the compatibility mode changed to SQL2104 (120).
We have several queries that used to run fine that now take forever to bring back results. There are no errors (which surprised me). They just take way too long now. PLus they seem to be causing high I/O and CPU.
If I change the compt level back to SQL2008 - these queries run fine.
QUERY with SQL2008 compt level - finished in 2 minutes. QUERY with SQL2014 compt level - finishes in 3 hours 22 minutes.
same exact query - same server - only thing changed was compatibility level.
WHat do I look for in the queries that could be causing this? (they look fine but obviously I'm missing something here)..
I've setup the option to mail the error to a person. When the option is on I get the error message by mail but the package does not finish (eg. the failing task does not become red and the output windows never says anything about the error) - if I set the option to off the task fails as expected.
I have this query I need for a report. Originally it was 4 queries to be used in Crystal Reports. Now I want to create the same report with SSRS and therefore I incorporated all queries in one in order not to use subreports [URL].....
Tempdb fills up to nearly 90 GB. I am running SQL Server on a local box, so I am sure there is no other traffic. Here is the query:
SELECT AdHaupt.NSprache_ID ,AdHaupt.mengentext AS mengentextHaupt ,AdHaupt.Einzelpreis ,AdHaupt.Anzeigebezeichnung ,AdHaupt.Gesamtpreis
[Code] ...
I ran it with TOP 10 as well, just to see if it will finish at all, but it never did (ran for an hour now).
I'm running my SSIS packages from a scheduler (WindowsService) that I wrote in .Net. For the logging of the SSIS events I only use te SSIS Log provider for SQL Server. Nevertheless, the package run start and stop events (and execution failures) are still logged in the Windows Event Log. Is there a way to stop SSIS from writing this event-log entries?
Hi all, There is nothing happen when I finished my checkout process, I expect the data will be saved to order and orderitem table in my SQL database, but no data found on order and orderitem table and no error messages display during operation!!! Below is my checkout.aspx.vb code, the whole code line number around 138, I captured the part from 1~ 64 line number, I suspect line 35 - 48 have a problem, can somebody help me, many thanks. 1 Imports System 2 Imports System.Data.SqlClient 3 Imports SW.Commerce 4 Partial Class CheckOut 5 Inherits System.Web.UI.Page 6 Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 7 If Not Page.IsPostBack Then 8 If Profile.Cart Is Nothing Then 9 NoCartlabel.Visible = True 10 Wizard1.Visible = False 11 End If 12 If User.Identity.IsAuthenticated Then 13 Wizard1.ActiveStepIndex = 1 14 Else 15 Wizard1.ActiveStepIndex = 0 16 End If 17 End If 18 End Sub 19 Sub chkUseProfileAddress_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) 20 21 ' fill the delivery address from the profile, but only if it’s empty 22 ' we don’t want to overwrite the values 23 24 If chkUseProfileAddress.Checked AndAlso txtName.Text.Trim() = "" Then 25 txtName.Text = Profile.Name 26 txtAddress.Text = Profile.Address 27 txtcity.Text = Profile.City 28 txtCountry.Text = Profile.Country 29 End If 30 End Sub 31 Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) 32 33 ' Insert the order and order lines into the database 34 35 Dim conn As SqlConnection = Nothing 36 Dim trans As SqlTransaction = Nothing 37 Dim cmd As SqlCommand 38 Try 39 conn = New SqlConnection(ConfigurationManager.ConnectionStrings("swshop").connectionstring) 40 conn.Open() 41 trans = conn.BeginTransaction 42 cmd = New SqlCommand() 43 cmd.Connection = conn 44 cmd.Transaction = trans 45 46 ' set the order details 47 48 cmd.CommandText = "INSERT INTO Order(MemberName, OrderDate, Name, Address1, Address2, Country, Total) VALUES (@MemberName, @OrderDate, @Name, @Address, @city, @Country, @Total)" 49 cmd.Parameters.Add("@MemberName", Data.SqlDbType.VarChar, 50) 50 cmd.Parameters.Add("@OrderDate", Data.SqlDbType.DateTime) 51 cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar, 50) 52 cmd.Parameters.Add("@Address", Data.SqlDbType.VarChar, 255) 53 cmd.Parameters.Add("@City", Data.SqlDbType.VarChar, 15) 54 cmd.Parameters.Add("@Country", Data.SqlDbType.VarChar, 50) 55 cmd.Parameters.Add("@Total", Data.SqlDbType.Money) 56 cmd.Parameters("@MemberName").Value = User.Identity.Name 57 cmd.Parameters("@OrderDate").Value = DateTime.Now() 58 cmd.Parameters("@Name").Value = CType(Wizard1.FindControl("txtName"), TextBox).Text 59 cmd.Parameters("@Address").Value = CType(Wizard1.FindControl("txtAddress"), TextBox).Text 60 cmd.Parameters("@City").Value = CType(Wizard1.FindControl("txtCity"), TextBox).Text 61 cmd.Parameters("@Country").Value = CType(Wizard1.FindControl("txtCountry"), TextBox).Text 62 cmd.Parameters("@Total").Value = Profile.Cart.Total 63 Dim OrderID As Integer 64 OrderID = Convert.ToInt32(cmd.ExecuteScalar())
SQLServer2005_SSMSEE in my notebook with vista business and nearly the end of installation the process is cancelled by an error. its necessary install some application before? SQL Server Express is installed by default in the notebook.
thaks a lot and excuse for my bad english (bettr more for me is SPANISH)
i searched and all i found is questions, not answers.
maybe it's a silly question, but i really can't find any documention / posts about this.
i have a scheduled job in sqlagent that executes a SSIS package that runs every minute. As a result, my application log in eventviewer gets filled very quickly.
i tried using "/REPORTING E" option with no luck.
i tried enabling logging on the package, and then select only the OnErro Event, no luck.
I'm setting up a Maintenance Plan to do a backup for me in the night of my SQL Databases but before the backup starts I have created a SQL Server Agent Job that runs a DOS batch (.bat) file using CmdExec. The problem is I need this process to finish processing before the rest of the tasks run in the Maintenance Plan. It seems like SQL Server Agent Job Task tells the batch file to start running (and it runs fine) but it just continues to the next task and does not wait for it to finish.
Is there any way I can configure this to wait before proceeding?
I was wondering if anyone knew of a way to disable the following, SQLISPackage start/finish events sent to windows event logs everytime a SSIS package is executed and completed.
This isn€™t an problem as such, it€™s more of a debate.
If a table needs a number of update triggers which do differing tasks, should these triggers be separated out or encapsulated into one all encompassing trigger. Speaking in terms of performance, it doesn€™t make much of an improvement doing either depending upon the tasks performed. I was wondering in terms of maintenance and best practice etc. My view is that if the triggers do totally differing tasks they should be a trigger each on their own.
I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.
What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.
There is anyway to make a "while statament" to wait until my exe application finishes?
Ive built an SSIS package which generates a file from a legacy system and then downloads the file into a designated folder on the server. I need the file watcher task to wait for a the file to completely finish loading before it says it is complete. Currently, as soon as the file is created, the WMI step finishes.
Is it possible to achieve this using triggers:When someone tries to delete a row in table A, the trigger should first delete a corresponding row in table B and then delete the row in table A. The reason being that, there is a foreign key set on Table B that references table A. So any attempt to delete a row in table A without deleting the corresponding row from B, throws an error.
Hello All, I have to write Trigger for Update, I have two tables, one is for storing records of current values, and one is for storing history of values. How to Write a Trigger on Main Table. As we have Inserted and Deleted Tables through which we can find Values, We dont have any Table for UPDATED Values. Help me. General Problem
I need to create a set of rows every time a new row is inserted into a table. Example (I think this would work)... select @insertedId = column1 from insertedselect @id = column1 from table1 where column2 in (select column1 from table2 where column2 = @insertedId)insert into table3 values(x, y, @id) Is it possible to do the same kind of thing in a situation where the select statement returns multiple values and execute the insert statement for each of these values? Also, if table3 was in fact the table on which the trigger acts, would it then be executed for every row created by the trigger? Sorry if I sound confused. I am.
hi everybody..i tried to put thios loop in sql server 2000 But it is not taking The @ action taken value ,,it is only taking the default value of @actiontaken value. SET @ActionTaken = 'A' IF (@AType = 'A')IF @Status= 'O' IF (@KAppInd ='Y' AND @DAppInd=null)BEGINSET @ActionTaken = 'O'END
Please tell me other option in sql server 2000 for setting variable value based on conditions
Hi using triggers i try to insert some values in to my 2 tables: But its showing teh error as "The request for procedure 'Triginsert123s' failed because 'Triginsert123s' is a trigger object." This is my code in back end: sqlcon.Open() Dim cmd As New SqlCommand("Triginsert123s '" & txtID.Text & "','" & txtName.Text & "','" & txtRole.Text & "','" & txtDep.Text & "'", sqlcon) cmd.ExecuteNonQuery() sqlcon.Close() My trigger is: CREATE TRIGGER Triginsert123s ON [dbo].[EmpRole] FOR INSERT AS declare @Eid as tinyint, @Ename as varchar(50), @Role as char(10) Insert into Emprole(Eid,Ename,Role) values(@Eid,@Ename,@Role) insert into empdep(eid,dep) values(@eid,@Role) Whats the probs?, Plz i am new to triggers help me,
Hi All, I'm using triggers to handle my transaction log to cature inserts and updates. It works fine except if the user clicks on the Save button more than once, the trigger is fired and the record is written to the log even if the record wasn't changed. Does anyone know how to check if the record was actually changed so that it isn't written to the table if it wasn't?
When I execute a stored proc from my asp.net page, will the results of a trigger be returned to my program?
For instance say my stored proc is:
Update Employees set (Lastname = @Lastname) where ID = @ID
And my trigger is:
CREATE TRIGGER tr_Employees_U on Employees FOR UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR ('cannot change lastname', 16, 1) ROLLBACK TRAN RETURN END GO
It seems like since this is an AFTER trigger that my webpage would actually get a valid return code from my stored procedure however the trigger would rollback those changes correct? Or would the trigger get fired and send it's return code to my webpage?
I'm trying to write an instead of trigger for a view in SqlExpress...the table and views are defined as such:CREATE TABLE [dbo].[Work]( [WorkID] [int] IDENTITY(1,1) Primary Key, [ResourceID] [int] NOT NULL, [TaskID] [int] NOT NULL, [WorkDate] [datetime] NOT NULL, [WorkQuantity] [float] NOT NULL, [IsEstimate] [bit] NOT NULL DEFAULT ((0)), [Project] [int] NOT NULL,);CREATE VIEW [dbo].[ActualWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 0);CREATE VIEW [dbo].[EstimatedWork]ASSELECT WorkID, ResourceID, TaskID, WorkDate, WorkQuantity, ProjectFROM dbo.[Work]WHERE (IsEstimate = 1);Given that, what is wrong with the following create trigger statement:Create Trigger trg_InsertActualWork ondbo.ActualWork Instead of InsertasBEGIN Insert into dbo.Work( ResourceID, TaskID, Project, WorkDate, WorkQuantity, IsEstimate ) values ( inserted.ResourceID, inserted.TaskID, inserted.Project, inserted.WorkDate, inserted.WorkQuantity, 0 );END
l'm trying to build a trigger on a table. The reason for the trigger is to check a certain field for the first three characters if it has ie abc it must update another field in this case loanbook to newabc.How do l write the trigger so that it also check if exists and perform the updates. Please help its Urgent. l've listed the trigger below.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR insert,Update AS
IF left('Loan_No',3)='ABC' update Test_TRG set loanbook = 'NEWABC'
else
IF left('Loan_No',3)='DEF' update Test_TRG set loanbook = 'NEWDEF' where loanbook is null
else
update Test_TRG set loanbook =left('Loan_No',3) where loanbook is null
I'm a bit confused on this bit please elaborate : " FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK ". The PK is on which field? Basically this trigger should ensure that on insertion of o new loan if The left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=MCG and its null then NEWMCG left(loan_no,3)=KVS and its null then NEWKVS left(loan_no,3)=MFS and its null then MFS left(loan_no,3)=TCR and its null then TCR left(loan_no,3)=ABL and its null then ABL
Listed below is what l've tried to do but l'm missing the PK part.Otherwise everything else you explained in the script is clear. Thanks man its urgent. When l parse the query its fine , but When l run it l get an error.
CREATE TRIGGER UpdTest_TRGData ON Test_TRG FOR INSERT, UPDATE AS
UPDATE Test_TRG SET LoanBook = CASE WHEN LEFT( i.Loan_No, 3 ) = 'MCG' THEN 'NewMCG' WHEN LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL THEN 'NewKVS' WHEN LEFT( i.Loan_No, 3 ) = 'MFS' AND i.LoanBook IS NULL THEN 'MFS' WHEN LEFT( i.Loan_No, 3 ) = 'ABL' AND i.LoanBook IS NULL THEN 'ABL'
WHEN i.LoanBook IS NULL THEN LEFT( i.Loan_No, 3 ) END FROM Test_TRG t INNER JOIN inserted i ON t.PK = i.PK -- Fill in Primary Key or other Join Column(s) WHERE LEFT( i.Loan_No, 3 ) = 'MCG' OR ( LEFT( i.Loan_No, 3 ) = 'KVS' AND i.LoanBook IS NULL ) OR i.LoanBook IS NULL
====== Error message =================
Server: Msg 207, Level 16, State 3, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'. Server: Msg 207, Level 16, State 1, Procedure UpdTest_TRGData, Line 11 Invalid column name 'PK'.
