I use the VB.NET transaction to update an sql server 2000 database. I call a number of stored procedures within this transaction.
The stored procedures will update tables. These tables use triggers..
My question is, when does the trigger get called? Is it after the each stored procedure, or is it after the whole transaction?
Jag
I'm trying convert my working CLR Trigger to a Linq-based implementation, but I don't seem to be able to get Linq to cooperate with the Trigger's context transaction (a local SubordinateTransaction with IsolationLevel="ReadCommitted").
For brevity's sake I've boiled down my scenario to a bare minimum: A SQL table [Test] with three columns (int Manual, int Automatic, IDENTITY int ID) needs any external updates on its [Manual] column to be applied to its [Automatic] column (with an additional sign change) through a Trigger.
A fairly direct translation of my non-Linq CLR Trigger to Linq yields the following code (the [Trigger_TestLinQs] SQL table has been modified to point to the Trigger's "INSERTED" table, [TestLinQs] points to the actual SQL table):
Code Snippet[SqlTrigger(Name = "LinQTrigger", Target = "dbo.TestLinQ", Event = "FOR INSERT, UPDATE")]
public static void LinQTrigger()
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
if (triggerContext.TriggerAction == TriggerAction.Insert || triggerContext.TriggerAction == TriggerAction.Update) {
//using (TransactionScope scope = new TransactionScope(Transaction.Current)) { /* err 6549 */ using (SqlConnection connection = new SqlConnection("context connection=true")) {
connection.Open();
using (MyDataContext context = new MyDataContext(connection)) {
List<int[]> updates = new List<int[]>();
foreach (Trigger_TestLinQ test in (from t in context.Trigger_TestLinQs select t)) { /* err 6522 */
if (test.Manual != null && triggerContext.IsUpdatedColumn(0))
updates.Add(new int[] { test.ID, -test.Manual.Value });
}
foreach (int[] update in updates) {
dbo.TestLinQ test = (from t in context.TestLinQs where t.ID == update[0] select t).Single<dbo.TestLinQ>();
test.Automatic = update[1];
}
context.SubmitChanges();
}
}
// scope.Complete(); //}
}
}
Running this on SQL Server 2005 / .NET 3.5 beta2 produces an Error 6522 "Cannot enlist in the transaction because a local transaction is in progress on the connection." in the line executing the Linq query because the Linq DataContext apparently insists on opening a transaction.
The recommended pattern for DataContext query operations - running them inside a TransactionScope as indicated by the commented-out sections - unfortunately leads to a different Error 6549 in the TransactionScope constructor complaining about a missing MSDTC. And there shouldn't be a need for a distributed transaction here, should there?
Using different TransactionScope constructors (TransactionScopeOptions.Required, TransactionOptions(){IsolationLevel=ReadCommitted}) did not make any difference, either.
What am I doing wrong here? Is there any way for the Linq DataContext to attach to the CLR Trigger's ambient transaction?
A server I'm working on has a very unique situation, where user tables and production tables reside on the same database. Users update / create tables or populates these tables, so it can't be a table-specific trigger. However, they give a new meaning to "kamikaze pilots" as it's not uncommon for them to "accidentally" update / insert / delete 500,000,000 + records in a single statement. I've tried educating them to use batching, but to no avail, so now I'm forced to stop these statements BEFORE they execute, based on rowcount, as they fill up the database log so quickly that it goes into recovery mode (It has a 200GB log file - insane, I know).
I recon the mosts transactions allowed should be 1,000,000 records in a single statement. Looking for database trigger to stop them from executing statements with large records?
I'm troubleshooting a performance issue , Looking at Profiler - for the given statement, I'm getting the following figures , why would there be such a disparity between the figures. ? How can I go about finding out why there is such difference?
SQL:Stmt Completed:CPU = 31, Reads = 129 , Duration = 32
SQL:Batch Completed: CPU = 2531, Reads = 6087 , Duration = 2593
Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com
Hi,
Am I right in saying that the insert triggers on a table will not execute if the table is being populated using DTS ? At least, that is what is happening with me. Is DTS logged ? Is there any way to populate the table thru' DTS and still make the triggers work ?
Thanks,
Deepak.
Hi Guys,
I have a requirement to create update,delete triggers in all the columns of each table in my development database. I would really appreciate if anyone could guideme or send me the script.
Thaks a lot for your consideration
Jay
Please help me in sorting out my Problem Providing me the solution .
My Problem is
I have a master table with Primary key on ID field (PatientID-(Patient-Table)) and it is an Identity field
And My child table has the same ID field(PatientID-(PatientDetails-Table)) and it has the relationship set
And the child table has its own Primary key of its own ID field(PatientdetailsID).
What I want is as soon as enter row of data into the master table (Patient-Table)and click save on my front end application(Which is ASP.Net web application)
I want to update Child Table’S (PatientDetails)ID field ( ie.,PaientID in the PatientDetailsTable) in the Child Table which relates the parent table ,by doing so I want to update the Primary key field (ie.,PatientDetailsID) & ForeignKey Field (PatientID)of the child table and to create row in the child table with two columns .(PatientID,&PatientDetailsID)
What I want to achieve is in my ASP.net Application as soon as I enter Master table
I want to Edit Child tables (about 15) one by one like a Wizard pages which will have The ID Field(PatientID) same in all my wizard pages .
I want to know whether I can incorporate triggers if so in which table (is it in Patient or PatientDetails) and I will be grateful If anyone gives the Script to-do so.I am also providing my two table sripts.
Sripts:CREATE TABLE [dbo].[Patient]( [PatientID] [int] IDENTITY(1,1) NOT NULL, [Date] [smalldatetime] NULL, [UserID] [int] NULL, [FirstName] [varchar](40) NOT NULL, [Surname] [varchar](30) NOT NULL, [DOB] [datetime] NULL, [Age] AS (floor(datediff(day,[DOB],getdate())/(365.25))), [Sex] [varchar](10) NULL, [Occupation] [varchar](30) NULL, [Ethinicity] [varchar](60) NULL, [HomeTel] [varchar](15) NULL, [Mobile] [varchar](15) NULL, [varchar](40) NULL, [AddressLine1] [varchar](30) NULL, [Line2] [varchar](30) NULL, [Line3] [varchar](30) NULL, [City] [varchar](20) NULL, [PostCode] [varchar](15) NULL, CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED ( [PatientID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[Patient] WITH CHECK ADD CONSTRAINT [FK_Patient_User] FOREIGN KEY([UserID])REFERENCES [dbo].[User] ([UserID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_User] CREATE TABLE [dbo].[PatientDetails]( [PatientID] [int] NOT NULL, [PatientDetID] [int] IDENTITY(1,1) NOT NULL, [Date] [smalldatetime] NULL, [NHSNumber] [varchar](12) NULL, [HospitalRefID] [varchar](10) NULL, [Ovaries] [varchar](15) NULL, [ReportFromGP] [image] NULL, [LMP] [datetime] NULL, [DateStopped] [datetime] NULL, [Comment] [varchar](150) NULL, CONSTRAINT [PK_PatientDetails_1] PRIMARY KEY CLUSTERED ( [PatientDetID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[PatientDetails] WITH CHECK ADD CONSTRAINT [FK_PatientDetails_Patient] FOREIGN KEY([PatientID])REFERENCES [dbo].[Patient] ([PatientID])GOALTER TABLE [dbo].[PatientDetails] CHECK CONSTRAINT [FK_PatientDetails_Patient]
I want to incorporate this through database level .
I am using SQL Server2005-Express
Although Iam using ASP.net C# I am new and I will not be able to do this in my front end.
Please help me wth the solution.
thanks
rameshs_2000
Returning "completed" when status = 1 and "not completed when status = 0
View 3 Replies View RelatedHi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
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.
www.handleysonline.com
Why do I get the message "Operation can't be completed?" when I try to save a stored procedure!!! When I create a new stored procedure and copy the code into it it works fine!
View 2 Replies View Related
Is there a way to tell if a report actually got sent? I have a report with an email subscription. As a test, I changed the data source credentials to something invalid. Then I ran the subscription using this:
exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='13baba1e-dce0-4d99-a9f5-9c3da02a0615'
The system gave me no indication that the report failed and was never sent by email. Is there a way to tell if a report worked or not?
Is there a way to run a stored procedure at the end of a report with a success or failure flag?
Thanks,
Stu
hello friends,
i am runing stored procedure which runs for around 20 min. as it is filtering data from lacks of record. after completion it shows above message i.e."query completed with errors" with 466814 records affected why this happining as it does not display any errror in message window . Does it is because of size of data . plz guide me how to debug it.
sp is containing 3 cursors
Thanx in advance
can anyone tell me what is the origin of this error "This SqlTransaction has completed; it is no longer usable".
View 2 Replies View RelatedHow can a tell if a tak completed succesfully from a stored procedure?
I have a task which is executed from a stored procedure. The sp_runtask only returns whether the task started successfully. How can I tell if it completed successfully?
Thanks
Hello, everyone:
Does any baby offer command to verify if database backup is completed? In backup wazard, there is an option "Verify backup upon completion" . Is there the SQL command? Thanks.
ZYT
Hello,
I have 2 tables one is filtered and the other not. I am trying to do merge between them and everthing appears to work fine and I reacht he end where it says 'Applied the snapshot and merged 1 data change' but nothing really is changed or updated on either end.
Hello,
I have 2 tables one is filtered and the other not. I am trying to do merge between them and everthing appears to work fine and I reacht he end where it says 'Applied the snapshot and merged 1 data change' but nothing really is changed or updated on either end.
hi, i have a message queue system using sql 2005 service broker.
the code and setup is the same on both dev and live database. but
soon after i restored a live backup to dev. the queue stopped
working on dev, live is ok thou. after some trouble shooting, i
found that the server is not sending the message at all, but it says
"Command(s) completed successfully" without any error messages.
setup:
-----------------------
create message type TestQueryMessage validation = none
create contract TestQueryContract (TestQueryMessage sent by initiator)
create queue TestSenderQueue
create service TestSenderService on queue TestSenderQueue
create queue TestQueueReceiver
create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract)
send message:
-------------------------
declare @conversationhandle uniqueidentifier;
begin dialog @conversationhandle
from service [TestSenderService]
to service 'TestServiceReceiver'
on contract [TestQueryContract]
with encryption = off;
send on conversation @conversationhandle
message type [TestQueryMessage] ('blah blah blah');
result:
----------------------------------
Command(s) completed successfully.
but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages.
please advise. thanks a lot.
I got 2 questions to ask:
1. I choose Windows and SQL authentication during setup. Will this have any impact on my connection string in Dot net?
2. If I want to host my db on my computer, what other protocols do I need to enable? Currently I got shared memory on.
Hi all,
Is it possible to create a trigger after creation of table during reinitialization? if so, how can I do that? Thanks in advance!
I have a SSIS package with the last three tasks in the control flow are stopping the SSAS, then "on success" the second last task is execute a batch file to copy a bunch of files to a remote server using the robocopy command, then "on success" the last task is to start the SSAS. I test all three tasks individually and they are all working fine. The problem is in-between the second last task and the last task, the second last task is to execute a batch file and then forward to the last task. The task just moved to the last task once the batch file is executed and it did not wait until the actual robocopy job is completed. Therefore it caused a problem in the robocopy process. Thanks.
View 1 Replies View RelatedI am trying to use the export wizard to export to a MS Access file, using the provide source query option. I get the error below when pasting the query in. The query does run successfully in SSMS, it is a long running query. About 8 minutes to complete.
TITLE: SQL Server Import and Export Wizard
------------------------------
The statement could not be parsed.
------------------------------
ADDITIONAL INFORMATION:
Deferred prepare could not be completed.
Query timeout expired (Microsoft SQL Native Client)
Any ideas?
michael.bliesner@premera.com
I have 2 SQL servers. And in the first one I have added the second SQL as a Link Server. When I run an SQL statement on the linked server I get the following message.
Server: Msg 7202, Level 11, State 1, Line 1Could not find server 'PROD' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.[OLE/DB provider returned message: Deferred prepare could not be completed.]
The SQL statement that I am runnins is
Select * from openquery(PROD,'Select * from PROD.GMS.dbo.qryDispCL')
But when I run only the SQL statement "Select * from PROD.GMS.dbo.qryDispCL" it works perfect. But I need to have the first statement running.
Please help. Your valuable feedback is greatly appriciated.
I execute a script that someone else wrote and I get Query Completed with Errors but there is not an error message.If I highlight and execute parts of the script, it completes successfully.
View 9 Replies View RelatedI have the following:
create table testdata
(
StepName varchar(25)
,StepStatus char(25)
,EntryDate datetime)
insert testdata values ('Step1','Starting',getdate())
insert testdata values ('Step2','Starting',getdate())
insert testdata values ('Step3','Starting',getdate())
insert testdata values ('Step4','Starting',getdate())
insert testdata values ('Step1','Finished',getdate())
insert testdata values ('Step2','Finished',getdate())
insert testdata values ('Step3','Finished',getdate())
I need a query that will basically return Step 4 hasn't finished.
I have tried a few ways but can't get it to work...
I have an existing EE setup that captures all failing queries (see code below). The problem is that I also want to somehow capture RPC_starting so that I can see which parameters are passed in whenever a query fails. Is there a way to somehow capture those two events (error_reported & rpc_starting), but only capture rpc_starting when there is actually an error reported?Or maybe just an event on rpc_starting and somehow filter to only capture when there is an error?
Existing error_reported EE code:
CREATE EVENT SESSION [what_queries_are_failing] ON SERVER ADD EVENT sqlserver.error_reported (
ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.session_id,
package0.collect_system_time, sqlserver.transaction_id, sqlserver.username, sqlserver.client_hostname)
[code]....
Hello,
I am trying to use the Import export wizard to created a package,
using the provide source query option. If i just copy the query from a text file
and try to paste , sql only accepts it partially. so i saved it as a sql file
and then opened it in the window. However, when i click on 'next' or 'parse' , i
get the below error.
TITLE: SQL Server Import and Export Wizard
------------------------------
The statement could not be parsed.
------------------------------
ADDITIONAL INFORMATION:
Deferred
prepare could not be completed.
Query timeout expired (Microsoft SQL Native Client)
The query is pretty big, but it executes successfully in the Management Studion Query Explorer window. I had no problem creating a package using DTS with the same query in Sql 2000. I also tried to migrate the package already existing in Sql 2000, but even though i can migrate it successfully , the package does not execute in Sql 2005. Also i tried other queries which are as big as this one, again the query source window during import/export does not seem to accept large queries??? I depend heavily on large queries for my packages, which i run daily. I have not had any issues with this is sql 2000. Can someone help me with this???
Thanks in advance.
Ram
I would like to add a calculation to provide an annualized Gross Amount value. Â Most solutions I have seen are for annualizing down to the day. Â For instance, if today is the 123rd day of the year, the calculation would be Amount / 123 * 365. Â What I want to do is annualize the amount based only on completed periods. Â Today is 8/26. Â
The last completed quarter ended on 6/30. Â Therefore I want to basically take the sum of Q1 and Q2, divide by two, and multiply by four to get an annualized amount based on completed quarters. Â Eventually I want to do something similar with months.
So here's what I have so far. Â In my DSV my DimDate is actually based on a view. Â The view has a Date column with the actual date, all of the normal fields you would expect in a DimDate table, and two columns that look like this:
CASE
WHEN [Date] < DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
THEN 'Y'
ELSE 'N'
END AS
CompletedQuarter,
[Code] ....
Note that for dates between 1/1 and 3/31 the CompletedQuarterCount will be 0. Â I want any annualized amount to be 0 in that case because I only want to use completed quarters in my calculation.
I added both to my Pay Date dimension (which uses vwDimDate from the DSV) in the cube. Â I have tried the calculation below and in my Excel pivot table I'm getting blanks for the calculated field.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross Amount Annualized by Pay Date Quarter]
AS SUM(
IIF([Pay Date].[Pay Date Completed Quarter Count] > 0,
[Gross Amount] / [Pay Date].[Pay Date Completed Quarter Count] * 4,
0)
),
FORMAT_STRING = "Currency",
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Claim' ;
So, is my calculation correct?
One of our dba's runs a process every night to update the database with a daily data file received from an external source. He was testing on a new SQL Server 2012/Windows 2012 R2 cluster that has an Availability Group. While trying to process INSERTs, the process failed with a error: "could not allocate a new page for database X because of insufficient disk space in filegroup PRIMARY."
The log also contains "Operating System Error 1237 (The operating could not be completed. A retry should be performed) encountered".
However, there is 300 GB free on the data drive (E:) where the .mdf file is located. The SQL Server service account has the "Perform Volume Maintenance Tasks" permission (instant file initialization).
All of the disks are VMware 5.1 or 5.5 VM's and the E: disk has thick/eager zero provisioning.
suddenly I'm getting a connect to server error dialogue box that says...
Cannot connect to serverinstance name
Additional information
The operation could not be completed. (WinMgmt)
...when trying to connect to RS2005 Server in Management Studio. I can reach Reporting Manager thru IE and run my reports.
Is it possible that my setting IIS to basic authentication (and turning off Windows Integrated) might be preventing me from connecting in MS, perhaps because MS has to go thru the RS service and doesnt know what basic auth is?
I'm temporarily unable to set IIS back to Windows Auth cuz the server is being used by users to test reports.
hello
I use SQLServer2000 when i begin a transaction and update set of records in a table
till i don't commit or rollback the transaction nobody can not select
previous values of that records and it must wait until i complete the transaction, i have tested it in oracle but it hasn't this limitation
don't you think it is Sqlserver weakness?
thanks
I have been following the tutorial/blog post HERE to create an annualization (or "run rate") of my Gross Amount measure.  What I want to do now is exclude any period that is not "complete". Â
For example, if today is 9/9 then Q3 is not complete - only Q1 and Q2 are complete. Â And if I'm looking at it monthly then January through August are complete, but September is not.
Cells B5 and D5 look exactly as I expect and want them to be. On row 6 below under each "Gross Annualized" value I have basically just put the formula for what it's actually doing.
What I would like to see in the blue cell is $67,211,697,268 - essentially the most recent annualization for a completed period. The annualization for Q3 is misleading because the quarter has not yet completed. Â There's $16b in Q1 and $17b in Q2, so the measly $78m in Q3 (yellow cell) is dragging the annualization down significantly. Even worse, the Gross Amount for Q4 is being treated as a $0.00, which is further dragging down the "2015 Gross Annualized" amount in blue. In a T-SQL average calculation, for example, I think the green cell would be treated as NULL rather than $0. Â That's kind of the behavior I want.
So I would like to do two things:
Create a calculation (probably just a 0 or 1 flag) that indicates whether the current period is complete or not. Â Again, using 9/9/2015 as an example, Q2 would be complete but Q3 would not be. And August would be complete, but September would not. Make the blue cell show $67,211,697,268 - an annualization based on completed quarters only.
For whatever it's worth here is the current calculation for Gross Annualized.
CREATE MEMBER CURRENTCUBE.[Measures].[Gross YTD]
AS AGGREGATE(
YTD([Pay Date].[Calendar].CurrentMember)
,[Measures].[Gross Amount]),
FORMAT_STRING = "Currency",
VISIBLE = 0;
[Code] .....